Re: DBMS_SQL.BIND_ARRAY: errors when specifying ranges

"Regi Thomas" <regiethomas_at_hotmail.com> wrote in message news:7928998b.0306170614.5880c5ba_at_posting.google.com...
> Hi,
> I am having a big problem with using DBMS_SQL with BIND_ARRAY. I need
> to update all rows of a table (up to 50,000,000 rows. In order to
> split the work into manageable bites I want to run an update statement
> bound with a range of values from the array, commit and then run the
> statement with a different range of values.
>
> The metacode goes like this, on oracle 8.1.7:
>
> declare variables
> DBMS_SQL.VARCHAR2_TABLE types to be used to store many key values to
> process by update statment
>
> Build keycursor statement for select the key values from the table.
>
> Build update statement for updating table given key values, using bind
> variables in the WHERE clause
>
> DBMS_SQL.OPEN_CURSOR for the keycursor statement
> DBMS_SQL.PARSE code for the keycursor statement
> DBMS_SQL.DEFINE_ARRAY for holding the keycursor values
> DBMS_SQL.OPEN_CURSOR for the update statement
> DBMS_SQL.PARSE code for the update statement
> DBMS_SQL.EXECUTE the keycursor statement
> DBMS_SQL.FETCH_ROWS from the keycursor. fetches n rows as dictated in
> DEFINE_ARRAY statement
> DBMS_SQL.COLUMN_VALUE to get the values in to the arrays
> DBMS_SQL.BIND_ARRAY to bind array values to the update statement
> DBMS_SQL.EXECUTE the update statement
>
>
> This all works fine when I do not specify a range in the
> DBMS_SQL.BIND_ARRAY statement. It also works fine when the start and
> end of the range are the same (i.e. process on row at a time) as
> specified by integer type variables. When specifying any larger range
> I get 'ORA_03113: end of file on communication channel' after
> processing around 50 records and binding the next range.
>
> Has anybody else had this problem? How can I get this to work? My main
> motivations are high performance and to avoid ORA-01555 'snapshot too
> old' errors.

Recommendation:

Do it in SQL. And if the rollback segments aren't big enough, create a big segment and set transaction use rollback segment big before your update. Then offline your big rollback segment till next time you need it. We've had this discussion on a few occasions. IIRC, last time Richard Foote summoned up an analogy with a boxing match. Anyway IMHO, the very worst way to go is to split a business transaction into
multiple physical transactions with procedural code such as this.

Regards,
Paul

P.S. Ryan: It's 50m rows, not 50,000
P.P.S. Regi: It doesn't help much to post to multiple C.D.O. groups Received on Tue Jun 17 2003 - 14:09:14 CDT