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;