Re: DBMS_SQL.BIND_ARRAY: errors when specifying ranges

From: Ryan Gaffuri <rgaffuri_at_cox.net>
Date: 17 Jun 2003 09:57:53 -0700
Message-ID: <1efdad5b.0306170857.194628be_at_posting.google.com>


regiethomas_at_hotmail.com (Regi Thomas) 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.

the only time i have seen performance improvements from splitting stuff up into smaller chunks is with bulk binds and pl/sql tables. Small SQL statements doesnt improve anything. Yes, if you have problems with snapshot too old and do NOT have the disk space to make larger rollback segments or change your process, you may want to look into this. 50k rows is not that much. How many bytes are in each row that your using up that much rollback and taking that long?

post your code, let me see it. you do realize the only time I have EVER needed to use dbms_sql is when I dont know how many columns I need to select. I have never needed it for an update statement. EXECUTE IMMEDIATE is much easier.

post the code. Ill look at it. Received on Tue Jun 17 2003 - 18:57:53 CEST

Original text of this message