9 Replies Latest reply on Sep 14, 2013 6:29 AM by ibney

    Dynamic Update statements

    user8228960

      Hello,

      I have one SP to do DMLs. This SP works fine without any problem, but for Update I have problem to construct. The update statement should be constructed depending on the values.

      For example, in a table we may have so many columns, but only few non primary key columns are updated. The SP is given below.

      Following is the update statement. the set_nk_update_cols should be dynamic.

         '    if rec.operation$ = ''UN'' then ' ||

                       '      update <owner>.' || p_target ||

                       '         set ' || replace(set_nk_update_cols,'= ','= rec.') ||

                       '       where (' || pk_target || ') = (select rec.' || replace(pk_target,',',',rec.') ||

                       '                                        from dual ' ||

        • 1. Re: Dynamic Update statements
          Greg Spall

          I just did something like this not long ago

           

          The trick is to build your 2 columns lists using LISTAGG seperately.

          1) build your PK col list. Look into dba_ind_columns for this.

          2) build your full col list. Look into dba_tab_columns

          3) splice them all together in the right place:  ie  PK list is:  where col1 and col2 and col3 .... etc.  Full list is: select col1, col2, col3 ... etc.

           

          [edit]

          Something else you may want to do to make things more "readable"

           

          WITH w_template AS

             ( SELECT TO_CLOB(

                   RTRIM(q'[UPDATE <table_name>           ]')||CHR(10)||

                   RTRIM(q'[   SET                        ]')||CHR(10)||

                   RTRIM(q'[<set_cols>                    ]')||CHR(10)||

                   RTRIM(q'[ WHERE                        ]')||CHR(10)||

                   RTRIM(q'[<where_cols>                  ]')||CHR(10)||

                   RTRIM(q'[;                             ]')||CHR(10)

                   ) tform

               FROM dual

               ),

             w_main AS (

                SELECT DISTINCT

                         dtc.table_name,

                         LISTAGG ( ?? )  set_cols,

                         LISTAGG ( ?? )  where_cols

                   FROM dba_tab_columns dtc,

                        dba_ind_columns dic

                  WHERE ???

             )

          Select replace ( replace ( replace(

                                        w_template, '<table_name>', table_name ),

                               '<set_cols>', set_cols ),

                      '<where_cols>', where_cols )

            from w_template t,

                 w_main;

           

          Something like that, anyway ..

          Sorry, I haven't got much free time to play with this at the moment, but hopefully this gives you (or somebody else?) a direction you could take.

          [/edit]

          • 2. Re: Dynamic Update statements
            user8228960

            Thanks for your response.

            You are correct. This works for insert and delete, but not for update statements. The update will happen only on 2 or 3 columns which is dynamic. Challenge to identify the not null columns to build update statement which I am currently facing problem.

            • 3. Re: Dynamic Update statements
              user8228960

              Thanks for your response.

              This logic works for insert and delete, but not for update. The updates can happen to 2 or 3 columns in a 10 table not all the columns. We have to construct the update columns depending on the changes of the columns. In this case, we have to identify the columns for which the data has been changed and then construct update statement dynamically.

              • 4. Re: Dynamic Update statements
                Greg Spall

                That's fine, use what I posted as a template, and build *YOUR* logic into it.

                 

                If you provide a more complete sample, you'll get a more complete answer

                • 5. Re: Dynamic Update statements
                  user8228960

                  For example, I have a table temp and columns n, m, o, p. q, r, s.  n, and m columns are primary key columns. The application can update column 'o' once and column 'p' other time and sometime it may update o,p, and s columns. We need to build a logic for the update statement in such a way, it has to be built dynamically depending upon the columns which has values.  From the DBA_TAB_COLUMNS, we get all the list of columns and their poistion. But we need to change the columns in the set clause when the column has a value otherwise it should not include that column in the update statement.

                  For example: 1) udpate table temp set o=value

                                           where n=value, and m=value;

                                      2) update table temp set p=value where n=value, and m=value;

                                      3) update table temp set p=value, o=value where n=value, and m=value;

                                      4) update table temp set o=value, s=value where n=value, and m=value; 

                   

                  the columns in the set clause will change. It is dynamic as all the columns are not updated. The PL/SQL or SP should be have proper logic to build the n different types of update statements. Hope this gives more idea to provide a logic.

                  we have to traverse the columns which has data and built the set clause logic for update statement.

                  • 6. Re: Dynamic Update statements
                    stratmo

                    Hi,

                    could you give me more details on this:

                     

                    But we need to change the columns in the set clause when the column has a value otherwise it should not include that column in the update statement.

                     

                    Talking about the UPDATE of type

                    update table temp set o=value, s=value where n=value, and m=value;

                     

                    Do you want to update this column if it is NOT NULL? If that is the case use something like a case-clause.

                    Ex.:

                    drop table x;

                    Table dropped.

                     

                    create table x (n number, m number);

                    Table created.


                    insert into x (n,m) values (1,2);

                    1 row created.

                    insert into x (n,m) values (null,3);

                    1 row created.


                    commit;

                    Commit complete.

                     

                    select * from x;

                     

                           N        M

                    -------- --------

                          1       2

                                   3

                    update x

                    set n =    case

                                   when n is null

                                   then null else 5

                                   end;   

                    2 rows updated.

                     

                    select * from x;

                           N        M

                    -------- --------

                          5        2

                                    3

                     

                    Bye

                    stratmo

                    • 7. Re: Dynamic Update statements
                      user8228960

                      Thanks for your response.

                      have to modify the below dynamic sql with decode/case for set clause columns.

                       

                        '    if rec.operation$ = ''UN'' then ' ||

                                       '      update <owner>.' || p_target ||

                                       '         set ' || replace(set_nk_update_cols,'= ','= rec.') ||

                                       '       where (' || pk_target || ') = (select rec.' || replace(pk_target,',',',rec.') ||

                                       '                                        from dual ' ||

                       

                      Example. The above dynamic sql generates the update statement like

                      update t set n=rec.n

                      where pk=(select rec.pk from dual)

                      Want to change the above dynamic sql to include decode like

                      update t set n=decode(rec.n, null, n, rec.n)

                      where pk=(select rec.pk from dual)

                      • 8. Re: Dynamic Update statements
                        stratmo

                        Hi,

                        what does "rec" look like. Could you give me a hint please. I probably overlooked  it in your previous postings, Sorry.

                        stratmo

                        • 9. Re: Dynamic Update statements
                          ibney

                          Please try with below changes

                            '    case when  rec.operation$ = ''UN'' then ' ||

                                           '      update <owner>.' || p_target ||

                                           '         set ' || replace(set_nk_update_cols,'= ','= rec.') ||

                                           '       where (' || pk_target || ') = (select rec.' || replace(pk_target,',',',rec.') ||

                                           '                                        from dual ' ||

                                           end ';