Solved

Add sysdate to file name with spool

Posted on 2001-08-16
9
3,627 Views
Last Modified: 2012-12-18
I want to run spool and add the sysdate to the end of the file name.  How can I do this in a sql script.  Basically I want to   spool C:\temp\newfile||sysdate.  This script will run and write out to NT O/S.  
0
Comment
Question by:tech84
9 Comments
 
LVL 9

Accepted Solution

by:
dbalaski earned 29 total points
ID: 6395486
Hi,
I've answered a question like this before,  pretty easy to do  .

In SQLPlus I would do something like this:
==================================================
set verify off timing off echo off heading off feedback off
set pagesize 0 linesize 150
spool x.sql
select 'spool '||to_char(sysdate,'YYYYMMDD')||'.log' from dual;
/
<other commands in select or prompt statments>
spool off
@x.sql
set verify on timing on echo on heading on
set feedback on pagesize 20 linesize 80
==================================================

I've used this method fairly successfully in several scripts...

Another method  which is a variation of the above script
instead of putting the commands inside the script,  
call the other script which you wish to run  at the end of your processing,  then spool off..
It beat writing the script as dynamic sql...  

This below example is actual output done with Oracle running on Solaris
The same principal will work on any other operating system.
=================================================
$ ls
query_script.sql  spool_script.sql
$
$ cat   spool_script.sql
set verify off timing off echo off heading off feedback off
set pagesize 0 linesize 150
spool x.sql
select 'spool '||to_char(sysdate,'YYYYMMDD')||'.log' from dual;
/
select '@query_script.sql' from dual
/
select 'spool off' from dual
/
spool off
set verify on timing on echo on heading on
set feedback on pagesize 20 linesize 80
@x.sql
$
$
$ # this is the script to run
$
$ cat query_script.sql
select tablespace_name, status  from dba_tablespaces;
select DBID, NAME, CREATED  from v$database;
$
$ sqlplus /

SQL*Plus: Release 8.1.6.0.0 - Production on Thu Aug 16 22:59:16 2001

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> @spool_script.sql
spool 20010816.log
spool 20010816.log
@query_script.sql
spool off
SQL> set feedback on pagesize 20 linesize 80
SQL> @x.sql
SQL> spool 20010816.log
SQL> spool 20010816.log
SQL> @query_script.sql
SQL> select tablespace_name, status     from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
USERS                          ONLINE
TOOLS                          ONLINE
TEMP                           ONLINE
APPL_D                         ONLINE
APPL_I                         ONLINE
RBSA                           ONLINE

7 rows selected.

Elapsed: 00:00:00.16
SQL> select DBID, NAME, CREATED  from v$database;

      DBID NAME      CREATED
---------- --------- ---------
 953726247 DEMO_T    25-JUN-01

1 row selected.

Elapsed: 00:00:00.01
SQL> spool off
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
$ ls
20010816.log      query_script.sql  spool_script.sql  x.sql
$
$ # lets see the output saved in the date-named logfile
$ cat 20010816.log
SQL> @query_script.sql
SQL> select tablespace_name, status  from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
USERS                          ONLINE
TOOLS                          ONLINE
TEMP                           ONLINE
APPL_D                         ONLINE
APPL_I                         ONLINE
RBSA                           ONLINE

7 rows selected.

Elapsed: 00:00:00.16
SQL> select DBID, NAME, CREATED  from v$database;

      DBID NAME      CREATED
---------- --------- ---------
 953726247 DEMO_T    25-JUN-01

1 row selected.

Elapsed: 00:00:00.01
SQL> spool off
$

=================================================

Hope this helps answers your question.
sincerely,
dBalaski
0
 

Author Comment

by:tech84
ID: 6396966
dBalaski
The first set of code only puts the date into a text file.  I want the actual text file name to include the date, not the contents inside the file. Can you help?  Thanks
0
 
LVL 9

Expert Comment

by:dbalaski
ID: 6397841
Hi tech84

Maybe I do not uderstand --  the way I presented it, this will build an output log withdate in the name of the scripts that you run.  
That is what you stated you wanted -- create a spool file with the system date as part of its name?

Re-read my answer through-- this is what my script does.

It works on the principal of dynamic sql.
1) spool_script.sql
   a)  generated a generic execution file (named x.sql)
       , with the first line being  the
         spool file  with the spool name being
         the sysdate
   b)  The second line in x.sql is on the name of the
       script(s) you wish to execute  (in my demo, it was
       a few simple queries in a file named  query.sql
   c)  the final line after it closes x.sql is then to
       call it for execution

The script:
When run, it generates a new temporary sql file,
with the first line being a spool with the systdate in the name.  Notice the last line   @x.sql  
it automatically runs the file it just generated.
========================================================
set verify off timing off echo off heading off feedback off
set pagesize 0 linesize 150
spool x.sql
select 'spool LOGFILE_DATE'||to_char(sysdate,'YYYYMMDD')||'.log' from dual;
/
select '@query_script.sql' from dual
/
select 'spool off' from dual
/
spool off
set verify on timing on echo on heading on
set feedback on pagesize 20 linesize 80
@x.sql
exit
=======================================================



