1 2 Previous Next 27 Replies Latest reply: Jan 29, 2013 6:50 AM by Purvesh K RSS

    Insert into target table and delete from source

    user9077483
      Hi Experts,

      We have a requirement to archive and purge the tables dynamically based on the control table input.
      For that we have to design a control table to gather the necessary information and passed to generate the queries.

      I have designed the table as below.But in this case I am not able to handle the parent and child relation ship.

      Suppose one table needs to be archived and purged and that table is parent table and it is having 2 child tables,
      so first required data will be inserted into target table and delete from source parent and child tables.
      so before deleting from parent we have to delete data from all 2 child tables.

      Suppose one table needs to be purged and that table is parent table and it is having 5 child tables,
      so before deleting from parent we have to delete data from all 5 child tables.

      To handle this scenario how can I design my control table.

      For archive and purge the query like this.
      INSERT INTO towner_name.ttable_name
      (SELECT * FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30));
      DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);
      for purge the quey is like this.
      DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);
      This is my control table and I have 300 tables list to archive and purge.
      CID  SOWNER_NAME STABLE_NAME       TOWNER_NAME   TTABLE_NAME       CONDITION_COLUMN PERIOD UNIT TYPE
      1     wedb_au    OFFER_HEADER       wedb_au      OFFER_HEADER       LAST_DATE        30     D    A
      1     wedb_sa    OFFER_CUSTOMER     wedb_sa      OFFER_CUSTOMER     LAST_DATE        60     D    A
      1     wedb_au    OFFER_SERVICE                                      LAST_DATE         1     Y    P 
      1     wedb_us    OFFER_CUSTOMER                                     LAST_DATE        90     D    P
      1     wedb_cn    OFFER_CARDS                                        UPDATE_DT        2      Y    P
      2     wedb_au    ORDER_HEAD         wedb_au      ORDER_HEAD         LAST_DATE        120    D    A 
      2     wedb_us    ORDER_CUSTOMER     wedb_us      ORDER_CUSTOMER     LAST_DATE        150    D    A
      2     wedb_sa    ORDER_HEAD         wedb_sa      ORDER_HEAD         CREATION_DT      1      Y    A
      3     wedb_us    DELIVERY_HEAD      wedb_us      DELIVERY_HEAD      UPDATE_DT        50     D    A
      3     wedb_au    DELIVERY_CARDS     wedb_au      DELIVERY_CARDS     UPDATE_DT        200    D    A
      3     wedb_au    DELIVERY_SERVICE   wedb_au      DELIVERY_SERVICE   LAST_DT          100    D    A
      WHERE TYPE=P means insert and delete
      TYPE=A means only delete

      wedb_au.OFFER_HEADER is Parent Table.
      child tables for wedb_au.OFFER_HEADER are wedb_au.OFFER_SERVICE,wedb_au.OFFER_BODY,wedb_au.OFFER_EMAIL,OFFER_TAX.

      wedb_au.OFFER_SERVICE is child table and parent for this table is wedb_au.OFFER_HEADER

      wedb_sa.OFFER_CUSTOMER Stand alone table no relationship

      wedb_us.OFFER_CUSTOMER Stand alone table no relationship

      wedb_cn.OFFER_CARDS is parent table.
      child tables for wedb_cn.OFFER_CARDS are wedb_cn.OFFER_OPTION,wedb_cn.OFFER_SERIES

      wedb_au.ORDER_HEAD is parent table.
      child tables for wedb_au.ORDER_HEAD are wedb_au.ORDER_CUSTOMER,wedb_au.ORDER_SERVICE

      wedb_us.ORDER_CUSTOMER is parent table.
      child tables for wedb_us.ORDER_CUSTOMER are wedb_us.ORDER_TAx,wedb_us.ORDER_SERIES.

      wedb_sa.ORDER_HEAD is stand alone table no relationship.

      wedb_us.DELIVERY_HEAD is parent table
      child tables for wedb_us.DELIVERY_HEAD are wedb_us.DELIVERY_SERVICE,wedb_us.DELIVERY_BODY

      wedb_au.DELIVERY_CARDS is child table and parent for this table is wedb_au.DELIVERY_OPTION

      wedb_au.DELIVERY_SERVICE is child table and parent for this table is wedb_au.DELIVERY_HEAD

      Please help me to design the control table.

      Thanks in advance.
        • 1. Re: Insert into target table and delete from source
          Purvesh K
          From my understanding, I would design the Control Table as below: (Please adjust the Constraints and column width according to your need)

          1. Add a Parent_ID column, which maps the Parent_ID (SEQ_ID) to each child. You may as well create a Foreign Key to set PARENT_ID to NULL, in case of deletion of Parent Row.
          create table ctrl_table
          (
            seq_id          number        primary key,
            s_owner_name    varchar2(30)  not null,
            s_table_name    varchar2(30)  not null,
            t_owner_name    varchar2(30)  not null,
            t_table_name    varchar2(30)  not null,
            condition_column  varchar2(30),
            period            number,
            unit              varchar2(1),
            type              varchar2(1),
            parent_id       number      ----> Add new column that Designates the Parent (Seq_ID); NULL indicates Parent
          );
          
          insert into ctrl_table values (1, 'test', 'test_table', 'scott', 'test_table', 'some_column', 30, 'D', 'P', null);
          insert into ctrl_table values (2, 'test', 'test_table1', 'scott', 'test_table1', 'some_column', 30, 'D', 'P', 1);
          insert into ctrl_table values (3, 'test', 'test_table2', 'scott', 'test_table2', 'some_column', 30, 'D', 'P', 2);
          insert into ctrl_table values (4, 'test', 'test_table3', 'scott', 'test_table3', 'some_column', 30, 'D', 'P', null);
          insert into ctrl_table values (5, 'test', 'test_table4', 'scott', 'test_table4', 'some_column', 30, 'D', 'P', null);
          
          
          /* Query that will retrieve the Child(s) first and then Parent */
          /* This query can be used as a Cursor that will perform the Execute Immediate operation to Insert into target table and then delete */
          select *
            from ctrl_table
            start with seq_id = &parent_seq_id
          connect by prior seq_id = parent_id
          order by level desc;
          • 2. Re: Insert into target table and delete from source
            user9077483
            Thank you very much for your reply.
            As I understood the last column is PARENT_ID in that NULL indicates that the table is parent.
            In that 1(test_table),2(test_table1) are parent tables for the child tables 'test_table1' and 'test_table2.
            Please correct me if my understand is wrong.
            null
            1
            2
            null
            null

            Can you please provide me one sample query to perform Execute Immediate operation to Insert into target table and and delete
            from child tables.

            Can you please provide one sample query to perform delete from parent all child tables.

            Please help me.
            Thanks.
            • 3. Re: Insert into target table and delete from source
              Purvesh K
              user9077483 wrote:
              Thank you very much for your reply.
              As I understood the last column is PARENT_ID in that NULL indicates that the table is parent.
              In that 1(test_table),2(test_table1) are parent tables for the child tables 'test_table1' and 'test_table2.
              Please correct me if my understand is wrong.
              null
              1
              2
              null
              null
              From the data I provided,
              Seq_ID 1 is parent with Seq_ID 2 & 3 as its child. (See the SELECT Query that connect parent with its Child) and returns the Child tables first followed by Parent Table.
              Can you please provide me one sample query to perform Execute Immediate operation to Insert into target table and and delete
              from child tables.

              Can you please provide one sample query to perform delete from parent all child tables.
              I do not have the tables with data at hand, so below is a script that can do the job for you. Being Untested, you will have to test it, if it does your job in entireity and improvise if you need to make any changes.

              This script is written assuming the table structures of Source and Target are same, if not, you will have to accomodate additional logic that fetches the Column Names from both Source and Target (using ALL_TAB_COLUMN with appropriate OWNER i.e. source owner and target owner) and then use the Column Names in Insert Into and Select column list.
              declare
                l_sql       varchar2(32767);
                
                cursor c_cur(l_parent_id number) is
                  select s_owner_name, s_table_name, t_owner_name, t_table_name, condition_column, period, unit, type
                    from ctrl_table
                   start with seq_id = &parent_seq_id
                 connect by prior seq_id = l_parent_id
                   order by level desc;
              begin
                for tabs in c_cur(1) loop     -->1 represents the Parent Table Sequence ID
                  /*Copy data from Table to Destination*/
                  /*Logic to fetch Columns should be used instead of * */
                  l_sql := 'FROM ' || tabs.s_owner_name || '.' || tabs.s_table_name || ' where '
                           || tabs.condition || ' = :1';
                  execute immediate 'INSERT INTO ' || tabs.t_owner_name || '.' || tabs.t_table_name || ' SELECT * ' || l_sql using tabs.period;
                  /*Your Logger procedure to maintain the record movement */
                  write_log(SQL%ROWCOUNT || ' records inserted into ' || tabs.t_owner_name || '.' || tabs.t_table_name || ' from ' || tabs.s_owner_name || '.' || tabs.s_table_name);
                  IF tabs.type = 'P' then
                    /*Remove the data from Source */
                    execute immediate 'DELETE ' || l_sql using tabs.period;
                    /*Your Logger procedure to maintain the record movement */
                    write_log(SQL%ROWCOUNT || ' records Deleted from ' || tabs.s_owner_name || '.' || tabs.s_table_name);
                  END IF;
                end loop;
                
                commit;
                
              exception
                when others then
                  rollback;
                  write_log('Error :: ' || sqlerrm);
                  raise;
              end;
              Edited by: Purvesh K on Jan 3, 2013 12:55 PM
              Added Condition before Delete i.e. when TYPE = 'P' then Delete else Skip.
              • 4. Re: Insert into target table and delete from source
                user9077483
                One more question we have to follow the order while inserting table names in to the ctr_table.
                I.e. first parent table name then the corresponding child names and agin paren table name then corresponding table name.
                Let us say

                test_table5 is parent table and child tables for test_table5 are test_table6 and test_table7.
                test_table8 is parent table and child table is test_table9
                test_table10 is stand alone table that means no relationship with any table.

                I am confusing how to make entry
                How the entry will be in the ctr_table.
                Please insert my info in the ctr_tabble.
                Please help me.
                Thanks.
                • 5. Re: Insert into target table and delete from source
                  Purvesh K
                  user9077483 wrote:
                  One more question we have to follow the order while inserting table names in to the ctr_table.
                  I.e. first parent table name then the corresponding child names and agin paren table name then corresponding table name.
                  Let us say

                  test_table5 is parent table and child tables for test_table5 are test_table6 and test_table7.
                  test_table8 is parent table and child table is test_table9
                  test_table10 is stand alone table that means no relationship with any table.

                  I am confusing how to make entry
                  No. That isn't the way Relational Databases work. The concept you are talking of is Sequential Entry.
                  The Order of Parent and Child would not matter. If, you are creating a FOREIGN KEY, then the Parent must exist before child dawns. If you do not intend to have a relational key, then you can have the child before parent is entered.
                  How the entry will be in the ctr_table.
                  Please insert my info in the ctr_tabble.
                  What have you tried? Post us your best effort.
                  You earlier asked to help with design, which I tried and posted. Now its your turn.

                  Prepare a script of few sample tables, say a parent and a child table.
                  1. Prepare the Create Table scripts, Insert some data.
                  2. For simplicity, create both source and Target tables in same owner.
                  3. Then prepare the Control Table with entries of the Tables.
                  4. Create a Function/Procedure that will implement the logic of Insert followed by Delete or simple Delete (I forgot to mention in my earlier solution)
                  5. Test the function on sample tables.

                  If you can provide us with the above details and the exact error you are facing, it might be possible to help resolve the error and point in right direction.
                  • 6. Re: Insert into target table and delete from source
                    user9077483
                    Thanks you so much for your prompt response.
                    Please help me I am struggling very lot.
                    My requirement is different and you have provided the code is different
                    I am providing my complete requirement very clearly as below.

                    The table names what I have provided in the below control table are needs to be archived or purge same tables list provided by the business.
                    CID  seq_id   SOWNER_NAME STABLE_NAME       TOWNER_NAME   TTABLE_NAME       CONDITION_COLUMN PERIOD UNIT TYPE
                    F     1        wedb_au    OFFER_HEADER       wedb_au      OFFER_HEADER       LAST_DATE        30     D    A
                    F     2        wedb_sa    OFFER_CUSTOMER     wedb_sa      OFFER_CUSTOMER     LAST_DATE        60     D    A
                    F     3        wedb_au    OFFER_SERVICE                                      LAST_DATE         1     Y    P
                    F     4        wedb_us    OFFER_SALES        wedb_us      OFFER_SALES                         90     D    A
                    F     5        wedb_cn    OFFER_CARDS        wedb_sa      OFFER_CARDS        UPDATE_DT        2      Y    A
                    R     6        wedb_au    ORDER_HEAD         wedb_au      ORDER_HEAD         LAST_DT          120    D    A
                    R     7        wedb_us    ORDER_CUSTOMER     wedb_us      ORDER_CUSTOMER     LAST_DATE        150    D    A
                    R     8        wedb_sa    ORDER_HEAD         wedb_sa      ORDER_HEAD         CREATION_DT      1      Y    A
                    L     9        wedb_us    DELIVERY_HEAD      wedb_us      DELIVERY_HEAD      UPDATE_DT        50     D    A
                    L     10       wedb_au    DELIVERY_CARDS     wedb_au      DELIVERY_CARDS     UPDATE_DT        200    D    A
                    L     11       wedb_au    DELIVERY_SERVICE   wedb_au      DELIVERY_SERVICE   LAST_DT          100    D    A
                    If I pass the CID through cursor it has to fectch all parent,child and stand alone(no relationship with any tables) tables for that particular CID.

                    Suppose if I pass the CID as 'F' then the script has to perform insert and delete on the following table.

                    wedb_au.OFFER_HEADER is Parent Table.
                    child tables for wedb_au.OFFER_HEADER are wedb_au.OFFER_SERVICE,wedb_au.OFFER_BODY,wedb_au.OFFER_EMAIL,OFFER_TAX.

                    wedb_au.OFFER_SERVICE is child table and parent for this table is wedb_au.OFFER_HEADER

                    wedb_sa.OFFER_CUSTOMER Stand alone table no relationship any other table

                    wedb_cn.OFFER_CARDS is parent table.
                    child tables for wedb_cn.OFFER_CARDS are wedb_cn.OFFER_OPTION,wedb_cn.OFFER_SERIES

                    wedb_us.OFFER_SALES Stand alone table no relationship with any other table.
                    but to compare date with sysdate to get the 90 days old data.
                    in that case I have to compare data with some other table which is having date column based on some common data.
                    In this case I am comparind data with OFFER_MARKET with same column in both the tables OFFER_ID.

                    The child tables wedb_au.OFFER_BODY,wedb_au.OFFER_EMAIL,OFFER_TAX.wedb_cn.OFFER_OPTION,wedb_cn.OFFER_SERIES are not provided in the list by the business
                    so that I didnt make entry in the control table,these tables also I should include in the control table to handle these scenarios.

                    Please help me on what are the rows and columns I need to append in the control table and the script.

                    Please help me I am struggling very lot.

                    Thanks.
                    • 7. Re: Insert into target table and delete from source
                      Purvesh K
                      I still feel that my script is very much in-line with your requirement.

                      Alright, below is my test case which, I think, matches your requirement. If it does not, point out where it does not.

                      I have simplified the test case by eliminating the condition part.
                      /* Clean Up script */
                      drop table test_table_p;
                      drop table test_table_c;
                      drop table test_table_c1;
                      drop table test_table_c2;
                      drop table t_test_table_p;
                      drop table t_test_table_c;
                      drop table t_test_table_c1;
                      drop table t_test_table_c2;
                      
                      drop table control_table;
                      drop function populate_target;
                      /* Clean up ends */
                      
                      /* Create Test Tables */
                      create table test_table_p (col1 number, col2 varchar2(5));
                      create table test_table_c (col1 number, col2 varchar2(5));
                      create table test_table_c1 (col1 number, col2 varchar2(5));
                      create table test_table_c2 (col1 number, col2 varchar2(5));
                      
                      create table t_test_table_p (col1 number, col2 varchar2(5));
                      create table t_test_table_c (col1 number, col2 varchar2(5));
                      create table t_test_table_c1 (col1 number, col2 varchar2(5));
                      create table t_test_table_c2 (col1 number, col2 varchar2(5));
                      
                      /* Create Control Table, Omitted Condition Columns*/
                      create table control_table
                      (
                        cid             varchar2(1),
                        seq_id          number        primary key,
                        s_owner_name    varchar2(30)  not null,
                        s_table_name    varchar2(30)  not null,
                        t_owner_name    varchar2(30)  not null,
                        t_table_name    varchar2(30)  not null,
                      --  condition_column  varchar2(30),
                      --  period            number,
                      --  unit              varchar2(1),
                        type              varchar2(1),
                        parent_id       number      ----> Add new column that Designates the Parent (Seq_ID); NULL indicates Parent
                      );
                      alter table control_table add constraint fk_parent_seq foreign key (parent_id) references control_table (seq_id) on delete set null;
                      
                      insert into control_table values ('F', 1, 'TEST', 'TEST_TABLE_P', 'TEST', 'T_TEST_TABLE_P', 'P', null);
                      insert into control_table values ('F', 2, 'TEST', 'TEST_TABLE_C', 'TEST', 'T_TEST_TABLE_C', 'P', 1);
                      insert into control_table values ('F', 3, 'TEST', 'TEST_TABLE_C1', 'TEST', 'T_TEST_TABLE_C1', 'P', 1);
                      insert into control_table values ('F', 4, 'TEST', 'TEST_TABLE_C2', 'TEST', 'T_TEST_TABLE_C2', 'A', 1);    --> Notice Type, No Delete should occur
                      
                      insert into test_Table_p values (1, 'A');
                      insert into test_table_p values (2, 'AA');
                      insert into test_Table_p values (3, 'AAA');
                      
                      insert into test_table_c values (1, 'B');
                      insert into test_table_c values (2, 'BB');
                      insert into test_table_c values (3, 'BBB');
                      
                      insert into test_table_c1 values (1, 'C');
                      insert into test_table_c1 values (2, 'CC');
                      insert into test_table_c1 values (3, 'CCC');
                      
                      insert into test_table_c2 values (1, 'D');
                      insert into test_table_c2 values (2, 'DD');
                      insert into test_table_c2 values (3, 'DDD');
                      
                      commit;
                      
                      select count(*)
                        from t_test_table_p;
                      
                      COUNT(*)               
                      ---------------------- 
                      0
                      
                      select count(*)
                        from t_test_table_c;
                      
                      COUNT(*)               
                      ---------------------- 
                      0
                        
                      select count(*)
                        from t_test_table_c1;
                      
                      COUNT(*)               
                      ---------------------- 
                      0
                      
                      select count(*)
                        from t_test_table_c2;
                      
                      COUNT(*)               
                      ---------------------- 
                      0
                      
                      select count(*)
                        from test_table_p;
                      
                      COUNT(*)               
                      ---------------------- 
                      3  
                      
                      select count(*)
                        from test_table_c;
                      
                      COUNT(*)               
                      ---------------------- 
                      3  
                        
                      select count(*)
                        from test_table_c1;
                      
                      COUNT(*)               
                      ---------------------- 
                      3  
                        
                      select count(*)
                        from test_table_c2;
                      
                      COUNT(*)               
                      ---------------------- 
                      3  
                      
                      /* Function to perform activity */
                      create or replace function populate_target
                      (
                        p_cid       in    varchar2
                      )
                      return varchar2
                      is
                        l_sql     varchar2(4000);
                        
                        cursor c_parents is
                          select seq_id, s_owner_name, s_table_name
                            from control_table
                           where cid = p_cid
                             and parent_id is null;
                             
                        cursor c_tables(p_parent_seq number) is
                          select s_owner_name, s_table_name, t_owner_name, t_table_name,-- condition_column, period, unit, 
                                 type
                            from control_table
                           start with seq_id = p_parent_seq
                         connect by prior seq_id = parent_id
                           order by level desc;
                      
                          
                      begin
                      
                        for parents in c_parents loop
                          dbms_output.put_line(' Processing ' || parents.s_owner_name || '.' || parents.s_table_name);
                          for tabs in c_tables(parents.seq_id) loop
                            l_sql := 'FROM ' || tabs.s_owner_name || '.' || tabs.s_table_name;
                            
                            /*Populate the Target Tables*/
                            execute immediate 'INSERT INTO ' || tabs.t_owner_name || '.' || tabs.t_table_name || ' SELECT * ' || l_sql;
                            dbms_output.put_line(SQL%ROWCOUNT || ' records inserted into ' || tabs.t_owner_name || '.' || tabs.t_table_name || ' from ' || tabs.s_owner_name || '.' || tabs.s_table_name);
                            
                            if tabs.type = 'P' then
                              execute immediate 'DELETE ' || l_sql;
                              dbms_output.put_line(SQL%ROWCOUNT || ' records delete from ' || tabs.s_owner_name || '.' || tabs.s_table_name);
                            end if;
                          end loop; --End of Tables Loop;
                          dbms_output.put_line('');
                        end loop;   --End of Parents Loop
                        
                        commit;
                        return 'OK';
                        
                      exception
                        when others then
                          rollback;
                          dbms_output.put_line('Error: ' || sqlerrm);
                          return 'Not OK';
                      end populate_target;
                      
                      /* Execute Function */
                      set serveroutput on;
                      declare
                        l_result  varchar2(4000);
                      begin
                        l_result := populate_target('F');
                        dbms_output.put_line('Result:- ' || l_result);
                      end;
                      
                      anonymous block completed
                       Processing TEST.TEST_TABLE_P
                      3 records inserted into TEST.T_TEST_TABLE_C from TEST.TEST_TABLE_C
                      3 records delete from TEST.TEST_TABLE_C
                      3 records inserted into TEST.T_TEST_TABLE_C2 from TEST.TEST_TABLE_C2
                      3 records inserted into TEST.T_TEST_TABLE_C1 from TEST.TEST_TABLE_C1
                      3 records delete from TEST.TEST_TABLE_C1
                      3 records inserted into TEST.T_TEST_TABLE_P from TEST.TEST_TABLE_P
                      3 records delete from TEST.TEST_TABLE_P
                      
                      
                      /* Verify records */
                      select count(*)
                        from t_test_table_p;
                      
                      COUNT(*)               
                      ---------------------- 
                      3
                      
                      select count(*)
                        from t_test_table_c;
                      
                      COUNT(*)               
                      ---------------------- 
                      3
                        
                      select count(*)
                        from t_test_table_c1;
                      
                      COUNT(*)               
                      ---------------------- 
                      3
                      
                      select count(*)
                        from t_test_table_c2;
                      
                      COUNT(*)               
                      ---------------------- 
                      3
                      
                      select count(*)
                        from test_table_p;
                      
                      COUNT(*)               
                      ---------------------- 
                      0  
                      
                      select count(*)
                        from test_table_c;
                      
                      COUNT(*)               
                      ---------------------- 
                      0  
                        
                      select count(*)
                        from test_table_c1;
                      
                      COUNT(*)               
                      ---------------------- 
                      0  
                        
                      select count(*)
                        from test_table_c2;
                      
                      COUNT(*)               
                      ---------------------- 
                      3
                      • 8. Re: Insert into target table and delete from source
                        user9077483
                        Thank you very much for your effort on this and solution
                        It's working fine.However some of the cases it's not handling

                        I am pointing out where it does not matches my requirement.

                        1)As of now if type='A' then it's only inserting into target .
                        if type='P' then it's only deleting from source.

                        My requirement:
                        In the below control table if TYPE is "A" then insert records into target table and delete from source table.
                        In the below control table if TYPE is "P" then delete records from source table.
                        Please help me on this.

                        2) If the table is stand alone table(no relationship with any other table)
                        that scenario you haven't handled in your script.

                        My requirement:
                        Let us say I have table TEST_TABLE_S, cid is "F", TYPE is "A" it's not having any relationship with other tables.
                        If we do any insertion or deletion on this table no need to do it on any other tables.
                        Please help me how do we make entry for this in the control table and script for this in the function.

                        3) If the table is stand alone table(no relationship with any other table) and not having date column to pass condition
                        that scenario you haven't handled in your script.

                        My requirement:
                        Let us say I have table TEST_TABLE_SS, cid is "F", TYPE is "A" it's not having any relationship with other tables.
                        And it's not having date column to compare date with sysdate to get the 100 days old data.
                        In that case I have to get date column from some other table which is having date column based on some common column in the both the tables.
                        In this case I am comparing data with OFFER_MARKET table with same column OFFER_ID in both the tables to get LAST_DATE column.
                        Please help me how do we make entry for this in the control table and script for this in the function.
                        insert into control_table values ('F', 1, 'TEST', 'TEST_TABLE_P', 'TEST', 'T_TEST_TABLE_P', 'P', null);
                        insert into control_table values ('F', 2, 'TEST', 'TEST_TABLE_C', 'TEST', 'T_TEST_TABLE_C', 'P', 1);
                        insert into control_table values ('F', 3, 'TEST', 'TEST_TABLE_C1', 'TEST', 'T_TEST_TABLE_C1', 'P', 1);
                        insert into control_table values ('F', 4, 'TEST', 'TEST_TABLE_C2', 'TEST', 'T_TEST_TABLE_C2', 'A', 1);
                        I hope for my requirement three we have to introduce additional columns to the control table.

                        Please provide me how to handle this it's a great help to me.
                        Thanks.
                        • 9. Re: Insert into target table and delete from source
                          Purvesh K
                          user9077483 wrote:
                          Thank you very much for your effort on this and solution
                          It's working fine.However some of the cases it's not handling

                          I am pointing out where it does not matches my requirement.

                          1)As of now if type='A' then it's only inserting into target .
                          if type='P' then it's only deleting from source.

                          My requirement:
                          In the below control table if TYPE is "A" then insert records into target table and delete from source table.
                          In the below control table if TYPE is "P" then delete records from source table.
                          Please help me on this.
                          Probably, You are not understanding the code I provided. When the TYPE is P then it is Insert and Delete, when Type is A then Only Insert. I am presuming that your Default operation will be Insert.

                          The script was just to give you a proper direction, further to which you would have to modify it to suit your requirement.

                          Now, to correct the script, I provided, just move the execute immediate for INSERT into condition
                          if tabs.type = 'P' then
                          and the execute immediate for DELETE follows the end of above IF condition. But Delete has to follow Insert as failing to which you cannot retrieve the records that are deleted.
                          2) If the table is stand alone table(no relationship with any other table)
                          that scenario you haven't handled in your script.

                          My requirement:
                          Let us say I have table TEST_TABLE_S, cid is "F", TYPE is "A" it's not having any relationship with other tables.
                          If we do any insertion or deletion on this table no need to do it on any other tables.
                          Please help me how do we make entry for this in the control table and script for this in the function.
                          I believe the, Relationship model of the Control Table shall handle it.
                          Did you try? What error did it give? Or did the script not work?

                          Just because it is not included in the Test case, does not mean it isn't handled.

                          To check, execute the below statements and create the corresponding tables, and then execute the function. (Remember to change the CID according to your requirement)
                          insert into control_table values ('F', 5, 'TEST', 'TEST_TABLE_P1', 'TEST', 'T_TEST_TABLE_P1', 'P', null);
                          insert into control_table values ('F', 6, 'TEST', 'TEST_TABLE_P1', 'TEST', 'T_TEST_TABLE_P1', 'P', null);
                          TO answer your question, For Standalone tables, the Parent_ID will be NULL.
                          3) If the table is stand alone table(no relationship with any other table) and not having date column to pass condition
                          that scenario you haven't handled in your script.

                          My requirement:
                          Let us say I have table TEST_TABLE_SS, cid is "F", TYPE is "A" it's not having any relationship with other tables.
                          And it's not having date column to compare date with sysdate to get the 100 days old data.
                          In that case I have to get date column from some other table which is having date column based on some common column in the both the tables.
                          In this case I am comparing data with OFFER_MARKET table with same column OFFER_ID in both the tables to get LAST_DATE column.
                          Please help me how do we make entry for this in the control table and script for this in the function.
                          You see, I have removed the Condition columns to reduce the complexity of code and give you a direction to progress. Adding those condition columns and improvising the select statement is a trivial job.

                          I find this requirement complex enough to excuse if from the table design. I would rather prefer to hard code such requirements.

                          insert into control_table values ('F', 1, 'TEST', 'TEST_TABLE_P', 'TEST', 'T_TEST_TABLE_P', 'P', null);
                          insert into control_table values ('F', 2, 'TEST', 'TEST_TABLE_C', 'TEST', 'T_TEST_TABLE_C', 'P', 1);
                          insert into control_table values ('F', 3, 'TEST', 'TEST_TABLE_C1', 'TEST', 'T_TEST_TABLE_C1', 'P', 1);
                          insert into control_table values ('F', 4, 'TEST', 'TEST_TABLE_C2', 'TEST', 'T_TEST_TABLE_C2', 'A', 1);
                          I hope for my requirement three we have to introduce additional columns to the control table.

                          Yes, it is completely your choice to increase new columns or to continue with those I provided.

                          Edited by: Purvesh K on Jan 3, 2013 8:48 PM
                          • 10. Re: Insert into target table and delete from source
                            user9077483
                            Thanks you very much for your effort and patience to answer for my multiple questions.
                            • 11. Re: Insert into target table and delete from source
                              Purvesh K
                              user9077483 wrote:
                              Thanks you very much for your effort and patience to answer for my multiple questions.
                              You are welcome!!!

                              If this answers, all your questions, then please mark the Thread as Answered and help keep the Forum clean.
                              • 12. Re: Insert into target table and delete from source
                                user9077483
                                Hi,
                                You have helped to me lot however I have stucked up with passing condition_column dynamically
                                for all child and sub-child tables.

                                My requirement is I want to dynamically insert records into parent,child and sub-child target tables and delete records
                                from source parent,child and sub-child tables which are less than 30 days old.

                                In my case the main parent table(test_table_p5) is having date column(LAST_DT)
                                and remaining child and sub-child's are not having date column so that we have to drive from main parent table.

                                The main parent table,child and sub child tables are having common column
                                based on the common column we have to join to main parent table and get the id's for the
                                records which are less than 30 days old then perform insert and delete.

                                In the control table condition_column contains common column names.

                                I am able to insert and delete from all parent,child and sub-child tables dynamically without date condition.
                                But I am not able to do it with date condition.

                                I am sending the test tables and data.
                                create table test_table_p5 (col1 number primary key, col2 NUMBER,col3 NUMBER,col4 varchar2(5),LAST_DT DATE);
                                
                                create table test_table_c5 (col1 number, col2 NUMBER,col3 varchar2(5), CONSTRAINT fk_P5_col1
                                 FOREIGN KEY (col1) REFERENCES test_table_p5(col1));
                                 
                                create table test_table_c6 (col1 number, col2 NUMBER,col3 varchar2(5), CONSTRAINT fk_P5_col1_2
                                FOREIGN KEY (col1) REFERENCES test_table_p5(col1));
                                
                                create table test_table_cp4 (col1 number,col2 NUMBER primary key, col3 varchar2(5),CONSTRAINT fk_P5_cp4_col1
                                 FOREIGN KEY (col1)
                                 REFERENCES test_table_p5(col1));
                                
                                create table test_table_cp3 (col1 number,col2 NUMBER,col3 NUMBER primary key,col4 varchar2(5),CONSTRAINT fk_cp4_cp3_col2
                                 FOREIGN KEY (col2)
                                 REFERENCES test_table_cp4(col2));
                                
                                create table test_table_c7 (col1 number,col2 NUMBER,col3 NUMBER,col4 varchar2(5),CONSTRAINT fk_cp3_c2_col3
                                 FOREIGN KEY (col3)
                                 REFERENCES test_table_cp3(col3));
                                
                                 create table t_test_table_p5 (col1 number primary key, col2 NUMBER,col3 NUMBER,col4 varchar2(5),LAST_DT DATE);
                                
                                create table t_test_table_c5 (col1 number, col2 NUMBER,col3 varchar2(5), CONSTRAINT fk_t_P5_col1
                                 FOREIGN KEY (col1) REFERENCES test_table_p5(col1));
                                 
                                create table t_test_table_c6 (col1 number, col2 NUMBER,col3 varchar2(5), CONSTRAINT fk_t_P5_col1_2
                                FOREIGN KEY (col1) REFERENCES test_table_p5(col1));
                                
                                create table t_test_table_cp4 (col1 number,col2 NUMBER primary key, col3 varchar2(5),CONSTRAINT fk_t_P5_cp4_col1
                                 FOREIGN KEY (col1)
                                 REFERENCES test_table_p5(col1));
                                
                                create table t_test_table_cp3 (col1 number,col2 NUMBER,col3 NUMBER primary key,col4 varchar2(5),CONSTRAINT fk_t_cp4_cp3_col2
                                 FOREIGN KEY (col2)
                                 REFERENCES test_table_cp4(col2));
                                
                                create table t_test_table_c7 (col1 number,col2 NUMBER,col3 NUMBER,col4 varchar2(5),CONSTRAINT fk_t_cp3_c2_col3
                                 FOREIGN KEY (col3)
                                 REFERENCES test_table_cp3(col3));
                                
                                 create table control_table
                                (
                                  cid             varchar2(1),
                                  seq_id          number        primary key,
                                  s_owner_name    varchar2(30)  not null,
                                  s_table_name    varchar2(30)  not null,
                                  t_owner_name    varchar2(30)  not null,
                                  t_table_name    varchar2(30)  not null,
                                  condition_column  varchar2(30),
                                  period            number,
                                  type              varchar2(1),
                                  parent_id       number      
                                );
                                
                                insert into control_table1 values ('F', 1, 'TEST', 'TEST_TABLE_P5', 'TEST', 'T_TEST_TABLE_P5','LAST_DT',30,'A', null);
                                insert into control_table1 values ('F', 2, 'TEST', 'TEST_TABLE_C5', 'TEST', 'T_TEST_TABLE_C5','COL1',30,'A', 1);
                                insert into control_table1 values ('F', 3, 'TEST', 'TEST_TABLE_C6', 'TEST', 'T_TEST_TABLE_C6', 'COL1',30,'A', 1);
                                insert into control_table1 values ('F', 4, 'TEST', 'TEST_TABLE_CP4', 'TEST', 'T_TEST_TABLE_CP4','COL1',30,'A', 1);
                                insert into control_table1 values ('F', 5, 'TEST', 'TEST_TABLE_CP3', 'TEST', 'T_TEST_TABLE_CP3','COL2',30,'A', 4);
                                insert into control_table1 values ('F', 6, 'TEST', 'TEST_TABLE_C7', 'TEST', 'T_TEST_TABLE_C7','COL3',30,'A', 5);
                                
                                insert into test_table_p5 values (1,10,100,'A','6-JAN-2012');
                                insert into test_table_p5 values (2,20,200,'AA','4-JAN-2012');
                                insert into test_table_p5 values (3,30,300,'AAA','15-NOV-2012');
                                insert into test_table_p5 values (4,40,400,'AAAA','20-NOV-2012');
                                insert into test_table_p5 values (5,50,500,'AAAAA','25-NOV-2012');
                                
                                insert into test_table_c5 values (1,10,'B');
                                insert into test_table_c5 values (2,20,'BB');
                                insert into test_table_c5 values (3,30,'BBB');
                                insert into test_table_c5 values (4,40,'BBBB');
                                insert into test_table_c5 values (5,50,'BBBBB');
                                
                                insert into test_table_c6 values (1,10,'C');
                                insert into test_table_c6 values (2,20,'CC');
                                insert into test_table_c6 values (3,30,'CCC');
                                insert into test_table_c6 values (4,40,'CCCC');
                                insert into test_table_c6 values (5,50,'CCCCC');
                                
                                insert into test_table_cp4 values (1,10,'D');
                                insert into test_table_cp4 values (2,20,'DD');
                                insert into test_table_cp4 values (3,30,'DDD');
                                insert into test_table_cp4 values (4,40,'DDDD');
                                insert into test_table_cp4 values (5,50,'DDDDD');
                                
                                insert into test_table_cp3 values (1,10,100,'E');
                                insert into test_table_cp3 values (2,20,200,'EE');
                                insert into test_table_cp3 values (3,30,300,'EEE');
                                insert into test_table_cp3 values (4,40,400, 'EEEE');
                                insert into test_table_cp3 values (5,50,500,'EEEEE');
                                
                                insert into test_table_c7 values (1,10,100,'F');
                                insert into test_table_c7 values (2,20,200,'FF');
                                insert into test_table_c7 values (3,30,300,'FFF');
                                insert into test_table_c7 values (4,40,400, 'FFFF');
                                insert into test_table_c7 values (5,50,500,'FFFFF');
                                Please help me how to make this dynamic.

                                Thanks.
                                • 13. Re: Insert into target table and delete from source
                                  Purvesh K
                                  user9077483 wrote:
                                  Hi,
                                  You have helped to me lot however I have stucked up with passing condition_column dynamically
                                  for all child and sub-child tables.

                                  My requirement is I want to dynamically insert records into parent,child and sub-child target tables and delete records
                                  from source parent,child and sub-child tables which are less than 30 days old.

                                  In my case the main parent table(test_table_p5) is having date column(LAST_DT)
                                  and remaining child and sub-child's are not having date column so that we have to drive from main parent table.

                                  The main parent table,child and sub child tables are having common column
                                  based on the common column we have to join to main parent table and get the id's for the
                                  records which are less than 30 days old then perform insert and delete.

                                  In the control table condition_column contains common column names.

                                  I am able to insert and delete from all parent,child and sub-child tables dynamically without date condition.
                                  But I am not able to do it with date condition.

                                  I am sending the test tables and data.
                                  I have tried to incorporate as many changes as I could. But the scenario of having sub-childs without having proper columns to compare data, isn't feasible in this structure. At least not in a very easy way.

                                  According to my understanding, it will involve structure similar to a B-Tree parsing, where you visit each Table to check if it has any child. If it has, then push the Table sequence into a Stack and process child; If it does not then, Insert and/or Delete the data.

                                  Given my choice, I will rather prefer to hard code the requirement for tables.

                                  I find few anomalies in your Table structures
                                  create table t_test_table_c5 (col1 number, col2 NUMBER,col3 varchar2(5), CONSTRAINT fk_t_P5_col1
                                   FOREIGN KEY (col1) REFERENCES test_table_p5(col1));
                                  This indicates a Parent Child relationship between t_test_table_c5 and test_table_p5. However, how do you expect the relationship to hold good when you Insert data from Test_Table_P5 to target table and then delete the data. The Foreign Key constraint shall fail in such scenario.

                                  Did you wanted to create the relationship between T_TEST_TABLE_C5 and T_TEST_TABLE_P5?

                                  Below is the code that takes Condition column (Date, extend to other datatypes if you want to) into consideration and perform the job of copying and purging data:
                                  create or replace function populate_target
                                  (
                                    p_cid       in    varchar2
                                  )
                                  return varchar2
                                  is
                                    l_sql                 varchar2(4000);
                                    
                                    cursor c_parents is
                                      select seq_id, s_owner_name, s_table_name, condition_column, period, type
                                        from control_table
                                       where cid = p_cid
                                         and parent_id is null;
                                         
                                    cursor c_tables(p_parent_seq number) is
                                      select parent_id, s_owner_name, s_table_name, t_owner_name, t_table_name, condition_column, period,
                                             type, prior s_owner_name par_owner_name, prior s_table_name par_table_name,
                                             prior condition_column par_condition_column, prior period par_period
                                        from control_table
                                       start with seq_id = p_parent_seq
                                     connect by prior seq_id = parent_id
                                       order by level desc; 
                                      
                                  begin
                                   
                                    for parents in c_parents loop
                                      dbms_output.put_line(' Processing ' || parents.s_owner_name || '.' || parents.s_table_name);
                                      
                                      for tabs in c_tables(parents.seq_id) loop
                                        /*
                                          Disable the Constraints on Target Tables as, they will cause Insert to Fail
                                          since a Bottom-Up approach i.e. First Child, then Parent approach is being followed
                                        */
                                        if tabs.parent_id is not null then
                                          for i in (
                                                    select constraint_name, owner || '.' || table_name tab
                                  --                    into l_constraint_name
                                                      from user_constraints
                                                     where table_name in (tabs.t_table_name)--, tabs.s_table_name)
                                                       and constraint_type = 'R'
                                                   )
                                          loop
                                            execute immediate 'alter table ' || i.tab || ' disable constraint ' || i.constraint_name;
                                          end loop;
                                        end if;      
                                        l_sql := ' SELECT ch.* FROM ' || tabs.s_owner_name || '.' || tabs.s_table_name || ' ch';
                                        
                                        /*
                                            If Condition to Verify if Join with Parent Table is required.
                                            If table being processed is Parent, then there is not need of Join as it already has Date column
                                  
                                            To join the parent and child, I assumed the Condition Column to be the Joining criteria.
                                        */
                                        if tabs.parent_id is not null then
                                          l_sql := l_sql || ' join ' || tabs.par_owner_name || '.' || tabs.par_table_name || ' par' ||
                                                    ' on (ch.' || tabs.condition_column || ' = ' || ' par.' || tabs.condition_column || ')' ||
                                                    ' where (trunc(sysdate) - par.' || tabs.par_condition_column || ') < ' || tabs.par_period;
                                        else
                                          l_sql := l_sql || ' where (trunc(sysdate) - ch.' || tabs.condition_column || ') < ' || tabs.period;
                                        end if;
                                        
                                        /*Populate the Target Tables*/
                                        execute immediate 'INSERT INTO ' || tabs.t_owner_name || '.' || tabs.t_table_name || l_sql;
                                        dbms_output.put_line(SQL%ROWCOUNT || ' records inserted into ' || tabs.t_owner_name || '.' || tabs.t_table_name || ' from ' || tabs.s_owner_name || '.' || tabs.s_table_name);
                                  
                                        /*
                                          Change the Condition of TYPE column, if you want to Insert and Delete if TYPE equals some
                                          value other than A.
                                        */
                                        if tabs.type = 'A' then
                                          l_sql := 'DELETE FROM ' || tabs.s_owner_name || '.' || tabs.s_table_name || ' ch';
                                          
                                          /*
                                            If Condition to Verify if Join with Parent Table is required.
                                            If table being processed is Parent, then there is not need of Join as it already has Date column
                                          */
                                          if tabs.parent_id is not null then
                                            l_sql := l_sql || ' where exists (select 1 from ' || tabs.par_owner_name || '.' || tabs.par_table_name || ' par' ||
                                                      ' where ch.' || tabs.condition_column || ' = ' || ' par.' || tabs.condition_column || ' and ' ||
                                                      ' (trunc(sysdate) - par.' || tabs.par_condition_column || ') < ' || tabs.par_period || ')';
                                          else
                                            l_sql := l_sql || ' where (trunc(sysdate) - ch.' || tabs.condition_column || ') < ' || tabs.period;
                                          end if;
                                  
                                          execute immediate l_sql;
                                          dbms_output.put_line(SQL%ROWCOUNT || ' records delete from ' || tabs.s_owner_name || '.' || tabs.s_table_name);
                                  
                                        end if;
                                  
                                      end loop; --End of Tables Loop;
                                      dbms_output.put_line('');
                                  
                                      /*
                                        Enable the Constraints
                                      */
                                      for tabs in c_tables(parents.seq_id) loop
                                        if tabs.parent_id is not null then
                                          for i in (
                                                    select constraint_name, owner || '.' || table_name tab
                                                      from user_constraints
                                                     where table_name in (tabs.t_table_name)--, tabs.s_table_name)
                                                       and constraint_type = 'R'
                                                   )
                                          loop
                                            execute immediate 'alter table ' || i.tab || ' enable constraint ' || i.constraint_name;
                                          end loop;
                                        end if;
                                      end loop;
                                    end loop;   --End of Parents Loop
                                    
                                    commit;
                                    return 'OK';
                                    
                                  exception
                                    when others then
                                      rollback;
                                      dbms_output.put_line('Error: ' || sqlerrm);
                                      return 'Not OK';
                                  end populate_target;
                                  I have tested the code for basic scenario and found it working. You will have to verify if it meets exactly your requirements and amend it, if required.

                                  I hope the code helps achieve your goal, if not, at least helps you move forward.
                                  • 14. Re: Insert into target table and delete from source
                                    user9077483
                                    Thanks you very much for your code.
                                    However this code is not meeting my requirement.
                                    For target tables we are not creating constraints so you can avoid that part.
                                    I have filter the code for only delete.
                                    And generated the delete statements.
                                    It's not giving the expected result.
                                    My requirement is if the tables are child and sub-child's then the tables
                                    should be use date column from main parent table only (because any of the child and sub child tables are not having date column)
                                    by joining with the immediate parent and main parent tables and use date condition from main parent table.
                                    DELETE FROM APPS_GLOBAL.TEST_TABLE_C7 ch where exists (select 1 from APPS_GLOBAL.TEST_TABLE_CP3 par where ch.COL3 =  par.COL3 and  (trunc(sysdate) - par.COL2) < 30)
                                    DELETE FROM APPS_GLOBAL.TEST_TABLE_CP3 ch where exists (select 1 from APPS_GLOBAL.TEST_TABLE_CP4 par where ch.COL2 =  par.COL2 and  (trunc(sysdate) - par.COL1) < 30)
                                    DELETE FROM APPS_GLOBAL.TEST_TABLE_CP4 ch where exists (select 1 from APPS_GLOBAL.TEST_TABLE_P5 par where ch.COL1 =  par.COL1 and  (trunc(sysdate) - par.LAST_DT) < 30)
                                    DELETE FROM APPS_GLOBAL.TEST_TABLE_C5 ch where exists (select 1 from APPS_GLOBAL.TEST_TABLE_P5 par where ch.COL1 =  par.COL1 and  (trunc(sysdate) - par.LAST_DT) < 30)
                                    DELETE FROM APPS_GLOBAL.TEST_TABLE_C6 ch where exists (select 1 from APPS_GLOBAL.TEST_TABLE_P5 par where ch.COL1 =  par.COL1 and  (trunc(sysdate) - par.LAST_DT) < 30)
                                    DELETE FROM APPS_GLOBAL.TEST_TABLE_P5 ch where (trunc(sysdate) - ch.LAST_DT) < 30
                                    The following two delete statements are not giving the expected result.

                                    In first and second delete statements
                                    Instead of par.COL2 it should be par.LAST_DT
                                    instead of par.COL1 it should be par.LAST_DT
                                    The dynamic queries shold be as below.

                                    The T_TABLE_NAME has to be joined with PAR_TABLE_NAME for each level.
                                    DELETE FROM APPS_GLOBAL.TEST_TABLE_C7 ch where exists
                                    (select 1 from APPS_GLOBAL.TEST_TABLE_CP3 cp3,APPS_GLOBAL.TEST_TABLE_CP4 cp4,APPS_GLOBAL.TEST_TABLE_C5 c5,
                                    APPS_GLOBAL.TEST_TABLE_C6 c6,APPS_GLOBAL.TEST_TABLE_P5 p5
                                    WHERE ch.col3=cp3.col3
                                    AND cp3.col2=cp4.col2
                                    AND cp4.col1=p5.col1
                                    AND c5.col1=p5.col1
                                    AND c6.col1=p5.col1
                                    AND  (trunc(sysdate) - p5.LAST_DT) < 30)
                                    
                                    DELETE FROM APPS_GLOBAL.TEST_TABLE_Cp3 ch where exists
                                    (select 1 from APPS_GLOBAL.TEST_TABLE_CP4 cp4,APPS_GLOBAL.TEST_TABLE_C5 c5,
                                    APPS_GLOBAL.TEST_TABLE_C6 c6,APPS_GLOBAL.TEST_TABLE_P5 p5
                                    WHERE ch.col2=cp4.col2
                                    AND cp4.col1=p5.col1
                                    AND c5.col1=p5.col1
                                    AND c6.col1=p5.col1
                                    AND  (trunc(sysdate) - p5.LAST_DT) < 30)
                                    
                                    DELETE FROM APPS_GLOBAL.TEST_TABLE_Cp4 ch where exists
                                    (select 1 from APPS_GLOBAL.TEST_TABLE_C5 c5,
                                    APPS_GLOBAL.TEST_TABLE_C6 c6,APPS_GLOBAL.TEST_TABLE_P5 p5
                                    WHERE ch.col1=p5.col1
                                    AND c5.col1=p5.col1
                                    AND c6.col1=p5.col1
                                    AND  (trunc(sysdate) - p5.LAST_DT) < 30)
                                    
                                    DELETE FROM APPS_GLOBAL.TEST_TABLE_C5 ch where exists
                                    (select 1 from APPS_GLOBAL.TEST_TABLE_C6 c6,APPS_GLOBAL.TEST_TABLE_P5 p5
                                    WHERE  ch.col1=p5.col1
                                    AND c6.col1=p5.col1
                                    AND  (trunc(sysdate) - p5.LAST_DT) < 30)
                                    
                                    DELETE FROM APPS_GLOBAL.TEST_TABLE_C6 ch where exists
                                    (select 1 from APPS_GLOBAL.TEST_TABLE_P5 p5
                                    WHERE ch.col1=p5.col1
                                    AND  (trunc(sysdate) - p5.LAST_DT) < 30)
                                    
                                    DELETE FROM APPS_GLOBAL.TEST_TABLE_P5 ch where (trunc(sysdate) - ch.LAST_DT) < 30
                                    Please help me.

                                    Thanks.
                                    1 2 Previous Next