Database, SQL and PL/SQL

Get Up to Speed with DBMS_SQL

Explore the latest DBMS_SQL features in Oracle Database 12c Release 2.

By Steven Feuerstein

March/April 2017

DBMS_SQL offers a broad and deep API for executing dynamic SQL and dynamic PL/SQL blocks from within PL/SQL. Most of the common and simpler dynamic SQL requirements are handled through native dynamic SQL and the EXECUTE IMMEDIATE statement. Yet DBMS_SQL is still quite handy for some of the more challenging tasks, including dynamic SQL method four, which lets your program execute dynamic SQL statements that contain a varying number of bind variables.

So it is important to keep up with DBMS_SQL and its latest capabilities for working with dynamic SQL in PL/SQL. In Oracle Database 12c Release 1 (Oracle Database 12.1), the EXECUTE IMMEDIATE statement was extended to support user-defined PL/SQL types, allowing you to, for example, bind a Boolean value in the USING clause. Now, in Oracle Database 12c Release 2 (Oracle Database 12.2), those same capabilities have been extended to DBMS_SQL, giving you more flexibility when handling complex dynamic PL/SQL requirements, and enabling you to come up with a simpler solution.

First, let’s take a look at the problems you would encounter in Oracle Database 12.1 and earlier releases if you tried to use DBMS_SQL with PL/SQL–specific and user-defined types.

Suppose I have this simple procedure defined in my schema:

PROCEDURE pass_boolean (b IN BOOLEAN) AUTHID DEFINER
IS
   local_b   BOOLEAN;
BEGIN
   local_b := b;
END;

Using a release prior to Oracle Database 12.2, if I try to bind a Boolean variable with DBMS_SQL, I see this error:

DECLARE
   cur NUMBER;
BEGIN
   cur := DBMS_SQL.open_cursor ();
   DBMS_SQL.parse (cur
      , 'BEGIN pass_boolean (:my_Boolean); END;'
      , DBMS_SQL.native);
   DBMS_SQL.bind_variable (cur, 'my_boolean', TRUE);
   dummy := DBMS_SQL.execute (cur);
   DBMS_SQL.close_cursor (cur);
END;
/
ORA-06550: line 8, column 4:
PLS-00306: wrong number or types of arguments in call to 'BIND_VARIABLE'

The same thing happens when I try to bind to a user-defined type, as shown in Listing 1.

Code Listing 1: Trying to bind a user-defined type before Oracle Database 12.2

CREATE OR REPLACE PACKAGE my_pkg AUTHID DEFINER
IS
   TYPE r IS RECORD (n NUMBER);
END;
/
CREATE OR REPLACE PROCEDURE pass_record (r_in IN my_pkg.r)
   AUTHID DEFINER
IS
BEGIN
   NULL;
END;
/
DECLARE
   cur   NUMBER;
BEGIN
   cur := DBMS_SQL.open_cursor ();
   DBMS_SQL.parse (cur,
                   'BEGIN pass_record (:my_record); END;',
                   DBMS_SQL.native);
   DBMS_SQL.bind_variable (cur, 'my_record', TRUE);
   dummy := DBMS_SQL.execute (cur);
   DBMS_SQL.close_cursor (cur);
END;
/
ORA-06550: line 8, column 4:
PLS-00306: wrong number or types of arguments in call to 'BIND_VARIABLE'

Now let’s see how things work in Oracle Database 12.2.

Bind to Booleans

Booleans, famously, are a native datatype in PL/SQL, but they are not supported natively in SQL. This is no longer a “blocking factor” when it comes to executing dynamic PL/SQL blocks that need to bind a Boolean.

Listing 2 demonstrates an example of constructing and executing a dynamic PL/SQL block, binding a Boolean as an IN value.

Code Listing 2: Binding a Boolean in Oracle Database 12.2

DECLARE
   c_with_boolean   CONSTANT VARCHAR2 (2000)
      := q'[
    DECLARE
       x BOOLEAN := :my_boolean;
    BEGIN
       IF (x) then
          DBMS_OUTPUT.PUT_LINE ('value of x = ' || 'true');
       ELSE
          DBMS_OUTPUT.PUT_LINE ('value of x = ' || 'false');
       END IF;
    END;]' ;
   dummy                     NUMBER;
   cur                       NUMBER;
