Re: Problem in dynamic SQL array processing

Try moving the DEFINE_ARRAY inside the loop just before the FETCH_ARRAY. (Leave the execute where it is).

This is how the code sample on my web-site works, and I did have a good reason for doing it that way (though I can't remember now what it was).

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Dick Bax wrote in message <38510A52.335A8C43_at_cuperus.nl>...
>I have a, at least for us, serious problem with Dynamic SQL in PL/SQL 8.
>I hope
>someone may have encountered this problem before and can point me to the
>
>reason why this occurs. We run Oracle 8.0.5.1.1 on WinNT.
>
>I have 2 tables. I want to copy all values from the primary key column
>in one
>table to another. However the procedure inserts double entries even
>though the
>source data is a primary key and contains no double entries.
>
>The source table is clustered and contains about 1.5 million records.
>Updates
>and inserts are very slow because of this. We have to create temporary
>scores
>for each record depending on the other data in the cluster. Therefore we
>have
>a second semi temporary table that contains two columns, the score
>values and
>off course a column to link the results to the clustered source table.
>
>I have made a stored PL/SQL procedure that copies the values from the
>primary key column in the source table to the primary key column in the
>target
>table. See the code at the end of this message.
>
>The procedure executes without a problem, but when we try to put a
>primary
>key constraint on the target table it fails because duplicate values
>exist
>in the column.
>
>Further testing found that the duplicate values occur only when the data
>is
>inserted into the target table. When the data in v_seq_nr is output to
>the screen
>using dbms_output no duplicate values are shown.
>
>The moment that the duplicate values occur seem to be related to the
>array size
>used. When the primary key on the target table is activated before any
>inserts
>the procedure fails on an unique violation (ORA-00001).
>When the array size is 5 or 10 it fails after 140 records. When the
>array size
>is 1000 it fails after 6600 records. When the array size is set to 1 no
>problem
>occurs and the data is inserted correctly (Making array processing
>rather
>useless though ).
>
>There is no problem with the source data. copying the same data in any
>other way
>gives no problem.
>
>Oracle support has been notified and have sofar not found a solution. In
>this
>case I can fairly easily work around the problem. However the same type
>of code
>using dbms_sql has been used in the project quite often and this problem
>makes
>me feel that might be unreliable.
>
>Regards,
>
>Dick Bax
>
>d.bax_at_cuperus.nl
>
>--------------------------------------------
>CREATE OR REPLACE PROCEDURE copy1
>IS
> v_BatchSize INTEGER := 5;
> v_seq_nr DBMS_SQL.NUMBER_TABLE;
>
> v_Cursor1 INTEGER;
> v_Cursor2 INTEGER;
> v_ReturnCode INTEGER;
> v_NumRows INTEGER;
> v_SelectStmt VARCHAR2(200);
> v_InsertStmt VARCHAR2(300);
>
>BEGIN
> v_Cursor1 := DBMS_SQL.OPEN_CURSOR;
> v_Cursor2 := DBMS_SQL.OPEN_CURSOR;
>
> v_SelectStmt := 'SELECT seq_nr FROM source_table';
> DBMS_SQL.PARSE(v_Cursor1, v_SelectStmt, DBMS_SQL.Native);
>
> v_InsertStmt := 'INSERT INTO target_table VALUES (:SeqNR)';
> DBMS_SQL.PARSE(v_Cursor2, v_InsertStmt, DBMS_SQL.Native);
>
> DBMS_SQL.DEFINE_ARRAY(v_Cursor1, 1, v_seq_nr, v_BatchSize, 1);
> v_ReturnCode := DBMS_SQL.EXECUTE(v_Cursor1);
>
> LOOP
> v_NumRows := DBMS_SQL.FETCH_ROWS(v_Cursor1);
>
> EXIT WHEN v_NumRows = 0;
>
> DBMS_SQL.COLUMN_VALUE(v_Cursor1, 1, v_seq_nr);
> DBMS_SQL.BIND_ARRAY(v_Cursor2, ':SeqNr', v_seq_nr, 1, v_NumRows);
>
> v_ReturnCode := DBMS_SQL.EXECUTE(v_Cursor2);
>
> EXIT WHEN v_NumRows < v_BatchSize;
> END LOOP;
>
> COMMIT;
> DBMS_SQL.CLOSE_CURSOR(v_Cursor1);
> DBMS_SQL.CLOSE_CURSOR(v_Cursor2);
>END;
>------------------------
>
>
Received on Fri Dec 10 1999 - 09:00:59 CST