Tuesday, August 30, 2011

Using NEW_VALUE to Record Start/End Time in Oracle SQL Script

In a SQL script, I need to record the start and end time of a block which performs some DMLs, then I will query the records being DML'ed during the peroid.

COLUMN CHAR_SYSDATE NEW_VALUE START_DATETIME
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') CHAR_SYSDATE FROM DUAL;

......
...The block
......

COLUMN CHAR_SYSDATE NEW_VALUE END_DATETIME
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') CHAR_SYSDATE FROM DUAL;
 
SELECT ... FROM ...
 WHERE audit_creation_date BETWEEN TO_DATE('&START_DATETIME','YYYYMMDDHH24MISS') AND TO_DATE('&END_DATETIME','YYYYMMDDHH24MISS');

No comments:

Post a Comment