Bulk limit stops at the limit value
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Bulk limit stops at the limit value

  1. #1
    Join Date
    Dec 2012
    Posts
    4

    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;

  2. #2
    Join Date
    Dec 2012
    Posts
    4
    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.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool

    Quote Originally Posted by omega1966 View Post
    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

  4. #4
    Join Date
    Dec 2012
    Posts
    4
    If there are 5 million records in employee table , then how will I process those records .

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool

    Quote Originally Posted by omega1966 View Post
    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

  6. #6
    Join Date
    Dec 2012
    Posts
    4
    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 ...

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width