This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Jan 29, 2013 4:50 AM by Purvesh K Go to original post RSS
  • 15. Re: Insert into target table and delete from source
    Purvesh K Guru
    Currently Being Moderated
    user9077483 wrote:
    Thanks you very much for your code.
    However this code is not meeting my requirement.
    I am aware of this. And I did mention in my previous reply.

    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.
    I mentioned previously, that the table and the logic could handle a Parent - Child relationship, but extending beyond is going to be a problem as you need to have specific columns in tables, which is not the case. So, in other words, you need to incorporate the logic of writing an entire SQL Generator, which shall begin with your Grand child and navigate up to the Grand Parent to check the Date column.

    I do not assure, but if possible, I will try to write a logic to generate such a query and post it to this thread.
  • 16. Re: Insert into target table and delete from source
    976208 Explorer
    Currently Being Moderated
    Hi ,

    I am waiting for your code.

    Please help me how can we made it dynamically.

    I think we can achieve this by finding the count of PAR_TABLE_NAME column (excluding NULL value) for each level and make those number of joins.

    But I don't know how to implement it.
    scenario 1:
    In the first scenario1 we have to make two join conditions.
    
    select seq_id,t_table_name,prior s_table_name PAR_TABLE_NAME,parent_id
          from control_table
         start with seq_id =4
       connect by prior seq_id = parent_id
         order by level desc; 
    
    SEQ_ID     T_TABLE_NAME       PAR_TABLE_NAME    PARENT_ID
    6     T_TEST_TABLE_C7       TEST_TABLE_CP3     5
    5     T_TEST_TABLE_CP3  TEST_TABLE_CP4     4
    4     T_TEST_TABLE_CP4                  1
    
    scenario 2:
    In the first scenario2 we have to make one join condition.
    
    select seq_id,t_table_name,prior s_table_name PAR_TABLE_NAME,parent_id
          from control_table
         start with seq_id =5
       connect by prior seq_id = parent_id
         order by level desc; 
    
    SEQ_ID     T_TABLE_NAME        PAR_TABLE_NAME   PARENT_ID
    6     T_TEST_TABLE_C7        TEST_TABLE_CP3     5
    5     T_TEST_TABLE_CP3                  4
    Thanks.
  • 17. Re: Insert into target table and delete from source
    976208 Explorer
    Currently Being Moderated
    Can you please help me.
  • 18. Re: Insert into target table and delete from source
    Purvesh K Guru
    Currently Being Moderated
    I do not think this is your thread. It can be considered as Hijacking of OP's thread. I will suggest you to post a Thread of yours, if you wish to get better help.

    Anyways, I have not attempted this logic yet. I shall post once I find time to write it.
  • 19. Re: Insert into target table and delete from source
    976208 Explorer
    Currently Being Moderated
    Hi ,

    You helped me lot .Thanks for your help.
    You can leave the dynamic script creation.
    There is one scenario added to the requirement.
    Please help me on this.

    The data in the GOMT_TABLE as follows.
    CATEGORY_ID     CATEGORY_NAME     ACT_TYPE
    XX1              OFFER           A
    XX2              ORL           A
    XX3              DAM           A
    The data in the CATEGORY_TABLE as follows.
    CATEGORY_ID  RETENTION_PERIOD  BUID
    XX1          24          0
    XX2          24          0
    XX1          44          5959
    The data in the CONTROL_TABLE as follows.
    CATEGORY_ID   SEQ_ID   TABLENAME          PARENT_COLUMN     CHILD_COLUMN     PARENT_ID   SCHEMA_NAME
    XX1          1     GEDIS_OFFER_HEADER     LAST_UPDATE     LAST_UPDATE      NULL          APPS_XX
    XX1          2     GEDIS_ORDER_BUILDS     OFFER_NO     OFFER_NO       1          APPS_XX
    XX2          3     FAX_HEADER          UPDATE_DATE     UPDATE_DATE      NULL          ORL
    Your code is working for one scenario and it is not working for another scenario.
    I am passing schema_name and buid from the procedure.
    If the variable p_buid(which is sending through the procedure) is equals to BUID in CATEGORY_TABLE it has to use the same buid
    and take the RETENTION_PERIOD for that BUID else it should use another BUID i.e.0.

    We never pass BUID "0" through the procedure.

    For example If I pass the p_buid as "5959" and p_schema name as "APPS_XX" through the procedure
    it has to generate the delete statements as below.
    DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO 
    AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 44);
    DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE_DATE_UTC) < 44;
    If BUID 5959 is not existed in the CATEGORY_TABLE it has to generate the delete statements as below.
    DELETE FROM GEDIS_ORDER_BUILDS ch WHERE EXISTS (SELECT 1 FROM GEDIS_OFFER_HEADER par WHERE par.OFFER_NO =  ch.OFFER_NO 
    AND  (TRUNC(SYSDATE) - par.LAST_UPDATE_DATE_UTC) < 24);
    DELETE FROM GEDIS_OFFER_HEADER ch WHERE (TRUNC(SYSDATE) - ch.LAST_UPDATE_DATE_UTC) < 24;
    If I pass some other BUID as 6789, if 6789 is not there in CATEGORY_TABLE table it should take BUID as "0".

    My below code is working fine if it is only one entry for each category in the CATEGORY_TABLE table as below.
    CATEGORY_ID RETENTION_PERIOD BUID
    XX1            24      0
    Please help me how to achieve this.

    I tried using case in the first cursor but it's not working.
    WHERE       gomt_table.category_id = category_table2.category_id
                    AND category_table2.CATEGORY_ID = control_table2.category_id
                   AND parent_id IS NULL
                   AND schema_name=p_schema_name
                   AND buid=CASE WHEN buid=p_buid THEN buid
                   ELSE 0
                   END;
    I am posting the my procedure and as well as test case scripts also.
    CREATE OR REPLACE PROCEDURE populate_target133
    (p_schema_name VARCHAR2,p_buid NUMBER)
    IS
       l_sql   VARCHAR2 (4000);
     
       CURSOR c_parents
       IS
          SELECT   control_table.category_id,
                   category_name,
                   seq_id,
                   tablename,
                   parent_column,
                   child_column,
                   parent_id,
                   schema_name,
                   act_type,
                   retention_period,
                   buid
            FROM   gomt_table, category_table, control_table
           WHERE       gomt_table.category_id = category_table.category_id
                   AND CATEGORY_TABLE.CATEGORY_ID = control_table.category_id
                   AND parent_id IS NULL;
     
       CURSOR c_tables (p_parent_seq NUMBER)
       IS
              SELECT   control_table.category_id,
                       category_name,
                       seq_id,
                       tablename,
                       parent_column,
                       child_column,
                       parent_id,
                       schema_name,
                       act_type,
                       retention_period,
                       buid,
                       prior tablename par_tablename,
                       prior parent_column par_parent_column
                FROM   gomt_table, category_table, control_table
               WHERE   gomt_table.category_id = category_table.category_id
                       AND CATEGORY_TABLE.CATEGORY_ID = control_table.category_id
          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
          FOR tabs IN c_tables (parents.seq_id)
          LOOP
          
          IF tabs.act_type = 'A' THEN
             l_sql :=
                   'DELETE FROM '
                || tabs.tablename
                || ' ch';
     
             IF tabs.parent_id IS NOT NULL
             THEN
                l_sql :=
                      l_sql
                   || ' WHERE EXISTS (SELECT 1 FROM '
                   || tabs.par_tablename
                   || ' par'
                   || ' WHERE par.'
                   || tabs.parent_column
                   || ' = '
                   || ' ch.'
                   || tabs.child_column
                   || ' AND '
                   || ' (TRUNC(SYSDATE) - par.'
                   || tabs.par_parent_column
                   || ') < ')';
             ELSE
                l_sql :=
                      l_sql
                   || ' WHERE (TRUNC(SYSDATE) - ch.'
                   || tabs.parent_column
                   || ') < '
                   || tabs.retention_period;
             END IF;
     
             --execute immediate l_sql;
             DBMS_OUTPUT.put_line (l_sql);
          END IF;
     
          END LOOP;                                          
       END LOOP;                                             
     
     -- commit;
     
    EXCEPTION
       WHEN OTHERS
       THEN
          DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
    END populate_target133;
    / 
    
    CREATE TABLE gomt_table
    (
       Category_Id     VARCHAR2 (10),
       Category_Name   VARCHAR2 (50),
       act_type        CHAR (1),
       );
     
     
    INSERT INTO gomt_table VALUES('XX1','OFFER','A');          
    INSERT INTO gomt_table VALUES('XX2','ORL','A');          
    INSERT INTO gomt_table VALUES('XX3','DAM','A');     
     
    CREATE TABLE Category_Table
    (
       Category_Id        VARCHAR2 (10),
       Retention_period   NUMBER,
       Buid               NUMBER,
    );
     
    INSERT INTO category_table VALUES('XX1',24,0);
    INSERT INTO category_table VALUES('XX2',24,0);
    INSERT INTO category_table VALUES('XX1',44,5959);
     
    CREATE TABLE control_table
    (
       category_id        VARCHAR2(30),
       seq_id             NUMBER,
       tablename         VARCHAR2(30),
       parent_column   VARCHAR2(30),
       child_column   VARCHAR2(30),
       parent_id          NUMBER,
       schema_name        VARCHAR2(30)
       );
     
    INSERT INTO control_table values('XX1',1,'GEDIS_OFFER_HEADER','LAST_UPDATE','LAST_UPDATE',NULL,'APPS_XX');
    INSERT INTO control_table values('XX1',2,'GEDIS_ORDER_BUILDS','OFFER_NO','OFFER_NO',1,'APPS_XX');
    INSERT INTO control_table values('XX2',3,'FAX_HEADER','UPDATE_DATE','UPDATE_DATE',NULL,'ORL');
    Thanks in advance.
  • 20. Re: Insert into target table and delete from source
    Purvesh K Guru
    Currently Being Moderated
    I already did mention, this is hijacking others thread.

    And you already have posted a question with your problem. So, i suggest you to wait until you get any ideas to implement the solution. This also allows volunteers to understand if a particular suggestion has already been made.

    If you want others to refer to this thread, simply post a Link to this thread.
  • 21. Re: Insert into target table and delete from source
    Purvesh K Guru
    Currently Being Moderated
    I guess, I have got the logic that shall help you build the query, dynamically.

    Ofcourse, this is tested only to the hierarchy of data that you have provided. But, IMO, it should work for a Deeper hierarchy as well.

    Before you execute the procedure on your environment, I will suggest you to comment the Execute Immediate parts of Insert and Delete statements,
    and Enable the DBMS_OUTPUT that prints the Generated SQL's.

    Try the SQL's and only once you are satisfied with the data returned, execute the procedure with execute immediate enabled.
    create or replace
    function populate_target
    (
      p_cid       in    varchar2
    )
    return varchar2
    is
      l_sql                 varchar2(4000);
      l_delete_sql          varchar2(4000);
      l_join_conditions     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 seq_id, 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('Parent Processing ' || parents.s_owner_name || '.' || parents.s_table_name);
        
        for tabs in c_tables(parents.seq_id) loop
          dbms_output.put_line(' Processing ' || tabs.s_owner_name || '.' || tabs.s_table_name);
          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
            /*
              below SQL generates the Join Conditions
              Starting with the Parent Sequence ID of current Table being processed, and
              progressing to the Root Table.
            */
            select join_cond
              into l_join_conditions
              from (
                    select parent_id,
                           replace(
                           sys_connect_by_path(
                           decode (level, 1, ' join ' || s_owner_name || '.' || s_table_name || ' lvl_' || to_char(level - 1) || ' on (!@# ' ||
                                    decode(nvl(parent_id, -1), -1, ' and trunc(sysdate) - ' || 
                                           ' lvl_' || to_char(level-1) || '.' || condition_column || ' < ' || period,
                                           null
                                          ) || ')',
                                    ' join ' || s_owner_name || '.' || s_table_name || ' lvl_' || to_char(level - 1)
                                    || ' on (' || 'lvl_' || to_char(level - 1) || '.' || prior condition_column || ' = ' || 
                                    'lvl_' || to_char(level-2) || '.' || prior condition_column || 
                                    decode(nvl(parent_id, -1), -1, ' and trunc(sysdate) - ' || 
                                           ' lvl_' || to_char(level-1) || '.' || condition_column || ' < ' || period,
                                           null
                                          )
                                    || ')'
                                  ) , '~')
                           , '~', chr(10)
                           )join_cond
                      from control_table
                     start with seq_id = (select nvl(parent_id, seq_id) from control_table where seq_id = tabs.seq_id)           --Use Table sequence ID instead of Parent ID, in case of processing Immediate Child to the Root table.
                    connect by prior parent_id = seq_id
                   ) a
             where a.parent_id is null;
            
            l_sql := l_sql || replace(l_join_conditions, '!@#', 'ch.' || tabs.condition_column || ' = ' || 'lvl_0.' || tabs.condition_column);
            
          else
            l_sql := l_sql || ' where trunc(sysdate) - ch.' || tabs.condition_column || ' < ' || tabs.period;
          end if;
    
    --      dbms_output.put_line('SQL :: ' || l_sql);   --> Uncomment this to verify the SQL generated and Comment the Below Execute Immediate
    
          /*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
            /*
              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_delete_sql := 'delete from ' || tabs.t_owner_name || '.' || tabs.t_table_name || ' ch where exists (' || replace(l_sql, 'ch.*', '1') || ')';
            else
              l_delete_sql := 'delete from ' || tabs.t_owner_name || '.' || tabs.t_table_name || ' ch where trunc(sysdate) - ch.' || tabs.condition_column || ' < ' || tabs.period;
            end if;
    
    --      dbms_output.put_line('Delete SQL :: ' || l_sql);   --> Uncomment this to verify the SQL generated and Comment the Below Execute Immediate
    
            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('');
      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;
  • 22. Re: Insert into target table and delete from source
    user9077483 Newbie
    Currently Being Moderated
    Thanks for your help.

    I am getting the below error

    ERROR line 67, col 25, ending_line 67, ending_col 33, Found 'join_cond', an alias is not allowed here

    ERROR line 68, col 19, ending_line 68, ending_col 22, Found 'from', Expecting: ) ,

    Please help me.

    Thanks.
  • 23. Re: Insert into target table and delete from source
    Purvesh K Guru
    Currently Being Moderated
    Below is a compiled version, and so was earlier, perhaps was a wrong Copy and paste I guess.
    create or replace
    function populate_target
    (
      p_cid       in    varchar2
    )
    return varchar2
    is
      l_sql                 varchar2(4000);
      l_delete_sql          varchar2(4000);
      l_join_conditions     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 seq_id, 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('Parent Processing ' || parents.s_owner_name || '.' || parents.s_table_name);
        
        for tabs in c_tables(parents.seq_id) loop
          dbms_output.put_line(' Processing ' || tabs.s_owner_name || '.' || tabs.s_table_name);
          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
            /*
              below SQL generates the Join Conditions
              Starting with the Parent Sequence ID of current Table being processed, and
              progressing to the Root Table.
            */
            select join_cond
              into l_join_conditions
              from (
                    select parent_id,
                           replace(
                           sys_connect_by_path(
                           decode (level, 1, ' join ' || s_owner_name || '.' || s_table_name || ' lvl_' || to_char(level - 1) || ' on (!@# ' ||
                                    decode(nvl(parent_id, -1), -1, ' and trunc(sysdate) - ' || 
                                           ' lvl_' || to_char(level-1) || '.' || condition_column || ' < )'),
                                    ' join ' || s_owner_name || '.' || s_table_name || ' lvl_' || to_char(level - 1)
                                    || ' on (' || 'lvl_' || to_char(level - 1) || '.' || prior condition_column || ' = ' || 
                                    'lvl_' || to_char(level-2) || '.' || prior condition_column || 
                                    decode(nvl(parent_id, -1), -1, ' and trunc(sysdate) - ' || 
                                           ' lvl_' || to_char(level-1) || '.' || condition_column || ' < ' || period,
                                           null
                                          )
                                    || ')'
                                  ) , '~')
                           , '~', chr(10)
                           )join_cond
                      from control_table
                     start with seq_id = (select nvl(parent_id, seq_id) from control_table where seq_id = tabs.seq_id)           --Use Table sequence ID instead of Parent ID, in case of processing Immediate Child to the Root table.
                    connect by prior parent_id = seq_id
                   ) a
             where a.parent_id is null;
            
            l_sql := l_sql || replace(l_join_conditions, '!@#', 'ch.' || tabs.condition_column || ' = ' || 'lvl_0.' || tabs.condition_column);
            
          else
            l_sql := l_sql || ' where trunc(sysdate) - ch.' || tabs.condition_column || ' < ' || tabs.period;
          end if;
     
    --      dbms_output.put_line('SQL :: ' || l_sql);   --> Uncomment this to verify the SQL generated and Comment the Below Execute Immediate
     
          /*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
            /*
              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_delete_sql := 'delete from ' || tabs.t_owner_name || '.' || tabs.t_table_name || ' ch where exists (' || replace(l_sql, 'ch.*', '1') || ')';
            else
              l_delete_sql := 'delete from ' || tabs.t_owner_name || '.' || tabs.t_table_name || ' ch where trunc(sysdate) - ch.' || tabs.condition_column || ' < ' || tabs.period;
            end if;
     
    --      dbms_output.put_line('Delete SQL :: ' || l_sql);   --> Uncomment this to verify the SQL generated and Comment the Below Execute Immediate
     
            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('');
      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;
  • 24. Re: Insert into target table and delete from source
    user9077483 Newbie
    Currently Being Moderated
    Thanks for your reply.

    Still I am getting the same error.

    ERROR line 64, col 25, ending_line 64, ending_col 33, Found 'join_cond', an alias is not allowed here (reserved for XMLCOLATTVAL, XMLFOREST and XMLATTRIBUTES only)
    ERROR line 65, col 19, ending_line 65, ending_col 22, Found 'from', Expecting: ) ,

    Please help me.

    Thanks.
  • 25. Re: Insert into target table and delete from source
    Purvesh K Guru
    Currently Being Moderated
    Unsure, why you got an error there.

    Is that error received at time of compilation?

    Please see below (one small change made to the procedure): (Uncomment the Execute Immediate when you execute this, that is only once you are sure of generated sql)

    My Oracle version is 10.2.0.4
    create or replace
    function populate_target
    (
      p_cid       in    varchar2
    )
    return varchar2
    is
      l_sql                 varchar2(4000);
      l_delete_sql          varchar2(4000);
      l_join_conditions     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 seq_id, 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('Parent Processing ' || parents.s_owner_name || '.' || parents.s_table_name);
        
        for tabs in c_tables(parents.seq_id) loop
          dbms_output.put_line(' Processing ' || tabs.s_owner_name || '.' || tabs.s_table_name);
          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
            /*
              below SQL generates the Join Conditions
              Starting with the Parent Sequence ID of current Table being processed, and
              progressing to the Root Table.
            */
            select join_cond
              into l_join_conditions
              from (
                    select parent_id,
                           replace(
                           sys_connect_by_path(
                           decode (level, 1, ' join ' || s_owner_name || '.' || s_table_name || ' lvl_' || to_char(level - 1) || ' on (!@# ' ||
                                    decode(nvl(parent_id, -1), -1, ' and trunc(sysdate) - ' || 
                                           ' lvl_' || to_char(level-1) || '.' || condition_column || ' < )', ')'),
                                    ' join ' || s_owner_name || '.' || s_table_name || ' lvl_' || to_char(level - 1)
                                    || ' on (' || 'lvl_' || to_char(level - 1) || '.' || prior condition_column || ' = ' || 
                                    'lvl_' || to_char(level-2) || '.' || prior condition_column || 
                                    decode(nvl(parent_id, -1), -1, ' and trunc(sysdate) - ' || 
                                           ' lvl_' || to_char(level-1) || '.' || condition_column || ' < ' || period,
                                           null
                                          )
                                    || ')'
                                  ) , '~')
                           , '~', chr(10)
                           ) join_cond
                      from control_table
                     start with seq_id = (select nvl(parent_id, seq_id) from control_table where seq_id = tabs.seq_id)           --Use Table sequence ID instead of Parent ID, in case of processing Immediate Child to the Root table.
                    connect by prior parent_id = seq_id
                   ) a
             where a.parent_id is null;
            
            l_sql := l_sql || replace(l_join_conditions, '!@#', 'ch.' || tabs.condition_column || ' = ' || 'lvl_0.' || tabs.condition_column);
            
          else
            l_sql := l_sql || ' where trunc(sysdate) - ch.' || tabs.condition_column || ' < ' || tabs.period;
          end if;
     
          dbms_output.put_line('SQL :: ' || l_sql);   --> Uncomment this to verify the SQL generated and Comment the Below Execute Immediate
     
          /*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
            /*
              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_delete_sql := 'delete from ' || tabs.t_owner_name || '.' || tabs.t_table_name || ' ch where exists (' || replace(l_sql, 'ch.*', '1') || ')';
            else
              l_delete_sql := 'delete from ' || tabs.t_owner_name || '.' || tabs.t_table_name || ' ch where trunc(sysdate) - ch.' || tabs.condition_column || ' < ' || tabs.period;
            end if;
     
          dbms_output.put_line('Delete SQL :: ' || l_sql);   --> Uncomment this to verify the SQL generated and Comment the Below Execute Immediate
     
    --        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('');
      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;
    
    
    DECLARE
      P_CID VARCHAR2(200);
      v_Return VARCHAR2(200);
    BEGIN
      P_CID := 'F';
    
      v_Return := POPULATE_TARGET(
        P_CID => P_CID
      );
      /* Legacy output: 
    DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
    */ 
      :v_Return := v_Return;
    END;
    
    
    Output:-
    Parent Processing TEST.TEST_TABLE_P5
     Processing TEST.TEST_TABLE_C7
    SQL ::  SELECT ch.* FROM TEST.TEST_TABLE_C7 ch 
     join TEST.TEST_TABLE_CP3 lvl_0 on (ch.COL3 = lvl_0.COL3 )
     join TEST.TEST_TABLE_CP4 lvl_1 on (lvl_1.COL2 = lvl_0.COL2)
     join TEST.TEST_TABLE_P5 lvl_2 on (lvl_2.COL1 = lvl_1.COL1 and trunc(sysdate) -  lvl_2.LAST_DT < 30)
    Delete SQL ::  SELECT ch.* FROM TEST.TEST_TABLE_C7 ch 
     join TEST.TEST_TABLE_CP3 lvl_0 on (ch.COL3 = lvl_0.COL3 )
     join TEST.TEST_TABLE_CP4 lvl_1 on (lvl_1.COL2 = lvl_0.COL2)
     join TEST.TEST_TABLE_P5 lvl_2 on (lvl_2.COL1 = lvl_1.COL1 and trunc(sysdate) -  lvl_2.LAST_DT < 30)
     Processing TEST.TEST_TABLE_CP3
    SQL ::  SELECT ch.* FROM TEST.TEST_TABLE_CP3 ch 
     join TEST.TEST_TABLE_CP4 lvl_0 on (ch.COL2 = lvl_0.COL2 )
     join TEST.TEST_TABLE_P5 lvl_1 on (lvl_1.COL1 = lvl_0.COL1 and trunc(sysdate) -  lvl_1.LAST_DT < 30)
    Delete SQL ::  SELECT ch.* FROM TEST.TEST_TABLE_CP3 ch 
     join TEST.TEST_TABLE_CP4 lvl_0 on (ch.COL2 = lvl_0.COL2 )
     join TEST.TEST_TABLE_P5 lvl_1 on (lvl_1.COL1 = lvl_0.COL1 and trunc(sysdate) -  lvl_1.LAST_DT < 30)
     Processing TEST.TEST_TABLE_CP4
    SQL ::  SELECT ch.* FROM TEST.TEST_TABLE_CP4 ch 
     join TEST.TEST_TABLE_P5 lvl_0 on (ch.COL1 = lvl_0.COL1  and trunc(sysdate) -  lvl_0.LAST_DT < )
    Delete SQL ::  SELECT ch.* FROM TEST.TEST_TABLE_CP4 ch 
     join TEST.TEST_TABLE_P5 lvl_0 on (ch.COL1 = lvl_0.COL1  and trunc(sysdate) -  lvl_0.LAST_DT < )
     Processing TEST.TEST_TABLE_C5
    SQL ::  SELECT ch.* FROM TEST.TEST_TABLE_C5 ch 
     join TEST.TEST_TABLE_P5 lvl_0 on (ch.COL1 = lvl_0.COL1  and trunc(sysdate) -  lvl_0.LAST_DT < )
    Delete SQL ::  SELECT ch.* FROM TEST.TEST_TABLE_C5 ch 
     join TEST.TEST_TABLE_P5 lvl_0 on (ch.COL1 = lvl_0.COL1  and trunc(sysdate) -  lvl_0.LAST_DT < )
     Processing TEST.TEST_TABLE_C6
    SQL ::  SELECT ch.* FROM TEST.TEST_TABLE_C6 ch 
     join TEST.TEST_TABLE_P5 lvl_0 on (ch.COL1 = lvl_0.COL1  and trunc(sysdate) -  lvl_0.LAST_DT < )
    Delete SQL ::  SELECT ch.* FROM TEST.TEST_TABLE_C6 ch 
     join TEST.TEST_TABLE_P5 lvl_0 on (ch.COL1 = lvl_0.COL1  and trunc(sysdate) -  lvl_0.LAST_DT < )
     Processing TEST.TEST_TABLE_P5
    SQL ::  SELECT ch.* FROM TEST.TEST_TABLE_P5 ch  where trunc(sysdate) - ch.LAST_DT < 30
    Delete SQL ::  SELECT ch.* FROM TEST.TEST_TABLE_P5 ch  where trunc(sysdate) - ch.LAST_DT < 30
  • 26. Re: Insert into target table and delete from source
    user9077483 Newbie
    Currently Being Moderated
    While creating function I am getting these errors.

    Still I am getting can I ignore these errors?

    Thanks.
  • 27. Re: Insert into target table and delete from source
    Purvesh K Guru
    Currently Being Moderated
    I find it very strange.

    Are you sure, you tried to compile the latest code? Earlier, there was an additional Single Quote which was causing trouble and has been removed.

    As an additional step, drop the function explicitly and then re-create using the code in my previous post (with the Output of SQL statements).
1 2 Previous Next

Legend

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