Thanks for the question.

Asked: April 22, 2016 - 7:07 am UTC

Last updated: December 11, 2020 - 5:52 pm UTC

Version: 11

Viewed 1000+ times

You Asked

I have a table with 4 columns and am passing the inputs of these 4 columns with old and new values to update them as shown below.
_________________________________________
Create table (column1 IN VARCHAR2,
column2 IN VARCHAR2,
column3 IN VARCHAR2,
column4 IN VARCHAR2,
)

insert into table values ('as','af','gh','kl');
insert into table values ('ss','sf','sh','sl');
insert into table values ('s1','ssf','sfh','sasfl');

procedure test1( oldcolumn1 IN VARCHAR2,
oldcolumn2 IN VARCHAR2,
oldcolumn3 IN VARCHAR2,
oldcolumn4 IN VARCHAR2,
newcolumn1 IN VARCHAR2,
newcolumn2 IN VARCHAR2,
newcolumn3 IN VARCHAR2,
newcolumn4 IN VARCHAR2)
________________________________________

Requirement here is to update these 4 columns with the new values where the actual column values areequal to oldcolumn inputs.But there is no guarantee that user may pass all 4 column values everttime.

If they pass oldcoulumn1 value in that case need to update only the column1.
If they pass oldcolumn4 values in that case need to update only the column4.
if they pass olculmn1 and oldcolumn5 then need to update column1 and column5.

Can you please suggest a way how to write update statements in such case?

and Chris said...

Sounds like you need some dynamic SQL!

Check whether each input value is null. If it has a value, add it to the update string.

Make sure you're using bind variables when you build up the update statement!

For example:

create table t
  (
    column1 varchar2(10), column2 varchar2(10), 
    column3 varchar2(10), column4 varchar2(10)
  );
insert into t values
  ( 'as','af','gh','kl'
  ) ;
insert into t values
  ( 'ss','sf','sh','sl'
  ) ;
insert into t values
  ( 's1','ssf','sfh','sasfl'
  ) ;

create or replace procedure test1
  (
    oldcolumn1 in varchar2, oldcolumn2 in varchar2,
    oldcolumn3 in varchar2, oldcolumn4 in varchar2,
    newcolumn1 in varchar2, newcolumn2 in varchar2,
    newcolumn3 in varchar2, newcolumn4 in varchar2
  )
as
  update_sql varchar2(4000) := 'update t';
  where_clause varchar2(4000) := 'where 1=1';
  set_clause   varchar2(4000) := 'set ';
  
  cur   binary_integer;
  dummy int;
  
  no_input exception;
begin
  if coalesce ( oldcolumn1, oldcolumn2, oldcolumn3, oldcolumn4) is null then
    raise no_input;
  end if;
  
  if oldcolumn1 is not null then 
    set_clause := set_clause || 'column1 = :new1,';
    
    where_clause := where_clause || ' and column1 = :old1';
    
  end if;
  if oldcolumn2 is not null then 
    set_clause := set_clause || 'column2 = :new2,';
    
    where_clause := where_clause || ' and column2 = :old2';
    
  end if;
  set_clause := substr(set_clause, 1, length(set_clause)-1);
  
  update_sql := update_sql || ' ' || set_clause || ' ' || where_clause;
  
  cur := dbms_sql.open_cursor;
  dbms_sql.parse(cur, update_sql, dbms_sql.native);
  
  if oldcolumn1 is not null then 
    
    dbms_sql.bind_variable(cur, 'old1', oldcolumn1);
    dbms_sql.bind_variable(cur, 'new1', newcolumn1);
    
  end if;
  if oldcolumn2 is not null then 
    
    dbms_sql.bind_variable(cur, 'old2', oldcolumn2);
    dbms_sql.bind_variable(cur, 'new2', newcolumn2);
    
  end if;
  
  dummy := dbms_sql.execute(cur);
  dbms_sql.close_cursor(cur);
end;
/

select * from t;

COLUMN1    COLUMN2    COLUMN3    COLUMN4  
---------- ---------- ---------- ----------
as         af         gh         kl        
ss         sf         sh         sl        
s1         ssf        sfh        sasfl   

exec test1('as', null, null, null, 'c', null, null, null);

select * from t;

COLUMN1    COLUMN2    COLUMN3    COLUMN4  
---------- ---------- ---------- ----------
c          af         gh         kl        
ss         sf         sh         sl        
s1         ssf        sfh        sasfl 

