RE: SQL+ aborts and exists
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: <gurenich@xxxxxxxxx>, "Oracle L" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Nov 2010 15:01:34 -0400
OH Bother!! Back when I was migrating a number of databases from 9I to
10GR2 the upgrade assistant would always fail. A look through the log
file didn't provide any insight either which is similar to your
experience. The solution recommended by OTS was to comment out the "set
serveroutput on" statements in all of the scripts. Sure enough that
fixed the problem.
Dick Goulet
Senior Oracle DBA
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Masha Gurenich
Sent: Friday, November 05, 2010 12:44 PM
To: Oracle L
Subject: SQL+ aborts and exists
Hi all,
We hit an issue that has never came up before: SQL+ will abort and exit
in the middle of a sql script that calls 170+ another sql scripts. So
let me explain a little bit of what's going on:
1) Release manager has to run about 170 sql scripts in order to complete
the release. All those scripts need to be run in a particular order (of
course). All those scripts need to be run by different users, so there
is a constant switching between usernames and prompts for the username,
password and dbname. The release manager got tired of sitting in front
of the screen and waiting for the username/pass prompt. he is willing to
create some sort of a super-bundle script that would include all those
170+ sql scripts with the preceding credentials. And he got that script
that technically looks like this:
vi super_bundle.sql
set SERVEROUTPUT ON
PROMPT
PROMPT Executing 1.sql
PROMPT
@@1.sql
PROMPT
PROMPT Executing 2.sql
PROMPT
@@2.sql
and so on 170+ times.
2) The problem that we are facing is that somewhere in the middle of
that super_bundle script SQL+ just dies. It terminates the session on
say script 98.sql as if there was EXIT command. If we were to look into
the script on which it fails and run it manually - it will run without
any problems. If we comment this 98.sql out - we will proceed a little
bit further along the road and will fail on say 104.sql.. Inside those
scripts are simple inserts, merges, creates etc. Nothing fancy.
Sometimes, there is a statement that will error out, for example, in
99.sql there is statement that tries to create the view that already
exists. If we comment out 98.sql, it will run 99.sql, will display the
ORA- error about object exists, will proceed to 100.sql and will abort
on 104.sql So, ORA- errors are no not the culprits (also, because we
have WHENEVER SQLERROR CONTINUE)...
3) My colleague and I, we have a feeling that this has something to do
with the buffer. Reading about 10.2 release, we figured, that if we set
SET SERVEROUTPUT ON it will give us unlimited buffer size, same approach
as setting DBMS_OUTPUT.ENABLE (buffer_size => NULL); That didn't help.
We tried to disconnect/connect right before the 98.sql script - didn't
help. We tried to clear screen - def didn't work. Unfortunately, we
cannot change the design of the release process to actually exit out of
sqlplus and then establish a new session.. I bet if we could do that, it
will help us, but we have to work what we have. And being at 10gR2 I was
hoping we do not have those silly buffer limits anymore..
If anybody had this problem before or have any thoughts on this, please
share!! Any help would be much appreciated!
Thanks,
M
Other related posts:
