SQL+ aborts and exists
- From: Masha Gurenich <gurenich@xxxxxxxxx>
- To: Oracle L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Nov 2010 12:43:44 -0400
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:
