-
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
-
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
-
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"
siva prakash
DBA
-
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
-
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
-
Here's how I did (I'm not SQL expert!)
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 .
-
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
-
then set something else for escape or unset it easy as that
-
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).
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|