OraFAQ Forum: SQL & PL/SQL » Output text filename from sql query

Home » SQL & PL/SQL » SQL & PL/SQL » Output text filename from sql query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Output text filename from sql query [message #205454]

Sat, 25 November 2006 00:14

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.

Report message to a moderator

Re: Output text filename from sql query [message #205464 is a reply to message #205454]

Sat, 25 November 2006 01:15

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

Report message to a moderator

Re: Output text filename from sql query [message #205467 is a reply to message #205464]

Sat, 25 November 2006 01:21

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

Report message to a moderator

Re: Output text filename from sql query [message #205468 is a reply to message #205467]

Sat, 25 November 2006 01:23

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

Report message to a moderator

Re: Output text filename from sql query [message #205470 is a reply to message #205468]

Sat, 25 November 2006 01:35

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

Report message to a moderator

Re: Output text filename from sql query [message #205471 is a reply to message #205470]

Sat, 25 November 2006 01:42

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

Report message to a moderator

Re: Output text filename from sql query [message #205472 is a reply to message #205471]

Sat, 25 November 2006 01:45

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

Report message to a moderator

Re: Output text filename from sql query [message #205473 is a reply to message #205472]

Sat, 25 November 2006 01:50

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;

Report message to a moderator

Re: Output text filename from sql query [message #205474 is a reply to message #205473]

Sat, 25 November 2006 01:52

cemehboob
Messages: 9
Registered: October 2005
Location: Rawalpindi

Junior Member

Dear

thanx fro reply and consideration

thanx once again

ejaz

Report message to a moderator

Re: Output text filename from sql query [message #205476 is a reply to message #205474]

Sat, 25 November 2006 01:59

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

Report message to a moderator

Re: Output text filename from sql query [message #205478 is a reply to message #205476]

Sat, 25 November 2006 02:05

cemehboob
Messages: 9
Registered: October 2005
Location: Rawalpindi

Junior Member

Dear

thanx, i will check it.

thanx once again

ejaz

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 10:54:18 CST 2024