7 Replies Latest reply: Feb 3, 2013 11:16 PM by Billy~Verreynne RSS

    DYNAMIC UPDATE

    Kevin2010
      I have a requirement to generate a dynamic update by selecting the existing values of the col from a table and show the existing values to the user before he decides to pass the new values. We know the unique identifier.

      Something like this
      TABLE1
      COl_PK   COl2    COl3    COl4
      100        1         2         3
      The script should generate the update dynamically like this
      update <table_name>
      set <col2> = <new value>, <col3> = <newvalue>
      col4=<new value> 
      where 
      col_pk = :PK
      When the user runs this update it should show prompt him with old value of col2, col3 and col4 i.e 1 2 and 3

      The user will decide if he wants to update or not.

      Is this possible to do this in SQL or we need PLSQL logic?

      Thanks

      db version 11.2.0.3
      O/S red linux

      Edited by: Kevin2010 on Feb 1, 2013 3:32 PM

      updated as reqested. Now can you please answer the question Sir?

      Edited by: Kevin2010 on Feb 1, 2013 3:55 PM
        • 1. Re: DYNAMIC UPDATE
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: DYNAMIC UPDATE
            Robert Angel
            You would have to use pl/sql, whilst you can use substitution variables or bind variables in conjunction with sql (in say sqlplus) you cannot use it iin the place of a table name - so you scenario would require pl/sql.

            regards,

            Robert.
            • 3. Re: DYNAMIC UPDATE
              onedbguru
              First, users should NEVER have direct access to the database in this manner. Use a scripting language or compiled language to do all of the presentation/decision processing and then YOU program the update statement.
              • 4. Re: DYNAMIC UPDATE
                Kevin2010
                Robert : Thanks for your response. I guess I need to use DBMS_SQL since I need to pass to add column_names , correct?

                Do you have any samples?

                Thanks
                • 5. Re: DYNAMIC UPDATE
                  sb92075
                  Kevin2010 wrote:
                  Robert : Thanks for your response. I guess I need to use DBMS_SQL since I need to pass to add column_names , correct?

                  Do you have any samples?
                  when all else fails Read The Fine Manual

                  http://www.oracle.com/pls/db112/search?remark=quick_search&word=dbms_sql&partno=
                  • 6. Re: DYNAMIC UPDATE
                    Etbin
                    If you're not looking for trouble letting the user type table and column names you should go along the lines as below:
                    - provide a list of appropriate tables
                    select table_name the_tables
                      from all_tables
                     where owner = sys_context('USERENV','SESSION_USER')  /* hopefully your_schema_name */
                    - once the table is chosen the user must chose the three colums of interest and provide the primary key value
                    select column_name || '(' || data_type || ')' the_columns
                      from all_tab_cols
                     where owner = :your_schema_name
                       and table_name = :chosen_table_name  /* taken from the list of tables */
                       and column_name != (select column_name  /* primary key column - single column assumed */
                                             from all_cons_columns acc,
                                                  all_constraints ac
                                            where acc.owner = ac.owner
                                              and acc.constraint_name = ac.constraint_name
                                              and acc.table_name = ac.table_name
                                              and ac.owner = sys_context('USERENV','SESSION_USER') /* hopefully your_schema_name */
                                              and ac.table_name = :chosen_table_name  /* taken from the list of tables */
                                              and ac.constraint_type = 'P'
                                          )
                    - now you can retrieve the old column values from the chosen row
                    declare
                      the_query         varchar2(4000);
                      chosen_table_name varchar2(30) := '';  /* not null - as chosen by user */
                      chosen_col1_name  varchar2(30) := '';  /* not null - as chosen by user */
                      chosen_col2_name  varchar2(30) := '';  /* not null - as chosen by user */
                      chosen_col3_name  varchar2(30) := '';  /* not null - as chosen by user */
                      pk_column_name    varchar2(30);
                      pk_column_value   varchar2(300)        /* not null - as chosen by user */
                      old_col1_value    varchar2(100);
                      old_col2_value    varchar2(100);
                      old_col3_value    varchar2(100);
                    begin
                      select column_name  /* primary key column - single column assumed */
                        into pk_column_name
                        from all_cons_columns acc,
                             all_constraints ac
                       where acc.owner = ac.owner
                         and acc.constraint_name = ac.constraint_name
                         and acc.table_name = ac.table_name
                         and ac.owner = sys_context('USERENV','SESSION_USER') /* hopefully your_schema_name */
                         and ac.table_name = chosen_table_name
                         and ac.constraint_type = 'P';
                    
                      the_query := 'select '||chosen_col1_name||','||chosen_col2_name||','||chosen_col3_name||
                                   '  from '||chosen_table_name||
                                   ' where '||pk_column_name||' = :pk_value';
                    
                      dbms_output.put_line(the_query); /* DEBUG ONLY: to verify the_query is well formed */
                      
                      execute immediate the_query into old_col1_value,old_col2_value,old_col3_value using pk_column_value;
                      
                      dbms_output.put_line(old_col1_value||','||old_col2_value||','||old_col3_value); /* DEBUG ONLY */
                    
                    /* to return the values to the user choose the method that best suits you */
                    - now it's the user's turn to decide whether or not to update
                    declare
                      the_query         varchar2(4000);
                      chosen_table_name varchar2(30) := '';  /* not null - as chosen by user */
                      chosen_col1_name  varchar2(30) := '';  /* not null - as chosen by user */
                      chosen_col2_name  varchar2(30) := '';  /* not null - as chosen by user */
                      chosen_col3_name  varchar2(30) := '';  /* not null - as chosen by user */
                      pk_column_name    varchar2(30) := '';  /* as retrieved previously */
                      pk_column_value   varchar2(300)        /* not null - as chosen by user */
                      old_col1_value    varchar2(100) := ''; /* as retrieved previously */
                      old_col2_value    varchar2(100) := ''; /* as retrieved previously */
                      old_col3_value    varchar2(100) := ''; /* as retrieved previously */
                      new_col1_value    varchar2(100) := ''; /* as set by the user */
                      new_col2_value    varchar2(100) := ''; /* as set by the user */;
                      new_col3_value    varchar2(100) := ''; /* as set by the user */;
                      
                    begin
                      the_query := 'update '||chosen_table_name||
                                   '   set '||chosen_col1_name||' = :new_value1,'||
                                   '       '||chosen_col2_name||' = :new_value2,'||
                                   '       '||chosen_col3_name||' = :new_value3'||
                                   ' where '||pk_column_name||' = :pk_value'||
                                   '   and '||chosen_col1_name||' = :old_value1'||
                                   '   and '||chosen_col2_name||' = :old_value2'||
                                   '   and '||chosen_col3_name||' = :old_value3';
                    
                      dbms_output.put_line(the_query); /* DEBUG ONLY: to verify the_query is well formed */
                      
                      execute immediate the_query using new_col1_value,new_col2_value,new_col3_value,
                                                        pk_column_value,
                                                        old_col1_value,old_col2_value,old_col3_value;
                    
                      rows_updated := sql%rowcount;  /* to check the update's outcome */
                      
                    end;
                    Regards

                    Etbin
                    • 7. Re: DYNAMIC UPDATE
                      Billy~Verreynne
                      Kevin2010 wrote:

                      I guess I need to use DBMS_SQL since I need to pass to add column_names , correct?
                      Correct. DBMS_SQL is the only cursor interface in PL/SQL that enables one to do dynamic binding (and dynamic fetching).

                      There are examples in the Oracle® Database PL/SQL Packages and Types Reference guide.

                      As for your specific problem... dynamic SQL seldom makes sense in the application layer and this time is no exception IMO.