Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Santosh.

Asked: October 09, 2000 - 3:56 pm UTC

Last updated: May 22, 2012 - 8:02 am UTC

Version: 8.1.5

Viewed 10K+ times! This question is

You Asked

Hi Tom
Ive written a piece of code in shell to extract data from various tables;;wherein eac table is conatained in several schemas.. The problem im facing is that the spool file contains some sql lines which are unwanted...
hereis the text of the shell program

while read TABLE
do
while read USER
do
sqlplus -s $ouser/$opassword <<EOF
set array 100
set pages 0
set wrap off
set flush off
set feed off
set feedback off
set echo off
set verify off
set term off
set head off
set trim on
set colsep "|"
set linesize 800

spool $USER.dat
select '$USER', $TABLE.* from $USER.$TABLE;
!cat $USER.dat >> $TABLE.dat
!rm $USER.dat
spool off
exit
EOF
done < user.lst ((CONTAINS SCHEMA names))
done < tables.lst (( CONTAINS TABLE names ))
_____________________________________________________

with this above routine a file $TABLE.dat is created..
The problem in this file is that besides conating the data it also contains certain SQL lines ..ill show a piece of data file to show the ouput..

SQL> select 'ABTASMA001', ALLNETWORKASSETS.* from ABTASMA001.ALLNETWORKASSETS;
colval11 | colval12 | colval13 |
colval21 | colval22 |colval23 |
SQL> select 'ABCD', PERF_FACT_T.* from ABCD.PERF_FACT_T;
colval31 | colval32 |col val 33|
.............
............
SQL> select "EFGH' , XYZ.* from EFGH.XYZ;
...............
.................


