CODEWITHSUNDEEP
sqloracle-database
Gerrat
Gerrat

Reputation: 29690

Is there a way to execute multiple sql statements using the **same** sysdate?

I have a program which executes sql statements. Within a transaction, I'd like to update several tables using the same sysdate. eg. (run the following 3 statements in a transaction)

update table1 set some_col = 'updated' where some_other_col < sysdate;
delete from table2 where some_col < sysdate;
insert into table3 (col1, col2) select c1, c2 from table4 where some_col < sysdate;

If these 3 statements are executed in a transaction, the "sysdate" each one is using will be whatever timestamp we are at currently as this statement is running, not at the start of the transaction.

I could create a stored procedure and initially select the sysdate into a variable, using PL/SQL, but I'd prefer to just run sql statements from an external program.

Upvotes: 1

Views: 5947

Answers (3)

DCookie
DCookie

Reputation: 43523

Which external program? If you are using SQL*Plus, this would work:

var d char(50)
begin select sysdate into :d from dual; end;
/

update table1 set some_col = 'updated' where some_other_col < :d;
delete from table2 where some_col < :d;
insert into table3 (col1, col2) select c1, c2 from table4 where some_col < :d;

You might have to adjust your NLS_DATE_FORMAT setting for the session...

Upvotes: 0

red
red

Reputation: 2050

I'm afraid it's working as it's supposed to, the time is recalculated with each query. Just store the timestamp in a variable in your program, and use that variable in your queries.

Upvotes: 0

tbone
tbone

Reputation: 15473

I could create a stored procedure and initially select the sysdate into a variable, using PL/SQL, but I'd prefer to just run sql statements from an external program

Use an anonymous block instead of a stored procedure, something like (untested):

declare
  v_sysdate date := sysdate;
begin
  update table1 set some_col = 'updated' where some_other_col < v_sysdate;
  delete from table2 where some_col < v_sysdate;
  insert into table3 (col1, col2) select c1, c2 from table4 where some_col < v_sysdate;
  commit;
exception
  when others then 
  rollback;
  raise;
end;

Upvotes: 5

Related Questions