61012

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!

Answer1:

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

Answer2:

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; ...

Recommend

  • write to Java console when my Javascript callback (made in Selenium) returns
  • Subtracting timestamp in oracle returning weird data
  • Modifying files nested in tar archive
  • Validity Method for Reference Classes
  • How to get google-services.json from Developer console?
  • How to return a number as a binary string with a set number of bits in python
  • Is it possible to send skype chat messages from a linux server without X?
  • findObjectsInBackgroundWithBlock block signature not correct
  • Graceful pod termination
  • Fat binaries in iOS
  • How does the HEXTORAW() function work? What is the algorithm?
  • What does certain JVM do after loading ByteCode into memory?
  • SIP API media codecs
  • ZipList with Scalaz
  • why calling cd shell command through system() or execvp() from a child process won't work?
  • jquery validation - waiting for remote check to complete
  • what does prefix @- mean in makefile?
  • Alamofire and Reachability.swift not working on xCode8-beta5
  • iOS Cordova first plugin - plugin.xml to inject a feature
  • Facebook iOS SDK Not Calling Completion Handler
  • How to 'create temp table as select' in Slick?
  • Do query loads all the data in memory
  • npm 5.4.1 install/uninstall all failing
  • Tomcat memory Leak
  • Question about instantiating object
  • SAVE attribute needed for Fortran variables when only the C_LOC address is returned to a C program?
  • PLSQL: Get number of records updated vs inserted when a merge statement is used
  • JqueryMobile Popup menu is not working
  • Security issues with PHP's Readfile method
  • What is the purpose of TaskExecutor in spring?
  • Automatically associate new Sonar project with custom quality profile and quality gate
  • Zurb Foundation _global.scss meta styles for js?
  • onBackPressed() not being executed
  • How to run “Deployd” on port 80 instead of port 5000 in webserver.
  • Email verification using google app script and google forms
  • In LanguageTool, how do you create a dictionary and use it for spell checking?
  • Delete MySQLi record without showing the id in the URL
  • QuartzCore.framework for Mono Develop
  • RestKit - RKRequestDelegate does not exist
  • Programmatically clearing map cache