This discussion is archived
7 Replies Latest reply: Feb 3, 2013 9:16 PM by BillyVerreynne RSS

DYNAMIC UPDATE

Kevin2010 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: DYNAMIC UPDATE
    Robert Angel Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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.

Legend

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