Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

spool file creating

646508May 18 2009 — edited May 18 2009
hi i am running a unix loop and in that creating a sql statement. now i want to spool the result to a file . but the problem is the spool file should have time as the name. loop runs after every 2 mins.




while [ true ];
do
sudo su - BATPBL<<!
sqlplus hr/hr<<!
spool /ankur/ank_XXXX #XXXX should be the time
select * from guard_usr order by guard_usr_id;
spool off
!
sleep 9
done


Please help
This post has been answered by Toni Lazarin on May 18 2009
Jump to Answer

Comments

Hoek
Hi,

See the examples over here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6894940174734
Toni Lazarin
Answer
You could implement in your spool project something like this :
[oracle@g5 ~]$ sqlplus fas_admin

SQL*Plus: Release 11.1.0.6.0 - Production on Mon May 18 11:27:25 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> !export DATE=$(date +"%m_%_d_%y %H:%M:%S")

SQL> spool datefile.txt$DATE
SQL> select me from you;
select me from you
               *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> spool off;
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@g5 ~]$ ls -l datefile.txt*
-rw-r--r-- 1 oracle oinstall 137 2009-05-18 11:28 datefile.txt05_18_09 11:23:39
[oracle@g5 ~]$
also you can visit http://en.wikipedia.org/wiki/Date_(Unix)
Marked as Answer by 646508 · Sep 27 2020
Hoek
Hi Toni,

That's a better solution ( less code = less bugs ;-) ) , thanks!
Toni Lazarin
anytime .. glad i could help
and glad you liked it ..
cheers Martijn :)
646508
Thanks toni n hoez...


Cheers

Khurana
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 15 2009
Added on May 18 2009
5 comments
884 views