Dynamic SPOOL file naming

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