Re: SQL+ aborts and exists

  • From: Jack van Zanen <jack@xxxxxxxxxxxx>
  • To: gurenich@xxxxxxxxx
  • Date: Thu, 11 Nov 2010 15:54:52 +1100

Hi,


Do you get any message at all?

If you keep calling  scripts from scripts eventually you could hit

SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.

But this happens only if you call a script which than calls another script
etc etc not when they are all listed in one script. So this may not be the
case for you as your example seems to suggest there is only 1 script that
calls everything else.

Just thought I throw in a few cents


Jack van Zanen

-------------------------
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation


On Sat, Nov 6, 2010 at 3:43 AM, Masha Gurenich <gurenich@xxxxxxxxx> wrote:

> 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: