This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Jan 29, 2013 4:50 AM by Purvesh K RSS

Insert into target table and delete from source

user9077483 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points