Home » SQL & PL/SQL » Client Tools » Spool out hangs after successful execution. How to avoid it?
Spool out hangs after successful execution. How to avoid it? [message #527344] Mon, 17 October 2011 13:17 Go to next message
stg68
Messages: 1
Registered: October 2011
Junior Member
My SQLPULS script below generates 2 million records and it works fine without any errors, which I run from my batch file. (sqlplus -S %CONNECT% @"SCRIPTS\mysql.sql"

The script itself runs 2 hours and exits via sqlplus.exe properly without any errors based on errorlevel from my batch and proper generated data. However, I have noticed that spool output file stops generating any records after 1 hour. It appears that client sqlplus.exe is sleeping or verifying something after spooling completed exactly the same period of time that takes to generate my out file.
Is there a way to configure my script to avoid this 1 hour sleep/verification process?

Thank you for your help!

whenever sqlerror exit sql.sqlcode

alter session set current_schema=blah;
/

set linesize 1000
set feedback off
set heading on
set underline off
set trimspool on
set array 100
set pages 0
set wrap off
set echo off
set verify off
set term off
set trim on
set serveroutput on


spool OUT\myoutput.txt;

declare deliminator varchar2(1);

begin
deliminator := chr(9);

dbms_output.put_line(
'Company' || deliminator ||
rtrim('State'));

for co in (
Select Company, State
FROM
companies
)
loop
dbms_output.put_line(
co.Company || deliminator ||
rtrim(co.State));
end loop;
end;
/
spool off
/
quit;
Re: Spool out hangs after successful execution. How to avoid it? [message #527353 is a reply to message #527344] Mon, 17 October 2011 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there a way to configure my script to avoid this 1 hour sleep/verification process?

There is no such thing that 1 hour sleep or verification in SQL*Plus.

Regards
Michel
Re: Spool out hangs after successful execution. How to avoid it? [message #527356 is a reply to message #527353] Mon, 17 October 2011 13:57 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
Does below work better/differently?

set linesize 1000
set feedback off
set heading on
set underline off
set trimspool on
set array 100
set pages 0
set wrap off
set echo off
set verify off
set term off
set trim on
set serveroutput on
set colsep CHR(9)

spool OUT\myoutput.txt;
Select Company, RTRIM(State) FROM companies;
SPOOL OFF
EXIT
Re: Spool out hangs after successful execution. How to avoid it? [message #527403 is a reply to message #527353] Tue, 18 October 2011 01:20 Go to previous message
flyboy
Messages: 1850
Registered: November 2006
Senior Member
Michel Cadot wrote on Mon, 17 October 2011 20:48

There is no such thing that 1 hour sleep or verification in SQL*Plus.

However, there is something like '/'(slash) sqlplus command, which executes the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer. So, that PL/SQL block is re-executed after SPOOL again.

@stg68: Maybe before using any tool, you should read about its features in its documentation. SQL*Plus is described e.g. in SQL*Plus User's Guide and Reference book, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
Previous Topic: Not able to launch sql prompt after entering user name and pwd
Next Topic: Unable to expand and see the tables under schema
Goto Forum:
  


Current Time: Tue Jan 16 20:15:17 CST 2018

Total time taken to generate the page: 0.02211 seconds