Home » SQL & PL/SQL » SQL & PL/SQL » Output text filename from sql query
Output text filename from sql query [message #205454] Sat, 25 November 2006 00:14 Go to next message
cemehboob
Messages: 9
Registered: October 2005
Location: Rawalpindi
Junior Member

Hi,

I am facing a problem regarding file name, I generate a file from spool command and now i want that name of this file is current date and time


anybody want to help me.
Re: Output text filename from sql query [message #205464 is a reply to message #205454] Sat, 25 November 2006 01:15 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

In SQL*Plus this is what you can do to get the current date/time as part of the spool file:
============================================================================================
SQL> column tm new_value file_time noprint
SQL> select to_char(sysdate, 'YYYYMMDD') tm from dual ;




1 row selected.

SQL> prompt &file_time
20020816
SQL> spool C:\Temp\logfile_id&file_time..log
.
.
. /* put your code here */
. /* it will go to a file called C:\Temp\logfile_id20020816.log */
.
.
SQL> spool off
SQL>
http://forums.oracle.com/forums/thread.jspa;jsessionid=8d92200830de2132641f2bb44e30a023e520b6c118b9.e34QbhuKaxmMai0MaNeMb3aTc3v0?messageID=196147&#196147


hope this helps
taj
Re: Output text filename from sql query [message #205467 is a reply to message #205464] Sat, 25 November 2006 01:21 Go to previous messageGo to next message
cemehboob
Messages: 9
Registered: October 2005
Location: Rawalpindi
Junior Member

dear

thanx 4 ur reply, i did it it works perfect. Plz if u never mind plz guide me about outfile formatting and also guide can i pass a parameter from plsql block to sql which is call down after plsql block.

thanx

ejaz
Re: Output text filename from sql query [message #205468 is a reply to message #205467] Sat, 25 November 2006 01:23 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi

guide me about outfile formatting

which file spool file or what type of formatting.


guide can i pass a parameter from plsql block to sql which is call down after plsql block.

i am not getting ur point.

you talking about variable.. or something else..

taj
Re: Output text filename from sql query [message #205470 is a reply to message #205468] Sat, 25 November 2006 01:35 Go to previous messageGo to next message
cemehboob
Messages: 9
Registered: October 2005
Location: Rawalpindi
Junior Member

Dear,

I have selected about 15 column in txt spool file and it have also a remarks colum in middle, result is mixing everything. i want refine output plz.
my second problem is i used a plsql block above and from there i get a value. I want to use this value as parameter in spool query.


PlSql Block -----> Value
Spool Query .... between Value and Value + 20000;

thanx

Ejaz
Re: Output text filename from sql query [message #205471 is a reply to message #205470] Sat, 25 November 2006 01:42 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,
you mean "heading" display in middle of spool file if yes then

sql>set heading off
Re: Output text filename from sql query [message #205472 is a reply to message #205471] Sat, 25 November 2006 01:45 Go to previous messageGo to next message
cemehboob
Messages: 9
Registered: October 2005
Location: Rawalpindi
Junior Member

Dear,

Plz reply complete. I want more things i have mentioned in my previous posting plz

thanx
Re: Output text filename from sql query [message #205473 is a reply to message #205472] Sat, 25 November 2006 01:50 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi

yes i know but i am understand what r you want ?
PlSql Block -----> Value
Spool Query .... between Value and Value + 20000;
Re: Output text filename from sql query [message #205474 is a reply to message #205473] Sat, 25 November 2006 01:52 Go to previous messageGo to next message
cemehboob
Messages: 9
Registered: October 2005
Location: Rawalpindi
Junior Member

Dear

thanx fro reply and consideration


thanx once again

ejaz
Re: Output text filename from sql query [message #205476 is a reply to message #205474] Sat, 25 November 2006 01:59 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

You're welcome !!!


http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14357/ch8.htm#sthref1551

go through above link

for "SQL*PLUS SCRIPT TUNNING"


hope this helps
Taj
Re: Output text filename from sql query [message #205478 is a reply to message #205476] Sat, 25 November 2006 02:05 Go to previous message
cemehboob
Messages: 9
Registered: October 2005
Location: Rawalpindi
Junior Member

Dear

thanx, i will check it.

thanx once again

ejaz
Previous Topic: Can Use Connect statement into PL/SQL?
Next Topic: Problem in Sql Query...help to find it out..
Goto Forum:
  


Current Time: Sun Dec 11 02:12:24 CST 2016

Total time taken to generate the page: 0.08797 seconds