DBMS_SQL.BIND_ARRAY: errors when specifying ranges

From: Regi Thomas <regiethomas_at_hotmail.com>
Date: 17 Jun 2003 07:14:29 -0700
Message-ID: <7928998b.0306170614.5880c5ba@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. Received on Tue Jun 17 2003 - 09:14:29 CDT