BEGIN
   cur := DBMS_SQL.open_cursor ();
   DBMS_SQL.parse (cur, c_with_boolean, DBMS_SQL.native);
   DBMS_SQL.bind_variable (cur, 'my_boolean', TRUE);
   dummy := DBMS_SQL.execute (cur);
   DBMS_SQL.close_cursor (cur);
END;
/
value of x = true

As you can see from this example, the Oracle Database 12.2 DBMS_SQL.BIND_VARIABLE procedure now includes an overloading for Booleans.

You can also extract Boolean values from a dynamic block through a Boolean overloading of the DBMS_SQL.VARIABLE_VALUE procedure.

To see this functionality in action, first I create a table:

CREATE TABLE tab (c1 VARCHAR2 (30));

Then I create the dyn_sql_bool_tab_out procedure, as shown in Listing 3, that will insert rows (first dynamically and then with static SQL inserts) into the table and, along the way, change the value of the variable.

Code Listing 3: Inserting rows and changing variable values

CREATE OR REPLACE PROCEDURE dyn_sql_bool_tab_out (b_in IN BOOLEAN)
   AUTHID DEFINER
AS
   local_b   BOOLEAN := b_in;
   str       VARCHAR2 (3000);
   dummy     NUMBER;
   cur       NUMBER;
BEGIN
   str := '
BEGIN
   IF :v1
   THEN
      :v1 := FALSE;
      INSERT INTO tab VALUES (''first true'');
   ELSE
      :v1 := TRUE;
      INSERT INTO tab VALUES (''first false'');
   END IF;
END;';
   cur := DBMS_SQL.open_cursor ();
   DBMS_SQL.parse (cur, str, DBMS_SQL.native);
   /* Bind the Boolean to pass the value in. */
   DBMS_SQL.bind_variable (cur, 'v1', local_b);
   dummy := DBMS_SQL.execute (cur);
   /* Extract the value back into the variable */
   DBMS_SQL.variable_value (cur, 'v1', local_b);
   DBMS_SQL.close_cursor (cur);
   IF local_b
   THEN
      INSERT INTO tab
           VALUES ('then true');
   ELSE
      INSERT INTO tab
           VALUES ('then false');
   END IF;
END;
/

I then execute the dyn_sql_bool_tab_out procedure and see the associated output:

BEGIN
   dyn_sql_bool_tab_out (TRUE);
   FOR rec IN (SELECT * FROM tab)
   LOOP
      DBMS_OUTPUT.put_line (rec.c1);
   END LOOP;
   ROLLBACK;
   dyn_sql_bool_tab_out (FALSE);
   FOR rec IN (SELECT * FROM tab)
   LOOP
      DBMS_OUTPUT.put_line (rec.c1);
   END LOOP;
END;
/
first true
then false
first false
then true

Bind to Records

Records are a very handy type of data in PL/SQL. They allow you to group together related variables under a single name. Suppose in my program I need to manipulate a user’s city, favorite color, and age. I could declare three variables, as follows:

DECLARE
   l_user_city VARCHAR2(100);
   l_user_fav_color VARCHAR2(100);
   l_user_age NUMBER;

But I could also work with a record instead:

DECLARE
   TYPE user_rt IS RECORD (
      city VARCHAR2(100),
      fav_color VARCHAR2(100),
      age NUMBER);
   l_user user_rt;

And the best place to define user-defined types is in a package specification, so they can be used across your application code base, as in the following:

CREATE OR REPLACE PACKAGE my_pkg
   AUTHID DEFINER
AS
   TYPE user_rt IS RECORD (
      city VARCHAR2(100),
      fav_color VARCHAR2(100),
      age NUMBER);
END ;
/

If, however, in Oracle Database 12.1 or earlier releases, I tried to bind and work with a record of this type inside a dynamic PL/SQL block, I would get another PLS-00306 error, as shown in Listing 4.

Code Listing 4: Attempting to bind and work with a record (before Oracle Database 12.2)

DECLARE
   l_block CONSTANT VARCHAR2 (2000) := q'[
DECLARE
   l_user   my_pkg.user_rt;
BEGIN
   l_user := :my_user;
END;]';
   dummy  NUMBER;
   cur    NUMBER;
   l_user my_pkg.user_rt;
