SQL in PL/SQL Enhancements

Oracle Database 12c enhances the PL/SQL function result cache, improves PL/SQL execution in SQL, adds a whitelist, and fine-tunes privileges.

By Steven Feuerstein

November/December 2013

With the release of Oracle Database 12c, PL/SQL offers several enhancements for writing and executing SQL statements from within PL/SQL. This article covers new Oracle Database 12c features that enable you to

• Bind values from PL/SQL-only datatypes to SQL statements

• Return result sets from SQL queries implicitly, easing the migration path from languages such as Transact-SQL to Oracle PL/SQL

• Define views that behave more or less like an invoker’s rights program unit

Prior to Oracle Database 12c, whenever you needed to bind a PL/SQL expression by using EXECUTE IMMEDIATE or DBMS_SQL, the datatype of that expression had to be a known SQL datatype. Specifically, you could not bind Booleans and user-defined types declared in a package specification, including records and collections.

Oracle Database 12c removes almost all of these restrictions.

You can now, for example, bind a Boolean value if you use EXECUTE IMMEDIATE to execute a dynamic PL/SQL block. Listing 1 demonstrates the block execution.

Code Listing 1: Binding a Boolean value and using EXECUTE IMMEDIATE

CREATE OR REPLACE PACKAGE restaurant_pkg
AS
   TYPE item_list_t
      IS TABLE OF VARCHAR2 (30);
   PROCEDURE eat_that (
      items_in              IN item_list_t,
      make_it_spicy_in_in   IN BOOLEAN);
END;
/
CREATE OR REPLACE PACKAGE BODY restaurant_pkg
AS
   PROCEDURE eat_that (
      items_in              IN item_list_t,
      make_it_spicy_in_in   IN BOOLEAN)
   IS
   BEGIN
      FOR indx IN 1 .. items_in.COUNT
      LOOP
         DBMS_OUTPUT.put_line (
               CASE
                  WHEN make_it_spicy_in_in
                  THEN
                     'Spicy '
               END
            || items_in (indx));
      END LOOP;
   END;
END;
/
DECLARE
   things   restaurant_pkg.item_list_t
      := restaurant_pkg.item_list_t (
            'steak',
            'quiche',
            'eggplant');
BEGIN
   /* Requires Oracle Database 12c or later */
   EXECUTE IMMEDIATE
      'BEGIN restaurant_pkg.eat_that(:l, :s); END;'
      USING things, TRUE;
END;
/

You can also bind associative arrays and use them inside a call to the TABLE operator, both impossible before Oracle Database 12c. The code in Listing 2 demonstrates binding and the use of a SELECT statement to retrieve data from an associative array.

Code Listing 2: Binding and using associative arrays

CREATE OR REPLACE PACKAGE names_pkg
   AUTHID CURRENT_USER
AS
   TYPE names_t
   IS TABLE OF VARCHAR2 (100)
      INDEX BY PLS_INTEGER;
   PROCEDURE display_names (
      names_in   IN names_t);
END names_pkg;
/
SHO ERR
CREATE OR REPLACE PACKAGE BODY names_pkg
AS
   PROCEDURE display_names (
      names_in   IN names_t)
   IS
   BEGIN
      FOR indx IN 1 .. names_in.COUNT
      LOOP
         DBMS_OUTPUT.put_line (
            names_in (indx));
      END LOOP;
   END;
END names_pkg;
/
SHO ERR
DECLARE
   l_names   names_pkg.names_t;
BEGIN
   l_names (1) := 'Loey';
   l_names (2) := 'Dylan';
   l_names (3) := 'Indigo';
   l_names (4) := 'Saul';
   l_names (5) := 'Sally';
   EXECUTE IMMEDIATE
      'BEGIN names_pkg.display_names (:names); END;'
      USING l_names;
   FOR rec
      IN (SELECT * FROM TABLE (l_names))
   LOOP
      DBMS_OUTPUT.put_line (
         rec.COLUMN_VALUE);
   END LOOP;
END;
/

And there’s more for SQL datatypes. Prior to Oracle Database 12c, the maximum length of a VARCHAR2 datatype in SQL was just 4,000 bytes, whereas the maximum length in PL/SQL was (and is) 32,767 bytes. With Oracle Database 12c, the maximum length for VARCHAR2 and NVARCHAR2 is now 32,767 bytes.

Note: the MAX_STRING_SIZE database parameter must be set to EXTENDED to take advantage of these extended lengths; the default value is STANDARD.

For more information on SQL datatypes, see Oracle Database SQL Language Reference 12c Release 1 (12.1).

Before Oracle Database 12c, PL/SQL did not support the ability to create a procedure that simply returns the contents of a result set to the screen. To do this in PL/SQL, developers had to write a query, iterate through the result set, and call DBMS_OUTPUT.PUT_ LINE to display the results.

In Oracle Database 12c, developers can now create a procedure that returns results directly to the screen in PL/SQL, which will be of benefit primarily to developers and applications migrating from Transact-SQL to PL/SQL (Welcome, new PL/SQL developers!) and also as a testing aid (because it’s now easier to write a quick procedure to verify the contents of a table). Oracle Database 12c implements this capability by adding new functionality to the DBMS_SQL package.

Suppose I want to display the last names of all employees in a given department. I can now write the following, which uses the new DBMS_SQL.RETURN_RESULT procedure:

CREATE OR REPLACE PROCEDURE show_emps (
   department_id_in IN
   employees.department_id%TYPE)
IS
   l_cursor   SYS_REFCURSOR;
BEGIN
   OPEN l_cursor FOR
        SELECT last_name
          FROM employees
         WHERE department_id =
               department_id_in
      ORDER BY last_name;
   DBMS_SQL.return_result (l_cursor);
END;
/

And when I execute the procedure in SQL*Plus for department ID 20, I see the following:

BEGIN
   show_emps (20);
END;
/
PL/SQL procedure successfully completed.
ResultSet #1
LAST_NAME
———————————————
Fay
Hartstein

You can also return more than one result from a single procedure. If you want to retrieve rows from a result from within a PL/SQL program, you can call the DBMS_SQL.GET_NEXT_RESULT procedure to get the next cursor and then use other DBMS_SQL subprograms to fetch rows of data. For more information on DBMS_SQL.RETURN_RESULT and DBMS_SQL.GET_NEXT_RESULT, see Oracle Database PL/SQL Packages and Types Reference 12c Release 1 (12.1).

Prior to Oracle Database 12c, if your view executed a function, it was always run under the privileges of the view’s owner, not the privileges of the function’s owner. So if the function was defined as invoker’s rights, the behavior could be quite different from what you would have expected.

Oracle Database 12c adds the BEQUEATH clause for views, so you can define a view that will accommodate invoker’s rights functions referenced in a view. To help you utilize this feature, Oracle Database 12c offers two new functions that tell you about the invoking user, based on whether invoker’s rights or definer’s rights are used:

• ORA_INVOKING_USER: Returns the name of the user who is invoking the current statement or view. If the invoking user is defined by the Real Application Security feature of Oracle Database, this function will return XS$NULL.

• ORA_INVOKING_USERID: Returns the identifier (ID) of the user who is invoking the current statement or view. If the invoking user is a Real Application Security–defined user, this function will return an ID that is common to all Real Application Security sessions but is different from the ID of any database user.

Let’s take a look at how this feature works. In the HR schema, I create a table, emps_12cemps, and a function, emps_count, that calls the ORA_INVOKING* functions, as shown in Listing 3.

Code Listing 3: Creating a table and a function to test the BEQUEATH clause

CREATE TABLE emps_12cemps
(
   employee_id     INTEGER,
   department_id   INTEGER,
   last_name       VARCHAR2 (100)
)
/
BEGIN
   INSERT INTO emps_12cemps VALUES (1, 100, 'abc');
   INSERT INTO emps_12cemps VALUES (2, 100, 'def');
   INSERT INTO emps_12cemps VALUES (3, 200, '123');
   COMMIT;
END;
/
CREATE OR REPLACE FUNCTION emps_count (
   department_id_in IN INTEGER)
   RETURN PLS_INTEGER
   AUTHID CURRENT_USER
IS
   l_count    PLS_INTEGER;
   l_user     VARCHAR2 (100);
   l_userid   VARCHAR2 (100);
