I want to spool data with SYSDATE in the output filename (example: abc-20031117.txt if it runs on November 17)....
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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> ".
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.