Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

7.2 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.

7.2.1 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.

The date and time returned by SYSDATE reflect the location of the database server, not the client. If you are using a PC to access data on a remote database in a different time zone, you may want to investigate the use of CURRENT_DATE, which was introduced in Oracle9 i Database and which returns the date and time in the session time zone.

7.2.2 Formatting the Date

You may find that the date format returned by SYSDATE is not what you
would prefer. It depends on the setting of the
NLS_DATE_FORMAT parameter, which can
vary from one database to the next. You can use the
ALTER SESSION statement to specify a
different format:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY';

ALTER SESSION changes the format for the duration of the SQL*Plus
session. Make sure you execute it prior to selecting SYSDATE from
DUAL. Another alternative is to use the built-in
TO_CHAR function to specify a format.

COLUMN current_date NEW_VALUE report_date
SELECT TO_CHAR(SYSDATE,'DD-Mon-YYYY') current_date FROM DUAL;

I specify a column alias of current_date in this
example to give a usable name to the date column, one that could be
used easily with the COLUMN command.

Consider using ALTER SESSION to format all dates in your report rather than using TO_CHAR to format each date column separately. This makes your SELECT statements easier to write, gives you one point at which to make changes, and helps to ensure consistency.

Table 7-1 shows some typical date format strings
that may be used with Oracle's built-in TO_CHAR
function or with the ALTER SESSION statement.

You may or may not care whether the output of the SELECT SYSDATE
statement appears on the display, but you can suppress it by using
the
SET TERMOUT command to toggle the
display output off and then back on again. Here's
how to do that:

SET TERMOUT OFF
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY';
COLUMN SYSDATE NEW_VALUE report_date
SELECT SYSDATE FROM DUAL;
SET TERMOUT ON Finally, you need to add the date to the report header or the report
footer. Here's an example of how to do that using
the BTITLE command from the
Project Hours and Dollars Detail report:

BTITLE LEFT '=============================================================' -
SKIP 1 -
LEFT report_date -
RIGHT 'Page ' FORMAT 999 SQL.PNO The addition of LEFT report_date to this BTITLE
command causes the date to print left-justified on the same line as
the page number. When you execute the report, the page footer will
look like this:

================================================================
22-Feb-1998 Page 1 In addition to using this technique with the system date, you can use
it to retrieve any other value from the database for inclusion in a
report.