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.

Table 7-1. Date format strings

Date format string

Output

mm/dd/yy

11/15/61

dd-Mon-yyyy

15-Nov-1961

dd-mon-yyyy

15-nov-1961

Mon dd, yyyy hh:mm am

Nov 15, 1961 10:15 AM (or PM, depending on the time of day)

Month dd, yyyy

November 15, 1961


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.

     

Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net