Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Getting the Current Date into a Header

You saw how to display the contents of a substitution variable in the header back in Chapter 5 when the Project Hours and Dollars Detail report was converted to a master/detail style. You saw how to use the COLUMN command to tell SQL*Plus to continuously update the contents of a substitution variable with the value of a column in the query. Getting the system date to display in the header involves a little trick that takes advantage of this use of the COLUMN command. The trick is to execute a query that returns the current date and use the NEW_VALUE clause of the COLUMN command to get that date into a substitution variable. That substitution variable sticks around for the duration of the session and can be used in subsequent reports.

Getting the Date from Oracle

I use the built-in SYSDATE function in the following example to return the current date from the database. Notice that the NEW_VALUE option of the COLUMN command is used to update the user variable report_date with the current value of SYSDATE as returned from the database.

COLUMN SYSDATE NEW_VALUE report_date
SELECT SYSDATE FROM DUAL;

SYSDATE is an Oracle built-in function that returns the current date and time. DUAL is a special Oracle table that always exists, always contains exactly one row, and always contains exactly one column. You can select SYSDATE from any other table, but DUAL works well because it returns only one row, which is all you need to return the date.

Tip

The date and ...