BEGIN
   cur := DBMS_SQL.open_cursor ();
   DBMS_SQL.parse (cur, l_block, DBMS_SQL.native);
   DBMS_SQL.bind_variable (cur, 'my_user', l_user);
   dummy := DBMS_SQL.execute (cur);
   DBMS_SQL.close_cursor (cur);
END;
/
ORA-06550: line 17, column 4:
PLS-00306: wrong number or types of arguments in call to 'BIND_VARIABLE'

In Oracle Database 12.2, you can now bind to user-defined record types, using a brand-new procedure, BIND_VARIABLE_PKG, which has the following signature:

DBMS_SQL.BIND_VARIABLE_PKG (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN 
  );

<datatype> can be any of the following:

RECORD
VARRAY
NESTED TABLE
INDEX BY PLS_INTEGER TABLE
INDEX BY BINARY_INTEGER TABLE

You might be wondering: what’s the “_PKG” suffix for? That’s just another way of telling you that the datatype of the value passed to the procedure must be declared in a package specification.

Because your dynamic SQL statement is executing in the SQL engine, it must be able to resolve references to your types. If a type is declared in the declaration section of a procedure, function, or anonymous block, the SQL engine cannot find it.

So using that same package-based record type for a user, and changing the name of the DBMS_SQL procedure call to BIND_VARIABLE_PKG, I can now work with user-defined records, as shown in Listing 5.

Code Listing 5: Binding and working with a record (in Oracle Database 12.2)

DECLARE
   l_block CONSTANT VARCHAR2 (2000) := q'[
DECLARE
   l_user   my_pkg.user_rt;
BEGIN
   l_user := :my_user;
   DBMS_OUTPUT.PUT_LINE (
      l_user.city || ' - ' || l_user.fav_color);
END;]';
   dummy  NUMBER;
   cur    NUMBER;
   l_user my_pkg.user_rt;
BEGIN
   l_user.city := 'Chicago';
   l_user.fav_color := 'Green';
   cur := DBMS_SQL.open_cursor ();
   DBMS_SQL.parse (cur, l_block, DBMS_SQL.native);
   DBMS_SQL.bind_variable_pkg (cur, 'my_user', l_user);
   dummy := DBMS_SQL.execute (cur);
   DBMS_SQL.close_cursor (cur);
END;
/
Chicago – Green

Bind to Integer-Indexed Associative Arrays

Associative arrays come in two flavors: INDEX BY BINARY_INTEGER (and any of its subtypes, such as the more common and preferred PLS_INTEGER) and INDEX BY VARCHAR2.

The INDEX BY BINARY INTEGER associative array, also referred to as an ibbi, was the first type of collection ever added to PL/SQL. This was back in Oracle7 (Oracle Database 7.3), and this type was referred to as a PL/SQL Table. String-indexed arrays were added in Oracle Database 11g and offer lots of interesting flexibility. They cannot, however, be bound to dynamic PL/SQL blocks, either with DBMS_SQL or EXECUTE IMMEDIATE.

With Oracle Database 12.2, though, you can bind integer-indexed associative arrays—even arrays of records—as long as they are declared in a package specification. Listing 6 demonstrates integer-indexed associative array binding.

Code Listing 6: Binding to an integer-indexed associative array

CREATE OR REPLACE PACKAGE my_pkg
   AUTHID DEFINER
AS
   TYPE user_rt IS RECORD
   (
      city        VARCHAR2 (100),
      fav_color   VARCHAR2 (100),
      age         NUMBER
   );
   TYPE users_t IS TABLE OF user_rt
      INDEX BY PLS_INTEGER;
END;
/
DECLARE
   dummy     NUMBER;
   cur       NUMBER;
   l_users   my_pkg.users_t;
   str       VARCHAR2 (3000)
      := q'[
DECLARE
   dyn_users my_pkg.users_t;
BEGIN
   dyn_users := :my_users;
   DBMS_OUTPUT.put_line (dyn_users (dyn_users.first).city);
   DBMS_OUTPUT.put_line (dyn_users (dyn_users.first).fav_color);
END;]';
BEGIN
   l_users (100).city := 'Chicago';
   l_users (100).fav_color := 'Green';
   cur := DBMS_SQL.open_cursor ();
   DBMS_SQL.parse (cur, str, DBMS_SQL.native);
   DBMS_SQL.bind_variable_pkg (cur, 'my_users', l_users);
   dummy := DBMS_SQL.execute (cur);
   DBMS_SQL.close_cursor (cur);
END;
/
Chicago
Green

But if I change to a string-indexed collection in the package specification, as follows, and run the anonymous block again, I will get a PLS-00306 error.

   TYPE users_t IS TABLE OF user_rt
      INDEX BY VARCHAR2(100);

Still Running into the PLS-00306 Error?

When you first start working with this Oracle Database 12.2 extended version of DBMS_SQL, you might find yourself encountering the PLS-00306 error that you expected to avoid.

Here are some of the reasons you might be hitting that error:

You are using BIND_VARIABLE instead of BIND_VARIABLE_PKG (or vice versa). Use BIND_VARIABLE for Booleans, but use BIND_VARIABLE_PKG for all user-defined types.
Your type is declared locally. User-defined types must be declared in the specification of a package in order for them to be available to DBMS_SQL. That package name must be included as a prefix to the type, both in the “outer” static block and in the dynamic PL/SQL block executed via DBMS_SQL.EXECUTE.
You are trying to bind to an associative array that is indexed by VARCHAR2. This is not supported for either DBMS_SQL or EXECUTE IMMEDIATE.

DBMS_SQL: Still Going Strong

There was a time when the only way you could implement dynamic SQL inside PL/SQL was through the use of DBMS_SQL. Then native dynamic SQL, with the simple EXECUTE IMMEDIATE statement, was introduced. This left DBMS_SQL to tackle only the most complex dynamic SQL requirements.

So while you might not run into a need for DBMS_SQL very often, when you do, make the best possible use of it. In Oracle Database 12.2, DBMS_SQL is now able to work with an expanded set of PL/SQL datatypes, greatly improving the scope of the problems it can solve.

Test Your New DBMS_SQL Knowledge

Select answers for accuracy and information

Question 1

Which of the following blocks will execute without an error?

DECLARE
   c_with_boolean   CONSTANT VARCHAR2 (2000)
      := q'[
    DECLARE
       x BOOLEAN := :my_boolean;
    BEGIN
       NULL;
    END;]' ;
   dummy NUMBER;
   cur   NUMBER;
BEGIN
   cur := DBMS_SQL.open_cursor ();
   DBMS_SQL.parse (cur, c_with_boolean, DBMS_SQL.native);
   DBMS_SQL.bind_variable (cur, 'my_boolean', TRUE);
   dummy := DBMS_SQL.execute (cur);
   DBMS_SQL.close_cursor (cur);
END;
/

DECLARE
   c_with_boolean   CONSTANT VARCHAR2 (2000)
      := q'[
    DECLARE
       x BOOLEAN := :my_boolean;
    BEGIN
       NULL;
    END;]' ;
   dummy NUMBER;
   cur   NUMBER;
BEGIN
   cur := DBMS_SQL.open_cursor ();
   DBMS_SQL.parse (cur, c_with_boolean, DBMS_SQL.native);
   DBMS_SQL.bind_variable_pkg (cur, 'my_boolean', TRUE);
   dummy := DBMS_SQL.execute (cur);
   DBMS_SQL.close_cursor (cur);
END;
/

DECLARE
   TYPE info_rt IS RECORD (info VARCHAR2(100));
   l_info info_rt;
   my_block CONSTANT VARCHAR2 (2000)
      := q'[
    DECLARE
       x info_rt := :my_record;
    BEGIN
       NULL;
    END;]' ;
   dummy NUMBER;
   cur   NUMBER;
BEGIN
   cur := DBMS_SQL.open_cursor ();
   DBMS_SQL.parse (cur, my_block, DBMS_SQL.native);
   DBMS_SQL.bind_variable_pkg (cur, 'my_record', TRUE);
   dummy := DBMS_SQL.execute (cur);
   DBMS_SQL.close_cursor (cur);
END;
/

Next Steps

LEARN more about Oracle Database 12c Release 2.

READ Oracle Database 12c Release 2 PL/SQL Language Reference.

WATCH Feuerstein’s Practically Perfect PL/SQL videos.

Share

Get Up to Speed with DBMS_SQL

Explore the latest DBMS_SQL features in Oracle Database 12c Release 2.