Home » SQL & PL/SQL » SQL & PL/SQL » SQLPLUS command HELP!! Write data to Files
icon11.gif  SQLPLUS command HELP!! Write data to Files [message #214443] Tue, 16 January 2007 08:21 Go to next message
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!!!!



Re: SQLPLUS command HELP!! Write data to Files [message #214479 is a reply to message #214443] Tue, 16 January 2007 12:26 Go to previous messageGo to next message
joy_division
Messages: 4644
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.
Re: SQLPLUS command HELP!! Write data to Files [message #214481 is a reply to message #214443] Tue, 16 January 2007 12:44 Go to previous messageGo to next message
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"
Re: SQLPLUS command HELP!! Write data to Files [message #214536 is a reply to message #214481] Tue, 16 January 2007 23:00 Go to previous messageGo to next message
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..
Re: SQLPLUS command HELP!! Write data to Files [message #214554 is a reply to message #214536] Wed, 17 January 2007 00:32 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: SQLPLUS command HELP!! Write data to Files [message #214666 is a reply to message #214554] Wed, 17 January 2007 07:36 Go to previous messageGo to next message
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
Re: SQLPLUS command HELP!! Write data to Files [message #215663 is a reply to message #214666] Tue, 23 January 2007 07:27 Go to previous messageGo to next message
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!!!
Re: SQLPLUS command HELP!! Write data to Files [message #216898 is a reply to message #214443] Tue, 30 January 2007 16:18 Go to previous messageGo to next message
Bill B
Messages: 1484
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]
Re: SQLPLUS command HELP!! Write data to Files [message #216900 is a reply to message #214443] Tue, 30 January 2007 16:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Bill B.
I was unaware that the SPOOL capabilities had been expanded in V10.

Thanks!
Re: SQLPLUS command HELP!! Write data to Files [message #216921 is a reply to message #214443] Tue, 30 January 2007 21:26 Go to previous message
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.
Previous Topic: help with cursors
Next Topic: error in create sequence.
Goto Forum:
  


Current Time: Sat Dec 10 02:58:13 CST 2016

Total time taken to generate the page: 0.07882 seconds