Problem in dynamic SQL array processing

From: Dick Bax <d.bax_at_cuperus.nl>
Date: Fri, 10 Dec 1999 15:12:34 +0100
Message-ID: <38510A52.335A8C43@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 - 08:12:34 CST