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 first published in November 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close