The exact question I was looking for.
Robert, February 12, 2003 - 4:37 pm UTC
Spool to an existing File
ANUP, March 25, 2003 - 6:30 am UTC
Is There Any option so that We Can Spool The Output Of a Sql SCRIPT File To An Existing File Without Overwriting it.
I Mean Can We open a file in Appeand mode by spool.
Thanks and regards
March 25, 2003 - 9:48 am UTC
no there is not.
Appending to a spool file
Mike, March 25, 2003 - 11:25 am UTC
It would be a nice enhancement to be able to append
to an existing spool file. The workaround now is to have the OS redirect the output to a file instead of appending,
and in the process do the appending...
I hope that Oracle would consider this sometime in the future...
March 25, 2003 - 12:06 pm UTC
The only way to get an enhancement request into the system is to develop a business case for it and file it as an official enhancement request using metalink.oracle.com -- the Oracle support web site.
If no one ever puts it into the system, it'll never have a chance of getting acted on except by sheer accident.
How to spool to any directory/filename
A reader, August 28, 2003 - 10:14 am UTC
I saw the example you provided to spool to any filename; passed as a parameter. But, is there a way to spool to a filename where the directory name has spaces. The directory name will not be known until run-time.
The script is
spool &spool_file
select Col1 from Table1 where Col3 = 'Y';
spool off;
exit;
And it I would like to run as
sqlplus user/pass@sid @my_script 'e:\AHD Desc\logs\My_file.txt'
August 29, 2003 - 7:45 am UTC
directories with spaces -- another great windoze innovation. even though you could do it on unix forever -- no one actually *did*. such a bad idea... cause of so many problems...
in anycase, you cannot specify a spool name with a space in it in 9i. you'll have to use the mapped 8.3 dos filename instead.
Spool to two different files
A reader, November 02, 2004 - 11:47 am UTC
Tom, do you know any way on *windoze* (HAH) to spool to two different files ?
I the manner of having a sql script like
spool filename_1.txt
spool off
spool filename_2.txt
spool off
Is that even possible ? Thank you!
November 03, 2004 - 5:34 am UTC
that works, no problem with that.
parameter of spool...!!!!
Sachin, December 18, 2004 - 5:26 am UTC
I dont want the queries in the spool file...
I need only query result.
December 18, 2004 - 8:59 am UTC
great, good thing that is the way it works then.
spool foo
select * from dual;
spool off
!cat foo.lst
produces
ops$tkyte@ORA9IR2> @test
D
-
X
D
-
X
ops$tkyte@ORA9IR2>
if you see queries in there, you set echo on yourself then. set it off
Spool
harish Patel, February 15, 2005 - 10:21 am UTC
Very nice & helpful foe me
Appending data to a spool file in unix
Alok Agarwal, March 07, 2005 - 8:09 am UTC
Hi,
Suppose I have a spool called alok
then i run that spool and it wrote some data in the file
now after exiting from the system i rerun the spool in unix what is does is overwrite the earlier data while i wanted to append the data already existing in that file.
if we use >> instaed of > it appends in current session but i want to append in different sessions
March 07, 2005 - 8:21 am UTC
<quote>
if we use >> instaed of > it appends in current session but i want to append in
different sessions
</quote>
not following you there, that is unix redirection, not spooling.
spool can append in 10g sqlplus (but not before)
>> would either work or not work all of the time in unix, unix doesn't know or care about sqlplus sessions.
"Nested" Spools
Job, September 27, 2005 - 2:41 pm UTC
Hi Tom,
Hope you are having a great week!
I am wondering if there is solution to this:
1. I have a main script (with multiple tables DDLs) that spools the results to a file (DMLoutput.lst)
2. There is another script (trigger generator) that reads the data dictionary after each table definition, spools the trigger script to a file (auditTbl_Trigger.sql) and runs it to generate the table's trigger. This leads to a "nested" spool case and kills the parent spool:
-- Script 1 (dbrealease.sql)
spool DMLoutput.lst
.
.
.
DMLs on tableA
--call trigger generator (generate_trigger.sql)
spool auditTableA_Trigger.sql
....
spool off
.
.
.
spool off
Any insight is greatly appreciated.
Thanks
September 27, 2005 - 3:09 pm UTC
sorry, no such concept as a 'nested spool', doesn't work that way.
I'd suggest they build the trigger in a string in PLSQL and then use execute immediate on it.
How to OMIT query results from spool?
Robert, December 07, 2005 - 12:18 pm UTC
Tom,
I am doing performance testing a query which returns 10M+ rows.
I want to spool out comments, begin time, end time, maybe explain plan, etc..... But I DON'T want to see the actual query results.
Is there a setting I can set, say, before running the query which will RUN the query (to get an actual test of the query) but not spool the results to the spool file?
Thanks,
Robert.
December 08, 2005 - 1:31 am UTC
set autotrace traceonly;
But I need to run the query to get the 'wall' time.
Robert, December 08, 2005 - 11:11 am UTC
Tom,
But I actually want to run the query... in order to get the actual elapsed time.
Thus...
spool sql_test1.log
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') BEGIN_TIME from dual;
set SHOW_OUTPUT_IN_SPOOL_FILE=FALSE
-- I don't care about seeing the query RESULTS
select count(*) from dba_objects;
set SHOW_OUTPUT_IN_SPOOL_FILE=TRUE
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') END_TIME from dual;
spool off
.........Now I can look at the spooled file, and quickly see the begin_time and end_time, without having to scroll down 30,000 rows of output.
Isn't there some such sql*plus setting that would do this (e.g."SHOW_OUTPUT_IN_SPOOL")?
Thanks,
Robert.
December 08, 2005 - 12:02 pm UTC
I'll say it again....
set autotrace traceonly
run the query
set autotrace off
Sorry for my DENSITY!
Robert, December 08, 2005 - 12:13 pm UTC
Tom,
I get it now.
Please forgive my laziness and thick-headedness!
Thanks,
Robert.
How to avoid query in spool
nelson, December 29, 2005 - 10:51 am UTC
Hi,
I am running a query from shell script.
select 'alter index '||index_name||' rebuild tablespace '||tablespace_name||' online ;' from user_indexes where blevel>2 ;
the result is query. I need to put the result in sql file. and then execute it.
problem iam facing : when i spool i even get the above query with result.and i am not able to execute the file .
please help me
December 29, 2005 - 1:02 pm UTC
no you don't, you don't want to run that query. I see what it thinks it is doing (rebuild everything in sight with a blevel > 2, ouch).
Spooling
anthony, December 30, 2005 - 12:18 am UTC
for example :
spool c:\test.sql
select * from tab ;
spool off ;
I am geeting result in test.sql as fallows:
SQL> select * from tab ;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BUSINESS_OBJECTS1 TABLE
CIMSAPLEANNONVAL TABLE
CIMSAPLEANVAL TABLE
I do not want
SQL> select * from tab ; in test.sql file.
How can i go about it ?
December 30, 2005 - 9:21 am UTC
put it into a script, run script:
----------- test.sql --------------
set echo off
spool test
select * from dual;
spool off
--------------------------------------
SQL> @test.sql
test.lst will just have the output
Fantastic work.
Jeff, January 08, 2006 - 8:40 pm UTC
all the info are useful, but can u help me?
Aiwen, January 12, 2006 - 8:39 pm UTC
why when i used spool command,-->spool filename, it was not store the query result, but it store all the sql statements? please help me...
January 13, 2006 - 11:04 am UTC
spooling
aiwen, January 12, 2006 - 11:27 pm UTC
i already used "set echo off" command, but it still contains a query statement... not only query result
January 13, 2006 - 11:09 am UTC
then you are not spooling, you are using redirection perhaps.
Maybe you need to give us a "for example"
If I run this script:
spool x
set echo off
select 'echo off' from dual;
set echo on
select 'echo on' from dual;
spool off
My x.lst has this output:
ops$tkyte@ORA9IR2> set echo off
'ECHOOFF
--------
echo off
ops$tkyte@ORA9IR2> select 'echo on' from dual;
'ECHOON
-------
echo on
ops$tkyte@ORA9IR2> spool off
So questions:
a) what do you see
b) what did you WANT to see?
spool to a file defined in a table
Steve, March 01, 2006 - 3:59 pm UTC
Hi Tom,
I want to spool a query to a file named
$APP/AAA_xxx.dat,
where xxx is sequence number stored in the table file_table. Bit the following code does't work.
column file_seq new_value new_seq noprint
select file_seq_num file_seq
from file_table
define SPOOL_FILE=$APP/AAA_&new_seq.dat
spool &1
...
Thanks!
March 02, 2006 - 8:54 am UTC
if $APP is an environment variable - sqlplus might well not have access to it.
but tell us, what part of the code isn't working? I see a missing ';' after the query.
Prevent command line being writtem to spool file
Hafeez Ali, March 12, 2006 - 2:36 am UTC
Hi
While spooling from sqlplus, the command line executed also gets written to the file, is there a way by which only the output can be spooled without the command line being shown in the file.
March 12, 2006 - 2:02 pm UTC
set echo off
run this:
set echo off
spool test
select * from dual;
spool off
host cat test.lst
and test.lst will have:
---------- begin -----------------
D
-
X
---------- end -------------------
put the command in a seperate file
Oraboy, March 13, 2006 - 5:41 pm UTC
Just a follow-up..
SQL>show echo
echo OFF
SQL>spool c:\echo_off_output.txt
SQL>select * from dual;
D
-
X
SQL>spool off
-- check the output file
-- ----------------------
SQL>host type c:\echo_off_output.txt
SQL>select * from dual;
D
-
X
SQL>spool off
-- you still see the SQL and 'spool off' etc
Now , to get rid of that , try putting everything into a SQL script file..(testsql.sql in my case below) and run it from sql prompt
SQL>host type c:\testsql.sql
set echo off
spool c:\echo_off_output_2.txt
select * from dual;
spool off
set echo on
SQL>@c:\testsql
D
-
X
-- check the output file
SQL>host type c:\echo_off_output_2.txt
D
-
X
March 14, 2006 - 10:02 am UTC
I assumed this was in a script.
spooling to multiple files
A reader, May 08, 2006 - 9:37 am UTC
Is there a way to spool segments of query data into
seperate files.
For e.g.
SELECT year, qtr, sum(sales)
FROM sales
GROUP BY year, qtr
I want the result of this query to be spooled to
multiple files, all rows with year=1991 goes in
a file 1991.txt, all rows with year=1992 goes in
1992.txt and so on. Is it possible?
Thanks.
May 08, 2006 - 10:49 am UTC
not really - spool doesn't start and stop in the middle of queries. There are "tricks" (using SQL to write SQL) we could employ - but it would involve creating a script file using spool - to create multiple spool on/off commands with the query broken up by date ranges.
(don't forget - <b>GROUP BY DOES NOT SORT - NEVER HAD TO, NEVER HAS TO, CANNOT BE RELIED TO</b>)
If you pretend object_type is your qtr and created is your year and object_id is your sales...
then the following script shows what you can "do"
select trunc(created,'y'), object_type, sum(object_id)
from all_objects
where object_type in ( 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'TRIGGER' )
group by trunc(created,'y'), object_type
/
select trunc(created,'y'), object_type, sum(object_id)
from all_objects
where object_type in ( 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'TRIGGER' )
group by trunc(created,'y'), object_type
order by 1, 2
/
select 'spool fy' || to_char(year,'yyyy') || chr(10) ||
'select ' || to_char(year,'yyyy') || ' year, object_type qtr, sum(object_id) sales ' || chr(10) ||
'from all_objects where created >= to_date('||to_char(year,'yyyy')||'01,''yyyymm'') and ' || chr(10) ||
'created <to_date('||(to_char(year,'yyyy')+1)||'01,''yyyymm'') and ' || chr(10) ||
'object_type in ( ''PACKAGE'', ''PROCEDURE'', ''FUNCTION'', ''TRIGGER'' ) ' || chr(10) ||
'group by object_type order by 2;' || chr(10) ||
'spool off'
from (
select distinct trunc(created,'y') year
from all_objects
where object_type in ( 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'TRIGGER' )
order by 1
)
/
set heading off
set feedback off
set echo off
set termout off
spool tmp.sql
/
spool off
set termout on
set heading on
set feedback on
set echo on
@tmp.sql
The output was:
ops$tkyte@ORA10GR2> select trunc(created,'y'), object_type, sum(object_id)
2 from all_objects
3 where object_type in ( 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'TRIGGER' )
4 group by trunc(created,'y'), object_type
5 /
TRUNC(CRE OBJECT_TYPE SUM(OBJECT_ID)
--------- ------------------- --------------
01-JAN-05 FUNCTION 9474352
01-JAN-05 TRIGGER 7776454
01-JAN-06 FUNCTION 863658
01-JAN-06 PROCEDURE 288858
01-JAN-05 PACKAGE 19867833
01-JAN-06 PACKAGE 291537
01-JAN-05 PROCEDURE 2071360
01-JAN-06 TRIGGER 71951
8 rows selected.
ops$tkyte@ORA10GR2> select trunc(created,'y'), object_type, sum(object_id)
2 from all_objects
3 where object_type in ( 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'TRIGGER' )
4 group by trunc(created,'y'), object_type
5 order by 1, 2
6 /
TRUNC(CRE OBJECT_TYPE SUM(OBJECT_ID)
--------- ------------------- --------------
01-JAN-05 FUNCTION 9474352
01-JAN-05 PACKAGE 19867833
01-JAN-05 PROCEDURE 2071360
01-JAN-05 TRIGGER 7776454
01-JAN-06 FUNCTION 863658
01-JAN-06 PACKAGE 291537
01-JAN-06 PROCEDURE 288858
01-JAN-06 TRIGGER 71951
8 rows selected.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select 'spool fy' || to_char(year,'yyyy') || chr(10) ||
2 'select ' || to_char(year,'yyyy') || ' year, object_type qtr, sum(object_id) sales ' || chr(10) ||
3 'from all_objects where created >= to_date('||to_char(year,'yyyy')||'01,''yyyymm'') and ' || chr(10) ||
4 'created <to_date('||(to_char(year,'yyyy')+1)||'01,''yyyymm'') and ' || chr(10) ||
5 'object_type in ( ''PACKAGE'', ''PROCEDURE'', ''FUNCTION'', ''TRIGGER'' ) ' || chr(10) ||
6 'group by object_type order by 2;' || chr(10) ||
7 'spool off'
8 from (
9 select distinct trunc(created,'y') year
10 from all_objects
11 where object_type in ( 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'TRIGGER' )
12 order by 1
13 )
14 /
'SPOOLFY'||TO_CHAR(YEAR,'YYYY')||CHR(10)||'SELECT'||TO_CHAR(YEAR,'YYYY')||'YEAR
-------------------------------------------------------------------------------
spool fy2005
select 2005 year, object_type qtr, sum(object_id) sales
from all_objects where created >= to_date(200501,'yyyymm') and
created <to_date(200601,'yyyymm') and
object_type in ( 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'TRIGGER' )
group by object_type order by 2;
spool off
spool fy2006
select 2006 year, object_type qtr, sum(object_id) sales
from all_objects where created >= to_date(200601,'yyyymm') and
created <to_date(200701,'yyyymm') and
object_type in ( 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'TRIGGER' )
group by object_type order by 2;
spool off
2 rows selected.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> set heading off
ops$tkyte@ORA10GR2> set feedback off
ops$tkyte@ORA10GR2> set echo off
ops$tkyte@ORA10GR2> @tmp.sql
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> spool fy2005
ops$tkyte@ORA10GR2> select 2005 year, object_type qtr, sum(object_id) sales
2 from all_objects where created >= to_date(200501,'yyyymm') and
3 created <to_date(200601,'yyyymm') and
4 object_type in ( 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'TRIGGER' )
5 group by object_type order by 2;
YEAR QTR SALES
---------- ------------------- ----------
2005 FUNCTION 9474352
2005 PACKAGE 19867833
2005 PROCEDURE 2071360
2005 TRIGGER 7776454
4 rows selected.
ops$tkyte@ORA10GR2> spool off
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> spool fy2006
ops$tkyte@ORA10GR2> select 2006 year, object_type qtr, sum(object_id) sales
2 from all_objects where created >= to_date(200601,'yyyymm') and
3 created <to_date(200701,'yyyymm') and
4 object_type in ( 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'TRIGGER' )
5 group by object_type order by 2;
YEAR QTR SALES
---------- ------------------- ----------
2006 FUNCTION 863658
2006 PACKAGE 291537
2006 PROCEDURE 288858
2006 TRIGGER 71951
4 rows selected.
ops$tkyte@ORA10GR2> spool off
Pushpendu, August 11, 2006 - 10:08 am UTC
I am running sql*plus from korn shell.Inspite of set ECHO OFF.I am getting the sql statement in my spool file.Please help.my code is
SET COLSEP ''
SET LINESIZE 2000
SET HEAD OFF
SET TERMOUT OFF
SPOOL $OUT_FILE;
sELECT * FROM TABLENAME;
spool off;
August 11, 2006 - 11:19 am UTC
I don't see a set echo off??
ops$tkyte%ORA10GR2> !cat test.sql
set echo on
SET COLSEP ''
SET LINESIZE 2000
SET HEAD OFF
SET TERMOUT OFF
set echo OFF
SPOOL x.lst;
sELECT * FROM dual;
spool off;
set echo on
!cat x.lst
ops$tkyte%ORA10GR2> @test
X
works for me when I have set echo off in there.
Spool
Srinivasa, September 07, 2007 - 8:27 am UTC
set serveroutput on
set pagesize 0
set heading off
set feedback off
set lines 160
set verify off
set termout on
set trims on
variable reccount number ;
begin
select count(*) into :reccount from loc
where trim(loc) is not null;
end;
/
spool $DATAFILES/&1_fg_loc.0001
select 'H'||'~'||'&1'||'_fg_loc.0001' ||'~'||'ASCII Text Format'||to_char(sysdate ,'DDMMYYYY HH:MM:SS')||'~0~This file has the Locations to be sent to the GDW' from dual;
select 'D'||'~'||'&1'||'~'||loc||'~'||descr
from loc
where trim(loc) is not null;
select 'F'||'~'||'&1'||'_fg_loc.0001'||'~'||'ASCII Text Format'||to_char(sysdate,'DDMMYYYY HH:MM:SS')||'~'||to_char(:reccount) from dual;
Spool off
exit;
This sql file is executed using a shell script. '&1' is defined and its value is NA.
I want to create a spool file such as na_fg_loc.0001
spool $DATAFILES/&1_fg_loc.0001 is not creating the desired file.
Could you please help resolve this .
Thanks in advance
September 12, 2007 - 10:04 am UTC
and what is $DATAFILES - if that is your "environment variable", you need to invoke sqlplus like this:
sqlplus u/p $DATADFILES
and then use &1 instead of $DATAFILES
spooling file path
Sachin, December 14, 2007 - 12:45 am UTC
Hi Tom,
look at below code,there is one prob, which is hardcoded spooling file path for windows OS.
how can make it path independet for linux and window env?
after making of spooling file, i m calling this file from
another scripts file.
thanks in advance...
set echo off
set feedback off
set term off
set pagesize 0
set linesize 200
set longchunksize 500
set heading off
SPOOL D:\upgrade\scripts\Data\Schema5\data\gen_list_defn.sql
prompt set define off
prompt set echo off
prompt set feedback off
prompt set term off
prompt set pagesize 0
prompt set linesize 200
prompt set longchunksize 500
prompt set heading off
prompt delete from GEN_LIST_DEFN
prompt /
select'insert into GEN_LIST_DEFN values('||list_id||','''||list_name||''','''||list_desc||''','''||table_name||''','''||replace(where_clause,'''','''''')||''','''||group_clause||''','''||sort_clause||''','''||explicit_cols ||''')'||';' from GEN_LIST_DEFN;
prompt commit;
prompt /
prompt set define on
prompt set echo on
prompt set feedback on
prompt prompt set term on
SPOOL OFF
set echo on
set feedback on
set term on
set heading on
December 14, 2007 - 1:10 pm UTC
well, since a unix system admin would never let you have /upgrade as a mount point - meaning the file names are going to be radically different anyhow - suggest you pass in the script name.
spooling path
Sachin, December 17, 2007 - 12:30 am UTC
Hi Tom ,
let me know , how can i do this for both OS.
i am new to linux.
December 17, 2007 - 10:57 am UTC
talk with YOUR admins and get their suggestions for your best path forward on YOUR systems.
I cannot answer this for you in general. You need to figure out where YOUR programs will store THEIR data.
Spool many columns
Danny Chen, February 19, 2008 - 7:00 pm UTC
Tom,
what about if a table has many columns, but there is only one row, how could display the result from the ¿SELECT *¿ statement in the vertical direction with the first column is the field name and the second column is the value, instead of wrapping around in the horizontal direction?
February 20, 2008 - 8:17 am UTC
OK
Saravanan, June 03, 2008 - 5:23 am UTC
Hi Tom,
In SQL*PLUS, is there any option to suppress the screen output and write the result set of a query directly to a file using SPOOL command?
June 03, 2008 - 11:36 am UTC
set termout off
spool .....
spool.....
set termout on
put that in a script, run script.
can i get this
venkata, May 25, 2011 - 2:51 pm UTC
Hi Tom,
Am back with some other query
PS: don't say my code as i hate.....
now what i want is
i want to spool the query result to a file with current date as file like say if today is 26-may-2011. then the query output should be spooled to 20110526.txt file. if the run the script file again tomorrow then the output file name must be 20110527.txt any way to get this done
May 26, 2011 - 7:22 am UTC
ps: if you don't post a when others then null bit of code, I won't be hating on it. if you do - I will. That is my 100% satisfaction guaranteed policy. I'm 100% satisfied when I do that.
ops$tkyte%ORA11GR2> column file_name new_val F
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select to_char(sysdate,'yyyymmdd') || '.txt' file_name from dual;
FILE_NAME
------------------------------
20110526.txt
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> !ls -l &F
ls: 20110526.txt: No such file or directory
ops$tkyte%ORA11GR2> spool &F
ops$tkyte%ORA11GR2> select * from dual;
D
-
X
ops$tkyte%ORA11GR2> spool off
ops$tkyte%ORA11GR2> !ls -l &F
-rw-rw-r-- 1 tkyte tkyte 78 May 26 08:21 20110526.txt
there you are again
Venkata, May 26, 2011 - 1:11 pm UTC
very smart, very nice, to the point, by the way when are YOU coming to INDIA Bangalore. When commenting about your replies i feel review column can be made as required not only "How do you rate..." column
May 26, 2011 - 1:39 pm UTC
talk to your local user group community - they are the ones that would typically be inviting me over for a conference or something. I typically need a six to nine month lead time to schedule...
I was in Bangalore not too long ago... for the AIOUG.
display based on intervals
A reader, October 19, 2011 - 12:50 am UTC
hello tom ,
how can i display my results based on interval?
suppose i have a query running for 12 sec i want to display wat ever happening in the query every 4 sec what should i do ,wat should i use
regards,
friend
October 19, 2011 - 4:45 pm UTC
you need to start by explaining this a bit better.
what do you mean by "display what ever is happening in the query" - what does that mean. Do you mean you would like to monitor a query and see what step of the plan it is working on - what it has done, what it has left to do?
If so, then search for:
oracle real time sql monitor
on google and read some of the articles (new in 11g). Before that - it was not truly possible to do this - although *sometimes* some information could be gotten from v$session_longops for some operations.
Oracle Spooling
Sen, December 29, 2011 - 2:35 am UTC
Same question was raised long ago and answered,
Just curious to know on enhancement made on spooling.
is there a way to force Oracle to write in the spool file
while each statement in the script executed?
December 29, 2011 - 11:17 am UTC
there hasn't been any change in behavior.
you can stop spooling, and then start again with append after each statement if you truly need this.
conclusion?
Richard, March 13, 2012 - 7:15 pm UTC
Sorry for the confusing interpretation of the previous example. It was about having 4000 blanks whereas normally there would be a newline or C/R immediately after the last character. (I thought the heading would be trimmed according to the longest line, of course it doesn't)
One thing is sure, it has nothing to do with your script getcode or getallcode, I see that now after running this:
CREATE TABLE TABLE1
(
COLUMN1 VARCHAR2(4000)
);
INSERT INTO TABLE1 VALUES ('Hello World! Once more we meet again');
running script "spool.sql":
column text format a80 word_wrapped /* next to placing it here I also ran this command in SQL> and on a SQL Worksheet in SQL Developer */
set termout off
set heading off
set trimspool on
set feedback off
set linesize 50
spool c:/temp/table.sql
SELECT * FROM TABLE1;
spool off
output file: table1.sql:
Hello World! Once more we meet again
So this is where the blanks end again. So yes, max varchar2 is 4000, I realize, but no trimming, no column text format (or I run it wrongly, I tested it everywhere, not even with ltrim, trim within the SQL query, only "to_clob(column1)" gave me somewhat satisfying result (shortened lines), but still... I sent the same script to my brother in order to check it on his server, to no avail. Same results, no, actually he had 1000 characters per line ?? (so he would have set line length 1000 in his login or so? Would have to ask). Am I the only one getting these long lines without being able to trim or limit them? Can't be.
I'm sorry that this leaked over into another topic as I understand now it has nothing to do with the origianl script you provided. However, since the script is so useful, more people must have had this issue as I am having, so this is an add-on. Therefore, I rate the review Useful - 3
I recreated the script to work with the UTL FILE package. If I need the files locally, I'll copy them from the server.
I don't know what the case is that I can't trim nor limit these lines (maybe hard-code the column text format parameter in the glogin.sql). But the script using UTL FILE is doing it's job well, thanks to your script I was able to write it. Thank you for your time, very appreciated, even inspiring.
My brother wondered whether the same person is still answering these questions after so many years, he doubted it. (Tom isn't Tom anymore?) One thing he was sure of: Thanks to this forum "asktom", Oracle became a lot more attractive to many people!
March 13, 2012 - 7:43 pm UTC
first of all
column TEXT format a80 word_wrapped
only formats a column named 'TEXT', the other page you were on had a column named text, this query has a column named COLUMN1 - not text. You would have to change that.
when I run your script in sqlplus (without any login.sql, all defaults), I get:
[tkyte@localhost ~]$ cat x.lst
Hello World! Once more we meet again
[tkyte@localhost ~]$
in sqlplus, do this:
SQL> show all
after you run your script and share the output with us.
column [column_name] format a80 word_wrapped
Richard, March 13, 2012 - 9:41 pm UTC
Hi Tom,
Jeez... I even tried "column column1 format a80 word_wrapped" as I saw how you applied it in your login.sql! Somehow I discarded it, must have tested it improperly! (also, because I found the exact sentence 'column text format a80' so many times in google that I started to consider the word 'text' as part of a command.
Don't know whether to laugh or to cry after so many silly hours, a bit of both I guess.
Here is the relevant output:
Hello World! Once more we meet again " <-- Here it reached 82
80 + 2 (newline and C/R I suppose due to Windows O/S)
Thanks Tom (wondering whether your name is still Tom), I'm glad I started asking you because it turned this painstaking mini-bug (in fact: mini-lack-of-knowledge) into something worthwhile.
-------------------------------
I also understand now why quite some people asking you stuff suddenly back out and don't follow up anymore, saying whether it worked or not.. too lazy to get to the bottom of it as they already are busy programming other stuff (like my UTL_FILE-using script) and then still write a ,clear, well-formulated, reply within a proper testing environment while we rush into the next issue.
To all: Sure worth the effort though! Turns whatever shared effort (by "Tom" and user) into something worthwhile for both parties.
Cheers!
March 14, 2012 - 6:38 am UTC
(wondering whether your name is still Tom)
sure it is, why would I change it?
Spooling problem
Kim, January 30, 2013 - 10:53 pm UTC
Hi Tom,
I have a complicated SQL which full table scan to some heavy tables, some of which are more than 1 million record.
It takes about 35 seconds to execute in SQL Developer, and returns 9 records.But it takes me forever to spool out the result to file in sqlplus.
Could you please advise where should I check for this?
I use this spool structure to test with a simple SQL, and it fine to spool out.
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTE
SET LINESIZE 1000
SET FEEDBACK OFF
SET EMBEDDED OFF
SET NEWPAGE NONE
SET PAGESIZE 5000
SET SCAN ON
SET SHOW OFF
SET SPACE 1
SET VERIFY OFF
SET TERM OFF
set heading ON
set colsep " | "
set header " | "
set trims on
set trim out on
set trimspool on
set rtrim on
column minimum_repayment format A20
column date_last_used format A15
column last_payment_date format A18
column DDMONYY new_value DDMONYY
SELECT to_char(sysdate -1 , 'DDMONYY') DDMONYY
FROM dual;
spool D:\RESULT_&DDMONYY
Ttitle 'ABC';
SQL content ;
SPOOL OFF
EXIT
Thanks alot for your help
Kim
January 31, 2013 - 2:29 pm UTC
1,000,000 rows are tiny tables in 2013, just putting it in perspective...
compare plans, pull them up from v$sql_plan using dbms_xplan.
spool OS command
Akshay Patil, August 05, 2013 - 11:00 pm UTC
Hi Tom,
We need to generate the checklist of our database server and for that we spool all the required details into a file.
All works fine until I am using sql commands, but I also need to show the mount point details of my server.
I use !df -h to show the output of mountpoints, but this output is not generated in my spool file. Can you please provide a solution for that?
August 08, 2013 - 4:28 pm UTC
use redirection instead of spooling.
(just like the original answer demonstrated above......)
NK, April 10, 2014 - 5:51 pm UTC
Hi Tom,
I am trying to spool output of a select statement and just before that I executing one .sql file. The problem is output of select statement is not caputred. Below is code excerpt...
$SQL_PATH/sqlplus -s /nolog <<EOF
connect $USER_NAME/$PASSWORD@$SQL_SERVER
whenever sqlerror exit sql.sqlcode;
spool ${PATH}/${PROP_FILE}
set echo on
set verify off
set termout on
set heading off
--set pages 50000
set feedback off
set newpage none
set linesize 300
SET TRIMSPOOL on
set serveroutput on
@${SCR_PATH}/test.sql ${arg1};
select extract_line from cnet_extract_gtt where extract_name = '${PROP_FILE}' order by sequence;
spool off;
EOF
Please advice.
Thanks,
NK
April 16, 2014 - 4:44 pm UTC
${PATH} looks wrong to me.
path is usually a delimited list of many directories. spool wants a file name, not
spool /x/y/z:/a/b/c:/l/m/n/foo.lst
which is what it would be if you use $PATH
Aakanksha, July 25, 2014 - 10:03 am UTC
if my select query throws some error.. how to add that error into a log file along with the line number