Forum Stats

  • 3,758,242 Users
  • 2,251,358 Discussions
  • 7,870,124 Comments

Discussions

Insert into target table and delete from source

user9077483
user9077483 Member Posts: 372
edited Jan 29, 2013 7:50AM in SQL & PL/SQL
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.
«13

Answers

  • Purvesh K
    Purvesh K Member Posts: 2,319 Silver Trophy
    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;
  • user9077483
    user9077483 Member Posts: 372
    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.
  • Purvesh K
    Purvesh K Member Posts: 2,319 Silver Trophy
    edited Jan 3, 2013 2:26AM
    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.
  • user9077483
    user9077483 Member Posts: 372
    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.
  • Purvesh K
    Purvesh K Member Posts: 2,319 Silver Trophy
    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.
  • user9077483
    user9077483 Member Posts: 372
    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.
  • Purvesh K
    Purvesh K Member Posts: 2,319 Silver Trophy
    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
  • user9077483
    user9077483 Member Posts: 372
    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.
  • Purvesh K
    Purvesh K Member Posts: 2,319 Silver Trophy
    edited Jan 3, 2013 10:21AM
    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
  • user9077483
    user9077483 Member Posts: 372
    Thanks you very much for your effort and patience to answer for my multiple questions.
This discussion has been closed.