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!

It seems that EXECUTE IMMEDIATE syntax doesn't allow for LIMIT in bulk collect clause<a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/executeimmediate_statement.htm#CJACGJJG" rel="nofollow">http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/executeimmediate_statement.htm#CJACGJJG</a>

<a href="https://i.stack.imgur.com/ivf3t.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/ivf3t.png" data-original="https://i.stack.imgur.com/ivf3t.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a> <br /><strong>bulk_collect_into_clause</strong><br /><a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/returninginto_clause.htm#CJAIAGHJ" rel="nofollow">http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/returninginto_clause.htm#CJAIAGHJ</a> <a href="https://i.stack.imgur.com/X8NRm.gif" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/X8NRm.gif" data-original="https://i.stack.imgur.com/X8NRm.gif" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

<hr />

You can use a cursor and FETCH .. LIMIT command together with OPEN..FOR command,<br /> 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; <hr />

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

You can use the <a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sql.htm" rel="nofollow">DBMS_SQL</a> 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; ...