and so on...
Kindly tell how to remove these """SQL> select 'EFGH',XYZ.* from EGFH.XYZ"" lines from my spool files.
Also tell a way so that i can add the unix system date to my filename while spooling...somethin like $TAVBLE0910.dat
thanks


and Tom said...

Don't use spool -- use redirection OR
Use spool but put the script into a temp file and just run

sqlplus u/p @tempscript.sql


To use redirection and name the file after "today", you can:

#!/bin/csh

setenv UNAME SCOTT
setenv TABLE EMP

sqlplus -s scott/tiger <<EOF >> $TABLE"_`date +%j_%Y`.dat"
set array 100
set pages 0
set wrap off
set flush off
set feed off
set feedback off
set echo off
set verify off
set term off
set head off
set trim on
set colsep "|"
set linesize 800

select '$UNAME', $TABLE.* from $UNAME.$TABLE;
exit
EOF


Rating

  (19 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How to supress a warning message genrate in spool file

kumar, February 12, 2002 - 3:37 pm UTC

Tom,

How can I supress a particular warning message that generate in my spool file. When I create procedure(s), some times the proceudures has a dependecy on other obejects. So at the time of Creation they throw an warning in the spool file like below.

Warning: Procedure created with compilation errors.

I don't want this warning in my spool file, but I want the rest like any ORA-XXX errors. Can you suggest me a way how I can produce the spool file with this warning supress.


what is trim on, flush off

ravi, October 22, 2002 - 2:40 pm UTC

tom,

can you tell us what for these

set trim on
set flush off

Tom Kyte
October 22, 2002 - 4:10 pm UTC

fortunately we document all of these things -- makes it easier to remember them!


</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a82950/ch8.htm#1001850 <code>




First Solution works for me.!

Sekhar, May 05, 2004 - 10:55 am UTC

Tom,
Your solution perfectly matched with my requirement.

Thanks a lot.

SC

A reader, December 10, 2004 - 9:00 am UTC 

Hi Tom,

create table sqlp ( x varchar2(3), y number(2));
insert into sqlp values ('a',1);


SQL> set colsep '|'
SQL> select x,y from sqlp;

X  |         Y
---|----------
a  |         1

Is there any way we can get result as:

a|1

unless running select x||y from sqlp; 
without colsep set
Thanks!

Tom Kyte
December 10, 2004 - 11:00 am UTC

sqlplus is going to want to fix width the columns. it is a reporting tool.

see
</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

spool and trim

Igor, August 12, 2005 - 6:51 am UTC

Hi Tom,

In SQL like select 'PART2|| ' from dual;

when stored in file (spooled or redirected) ending blanks are either trimmed or set as per linesize limit?
Is there anything with which sqlplus would treat in different manner these blanks and store it as "PART2|| " ?
(version 9.2)

Thank you.

Tom Kyte
August 12, 2005 - 8:46 am UTC

trimspool either trims or not, you would have to put something "not whitespace" at the end to preserve it.

Igor, August 12, 2005 - 9:21 am UTC

Thank you for your answer.

It's a pity that it could not store just "produced" part...


Tom Kyte
August 12, 2005 - 9:33 am UTC

sqlplus is just a simple, very simple, command line tool to interact with the database.

Igor, August 12, 2005 - 11:43 am UTC

Yes, I forget it sometimes :-)
I guess I miss standard unloader here...



silent option in SQLPLUS

aman, August 12, 2005 - 11:13 pm UTC

Sir,
I was reading the ORAFAQ.There was this option of starting the SQLPKLUS with the "-s" option(silent).I tried it but I couldnot get what exactly is the use of this option.Why this option is used?
with best regards
Aman

Tom Kyte
August 13, 2005 - 9:55 am UTC

generally when using redirection to a file, so the sqlplus banner doesn't appear in the output.

Avoid extra line at the end of spool file

A reader, September 20, 2006 - 1:06 am UTC

How to avoid extra line that comes at the end of spool file.

Tom Kyte
September 20, 2006 - 2:59 pm UTC

don't know what "extra line" you mean, if you are writing a data unloader, maybe this will be of interest however:

</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

About set colsep usage in this topic

Richard Tan, January 25, 2007 - 9:37 am UTC

Hi Tom,

Thank you very much for your previous help, which I appreciated a lot. I have one related issue.

In sqlplus session, I want to use
set colsep ... and use TAB as separater in order to display select statement output properly, I know how to do it using chr(9) in each select statement individually, but I want to use set colsep to set universally in sqlplus, how?

I have tried a variety of ways, it does not work. Please advice.

Tom, thank you very much for advice.

Richard Tan

Any performance/network options in SQL Plus?

Mike, August 12, 2008 - 10:18 am UTC

I frequently run lengthy SQL scripts via SQLPlus (for Windows) over a VPN connection, and the echoing of script lines is sometimes brutally slow. Are there any options for changing the buffer size for SQL Plus to read the script file, or any other suggestions that may affect this?

I tried SET ECHO OFF but that did not seem to help - which leads me to believe it is the I/O from the script file. I also saw SET ARRAYSIZE but that sounds like it is only for row retrieval from the database - again, not I/O from the script file.
Tom Kyte
August 13, 2008 - 4:35 am UTC

set termout off
....
set termout on



sqlplus reads locally until it gets a sql statement, it then post-processes it (replacing &1 and the like), figures out what it is (select, update, begin, whatever) and then submits it to the database. If it is screen IO that is the problem for you - put termout off in your scripts and termout on at the end... spooling and such will still work, it just won't do IO to the screen

dead link...

Raj, February 17, 2010 - 9:21 pm UTC

Hi Tom,
Is there any chance that you've got an updated link for the one mentioned above ( http://asktom.oracle.com/~tkyte/flat/index.html ).

Thanks!
Tom Kyte
February 18, 2010 - 8:33 am UTC

see home page for new master link.

Spool even when window is closed

Snehasish Das, November 11, 2011 - 12:20 pm UTC 

Hi Tom,

Hope you are doing well. I have a question on spooling.
Client is on windows. Oracle version is 10.2.0.5

sql> spool c:\a.txt
sql> select * from dual;
....
...
sql> select * from emp;
....
....
sql> select * from gb_size_table; -- millions of records;
--- we close the sqlplus client window now as we have by mistake selected data from a table which has millions of records.

Now the spool file will not have any results. But we wanted that the output of dual and emp to be there in spool

what setting do we need to do for this

Regards,
Snehasish Das

Tom Kyte
November 11, 2011 - 12:31 pm UTC



spool off after each query. then spool c:\a.txt APPEND before each subsequent query.

Thanks

Snehasish Das, November 11, 2011 - 12:37 pm UTC

Hi Tom,

Thanks a lot, Nice option ;).
But isn't there any way we can force the client to write the data as soon as got into the spool file instead of the write limit buffer (which i guess is 8K).

Thanks and Regards.
Snehasish Das
Tom Kyte
November 11, 2011 - 12:50 pm UTC

that would make it *really* utterly slow.

I afraid it is buffered output. It is part of the C printf stuff - that is all buffered IO.


Spooling

snehasish das, November 11, 2011 - 1:17 pm UTC

Hi Tom ,
Thanks a lot.

What i was doing is, whenever i login, I have set the glogin to create a spool file. So each login of me creates a spool file with the date and the instance. So the spool Off and Append would become a bit tedious. So was thinking of writing what ever was present before the close of window to the spool file.

Thanks a lot
Snehasish Das
Tom Kyte
November 11, 2011 - 2:53 pm UTC

windows just kills us, there is no opportunity to "flush the buffer". sorry.

set colsep

A reader, May 21, 2012 - 8:01 pm UTC

Hello Sir,

Is it possible to use special characters (BELL - Chr(7), Tab etc) as column separator? If yes then can you pl. show me how to do this?

Thanks
Tom Kyte
May 21, 2012 - 8:08 pm UTC

just:

set colsep "<whatever character you want>"

I just used vi, put that in a script with a tab and viola - done. tab separated columns (they will be formatted with spaces)

Are you having an issue with this? Perhaps "user friendly" windows is getting in your way and you need a more basic (something that does what you tell it to) editor?


You could use:

http://asktom.oracle.com/Misc/httpasktomoraclecomtkyteflat.html

the sqlldr unloader script (uses sqlplus spool) to unload with tabs or pipes or whatever.

A reader, May 22, 2012 - 12:59 am UTC

Thank you sir

I am also using vi.. my shell script is as under. but my output file does not print BELL as separator but it print chr(7).

Query="SELECT
empno,
replace( translate( TO_CHAR(LOGDATE,'YYYY-MM-DD HH24:MI:SS'), chr(9) || chr(13) || chr(10) , 'X'), 'X', '') ,
name
from emp
;"

SpoolFile=c.txt

sqlplus -s 'user/pass@db' << EOF
set echo off
set feed off
set heading off
set termout off
set trimout on
set colsep chr(7)
set verify off
set feedback off
set head off
set linesize 10000
set pagesize 0
set trimspool on
set tab off

SPOOL ${SpoolFile}
${Query}
SPOOL OFF;

EOF

Tom Kyte
May 22, 2012 - 8:02 am UTC

no, you actaully put the character in there, not a sql function. sqlplus set commands are not sql.

ops$tkyte%ORA11GR2> !cat test.sql
set colsep "    "
select ename, empno, hiredate from scott.emp;

ops$tkyte%ORA11GR2> !od -c test.sql
0000000   s   e   t       c   o   l   s   e   p       "  \t   "  \n   s
0000020   e   l   e   c   t       e   n   a   m   e   ,       e   m   p
0000040   n   o   ,       h   i   r   e   d   a   t   e       f   r   o
0000060   m       s   c   o   t   t   .   e   m   p   ;  \n
0000075



I have an actual tab in there.

study111, September 03, 2012 - 10:35 pm UTC

hi,

I am referring to the first question(spooled file contains SQL statements). Thank you so much for the answer!! it worked for me. It is a simple solution but yet so very correct!!

spool a procedure body

Aromal, February 18, 2014 - 10:37 am UTC 

Hi Tom,

 When I try to spool a procedure body through sqlplus, I always get truncated data.
Eg: 
SQL> spool C:\spool.txt

SQL> Select Dbms_Metadata.Get_Ddl('PROCEDURE', 'SAMPLE PROCEDURE') from dual;

SQL> spool off;

Could you please let me know if there's any way to get the whole procedure body without any truncation from SQL Plus through spool?

Thanks

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.