exec test1(null, 'af', null, null, null, 'd', null, null);

select * from t;

COLUMN1    COLUMN2    COLUMN3    COLUMN4  
---------- ---------- ---------- ----------
c          d          gh         kl        
ss         sf         sh         sl        
s1         ssf        sfh        sasfl   

exec test1('ss', 'ssf', null, null, 'e', 'f', null, null);

select * from t;

COLUMN1    COLUMN2    COLUMN3    COLUMN4  
---------- ---------- ---------- ----------
c          d          gh         kl        
ss         sf         sh         sl        
s1         ssf        sfh        sasfl

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

madhusudan, April 22, 2016 - 4:23 pm UTC

A reader, May 30, 2016 - 6:27 am UTC

Hi Chris,

Thank you very much for the provided logic. It worked for me.
But need to do some extension to it..but failed to do so.
In this based on the where_clause that got built till now,I need to execute one select query for one column, before execute the final update statement.

if oldValue5 is not null then
set_clause := set_clause || 'Value_5 = :new15,';
where_clause := where_clause || ' and Value_5 = :old15';
end if;
if oldValue6 is not null then
set_clause := set_clause || 'Value_6= :new16,';
where_clause := where_clause || ' and Value_6 = :old16';
end if;
If the column value is 4 , then need to append the where_clause to some condition. If not 4 then some other condition.

But how to execute select statement in dynmaic sql based on the existing where_clause .

BEGIN
EXECUTE IMMEDIATE 'SELECT column_4 from table_4 where_clause'

INTO c4output';

if c4output = 4 then
where _caluse := where _clause|| 'and column_5 < sysdate';
else
where _caluse := where _clause|| 'and column_6 is not null';

END;

Your quick help is much appreciated.

Thanks

May 31, 2016 - 1:30 pm UTC

What are you trying to do here? Dynamically change the update where clause based on the output of a select statement?

Why do you need to use execute immediate for your select? Why not static SQL?

What errors are you getting?

A reader, May 30, 2016 - 2:46 pm UTC

Thank you very much for the logic. It worked. To extend this....need to have select statement on one of the column using the same where_clause beore go for final update.

select query column value output need to stoe in one varaible,based on which need to change my where_clause for my final update.

If i write simple select its giving error for bind varaibles...so shud we have again diff cursor for select stmt and bindings etc?

Please help.

Thnaks

A reader, May 31, 2016 - 4:43 pm UTC

Actually....we have built the where_clause dynamically.

Now how can we write a static select statement here?

If write so, the values for bind varaibles are not getting replaced.

Would you please extend your first programm by reading one column value through static select stmt, with the where_clause built, and storing it in one variable.

Need to change the where_caluse based on that column variable.

And can we write this static logic,before execute final uupdate_sql??

June 01, 2016 - 7:39 am UTC

I'm not sure I'm following still...

If the query is static, you can just write it directly in PL/SQL. No need for execute immediate/dbms_sql.

Then use the value from the variable to build your dynamic SQL:

begin
  select col
  into   var
  from   <your_query>;

  if var = whatever then
    where_clause := ...
  else
    where_clause := ...    
  end if;
 
  ...

end;
/

Static SQL

Rajeshwaran, Jeyabal, June 01, 2016 - 8:02 am UTC

Dynamic sql produce multiple cursors based on inputs, in case of static sql it will be a single shared cursors across inputs.

Team - Any Technical reason to go with Dynamic sql in this case?

demo@ORA11G> create or replace procedure test1
  2    (
  3      oldcolumn1 in varchar2, oldcolumn2 in varchar2,
  4      oldcolumn3 in varchar2, oldcolumn4 in varchar2,
  5      newcolumn1 in varchar2, newcolumn2 in varchar2,
  6      newcolumn3 in varchar2, newcolumn4 in varchar2
  7    )
  8  as
  9     no_inputs exception;
 10  begin
 11     if coalesce(oldcolumn1,oldcolumn2,
 12                     oldcolumn3,oldcolumn4) is null then
 13             raise no_inputs ;
 14     end if;
 15
 16     update t
 17     set column1 = case when column1 = oldcolumn1
 18                    then newcolumn1
 19                    else column1 end,
 20             column2 = case when column2 = oldcolumn2
 21                    then newcolumn2
 22                    else column2 end,
 23             column3 = case when column3 = oldcolumn3
 24                    then newcolumn3
 25                    else column3 end,
 26             column4 = case when column4 = oldcolumn4
 27                    then newcolumn4
 28                    else column4 end
 29     where column1 = oldcolumn1 or
 30               column2 = oldcolumn2 or
 31               column3 = oldcolumn3 or
 32               column4 = oldcolumn4 ;
 33     exception
 34             when no_inputs then
 35                     raise_application_error(-20457,
 36                     'No Inputs available');
 37  end;
 38  /

