Home » SQL & PL/SQL » SQL & PL/SQL » How to add sysdate to the output file(spool) name in sqlplus (Oracle 8i)
How to add sysdate to the output file(spool) name in sqlplus [message #312023] Mon, 07 April 2008 18:54 Go to next message
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 #312025 is a reply to message #312023] Mon, 07 April 2008 18:58 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Are you incapable or unwilling to follow clear instructions????????

Before you post anything in this forum be aware that this forum is meant for Expert PL/SQL problems.
This does NOT mean that experts only read this forum; 99% also read the Newbie-forum!

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 you answer at least one of the above questions with "No" or "Don't know", find the appropriate forum here at OraFAQ.
If all three are answered "Yes", you are most welcome to post here!

Re: How to add sysdate to the output file(spool) name in sqlplus [message #312028 is a reply to message #312025] Mon, 07 April 2008 19:02 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
I think the nature of the question and its comlexity, it is for advanced pl/sql.

How do we determine it is a complex issue?
Do you have any checklist that can help us?
The questions you put wasnot that helping.
Sorry if I sound harsh.
Re: How to add sysdate to the output file(spool) name in sqlplus [message #312029 is a reply to message #312023] Mon, 07 April 2008 19:03 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
yes, try to match your question with the right forum. You must be aware that anecedent has the text of hist post hot keyed to F11 on his keyboard.

Sorry anecedent, I could not help myself after seeing your posts tonight. Kevin
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25042
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!
Re: How to add sysdate to the output file(spool) name in sqlplus [message #312071 is a reply to message #312023] Mon, 07 April 2008 22:22 Go to previous messageGo to next message
bibsdash
Messages: 47
Registered: April 2008
Member
anacedent

I am not claiming an expert.
I donot think anyone in this world can be able to answer all and say an expert.

Well, That is what I got from this link.So I pasted this.

http://pbarut.blogspot.com/2007/06/dynamically-set-name-of-spool-file-in.html


Now you have to go back to above blog( posted by Paweł Barut
Kraków, Poland) and tell him whether he is an expert.

Re: How to add sysdate to the output file(spool) name in sqlplus [message #312072 is a reply to message #312023] Mon, 07 April 2008 22:29 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Your original post in this thread was posted in PL/SQL Expert forum which contain the following guidelines
http://www.orafaq.com/forum/t/97952/74940/
which in part say
"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!"

IMO, the correct answer for all 3 questions are:
NO
NO
NO

So why do you ignore posting guidelines & never use <code tags>?
Re: How to add sysdate to the output file(spool) name in sqlplus [message #312075 is a reply to message #312023] Mon, 07 April 2008 22:32 Go to previous message
bibsdash
Messages: 47
Registered: April 2008
Member

anacedent

I am sorry.
I will take care of the same.
Previous Topic: Is tuning needed for this SQL ?
Next Topic: Oracle PL/SQL Batch Processing Reports
Goto Forum:
  


Current Time: Tue Dec 06 15:59:06 CST 2016

Total time taken to generate the page: 0.10620 seconds