Home » SQL & PL/SQL » Client Tools » can we force a paramter value to change the filename for every execution of sql script (SQL * PLUS)
icon5.gif  can we force a paramter value to change the filename for every execution of sql script [message #540578] Tue, 24 January 2012 11:27 Go to next message
dora
Messages: 13
Registered: January 2012
Junior Member
Hi Sir/Madam,

I wrote an sql script (spooler) which runs a select query and produces results into a file.

I want to have the filename with the year attached.
Can i pass a parameter when i run the script from SQL*PLUS

@'B:\ Year Of Financing\PLSQL\spoolers\Spooler_File_Creation' 01/01/2010 2010;

the first parameter 01/01/2010 is passed to the select query and taht works fine.

Since the output file contains 2010 data, I want the spooled file to mention in its name the 2010, so User's can know the file contains 2010 data in it.

So i wrote the statement within the spooler script

SPOOL X:\HARS\Organizations\FinancialDataFor&2.txt

&2 should take 2010 has its value and produce the file
FinancialDataFor2010.txt

but it is not doing it, Can anyone pls suggest how to use Substitution variables to produce the Filename i want.

Thank You for taking time, to read my question. Pls I need some help in forming the file name.

Thanks Again.

Re: can we force a paramter value to change the filename for every execution of sql script [message #540579 is a reply to message #540578] Tue, 24 January 2012 11:35 Go to previous messageGo to next message
BlackSwan
Messages: 22677
Registered: January 2009
Senior Member
http://www.lmgtfy.com/?q=oracle+spool+filename+date

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Tue, 24 January 2012 11:35]

Report message to a moderator

Re: can we force a paramter value to change the filename for every execution of sql script [message #540594 is a reply to message #540578] Tue, 24 January 2012 12:44 Go to previous messageGo to next message
dora
Messages: 13
Registered: January 2012
Junior Member
Sir,

I am producing the files, where select query returns data for the year I mention (for example:I want 2005 data)
The spooled file name should help me recognize that its 2005 data in it.
So the filename should be something like "filename2005.txt"

If i want 2007 data the file name should be "filename2007.txt"

I dont want system date. Pls let me know how to achieve it.

Presently i am running the spool script using
SQL*Plus worksheet

The command i give is

@'the path of the spool script\scriptname' paramter(ie.,01/01/2010, required for clause in select query);

Thank you. Pls let me know how to achieve the year next to the filename.
Re: can we force a paramter value to change the filename for every execution of sql script [message #540598 is a reply to message #540594] Tue, 24 January 2012 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 58840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So in the select to get the year instead of using "sysdate" use "substr('&1',7)".

Regards
Michel
Re: can we force a paramter value to change the filename for every execution of sql script [message #540605 is a reply to message #540598] Tue, 24 January 2012 13:17 Go to previous messageGo to next message
dora
Messages: 13
Registered: January 2012
Junior Member

Sir,

My question is,

The spooled file,ie., the filename should explain me what data it contains.

my select query within the spool script looks like this:

SPOOL C:\Programs\DATA&year..txt

select sno,name,school_id,
(select person_data from school
where to_date('&1','mm/dd/yyyy') between person_start_date
and person_end_date and person_age = 21 and county_id = T1.county_id),
(select person_data from school
where to_date('&1','mm/dd/yyyy') between person_start_date
and person_end_date and person_age = 22 and county_id = T1.county_id),
...............
from main T1,.......
SPOOL OFF;



Now i execute it in SQL*Plus worksheet like this

@'C:\Documents and Settings\Dora\SchoolInformation' 01/01/2005;

means &1 within the select query is substituted with 01/01/2005.
The file contains information such as
sno name school_id person_data

So when i look at the file, I dont know which year's data this file contains, if i give a fixed filename
Instead if i can generate a file with filename which contains year (2005) for 2005 data, its easy for me to know that the file generated contains 2005 data in it.

Hope someone understands what I am looking for, Pls help me, in creating the filename with the year I mention as parameter.

Thank You
Re: can we force a paramter value to change the filename for every execution of sql script [message #540607 is a reply to message #540605] Tue, 24 January 2012 13:19 Go to previous messageGo to next message
BlackSwan
Messages: 22677
Registered: January 2009
Senior Member
set termout off
col dt new_value dt
select to_char(sysdate,'YYYYMMDDHH24MISS') dt from dual;
set termout on
spool file_&dt
...
spool off
Re: can we force a paramter value to change the filename for every execution of sql script [message #540609 is a reply to message #540607] Tue, 24 January 2012 13:37 Go to previous messageGo to next message
dora
Messages: 13
Registered: January 2012
Junior Member
Sorry, But this is not what i am looking for.

Please see my explanation.

I dont want current year or sysdate.

Pls.
Re: can we force a paramter value to change the filename for every execution of sql script [message #540611 is a reply to message #540598] Tue, 24 January 2012 13:42 Go to previous messageGo to next message
dora
Messages: 13
Registered: January 2012
Junior Member
Sir,

In my script , I gave this statement

COL substr('&1',7) new_value year
SPOOL C:\Programs\SchoolInformation_&year..txt

But it doesnot give me the file name as

SchoolInformation_2010.txt

instead gives me,

SchoolInformation_OEM_sqlplus_input_finished.txt

I am not knowing from where it is getting
OEM_sqlplus_input_finished

Pls help me, in getting the right file name.Thank You.
Re: can we force a paramter value to change the filename for every execution of sql script [message #540612 is a reply to message #540611] Tue, 24 January 2012 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read SQL*PlusĀ® User's Guide and Reference.

Col yr new_value yr
select substr('&1',7) yr from dual;
spool X&yr..txt
...

Regards
Michel

[Updated on: Tue, 24 January 2012 13:53]

Report message to a moderator

Re: can we force a paramter value to change the filename for every execution of sql script [message #540613 is a reply to message #540611] Tue, 24 January 2012 13:57 Go to previous messageGo to next message
dora
Messages: 13
Registered: January 2012
Junior Member


Thank you everyone,

I have the answer for my question,


COLUMN year NEW_VALUE year
select 'C:\Programs\SchoolInformation_' || substr('&1',7) As year from sys.dual;
SPOOL &year..txt

--select query here

SPOOL OFF:
Re: can we force a paramter value to change the filename for every execution of sql script [message #540614 is a reply to message #540613] Tue, 24 January 2012 14:01 Go to previous message
Michel Cadot
Messages: 58840
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not use YEAR as variable name, it is a reserved word.

Regards
Michel
Previous Topic: Problem creating procedure using SQL Developer
Next Topic: Unable to Print The Query Output On TOAD
Goto Forum:
  


Current Time: Wed Aug 20 06:23:20 CDT 2014

Total time taken to generate the page: 0.10828 seconds