Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Please Help to solve this problem

Re: Please Help to solve this problem

From: <>
Date: 21 Feb 2006 07:27:46 -0800
Message-ID: <>

senthil wrote:
> The output of the sql file is spooled in a delimited format to txt file
> in our server. so even though the server is down from where we are
> retrieving the data, the spool file in our server contains data.
> set heading off;
> set pagesize 0;
> set verify off;
> set linesize 9999;
> set trimspool on;
> set feedback off;
> set termout off;
> set trimspoll on;
> spool /apps/rptm/crons/temp/;
> SELECT column_name1, column_name2 from table_name;
> spool off;
> exit;
> /

Your first goal is to coordinate, with the administrator of the remote server, WHEN to run this script to allow it to complete.

Of course, that isn't on your list of tasks ... else you wouldn't be asking about what error messages to expect in the output text. And if this truly has happened to you before you should have all of the examples of what to expect as the error text will likely be present in one or more of these spool files. That being the case the possibilities are:

ORA-03113: end-of-file on communication channel ORA-00028: your session has been killed

Newly armed with this information I expect you'll modify your script to handle such errors, which is a wise move on your part. Another extremely wise move would be to add to the top of your SQL*Plus script:

whenever sqlerror exit sqlcode

to enable you to see why your session stopped spooling data. However editing
your script without also arranging for a suitable window for error-free execution is foolish; the error handling should be for exceptions, and you, apparently, have this situation as a regular occurrence, the exception being an error-free run.

Fix your script, then fix your schedule. Constant failure notices make you look clueless which is the last image you need to create and could cost you your position.

David Fitzjarrell Received on Tue Feb 21 2006 - 09:27:46 CST

Original text of this message