BEGIN
   SELECT COUNT (*)
     INTO l_count
     FROM emps_12cemps
    WHERE department_id = department_id_in;
   /* Show who is invoking the function */
   SELECT ora_invoking_user INTO l_user FROM DUAL;
   SELECT ora_invoking_userid INTO l_userid FROM DUAL;
   DBMS_OUTPUT.put_line (l_user);
   DBMS_OUTPUT.put_line (l_userid);
   RETURN l_count;
END;
/

Then I create a view, emp_counts_v, specifying invoker’s rights (CURRENT_USER) in the BEQUEATH clause, and I make sure that SCOTT can query that view:

CREATE OR REPLACE VIEW emp_counts_v
   BEQUEATH CURRENT_USER
AS
   SELECT department_id,
          emps_count (department_id)
          emps_in_dept
     FROM emps_12cemps
/
GRANT SELECT ON emp_counts_v TO scott
/

In the SCOTT schema, I create another emps_12cemps table but populate it with different data:

CREATE TABLE emps_12cemps
(
   employee_id     INTEGER,
   department_id   INTEGER,
   last_name       VARCHAR2 (100)
)
/
BEGIN
   INSERT INTO emps_12cemps
   VALUES (1, 200, 'SCOTT.ABC');
   INSERT INTO emps_12cemps
   VALUES (2, 200, 'SCOTT.DEF');
   INSERT INTO emps_12cemps
   VALUES (3, 400, 'SCOTT.123');
   COMMIT;
END;
/

Then I turn on serveroutput and select all the rows from the emp_counts_v view. Here’s the output of the query:

SQL> SELECT * FROM hr.emp_counts_v
  2  /
DEPARTMENT_ID  EMPS_IN_DEPT
—————————————— —————————————
          100             0
          100             0
          200             2
SCOTT
107
SCOTT
107
SCOTT
107

As you can see, the data returned by the view is from HR’s table (there is a department ID value of 100), but the totals returned by the emps_count function call reflect data in SCOTT’s table. And the ORA_INVOKING* functions return SCOTT’s information.

Note that BEQUEATH CURRENT_USER does not transform the view itself into an invoker’s rights object. Name resolution within the view is still handled with the view owner’s schema, and privilege checking for the view is done with the view owner’s privileges.

The primary benefit of this feature is that it enables functions such as SYS_CONTEXT and USERENV to return consistent results when these functions are referenced in a view.

In the next issue of Oracle Magazine, I will explore the remaining and most interesting new features in the Oracle Database 12c PL/SQL language. These include the UTL_CALLSTACK package—which gives much-more-detailed information about the execution call stack, error stack, and error backtrace—and the new DBMS_UTILITY.EXPAND_SQL_TEXT procedure—which recursively replaces any view references in the input SQL query with the corresponding view subquery.

CREATE TABLE plch_autos
(
   auto_name   VARCHAR2 (100),
   auto_type   VARCHAR2 (100)
)
/
BEGIN
   INSERT INTO plch_autos
        VALUES ('Corvette', 'Sports');
   INSERT INTO plch_autos
        VALUES ('Yugo', 'Not Really');
   INSERT INTO plch_autos
        VALUES ('Carrera', 'Sports');
   COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE
plch_show_autos (
   auto_type_in   IN plch_autos.auto_type%TYPE)
IS
BEGIN
   FOR rec IN (  SELECT auto_name
                   FROM plch_autos
                  WHERE auto_type = auto_type_in
               ORDER BY auto_name)
   LOOP
      DBMS_OUTPUT.put_line (rec.auto_name);
   END LOOP;
END;
/
BEGIN
   plch_show_autos ('Sports');
END;
/

CREATE OR REPLACE PROCEDURE
plch_show_autos (
   auto_type_in   IN plch_autos.auto_type%TYPE)
IS
   l_cursor   SYS_REFCURSOR;
BEGIN
   OPEN l_cursor FOR
        SELECT auto_name
          FROM plch_autos
         WHERE auto_type = auto_type_in
      ORDER BY auto_name;
   DBMS_SQL.return_result (l_cursor);
END;
/
BEGIN
   plch_show_autos ('Sports');
END;
/

SELECT auto_name
  FROM plch_autos
 WHERE auto_type = 'Sports'
ORDER BY auto_name
/