Re: SQL+ aborts and exists
- From: Masha Gurenich <gurenich@xxxxxxxxx>
- To: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- Date: Mon, 8 Nov 2010 14:35:46 -0500
are you kidding me? O.o
i will bang myself on the wall if it works!!!! >:
On Fri, Nov 5, 2010 at 3:01 PM, Goulet, Richard
<Richard.Goulet@xxxxxxxxxxx>wrote:
> 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:
