-
Bulk limit stops at the limit value
I have 5 million rows that are to be read from Java and using pl/sql , I have to insert into Oracle. This is what I have written.
It stops working at the limit value. How should this be re written , or modified.
Pls give an example. The values have to be read by java and written to Oracle.
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS
type emp_rec_type is record ( empid number (5), empname varchar2 (30));
TYPE array_t IS TABLE OF emp_rec_type INDEX BY BINARY_INTEGER;
rec_array array_t;
BEGIN
FETCH p_cursor BULK COLLECT INTO rec_array;
FOR i IN rec_array.FIRST .. rec_array.LAST
LOOP
dbms_output.put_line(rec_array(i).empid);
dbms_output.put_line(rec_array(i).empname);
END LOOP;
END pass_ref_cur;
/
set serveroutput on
DECLARE
rec_array SYS_REFCURSOR;
BEGIN
OPEN rec_array FOR
'SELECT empid, empname FROM employees';
pass_ref_cur(rec_array);
CLOSE rec_array;
END;
-
FETCH p_cursor BULK COLLECT INTO rec_array limit 1000;
means it stops at 1000
FETCH p_cursor BULK COLLECT INTO rec_array limit 5000;
stops at 5000. AFter that it is not progressing.
-
Originally Posted by omega1966
FETCH p_cursor BULK COLLECT INTO rec_array limit 1000;
means it stops at 1000
FETCH p_cursor BULK COLLECT INTO rec_array limit 5000;
stops at 5000. AFter that it is not progressing.
That is the expected behavior from Oracle, what did you expect?
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
If there are 5 million records in employee table , then how will I process those records .
-
Originally Posted by omega1966
If there are 5 million records in employee table , then how will I process those records .
With a LOOP?
It's right there in the fine Oracle® Database PL/SQL Language Reference.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
If I put the limit value as 10000 it only process 10000 records and stops . it is not going and fetching the balance records . is something wrong in my code ...
-
You can use the stop limit to update rows in a table. For example if you use:
FETCH p_cursor BULK COLLECT INTO rec_array limit 5000;
to collect rows where processed=0, then you process the rows that are queried changing processed to 1. Then every time you run it you get a different rows, hence you eventually process everything that you need to process. But if you are inserting rows based on a query, you probably don't want to use a stop limit.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|