Spool out hangs after successful execution. How to avoid it? [message #527344] |
Mon, 17 October 2011 13:17 |
|
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 #527403 is a reply to message #527353] |
Tue, 18 October 2011 01:20 |
flyboy
Messages: 1903 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/
|
|
|