This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Apr 3, 2013 9:05 AM by Etbin Go to original post RSS
  • 15. Re: Create dynamic delete statements with dynamc where condition.
    Ramin Hashimzadeh Expert
    Currently Being Moderated
    i recomended you google oracle change data capture first, then try to understand core, i think cdc do samething what you want

    Edited by: Ramin Hashimzadeh on Mar 31, 2013 2:56 AM
  • 16. Re: Create dynamic delete statements with dynamc where condition.
    user8228960 Newbie
    Currently Being Moderated
    Yes, As part of CDC, after the changes are populated in change table, I have to sync this in the target tables. For this, If I am unable to pass/loop for 500 tables, I have to write 500 procedure for each table. To avoid this, I am trying to automate this by passing the tablename and constructing the insert statements, delete statement, and update statements.
    I am able to do this for inserts, but unable to do this for delete, and update. For example,
    execute immediate 'delete from '||table_name||' where variable1=rec.variables and variable2=rec.variable2, etc...for all the primary columns.
    Hence the rec is cursor, but the column names are dynamic.
  • 17. Re: Create dynamic delete statements with dynamc where condition.
    sb92075 Guru
    Currently Being Moderated
    The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
    Then print the variable before passing it to EXECUTE IMMEDIATE.
    COPY the statement & PASTE into sqlplus to validate its correctness.
  • 18. Re: Create dynamic delete statements with dynamc where condition.
    Etbin Guru
    Currently Being Moderated
    Maybe (according to the [url http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:73891904732164]mantra I'm trying to do it without loops as the first attempt) NOT TESTED ! No Database available. (column correspondence by column_id assumed)
    procedure sync_up(p_source varchar2,p_target varchar2) is
    
      source_cols clob;
      target_cols clob;
      pk_source   clob;
      pk_target   clob;
      set_nk_cols clob;
      insert_sql  clob;
      delete_sql  clob;
      update_sql  clob;
    
    begin
    
      select listagg(column_name,',') within group (order by column_id)
        into source_cols
        from user_tab_cols
       where table_name = p_source;
    
       select listagg(column_name,',') within group (order by column_id)
        into target_cols
        from user_tab_cols
       where table_name = p_target;
    
      select listagg(cc.column_name,',') within group (order by cc.position)
        into pk_source
        from user_constraints c,
             user_cons_columns cc
       where c.table_name = cc.table_name
         and c.constraint_name = cc.constraint_name
         and c.table_name = p_source
         and c.constraint_type = 'P'
       group by cc.table_name,cc.constraint_name;
             
      select listagg(cc.column_name,',') within group (order by cc.position)
        into pk_target
        from user_constraints c,
             user_cons_columns cc
       where c.table_name = cc.table_name
         and c.constraint_name = cc.constraint_name
         and c.table_name = p_target
         and c.constraint_type = 'P'
       group by cc.table_name,cc.constraint_name;
    
      select listagg('t.' || t.column_name || ' = s.' || s.column_name,',') within group (order by s.column_id)
        into set_nk_cols
        from (select table_name,column_id,column_name
                from user_tab_cols
               where table_name = p_source
                 and instr(',' || pk_source || ',',',' || column_name || ',') = 0 
             ) s,
             (select table_name,column_id,column_name
                from user_tab_cols
               where table_name = p_target
                 and instr(',' || pk_target || ',',',' || column_name || ',') = 0 
             ) t
       where s.column_id = t.column_id
       group by s.table_name,t.table_name
    
      insert_sql := 'insert into ' || p_target || '(' || target_cols || ') ' ||
                    'select ' || source_cols || ' from ' || p_source ||
                    ' where operation = ''I'' ';
    
      delete_sql := 'delete from ' || p_target || ' where (' || pk_target || ') in (' ||
                    'select ' || pk_source || ' from ' || p_source ||
                    ' where operation = ''D'' ';
    
      update_sql := 'merge into ' || p_target || ' t using (' ||
                    'select || source_cols || ' from ' || p_source ||
                    ' where operation = ''U'' ) s on (' || pk_target || ') in (' ||
                    'select ' || pk_source || ' from ' || p_source ||
                    ' where operation = ''U'' ) when matched then update set ' || set_nk_cols;
    
    /* the following merge should be active only for debugging - to examine the written queries */
      merge into sql_check c
      using (select p_source,p_target,insert_sql,delete_sql,update_sql
               from dual
            ) d
         on (c.source_table = d.p_source and c.target_table = d.p_target)
      when matched
      then update
              set c.insert_sql = d.insert_sql,
                  c.delete_sql = d.delete_sql,
                  c.update_sql = d.update_sql
      when not matched
      then insert (source_table,target_table,insert_sql,delete_sql,update_sql)
      values (p_source,p_target,d.insert_sql,d.delete_sql,d.update_sql);
    /* until the dml queries are correct you'd better comment out their execution */
    
      execute immediate delete_sql;
    
      execute immediate update_sql;
    
      execute immediate insert_sql;
    
    end;
    Regards

    Etbin
  • 19. Re: Create dynamic delete statements with dynamc where condition.
    user8228960 Newbie
    Currently Being Moderated
    Outstanding !!
    I have one small question, As per the CDC(Change Data Capture), the first and most important is to order by CSCN(Change SCN) and RSID$. How could I include these 2 in the code.
    I appreciate your help.
  • 20. Re: Create dynamic delete statements with dynamc where condition.
    Etbin Guru
    Currently Being Moderated
    One small question represents a big problem:
    At the time of writing the procedure the involvement of CDC wasn't mentioned yet so the procedure cannot handle duplicate primary keys in source tables
    that was left to you to find out as an exercise ;)
    If you insist on using the same solution type things get complicated:
    maybe - just an idea - introducing:
    - <tt>dense_rank() over (partition by <pk_source>,operation order by cscn$,rsid$) as the_rank</tt> for the source table (p_source)
    - add a predicate containing a bind variable to the source query <tt>and the_rank = :this_rank</tt>
    - establishing dense_rank() max value <tt>max(the_rank) as max_rank</tt>
    - setting up a loop <tt>for i in 1 .. max_rank loop</tt> containing the three <tt>execute immediate insert_sql using i</tt>
    could work for you.
    Again, the procedure assumptions are very strict: the leading colums of the source tables must match the columns of the corresponding target tables positionally (by column_id - column names need not be identical) source tables control columns (operation,cscn$,rsid$, ...) must have column_ids higher than the corresponding target tables <tt>max(column_id)</tt>
    At the moment I don't feel like being able to write the code without using a Database - way too many attempts failed until now. :(
    As an underprivileged user (a sort of thirteenth pig as in use around here) I cannot access CDC functionality - just read the documentation :(
    If I find time I'll try to provide a static version to find out if it can work for you, or even better: can you provide an example along the lines of {message:id=9360002} so other members (with a Database at hand) could help you.

    Regards

    Etbin
  • 21. Re: Create dynamic delete statements with dynamc where condition.
    user8228960 Newbie
    Currently Being Moderated
    Thank you very much for your advise.
    Requirement: As part of CDC, once the date is populated in stage tables(change tables) from source, we need to write stored procedures to sync the target tables.
    In my scenario, i have 200+ source/target tables with the same structure. The change tables are created for each table, hence we have 200+ change tables in staging DB. To sync these 200+ target tables from change tables in stage DB, we need to write 200+ stored procedures. We started writing a stored procedure which accepts change table and depending on the operations the target tables should be populated. The operations include DMLs(Insert/Delete/Update). Inside the stored procedure we wrote a procedure the target columns to construct the insert statement dynamically. But, we are unable the same for delete/update statements.
    Example change table/example data:
    CREATE TABLE cdc_change_table_vw_VW
    (
    OPERATION$ CHAR(2 BYTE) NULL,
    CSCN$ NUMBER NULL,
    COMMIT_TIMESTAMP$ DATE NULL,
    XIDUSN$ NUMBER NULL,
    XIDSLT$ NUMBER NULL,
    XIDSEQ$ NUMBER NULL,
    DDLOPER$ NUMBER NULL,
    DDLPDOBJN$ NUMBER NULL,
    RSID$ NUMBER NULL,
    TIMESTAMP$ DATE NULL,
    BEGIN_DT DATE NULL,
    CODE VARCHAR2(120 BYTE) NULL,
    CREATE_DT TIMESTAMP(6) NULL,
    CREATE_USER CHAR(40 BYTE) NULL,
    ELEMENT_NA VARCHAR2(400 BYTE) NULL,
    ELEMENT_VALUE VARCHAR2(4000 BYTE) NULL,
    MDFCTN_DT TIMESTAMP(6) NULL,
    MDFCTN_USER CHAR(40 BYTE) NULL,
    SUB_CD VARCHAR2(200 BYTE) NULL,
    UPDATE_NU NUMBER(9) NULL
    )
    LOGGING
    NOCOMPRESS
    NOCACHE
    NOPARALLEL
    MONITORING
    /


    SET DEFINE OFF;
    Insert into cdc_change_table_vw_VW
    (OPERATION$, CSCN$, COMMIT_TIMESTAMP$, XIDUSN$, XIDSLT$, XIDSEQ$, RSID$, TIMESTAMP$, BEGIN_DT, CODE, CREATE_DT, CREATE_USER, ELEMENT_NA, ELEMENT_VALUE, MDFCTN_DT, MDFCTN_USER, SUB_CD, UPDATE_NU)
    Values
    ('D ', 3521327, TO_DATE('03/26/2013 14:32:09', 'MM/DD/YYYY HH24:MI:SS'), 20, 23,
    5400, 60001, TO_DATE('03/26/2013 14:31:56', 'MM/DD/YYYY HH24:MI:SS'),
    TO_DATE('01/01/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CHARGE_CODES', TO_TIMESTAMP('10/11/2012 2:00:00.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'UNKNOWN ', '_DESCRIPTION',
    'CHRG', TO_TIMESTAMP('10/11/2012 2:00:00.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'unknown ', 'C', 0);
    Insert into cdc_change_table_vw_VW
    (OPERATION$, CSCN$, COMMIT_TIMESTAMP$, XIDUSN$, XIDSLT$, XIDSEQ$, RSID$, TIMESTAMP$, BEGIN_DT, CODE, CREATE_DT, CREATE_USER, ELEMENT_NA, ELEMENT_VALUE, MDFCTN_DT, MDFCTN_USER, SUB_CD, UPDATE_NU)
    Values
    ('D ', 3521327, TO_DATE('03/26/2013 14:32:09', 'MM/DD/YYYY HH24:MI:SS'), 20, 23,
    5400, 60002, TO_DATE('03/26/2013 14:31:56', 'MM/DD/YYYY HH24:MI:SS'),
    TO_DATE('01/01/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CHARGE_CODES', TO_TIMESTAMP('10/11/2012 2:00:00.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'UNKNOWN ', '_SIGN',
    '-', TO_TIMESTAMP('10/11/2012 2:00:00.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'unknown ', 'C', 0);
    Insert into cdc_change_table_vw_VW
    (OPERATION$, CSCN$, COMMIT_TIMESTAMP$, XIDUSN$, XIDSLT$, XIDSEQ$, RSID$, TIMESTAMP$, BEGIN_DT, CODE, CREATE_DT, CREATE_USER, ELEMENT_NA, ELEMENT_VALUE, MDFCTN_DT, MDFCTN_USER, SUB_CD, UPDATE_NU)
    Values
    ('I ', 3521327, TO_DATE('03/26/2013 14:32:09', 'MM/DD/YYYY HH24:MI:SS'), 20, 23,
    5400, 60038, TO_DATE('03/26/2013 14:31:58', 'MM/DD/YYYY HH24:MI:SS'),
    TO_DATE('03/26/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CHARGE_CODES', TO_TIMESTAMP('3/26/2013 12:00:00.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'UNKNOWN ', '_SIGN',
    '-', TO_TIMESTAMP('3/26/2013 12:00:00.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'unknown ', 'C', 0);
    Insert into cdc_change_table_vw_VW
    (OPERATION$, CSCN$, COMMIT_TIMESTAMP$, XIDUSN$, XIDSLT$, XIDSEQ$, RSID$, TIMESTAMP$, BEGIN_DT, CODE, CREATE_DT, CREATE_USER, ELEMENT_NA, ELEMENT_VALUE, MDFCTN_DT, MDFCTN_USER, SUB_CD, UPDATE_NU)
    Values
    ('I ', 3521327, TO_DATE('03/26/2013 14:32:09', 'MM/DD/YYYY HH24:MI:SS'), 20, 23,
    5400, 60039, TO_DATE('03/26/2013 14:31:58', 'MM/DD/YYYY HH24:MI:SS'),
    TO_DATE('03/26/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CHARGE_CODES', TO_TIMESTAMP('3/26/2013 12:00:00.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'unknown ', '_DESCRIPTION',
    'Receive', TO_TIMESTAMP('3/26/2013 12:00:00.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'unknown ', 'RC', 0);

    Target table:
    CREATE TABLE target_table
    (
    BEGIN_DT DATE NULL,
    CODE VARCHAR2(120 BYTE) NULL,
    CREATE_DT TIMESTAMP(6) NULL,
    CREATE_USER CHAR(40 BYTE) NULL,
    ELEMENT_NA VARCHAR2(400 BYTE) NULL,
    ELEMENT_VALUE VARCHAR2(4000 BYTE) NULL,
    MDFCTN_DT TIMESTAMP(6) NULL,
    MDFCTN_USER CHAR(40 BYTE) NULL,
    SUB_CD VARCHAR2(200 BYTE) NULL,
    UPDATE_NU NUMBER(9) NULL
    )
    LOGGING
    NOCOMPRESS
    NOCACHE
    NOPARALLEL
    MONITORING
    /

    Store procedure:
    We have to order by CSCN$, RSID$ to know the actual operations time and order.
    Operation$ column will have : I -- Insert, D-Delete-, and UN- Update new.
    The condition to populate target table is depending on the operation$ value.
    the primary key columns are code,sub_cb_begin_dt, and update_nu(composite primary key). The primary key columns are different for different tables. Hence we need to know the primary key columns dynamically once we know the target table name. The delete/update statements where condition has to be on primary key columns.
    Hope this information is helpful to help me to write a store procedure.
    Logic so far we looked:
    For rec in (select * from cdc_change_table_vw_VW order by CSCN$,RSID$)
    loop
    if operation$='I'
    then
    execute immediate 'insert into '||target_table '|| values select '||v_columns_list(excludes CDC mandatory columns) from '||cdc_change_table_vw_VW;
    end if;
    if operation$='D'
    then
    execute immmediate 'delete from '||target_table ||' where '||column1 ||'='||rec.column1
    ||' and '|| column2||'='||rec.column2;
    --->As rec.column1 is cursor value and variable, we are unable to construct this.
    end if;
    if operation$='UN'
    then
    execute immediate 'update '|| target_table ||' set '|| <non-primary-key columns>
    where <primary key columns>
    end if;
    end loop;
  • 22. Re: Create dynamic delete statements with dynamc where condition.
    sb92075 Guru
    Currently Being Moderated
    user8228960 wrote:
    if operation$='D'
    then
    execute immmediate 'delete from '||target_table ||' where '||column1 ||'='||rec.column1
    ||' and '|| column2||'='||rec.column2;
    --->As rec.column1 is cursor value and variable, we are unable to construct this.
    If you can not write DELETE for single static case, then it is not possible for you to automate it.
    post valid example of what a correct DELETE statement would be for just 1 table
  • 23. Re: Create dynamic delete statements with dynamc where condition.
    user8228960 Newbie
    Currently Being Moderated
    one single static delete statement is delete from target_Table where column=rec.column and column2=rec.column2;
  • 24. Re: Create dynamic delete statements with dynamc where condition.
    sb92075 Guru
    Currently Being Moderated
    user8228960 wrote:
    one single static delete statement is delete from target_Table where column=rec.column and column2=rec.column2;
    post complete session where sqlplus successfully processes DELETE state above.
    How many rows are removed by posted SQL?
  • 25. Re: Create dynamic delete statements with dynamc where condition.
    gaverill Journeyer
    Currently Being Moderated
    You're better off performance-wise generating those 500 stored procedures in the same manner as you plan on generating the dynamic SQL itself; simply wrap it in a "create or replace procedure" statement. If the use of these stored procedures is seldom, you can drop and re-create them as needed.

    Gerard
  • 26. Re: Create dynamic delete statements with dynamc where condition.
    user8228960 Newbie
    Currently Being Moderated
    Thanks for your advise.
    Maintenance is seldom. Performance is acceptable.
  • 27. Re: Create dynamic delete statements with dynamc where condition.
    Etbin Guru
    Currently Being Moderated
    Maybe: NOT TESTED! take it as a template and use it at your own risk as it's just a "paper and pencil" work
    procedure sync_up(p_source varchar2,p_target varchar2,p_control_cols number) is
     
      source_cols clob;
      target_cols clob;
      pk_target   clob;
      pk_source   clob;
      set_nk_cols clob;
      plsql_block clob;
     
    begin
    /* source table column list */ 
      select listagg(column_name,',') within group (order by column_id)
        into source_cols
        from user_tab_cols
       where table_name = p_source
         and column_id > p_control_cols;
         
    /* target table column list */ 
       select listagg(column_name,',') within group (order by column_id)
        into target_cols
        from user_tab_cols
       where table_name = p_target;
    
    /* target table primary key column list */
      select listagg(cc.column_name,',') within group (order by cc.position)
        into pk_target
        from user_constraints c,
             user_cons_columns cc
       where c.table_name = cc.table_name
         and c.constraint_name = cc.constraint_name
         and c.table_name = p_target
         and c.constraint_type = 'P'
       group by cc.table_name,cc.constraint_name;
    
    /* target table primary key corresponding source columns column list */
      select listagg(s.column_name,',') within group (order by cc.position)
        into pk_source
        from user_tab_col s,
             user_constraints c,
             user_cons_columns cc
       where c.table_name = cc.table_name
         and c.constraint_name = cc.constraint_name
         and c.table_name = p_target
         and c.constraint_type = 'P'
         and s.table_name = p_source
         and s.column_id = cc.position + p_control_cols
       group by cc.table_name,cc.constraint_name;
       
    /* non primary key columns setting */ 
      select listagg(t.column_name || ' = ' || s.column_name,',') within group (order by t.column_id)
        into set_nk_cols
        from (select table_name,column_id - p_control_cols column_id,column_name
                from user_tab_cols
               where table_name = p_source
                 and column_id > p_control_cols 
             ) s,
             (select table_name,column_id,column_name
                from user_tab_cols
               where table_name = p_target
                 and instr(',' || pk_target || ',',',' || column_name || ',') = 0 
             ) t
       where s.column_id = t.column_id
       group by s.table_name,t.table_name
    
    /* the pl/sql block */  
      plsql_block := 'begin' ||
                     '  for rec in (select * from ' || p_source || ' order by cscn$,rsid$) ' ||
                     '  loop' ||
                     '    if operation$ = ''I '' then ' ||
                     '      insert into ' || p_target || '(' || target_cols || ') ' || 
                     '      values (rec.' || replace(source_cols,',',',rec.') || '); ' || 
                     '    end if ' ||
                     '    if operation$ = ''D '' then ' ||
                     '      delete from ' || p_target || 
                     '       where (' || pk_target || ') = (select rec.' || replace(pk_source,',',',rec.') ||
                     '                                        from dual ' ||
                     '                                     ); ' ||
                     '    end if ' ||
                     '    if operation$ = ''UN'' then ' ||
                     '      update ' || p_target
                     '         set ' || replace(set_nk_cols,'= ','= rec.')
                     '       where (' || pk_target || ') = (select rec.' || replace(pk_source,',',',rec.') ||
                     '                                        from dual ' ||
                     '                                     ); ' ||
                     '    end if ' ||
                     '  end loop ' ||
                     'end;';
    
    /* the following code should be active only for debugging - to check if the plsql_block is correct */
    
      insert into clob_test_table (the_clob)
      values (plsql_block);
    
    /* until the plsql_block is formed correctly you'd better comment out its execution line */
    
      execute immediate plsql_block;
     
    end;
    Regards

    Etbin
  • 28. Re: Create dynamic delete statements with dynamc where condition.
    user8228960 Newbie
    Currently Being Moderated
    Thank you very much !!
    I will test and let you know the outcome.
  • 29. Re: Create dynamic delete statements with dynamc where condition.
    Etbin Guru
    Currently Being Moderated
    This works for me (sorry for syntax errors in previous post)
    create or replace procedure sync_up(p_source varchar2,p_target varchar2,p_control_cols number) is
     
      source_cols clob;
      target_cols clob;
      pk_target   clob;
      pk_source   clob;
      set_nk_cols clob;
      plsql_block clob;
    
    begin
    /* source table column list */ 
      select listagg(column_name,',') within group (order by column_id)
        into source_cols
        from user_tab_cols
       where table_name = p_source
         and column_id > p_control_cols;
         
    /* target table column list */ 
       select listagg(column_name,',') within group (order by column_id)
        into target_cols
        from user_tab_cols
       where table_name = p_target;
     
    /* target table primary key column list */
      select listagg(cc.column_name,',') within group (order by cc.position)
        into pk_target
        from user_constraints c,
             user_cons_columns cc
       where c.table_name = cc.table_name
         and c.constraint_name = cc.constraint_name
         and c.table_name = p_target
         and c.constraint_type = 'P'
       group by cc.table_name,cc.constraint_name;
     
    /* target table primary key corresponding source columns column list */
      select listagg(s.column_name,',') within group (order by cc.position)
        into pk_source
        from user_tab_cols s,
             user_tab_cols t,
             user_constraints c,
             user_cons_columns cc
       where c.table_name = cc.table_name
         and c.constraint_name = cc.constraint_name
         and c.table_name = p_target
         and c.constraint_type = 'P'
         and t.table_name = p_target
         and t.column_name = cc.column_name
         and s.table_name = p_source
         and s.column_id = t.column_id + p_control_cols
       group by cc.table_name,cc.constraint_name;
       
    /* non primary key columns setting */ 
      select listagg(t.column_name || ' = ' || s.column_name,',') within group (order by t.column_id)
        into set_nk_cols
        from (select table_name,column_id - p_control_cols column_id,column_name
                from user_tab_cols
               where table_name = p_source
                 and column_id > p_control_cols 
             ) s,
             (select table_name,column_id,column_name
                from user_tab_cols
               where table_name = p_target
                 and instr(',' || pk_target || ',',',' || column_name || ',') = 0 
             ) t
       where s.column_id = t.column_id
       group by s.table_name,t.table_name;
     
    /* the pl/sql block */  
      plsql_block := 'begin' ||
                     '  for rec in (select * from ' || p_source || ' order by cscn$,rsid$) ' ||
                     '  loop' ||
                     '    if rec.operation$ = ''I '' then ' ||
                     '      insert into ' || p_target || '(' || target_cols || ') ' || 
                     '      values (rec.' || replace(source_cols,',',',rec.') || '); ' || 
                     '    end if; ' ||
                     '    if rec.operation$ = ''D '' then ' ||
                     '      delete from ' || p_target || 
                     '       where (' || pk_target || ') = (select rec.' || replace(pk_source,',',',rec.') ||
                     '                                        from dual ' ||
                     '                                     ); ' ||
                     '    end if; ' ||
                     '    if rec.operation$ = ''UN'' then ' ||
                     '      update ' || p_target ||
                     '         set ' || replace(set_nk_cols,'= ','= rec.') ||
                     '       where (' || pk_target || ') = (select rec.' || replace(pk_source,',',',rec.') ||
                     '                                        from dual ' ||
                     '                                     ); ' ||
                     '    end if; ' ||
                     '  end loop; ' ||
                     'end;';
     
    /* the following code should be active only for debugging - to check if the plsql_block is correct */
     
      insert into clob_tab (column1,column2)
      values (to_char(sysdate,'hh24:mi:ss'),plsql_block);
     
    /* until the plsql_block is formed correctly you'd better comment out its execution line */
     
      execute immediate plsql_block;
     
    end;
    Regards

    Etbin

    Edited by: Etbin on 2.4.2013 14:25
    Further verification of delete and update operations made me change the /* target table primary key corresponding source columns column list */
    query (I misinterpreted the user_cons_columns.position column - it indicates the column position within the primary key not the column_id within the target table) :(

Legend

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