Procedure created.

demo@ORA11G>
demo@ORA11G> select * from t order by 1;

COLUMN1    COLUMN2    COLUMN3    COLUMN4
---------- ---------- ---------- ----------
as         af         gh         kl
s1         ssf        sfh        sasfl
ss         sf         sh         sl

3 rows selected.

demo@ORA11G> exec test1('as', null, null, null, 'c', null, null, null);

PL/SQL procedure successfully completed.

demo@ORA11G> select * from t order by 1;

COLUMN1    COLUMN2    COLUMN3    COLUMN4
---------- ---------- ---------- ----------
c          af         gh         kl
s1         ssf        sfh        sasfl
ss         sf         sh         sl

3 rows selected.

demo@ORA11G> exec test1(null, 'af', null, null, null, 'd', null, null);

PL/SQL procedure successfully completed.

demo@ORA11G> select * from t order by 1;

COLUMN1    COLUMN2    COLUMN3    COLUMN4
---------- ---------- ---------- ----------
c          d          gh         kl
s1         ssf        sfh        sasfl
ss         sf         sh         sl

3 rows selected.

demo@ORA11G> exec test1('ss', 'ssf', null, null, 'e', 'f', null, null);

PL/SQL procedure successfully completed.

demo@ORA11G> select * from t order by 1;

COLUMN1    COLUMN2    COLUMN3    COLUMN4
---------- ---------- ---------- ----------
c          d          gh         kl
e          sf         sh         sl
s1         f          sfh        sasfl

3 rows selected.

demo@ORA11G>

June 01, 2016 - 8:19 am UTC

A couple of things:

- With 4 OR conditions, you'll almost certainly have a full table scan. Not great if you're normally only updating by primary/unique key.

- Setting a column equal to itself still generates redo. Again, if you're normally only updating one column this generates a lot of extra overhead:

SQL> create table t as
  2    select rownum x,
  3            sysdate y,
  4                              dbms_random.string('a', 20) z
  5     from dual
  6     connect by level <= 1000;

Table created.

SQL>
SQL> set autotrace on stat
SQL> update t
  2  set    x = x, y = y, z = lpad('x', 20, 'x');

1000 rows updated.


Statistics
----------------------------------------------------------
         13  recursive calls
         29  db block gets
         31  consistent gets
          6  physical reads
     154560  redo size
        754  bytes sent via SQL*Net to client
       1192  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> update t
  2  set    z = lpad('x', 20, 'x');

1000 rows updated.


Statistics
----------------------------------------------------------
          4  recursive calls
         23  db block gets
         19  consistent gets
          0  physical reads
     130540  redo size
        754  bytes sent via SQL*Net to client
       1178  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

154560 redo for updating all columns, two with a "no change" update. But only 130540 when you only update the column that actually changed.

It could also lead to unnecessary firing of triggers if you have some that only happen when you update certain columns.

Rollbacking the committed updated statements

Rajasekhar, December 11, 2020 - 5:18 pm UTC

Hello Chris,

Is there any possibility of rolling back the update statement?
there is a scenario, where we need to update certain records and commit it. followed by pkg deployment. due to some issue, we need to rollback. Now, we can recompile the original pkg as we can take backup for it. can you suggest how we can rollback the updated records to its original values if they are committed.

sry, if it is baseless.

Thanks in advance

December 11, 2020 - 5:52 pm UTC

Once you've committed a change, you can't roll it back. You need to write a new update to restore the original values.

If you're quick, you may be able to retrieve these values using Flashback Query.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Static SQL

Rollbacking the committed updated statements

More to Explore

PL/SQL demos

PL/SQL docs

Oracle update statement

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Static SQL

Rollbacking the committed updated statements

More to Explore

PL/SQL demos

PL/SQL docs