here is the x.sql file it generated:  (remember this file has already run,  it is called in the 2nd to last line in the orginal file!)
=======================================================
spool LOGFILE_DATE_20010817.log
@query_script.sql
spool off
=======================================================

Notice the first line -- spool LOGFILE_DATE_20010817.log
(that is the sysdate   2001-08-17  )

so I can now look at the OS for the file by that name:
drwxr-xr-x   2 oracle   oinstall     512 Aug 17 11:44 .
drwxr-x---   6 oracle   dba         1024 Aug 16 22:48 ..
-rw-r--r--   1 oracle   oinstall    1200 Aug 17 11:38 LOGFILE_DATE_20010817.log
-rw-r--r--   1 oracle   oinstall      99 Aug 16 22:58 query_script.sql
-rw-r--r--   1 oracle   oinstall     348 Aug 17 11:38 spool_script.sql
-rw-r--r--   1 oracle   oinstall     453 Aug 17 11:38 x.sql

there it is!
inside the file:  LOGFILE_DATE_20010817.log
is the output of whatever commands were run inside the query script...

ie:
=======================================================
$ cat LOGFILE_DATE_20010817.log
SQL> @query_script.sql
SQL> select tablespace_name, status  from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
USERS                          ONLINE
TOOLS                          ONLINE
TEMP                           ONLINE
APPL_D                         ONLINE
APPL_I                         ONLINE
RBSA                           ONLINE

7 rows selected.

Elapsed: 00:00:00.00
SQL> select DBID, NAME, CREATED  from v$database;

      DBID NAME      CREATED
---------- --------- ---------
 953726247 DEMO_T    25-JUN-01

1 row selected.

Elapsed: 00:00:00.01
SQL> spool off
$
========================================================


So as you can see,  the DATE is part of the name of the SPOOL file,  not just a line inside the file.

Correct me if I am wrong -- that this is not what you are looking to do.


sincerely,

dBalaski

0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6398662
x.sql
------
col dt noprint new_value dt1
select sysdate dt from dual;
spool c:\temp\newfile&dt1
select *  from scott.emp;
spool off;


sql>@x.sql

C:\TEMP>dir *.lst
 Volume in drive C has no label.
 Volume Serial Number is 07D0-031C

 Directory of C:\TEMP

08/17/01  03:38p                 2,191 newfile17-AUG-01.LST
               1 File(s)          2,191 bytes
                            154,370,048 bytes free

Hope this helps

Sameer
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Expert Comment

by:Moondancer
ID: 6863382
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.

Question(s) below appears to have been abandoned. Your options are:
 
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you wish to do this, and allow for Expert response.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question. Again, please comment to advise the other participants why you wish to do this.

For special handling needs, please post a zero point question in the link below and include the question QID/link(s) that it regards.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer process.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Please click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues, to track all your open and locked questions at this site.  If you are an EE Pro user, use the Power Search option to find them.

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.20095876.html
http://www.experts-exchange.com/questions/Q.20117988.html
http://www.experts-exchange.com/questions/Q.20169853.html
http://www.experts-exchange.com/questions/Q.20182951.html
http://www.experts-exchange.com/questions/Q.20263350.html


To view your locked questions, please click the following link(s) and evaluate the proposed answer.
http://www.experts-exchange.com/questions/Q.20090225.html
http://www.experts-exchange.com/questions/Q.20092450.html
http://www.experts-exchange.com/questions/Q.20099508.html
http://www.experts-exchange.com/questions/Q.20093487.html

PLEASE DO NOT AWARD THE POINTS TO ME.  
 
------------>  EXPERTS:  Please leave any comments regarding your closing recommendations if this item remains inactive another seven (7) days.  Also, if you are interested in the cleanup effort, please click this link http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
 
Thank you everyone.
 
Moondancer
Moderator @ Experts Exchange

P.S.  For any year 2000 questions, special attention is needed to ensure the first correct response is awarded, since they are not in the comment date order, but rather in Member ID order.
0
 
LVL 9

Expert Comment

by:dbalaski
ID: 6863573
The solution of using  SVRMGRL and not SQLPLUS is really falling back into the academic.

Oracle has officially dropped the SVRMGRL product in favor of SQLPLUS  with Oracle 9i  because you can do everything and more in sqlplus now,   thus making the svrmgrl product redundant.

dBalaski
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7057928
Suggested disposition:

       Split points between: dbalaski and Wadhwa

DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7058139
Thanks, Dan.

Points have been split.
Points for Wadhwa -> http://www.experts-exchange.com/jsp/qShow.jsp?qid=20308364

Moondancer - EE Moderator
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7058143
**** tech84 *****  You logged in here as recently as May, and have not taken care of your business here.  Please click the links I posted above for you back in March, and close them.  If not, I will.  Has Administration not sent you an Email with directives and ultimatum?

Moondancer - EE Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now