OraFAQ Forum: SQL & PL/SQL » SQLPLUS command HELP!! Write data to Files

Home » SQL & PL/SQL » SQL & PL/SQL » SQLPLUS command HELP!! Write data to Files

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

SQLPLUS command HELP!! Write data to Files [message #214443]

Tue, 16 January 2007 08:21

umrigar_kunal
Messages: 10
Registered: December 2006
Location: pune

Junior Member

#1. Wanted to know if there are commands in sqlplus which can be used to wite multiple text files from sql script??

#2. Actually i want to write two files with different data one with customized "log" and other with the "Result Set"..
>is that possible through sql script??

#3. Tried using SPOOL but it just copies buffer to a file. But cannot append a file using spool.

IS there a SQLPLUS command which will write customized text to a file using SQL script.

I m using ORACLE 9.2.0 on Red Hat Linux

Thanks in advance!!!!

Report message to a moderator

Re: SQLPLUS command HELP!! Write data to Files [message #214479 is a reply to message #214443]

Tue, 16 January 2007 12:26

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Not really sure what you are asking. Issue multiple SPOOL commands in the sql file.
To append files, host out and use the cat command.

Report message to a moderator

Re: SQLPLUS command HELP!! Write data to Files [message #214481 is a reply to message #214443]

Tue, 16 January 2007 12:44

andrew again
Messages: 2577
Registered: March 2000

Senior Member

sqlplus has numerous title commands etc. for creating basic reports. Also - you can ouput your own lines...

prompt "This is line 1"

Report message to a moderator

Re: SQLPLUS command HELP!! Write data to Files [message #214536 is a reply to message #214481]

Tue, 16 January 2007 23:00

umrigar_kunal
Messages: 10
Registered: December 2006
Location: pune

Junior Member

Thank you Joy and Andrew!!

This is what i want to do..

***************SHELL SCRIPT** temp.sh***************

export ORACLE_HOME=/opt/oracle/product/9\.2\.0
export ORACLE_BASE=/opt/oracle
export ORACLE_SID=n01p
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

log=temp.log #Log File FOr SQL Logs
result=result.lst #Result set file

echo "
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING OFF

SPOOL $log
PROMPT SQL : SELECT Statement 1 Start
SPOOL OFF

SPOOL $result
SELECT name
FROM temp
WHERE id LIKE '%&1%' ;
SPOOL OFF

SPOOL $log
PROMPT SQL : SELECT Statement 1 END
SPOOL OFF

SPOOL $log
PROMPT SQL : SELECT Statement 2 Start
SPOOL OFF

SPOOL $result
SELECT salary
FROM temp
WHERE id LIKE '%&2%';

SPOOL OFF

SPOOL $log
PROMPT SQL : SELECT Statement 2 END
SPOOL OFF

EXIT " >temp.sql

sqlplus appx/ppxx @temp.sql 4 8

*********************SHELL SCRIPT*** END*************

But as soon as i write using SPOOL in the same file for the second time my previous data is lost.

Hope this has made the situation more clear..

Report message to a moderator

Re: SQLPLUS command HELP!! Write data to Files [message #214554 is a reply to message #214536]

Wed, 17 January 2007 00:32

Frank
Messages: 7901
Registered: March 2000

Senior Member

Save your logs in a global temporary table. (insert them).
When you're done, close the spool of the actual result and start spooling the logtable contents.

Report message to a moderator

Re: SQLPLUS command HELP!! Write data to Files [message #214666 is a reply to message #214554]

Wed, 17 January 2007 07:36

umrigar_kunal
Messages: 10
Registered: December 2006
Location: pune

Junior Member

Hey Thank you Frank!!

Seems a great idea ll see how well it works out... Thanks.. if possible can give me some good links On GTT.
Smile

regards
-kunal

Report message to a moderator

Re: SQLPLUS command HELP!! Write data to Files [message #215663 is a reply to message #214666]

Tue, 23 January 2007 07:27

umrigar_kunal
Messages: 10
Registered: December 2006
Location: pune

Junior Member

Used UTL_FILE to write multiple files

http://www.psoug.org/reference/utl_file.html

http://www.jlcomp.demon.co.uk/faq/utl_file.html

Smile

cheers!!!

Report message to a moderator

Re: SQLPLUS command HELP!! Write data to Files [message #216898 is a reply to message #214443]

Tue, 30 January 2007 16:18

Bill B
Messages: 1971
Registered: December 2004

Senior Member

utl_file is the way to go. However it has one limitation, the files must reside on the database server. Pity you were not on 10G, they finally expanded spool to do exactly what you wanted.

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

Report message to a moderator

Re: SQLPLUS command HELP!! Write data to Files [message #216900 is a reply to message #214443]

Tue, 30 January 2007 16:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Bill B.
I was unaware that the SPOOL capabilities had been expanded in V10.

Thanks!

Report message to a moderator

Re: SQLPLUS command HELP!! Write data to Files [message #216921 is a reply to message #214443]

Tue, 30 January 2007 21:26

ebrian
Messages: 2794
Registered: April 2006

Senior Member

If this is still an issue for the OP, if possible for you, you could install the 10g client into a separate ORACLE_HOME to access the 9i database and append to log files you need to create.

Report message to a moderator

Previous Topic:	help with cursors
Next Topic:	error in create sequence.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 00:33:20 CST 2024