How to add sysdate to the output file(spool) name in sqlplus [message #312023] |
Mon, 07 April 2008 18:54  |
bibsdash
Messages: 47 Registered: April 2008
|
Member |
|
|
HI
I want to create a sqlplus spool file whose filename will have date it was run appended to it.
See the spool file name below
(spool c:\temp\purchase_no_vdate.xls).
For example
**********************************************
declare
v_date date;
begin
select sysdate
into v_date from dual;
spool c:\temp\purchase_no_vdate.xls
select po_number purchase no
,creation_date purchase date
from po_details;
spool off;
exit;
*****************************************************
|
|
|
|
|
|
Re: How to add sysdate to the output file(spool) name in sqlplus [message #312055 is a reply to message #312023] |
Mon, 07 April 2008 20:51   |
bibsdash
Messages: 47 Registered: April 2008
|
Member |
|
|
Anayway, I got the answer after I googled:
In case someone needs it here it is:
***************************************************
Dynamically set name of spool file in SQL*Plus
Small tip for today. Often there is a need to set name of spool file based on current time and might by also database you are connected to. It's often needed when script is run as scheduled task and there is a need to generate files with different names. So here is an solution for using date:
define logname=date
column clogname new_value logname
select 'prefix_'||to_char(sysdate, 'yyyymmdd') clogname from dual;
spool '&logname'If there is a need to have also name of database included into filename then it can be read from global_name view:
define logname=date
column clogname new_value logname
select 'prefix_'||substr(global_name,1,
decode(dot,0,length(global_name),dot-1))||
to_char(sysdate, 'yyyymmdd') clogname
from (select global_name, instr(global_name, '.') dot from global_name);
spool '&logname'
*************************************************
Another link :
http://www.orafaq.com/forum/m/205454/102589/?srch=date+spool+file+name#msg_205454
|
|
|
Re: How to add sysdate to the output file(spool) name in sqlplus [message #312067 is a reply to message #312023] |
Mon, 07 April 2008 22:07   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
bibsdash (an expert in his own mind),
>I think the nature of the question and its comlexity, it is for advanced pl/sql.
You don't know what YOU don't know. Solution has NOTHING to do with PL/SQL
>Do you have any checklist that can help us?
So, before posting here ask yourself the following:
- Do I consider myself an expert?
- Do I think the problem at hand is a difficult one?
- Is my problem strictly PL/SQL-related?
If all three are answered "Yes", you are most welcome to post here!
SQL> set term on echo on
SQL> declare
2 v_date date;
begin
3 4 select sysdate
into v_date from dual;
5 6
7 spool c:\temp\purchase_no_vdate.xls
8
9 select po_number purchase no
10 ,creation_date purchase date
11 from po_details;
12 13 spool off;
14 15 exit;
16 /
spool c:\temp\purchase_no_vdate.xls
*
ERROR at line 7:
ORA-06550: line 7, column 7:
PLS-00103: Encountered the symbol "C" when expecting one of the following:
You need to learn the difference between SQL, SQL*Plus, & PL/SQL!
All are different from each other.
SPOOL is a SQL*Plus command.
When using SELECT inside PL/SQL the INTO clause is required & is missing for
SELECT starting on line #9.
Your spool file is NOT a spreadsheet file (.xls)!
IMO, a simpler solution exists below.
spool date.sql
select 'spool \temp\purchase_no_' || to_char(sysdate, 'YYYY-MM-DD') || '.lis' FROM dual;
spool off
@date.sql
select po_number purchase no ,creation_date purchase date from po_details;
spool off;
Not exactly EXPERT PL/SQL complexity!
|
|
|
|
|
|