BULK COLLECT LIMIT in EXECUTE IMMEDIATE

Question

Is it possible to put a limit in a bulk collect using execute immediate?

I have below script but I am getting error when using a LIMIT.

declare
v_query varchar2(3000); 
begin 

v_query := 'select 1 from dual' -- this is just a sample query. 

execute immediate  v_query 
bulk collect into table1 --table type

end;

If limit is not possible with my code, is there any work around?

Thanks!

You tagged your question with "oracle 10g" but LIMIT clause was not available at 10g — Wernfried Domscheit, Nov 15 '15 at 14:28

krokodilko · Answer 1 · 2015-11-15T11:24:18.780

It seems that EXECUTE IMMEDIATE syntax doesn't allow for LIMIT in bulk collect clause

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/executeimmediate_statement.htm#CJACGJJG

bulk_collect_into_clause
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/returninginto_clause.htm#CJAIAGHJ

You can use a cursor and FETCH .. LIMIT command together with OPEN..FOR command,
in this way:

DECLARE
  ....
  cur sys_refcursor;
BEGIN
  v_query := 'SELECT level AS x FROM dual CONNECT BY LEVEL <=10';
  OPEN cur FOR v_query;
  LOOP
     FETCH cur BULK COLLECT INTO collection LIMIT 3;
     EXIT WHEN collection.COUNT = 0;

     /* Process data from `collection` */

  END LOOP;
  CLOSE cur;
END;

Example:

DECLARE
  TYPE col_typ IS table of NUMBER;
  collection col_typ;
  v_query varchar2(3000); 
  cur sys_refcursor;
  i int := 0;
  x int;
BEGIN
  v_query := 'SELECT level AS x FROM dual CONNECT BY LEVEL <=10';

  OPEN cur FOR v_query;
  LOOP
     FETCH cur BULK COLLECT INTO collection LIMIT 3;
     EXIT WHEN collection.COUNT = 0;

     /* Process data from `collection` */
     i := i + 1;
     DBMS_OUTPUT.PUT_LINE('==== Batch nbr #' || i );
     FOR x IN 1 .. collection.COUNT LOOP
       DBMS_OUTPUT.PUT_LINE( collection( x ) );
     END LOOP;
  END LOOP;
  CLOSE cur;
END;
/

Result:

==== Batch nbr #1
1
2
3
==== Batch nbr #2
4
5
6
==== Batch nbr #3
7
8
9
==== Batch nbr #4
10

Thank you very much for this. I still don't understand why limit doesn't work with execute immediate but i'll let my future self figure it out. Just one question though, would adding a limit to my bulk collect change the performance of my script? My main purpose of adding a LIMIT is to avoid getting "snapshot too old" oracle error but I'm not sure which would be faster. having a limit or without a limit. — user2058738, Nov 15 '15 at 11:52

score 0 · Answer 2 · answered Nov 15 '15 at 15:03

You can use the DBMS_SQL to do this. However, you have to go over cursor variables.

DECLARE

  src_cur SYS_REFCURSOR;
  curid NUMBER;
  v_query varchar2(3000); 
  ret NUMBER;

BEGIN
  -- DBMS_SQL.OPEN_CURSOR
  curid := DBMS_SQL.OPEN_CURSOR;
  v_query := 'select 1 from dual';

  DBMS_SQL.PARSE(curid, v_query, DBMS_SQL.NATIVE);
  ret := DBMS_SQL.EXECUTE(curid);

  -- Switch from DBMS_SQL to native dynamic SQL
  src_cur := DBMS_SQL.TO_REFCURSOR(curid);

  -- Fetch with native dynamic SQL
  FETCH src_cur BULK COLLECT INTO ... LIMIT x;

  ...

BULK COLLECT LIMIT in EXECUTE IMMEDIATE

2 Answers2

Linked