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.
For Oracle Database 12c Release 1 (12.1), Oracle Database PL/SQL Language Reference documents new features, including
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.
Each Oracle Magazine PL/SQL article by Steven Feuerstein offers a quiz to test your knowledge of the information provided in it. The quiz appears below and also at the PL/SQL Challenge (plsqlchallenge.com), a website that offers online quizzes on the PL/SQL language as well as SQL, Oracle Application Express, database design, and deductive logic.
Here is your quiz for this article.
I create and populate a table as follows:
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; /Which of the choices will display “Carrera” followed by “Corvette” after execution?
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 /
DOWNLOAD Oracle Database 12c
TEST your PL/SQL knowledge
READ more Feuerstein
Photography by Meric Dagli, Unsplash