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