SPOOL IN SQL PLUS

[ashley75]

Hi all,

In SQL plus, when you use SPOOL option to spool out the output to a file, is it possible for us to have the date concatnate with the file name.

We don't want to hard code the file name and date. what I am trying to do is if I spool this file today it will stamp today date on my file name and let's say tomorrow I run it again it will stamp the date of tomorrow on the file instead of overwriting today's file.

Any advises???
Thanks

[pando]

SQL> col today new_value mydate
SQL> set escape /
SQL> select sysdate today from dual;

TODAY
---------
2002SEP12

SQL> spool c:\&mydate/SPOOL.txt
SQL> select 1 from dual;

1
----------
1

SQL> spool off

[prakashs43]

Try this ( if u are using shell script )

set `date`

ORACLE_SID=orcl
export ORACLE_SID
sqlplus system/xxxx < spool temp1$2.$3log
select sysdate from dual ;
spool off
!

echo "\nJob Over"

[ashley75]

Pando,

thanks so much and your scripts work great; however, quick question.

what is the "set ESCAPE /" do???


I am trying to find out why I build your scripts into mine and it broke

thanks

[pando]

if i do

spool c:\&mydateSPOOL.txt

it will askme to enter a value for mydatespool, it will think my variable is mydateSPOOL instead of mydate. If I set escape / I can use / to seperate variable name and literals

just like UNIX

[JMac]

In the script I did ...


spool spoolfilename.sql

SELECT 'spool C:\Dest1\'||''File1_'||sysdate
FROM dual;
spool off

@spoolfilename

This spooled output to a File called, for example
File1_12-May-02
in my C:\Dest folder .

[ashley75]

Pando,

I have a quick question. I am trying to build your scripts with mine and I had some problem. Can you help me out???

Let's say if I am in the database A and run the below SQL:

SQL> col today new_value mydate
SQL> set escape /
SQL> select sysdate today from dual;

TODAY
---------
2002SEP12

SQL> spool c:\&mydate/SPOOL.txt
SQL> select 1 from dual;

1
----------
1
and then I have this SQL statement to connect to another database as follow:

SQL> connect system/manager@DATABASE_B
Enter password:

it asked me for the password. seem like I missed soemthing here.

Do you have any advises???

Thanks

[pando]

then set something else for escape or unset it easy as that

[jmodic]

You have set your escape character to "/". So when you do
CONNECT SYSTEM/MANAGER@DATABASE_B, Oracle treats character '/' as an escape character, so it thinks you want to connect as a user with username SYSTEMMANAGER and asks for a password for that user.

So before isuing CONNECT command, you must disable escape character or set it to some other character, using either
SET ESCAPE OFF or SET ESCAPE # (where you can replace # with any suitable character).

[ashley75]

thanks Pando and Jmodic