-1

For operating on millions of records I want to put a limit of 500 but the following code gives error. Error report: ORA-06550: line 6, column 49: PLS-00103: Encountered the symbol "LIMIT" when expecting one of the following:

DECLARE
    TYPE EMP_T IS TABLE OF NUMBER;
    EMP_ID EMP_T;
    QRY VARCHAR2(4000):='SELECT EMPLOYEE_ID FROM EMPLOYEES';
begin   
    execute immediate QRY bulk collect into EMP_ID LIMIT 500;
END;
Bhargav Rao
  • 41,091
  • 27
  • 112
  • 129
PTK
  • 161
  • 14
  • 2
    Possible duplicate of ["Bulk Collect Into" and "Execute Immediate" in Oracle](http://stackoverflow.com/questions/21117021/bulk-collect-into-and-execute-immediate-in-oracle) – Maheswaran Ravisankar Feb 22 '17 at 07:42
  • `LIMIT 500`. I guess you dont understand why LIMIT is used and whats the best LIMIT you can put for bulk collect. LIMIT simply is not a number of row counter. If you want a row counter use that in your dynamic query to restrict row. Read this . http://stackoverflow.com/questions/38659739/setting-a-value-for-limit-while-using-bulk-collect – XING Feb 22 '17 at 08:10

1 Answers1

0

That's not the way to use LIMIT clause. I think you can't use LIMIT clause in BULK COLLECT with EXECUTE IMMEDIATE. BULK COLLECT LIMIT in EXECUTE IMMEDIATE

Example:

DECLARE
    TYPE EMP_T IS TABLE OF NUMBER;
    EMP_ID EMP_T;
    CURSOR c_data IS SELECT empid FROM EMPLOYEE;
begin   
OPEN c_data;
  LOOP
    FETCH c_data
    BULK COLLECT INTO EMP_ID LIMIT 100;
    EXIT WHEN EMP_ID.count = 0;

    -- Process contents of collection here.
    DBMS_OUTPUT.put_line(EMP_ID.count || ' rows');
  END LOOP;
  CLOSE c_data;
END;
/
Community
  • 1
  • 1
JSapkota
  • 2,961
  • 1
  • 10
  • 19
  • I can't use cursor as my query is not static. Query is built dynamically and then used in execute immediate. – PTK Feb 22 '17 at 07:50
  • If you are using Oracle 12c you can use the row limiting clause, something like FETCH FIRST 500 ROWS – BobC Feb 22 '17 at 10:48