Toad World® Forums

Using BULK COLLECT INTO

Hi, I am trying to use BULK COLLECT INTO with LIMIT clause in a stored procedure. I am using a cursor to get data (500k rows) from a remote database (a table in Teradata), then insert these records to a table in Oracle. After I run the procedure, the table in Oracle only has 1000 rows. Just wondering why not all the 500k rows were being inserted. Here is what the code looks like:

CREATE OR REPLACE PROCEDURE P_TEST

AS

v_msg_proc_name varchar2(30) := ‘P_TEST’;

g_err_code VARCHAR2(10);

g_err_msg VARCHAR2(400);

g_err_msg_log VARCHAR2(4000);

– Create a record set for bulk insert

CURSOR c_test_rec

IS

(

select a.*

from a table in Teradata@dblink a – a table in Teradata that has 500k rows and 50 columns

);

TYPE c_test_table IS TABLE OF c_test_rec%ROWTYPE;

c_test_table_ins c_test_table := c_test_table();

BEGIN

EXECUTE IMMEDIATE ‘TRUNCATE TABLE TEST’;

OPEN c_test_rec;

LOOP

FETCH c_test_rec

BULK COLLECT INTO c_test_table_ins LIMIT 1000;

EXIT WHEN c_test_table_ins.COUNT = 0;

FORALL i IN 1 … c_test_table_ins.COUNT

INSERT /*+ APPEND_VALUES */ INTO test VALUES c_test_table_ins(i);

COMMIT;

END LOOP;

CLOSE c_test_rec;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

g_err_code := SQLCODE;

g_err_msg := substr(SQLERRM, 1, 200);

g_err_msg_log := g_err_msg_log || v_msg_proc_name || ‘: ’ || g_err_code ||’ - ’ || g_err_msg || chr(13);

IF c_test_rec%ISOPEN

THEN

CLOSE c_test_rec;

END IF;

END P_TEST;

FYI In https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9530259800346360042 the same question was posted… with answer.

Thanks!