| prasad |
Posted: Jun 12, 2008 06:08:03 AM |
Total Post: 35
Joined: Jan, 2007
|
Hello friends,
I want to spool a file from SQL.....& while spoolong the file I want to pass a value from SQL statement to that file name.
If it was normal spool file....then proper naming convention Or spoolong a file from Windows could have been a good solution.
I want to spool a file with passing a value to file name at run time.
I am spoolong a file every week...so I want to append the date to the file name. e.g: xyz20080506.lst
When I will spool file in the mext week the name would be xyz20080512.lst.
So I want to fetch the date from a table through SQL query & want to append that date to file name.
Can you help me out plz....?
|
|
|
 Geetha |
| Posted: Jun 12, 2008 07:52:04 AM |  |
|
Total Post: 21
Joined: Mar, 2008
|
Hi Prasad,
You can pass a value to at run time to spool a file. You can give like,
SPOOL $1
inside the file replacing,
SPOOL <<filename>>
While executing, give the run time value (i.e., the file name to create) along with the file name for execution.
Here, while execution the input value passed along with the file name will be created as a file in the name of the input value given by you in the run time.
Hope this'll help you out...
Let me know in case of any issues.
Regards,
Geetha
|
|
|
|
|
| prasad |
| Posted: Jun 12, 2008 08:39:52 AM | |
|
Total Post: 35
Joined: Jan, 2007
|
Thanks Geetha for replying.
I guess....the solution which you have given is for entering filename(parameter) manually.
But I do not want any manual work.
My SQL script is like this:
spool /dataproj/nyucc_sb/test.lst
insert into nyucc_sb
(select fileno from ny000ucc90
where fileno like '2008%' and substr(fileno,1,8) < (select filedate + 1 from curfiledate where appl='UCC' and stcode='NY' and dbflag = 'UD')
minus
(
select fileno from ny000ucc01
where fileno like '2008%' and filedate < 'select filedate + 1 from curfiledate where appl=''UCC'' and stcode=''NY'' and dbflag = ''UD'''
union
select ucc3num from ny000ucc04
where ucc3num like '2008%' and ucc3filedate < 'select filedate + 1 from curfiledate where appl=''UCC'' and stcode=''NY'' and dbflag = ''UD'''));
spool off
Here I am generating test.lst as a spool file.
I want to append result of following query to append the spool file name.
SELECT FILEDATE FROM DUAL;
So ultimately my spool file name should be xyz + "filedate".lst
So everytime I run my SQL script, it should fetch fildate from SQL query & it should append to spool file name.
Hope I have explained in details. Still if you are not getting plz let me know.
Thanks in advance
|
|
|
|
|
| Gitesh |
| Posted: Jun 12, 2008 11:05:49 PM | |
|
Total Post: 322
Joined: May, 2005
|
first login in database.
after that execute following.
col fname new_value filename
select 'D:\report\rep\report_' || to_char(trunc(sysdate),'DDMMYY') || '.txt' fname from dual;
spool &filename
Your spool file name contains report_sysdate
|
|
|
|
|
| prasad |
| Posted: Jun 13, 2008 03:00:16 AM | |
|
Total Post: 35
Joined: Jan, 2007
|
Thanks Gitesh.
Thank you very much for the help. :)
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
