Re: SQL+ aborts and exists
- From: Masha Gurenich <gurenich@xxxxxxxxx>
- To: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- Date: Wed, 10 Nov 2010 18:10:40 -0500
So, commenting out, removing, setting to OFF of the following: set
serveroutput on didn't do a thing. Still crashes on the same spot.
Any other ideas, people? Oracle Support have no idea (why am i not
surprised..)
Thanks beforehand,
M
On Mon, Nov 8, 2010 at 2:51 PM, Masha Gurenich <gurenich@xxxxxxxxx> wrote:
> hehehe.
> i will let you know how it goes tomorrow!
> thank you for suggestions and please, pray to SQL+ god :D
>
> On Mon, Nov 8, 2010 at 2:39 PM, Goulet, Richard <
> Richard.Goulet@xxxxxxxxxxx> wrote:
>
>> Humm, better send you some new wallboard!!
>>
>>
>> Dick Goulet
>> Senior Oracle DBA
>>
>>
>> ------------------------------
>> *From:* Masha Gurenich [mailto:gurenich@xxxxxxxxx]
>> *Sent:* Monday, November 08, 2010 2:36 PM
>> *To:* Goulet, Richard
>> *Cc:* Oracle L
>> *Subject:* Re: SQL+ aborts and exists
>>
>> 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:
