Q

Dynamic SPOOL file naming

I want to spool data with SYSDATE in the output filename (example: abc-20031117.txt if it runs on November 17)....

I want to run this script in batch mode. Here's the best way I know:

COLUMN  v_ymd        OLD_VALUE       _ymd

SELECT  TO_CHAR (SYSDATE, 'YYYYMMDD')  AS v_ymd
FROM    dual;

SPOOL   abc-&_ymd..txt

Notes

COLUMN c OLD_VALUE s
This command tells SQL*Plus "When you SELECT a value into a column called c, put the same value into a substitution variable called s". (In the case of multi-row results, s gets the last value, after the ORDER BY is applied.) The NEW_VALUE clause does exactly the thing; the only difference between them occurs when the substitution variables appear in BTITLE or TTITLE commands, in which case OLD_VALUE means the value on the last row that appeared on the page before the break, and NEW_VALUE refers to the first value that will appear on the page after the break.

The double-dot in "abc-&_ymd..txt"
In this script, the first period signals the end of the substitution variable name; the second period is literally a period. Why? Suppose you wanted to call the output file "abc-20031117txt" (a valid file name). If you coded it as "abc&_ymdtxt" SQL*Plus would think you were referring to a variable called "_ymdtxt" (a valid variable name). That is, SQL*Plus interprets everything from the ampersand (&) up to the next character that is not valid in an Oracle name as being part of the variable name. When you need to explicitly mark the end of a variable name, use the character defined in a SET CONCAT command. The default character happens to be a period.

More Fun with OLD_VALUE

I use several different Oracle accounts in several different databases, often in different windows simultaneously. I put the following code in my LOGIN.SQL file to automatically change the SQL*Plus prompt to remind myself where and how each session is connected.

COLUMN  v_db_name       OLD_VALUE       _db_name
COLUMN  v_user_name     OLD_VALUE       _user_name

SELECT  LOWER (SYS_CONTEXT ('USERENV', 'DB_NAME'))  AS v_db_name,
        UPPER (USER)                                AS v_user_name
FROM    dual;

SET  SQLPROMPT       "&_db_name &_user_name> "

After running this, instead of getting a "SQL> " prompt, I get one like "test SCOTT> ".

This was last published in November 2003

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close