Re: Help on SQL*Plus Command

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Wed, 08 Sep 1999 01:12:54 GMT
Message-ID: <37d5b606.13228351@netnews.worldnet.att.net>

On Tue, 07 Sep 1999 16:27:08 -0500, Chiunta Chen <chiunta_at_worldnet.att.net> wrote:

>Here is what I tried to accomplish
>
>ACCEPT W_DATE_BEG PROMPT "Enter Period Start Date (mmddyyyy): ";
>ACCEPT W_DATE_END PROMPT "Enter Period End Date (mmddyyyy): ";
>
>I want to generate a spool file with names like BTyymmdd in c:\temp.
>I was stumped at

This is possible to. Try the following:

ACCEPT W_DATE_BEG PROMPT "Enter Date (mmddyyyy): " SPOOL c:\a\BT&w_date_beg

prompt this is a test
spool off

If you must reverse the date, i.e. you want to enter it as mmddyyyy, but use yymmdd in the filename, then your task is more difficult. Try this approach:

ACCEPT W_DATE_BEG PROMPT "Enter Date (mmddyyyy): " COLUMN file_name NOPRINT NEW_VALUE file_name SET TERMOUT OFF
SELECT 'c:\a\BT' ||

       TO_CHAR(TO_DATE('&w_date_beg','mmddyyyy')
              ,'yymmdd') file_name

FROM dual;
SET TERMOUT ON
SPOOL &file_name
prompt this is a test
spool off

SQL*Plus doesn't support expressions. In order to manipulate values in substitution variables you have to drop into SQL (or PL/SQL), and resort to tricks like that shown above.

Jonathan

---

jonathan_at_gennick.com
http://gennick.com
Brighten the Corner Where You Are Received on Tue Sep 07 1999 - 20:12:54 CDT