Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: