Handling ORA-4030

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Wed, 24 Jan 2001 12:46:56 -0500
Message-Id: <10751.127385@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C0862D.A78337BE
Content-Type: text/plain;

charset="iso-8859-1"

Hello everyone,

I'm adding a module to a load program that uses pl/sql tables populated by BULK COLLECT and updates the database with the FORALL statement. However, when there are a lot of records it fails with ORA-4030. That's all fine and dandy, so I'm trying to handle the exception by nulling out the PL/SQL table that blew memory and then executing the traditional cursor-through and update in the named 4030 exception.

It keeps failing and never seems to make it through the exception block. My question is, once you hit ORA-4030 can you really do anything about it? I'm beginning to think that any manipulation of any type after I hit ORA-4030 is moot, because it's hosed. I'm beginning to think I really need to handle the exception in the shell script that calls this, having it check for any errors in execution, and if it returns 4030 fire off a separate process that does the cursor stuff.

Any comments are appreciated.
Thanks

Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319

V: 954.484.3191, x174
F: 954.484.2933 
C: 954.658.5849

http://www.qode.com

"The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."

------_=_NextPart_001_01C0862D.A78337BE
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2650.12">
<TITLE>Handling ORA-4030</TITLE>
</HEAD>
<BODY>

Hello everyone, 


I'm adding a module to a load program =
that uses pl/sql tables populated by BULK COLLECT and updates the = database with the FORALL statement.

However, when there are a lot of =
records it fails with ORA-4030.  That's all fine and dandy, so I'm = trying to handle the exception by nulling out the PL/SQL table that = blew memory and then executing the traditional cursor-through and = update in the named 4030 exception.

It keeps failing and never seems to =
make it through the exception block.  My question is, once you hit = ORA-4030 can you really do anything about it?  I'm beginning to = think that any manipulation of any type after I hit ORA-4030 is moot, = because it's hosed.  I'm beginning to think I really need to = handle the exception in the shell script that calls this, having it = check for any errors in execution, and if it returns 4030 fire off a = separate process that does the cursor stuff.

Any comments are appreciated. 
 Thanks


Lisa Rutland Koivu
 Oracle Database Administrator
 Qode.com
 4850 North State Road 7
 Suite G104
 Fort Lauderdale, FL  =
33319


V: 954.484.3191, x174
 F: 954.484.2933 
 C: 954.658.5849
 <A HREF=3D"http://www.qode.com" =
TARGET=3D"_blank">http://www.qode.com</A>


"The =
information contained herein does not express the opinion or position = of Qode.com and cannot be attributed to or made binding upon = Received on Wed Jan 24 2001 - 11:46:56 CST