BULK COLLECT with Native Dynamic SQL

How BULK COLLECT is used with Native Dynamic SQL and OPEN FOR Statement?

You can also use Bulk fetch from Native Dynamic SQL statements. In the following example, we will fetch a collection of keys from a table based on a different set of criteria passed in as a parameter (Parameter in this case will be represented by a sqlplus variable).

VAR Where_clause VARCHAR2(256)

Exec: Where_clause := 'Created > TRUNC(SYSDATE)-50';

PL/SQL procedure successfully completed.

DECLARE

       TYPE V_Dynamic_Collection IS TABLE OF User_Objects%ROWTYPE

       INDEX BY PLS_INTEGER;

       V_Rec V_Dynamic_Collection;

       V_clause VARCHAR2(100) := :Where_clause;

    BEGIN

      /* Execute the statement and bulk fetch the results.*/

      EXECUTE IMMEDIATE 'SELECT *

                         FROM   User_Objects

                         WHERE  ' || V_clause

      BULK COLLECT INTO V_Rec;

         /* Check the Fetched Records*/

      DBMS_OUTPUT.PUT_LINE (V_Rec.COUNT ||

                               ' Records Fetched from USER_OBJECTS' );

END;

SQL> VAR Where_clause VARCHAR2(256)
SQL> Exec :Where_clause := 'Created > TRUNC(SYSDATE)-50';

PL/SQL procedure successfully completed.

SQL> DECLARE
  2         TYPE V_Dynamic_Collection IS TABLE OF User_Objects%ROWTYPE
  3         INDEX BY PLS_INTEGER;
  4         V_Rec V_Dynamic_Collection;
  5         V_clause VARCHAR2(100) := :Where_clause;
  6      BEGIN
  7        /* Execute the statement and bulk fetch the results.*/
  8        EXECUTE IMMEDIATE 'SELECT *
  9                           FROM   User_Objects
 10                           WHERE  ' || V_clause
 11        BULK COLLECT INTO V_Rec;
 12           /* Check the Fetched Records*/
 13        DBMS_OUTPUT.PUT_LINE (V_Rec.COUNT ||
 14                                 ' Records Fetched from USER_OBJECTS' );
 15  END;
 16  /
67 Records Fetched from USER_OBJECTS

PL/SQL procedure successfully completed.

The BULK COLLECT extension to EXECUTE IMMEDIATE.INTO combined with the enhancement to bulk fetch into an associative array / collection of records.

BULK COLLECT with Native Dynamic SQL also includes the OPEN FOR statement.

DECLARE

       TYPE V_Dynamic_Collection IS TABLE OF User_Objects%ROWTYPE

       INDEX BY PLS_INTEGER;

       V_Rec V_Dynamic_Collection;  

       v_clause VARCHAR2(256) := :Where_clause;

       C  SYS_REFCURSOR;

BEGIN  

  /* Open a cursor variable for Dynamic SQL*/

    OPEN C FOR 'SELECT * FROM   User_Objects

                    WHERE  ' || v_clause;

  

  /* Fetch from the Ref Cursor... */

      FETCH C BULK COLLECT INTO V_Rec;

      CLOSE C;

 /*Check the Fetched Records*/

      DBMS_OUTPUT.PUT_LINE (V_Rec.COUNT ||

                               ' Records Fetched from USER_OBJECTS');

END;

SQL> DECLARE
  2         TYPE V_Dynamic_Collection IS TABLE OF User_Objects%ROWTYPE
  3         INDEX BY PLS_INTEGER;
  4         V_Rec V_Dynamic_Collection;
  5         v_clause VARCHAR2(256) := :Where_clause;
  6         C  SYS_REFCURSOR;
  7  BEGIN
  8    /* Open a cursor variable for Dynamic SQL*/
  9      OPEN C FOR 'SELECT * FROM   User_Objects
 10                      WHERE  ' || v_clause;
 11
 12    /* Fetch from the Ref Cursor... */
 13        FETCH C BULK COLLECT INTO V_Rec;
 14        CLOSE C;
 15   /*Check the Fetched Records*/
 16        DBMS_OUTPUT.PUT_LINE (V_Rec.COUNT ||
 17                                 ' Records Fetched from USER_OBJECTS');
 18  END;
 19  /
67 Records Fetched from USER_OBJECTS

PL/SQL procedure successfully completed.

We explicitly open a Cursor variable for our Dynamic SQL statement and then We bulk fetch the cursor into our associative array of records.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.

So, leave your thoughts before you leave the page