With the release of Oracle Database 12c Release 2 (Oracle Database 12.2) on the Oracle Cloud platform in Oracle Database Exadata Express Cloud Service, developers can quickly take advantage of several powerful new or enhanced features of the PL/SQL language. This article introduces some of the most exciting of these PL/SQL features.PL/Scope Extended to SQL
PL/Scope was first introduced in Oracle Database 11g, providing a powerful new tool for analyzing PL/SQL program units. With PL/Scope enabled, when you compile a program unit, information about your PL/SQL identifiers (program names, variable names, exceptions, and so on) is stored in a data dictionary view.
You can use SELECT statements to answer questions such as
In Oracle Database 12.2, PL/Scope has been extended to report on occurrences of static SQL in PL/SQL units as well as all usages of EXECUTE IMMEDIATE and OPEN-FOR (dynamic SQL). This exttended support means that you can now answer many new questions such as
To gather information about PL/SQL identifiers and SQL statements in your code base, execute the following statement in your session (you can also enable this collection at an individual-program-unit level):
ALTER SESSION SET plscope_settings='identifiers:all, statements:all' /
After your code is compiled, you can execute queries against ALL_IDENTIFIERS and ALL_STATEMENTS (this latter view is also new to Oracle Database 12.2). The following query, for example, identifies dynamic SQL usage in your code:
SELECT st.owner, st.object_name, st.line, s.text FROM all_statements st, all_source s WHERE st.TYPE IN ('EXECUTE IMMEDIATE', 'OPEN') AND st.owner = s.owner AND st.object_name = s.name AND st.line = s.line
Want to see all program units that contain SQL with hints? No problem!
SELECT owner, object_name, line, full_text FROM all_statements WHERE has_hint = 'YES'
With support for SQL statements, PL/Scope now offers endless opportunities to perform fine-grained change impact and quality assurance reports on your code base. I will explore Oracle Database 12.2 PL/Scope in more detail in a future article.Binding PL/SQL-Only Datatypes
Oracle Database 12c Release 1 (Oracle Database 12.1) introduced the ability to bind values of PL/SQL-only datatypes, most notably binding tables of records to SQL statements, using associative arrays (aka INDEX BY collections) and BOOLEANs.
But in Oracle Database 12.1, I could not accomplish the same binding with DBMS_SQL. In Oracle Database 12.2, however, the DBMS_SQL API has achieved parity with native dynamic SQL.
For example, even though the PL/SQL Boolean datatype is not recognized in SQL, I can now use DBMS_SQL to execute a call to a PL/SQL function that returns a Boolean in EXECUTE IMMEDIATE:
DECLARE c_with_boolean CONSTANT VARCHAR2 (2000) := q'[ DECLARE x BOOLEAN := :my_boolean; BEGIN IF x then DBMS_OUTPUT.PUT_LINE ('x = ' || 'true'); ELSE DBMS_OUTPUT.PUT_LINE ('x = ' || 'false'); END IF; END;]'; dummy NUMBER; cur NUMBER cur := DBMS_SQL.open_cursor (); BEGIN 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; / x = true
Listing 1 provides an example of how to use the new BIND_VARIABLE_PKG procedure in DBMS_SQL to bind an associative array whose type is defined in a package specification.
Code Listing 1: DBMS_SQL.BIND_VARIABLE_PKG procedure binds an associative array
CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS TYPE numbers_rt IS RECORD ( n1 NUMBER, n2 NUMBER ); TYPE numbers_t IS TABLE OF numbers_rt INDEX BY PLS_INTEGER; END; / DECLARE c_block CONSTANT VARCHAR2 (1000) := q'[ declare l_numbers pkg.numbers_t; begin l_numbers := :nums; dbms_output.put_line ( l_numbers(1).n1 ||' = ' || l_numbers(1).n2); end;]' ; l_feedback NUMBER; l_cursor NUMBER; l_numbers pkg.numbers_t; BEGIN l_numbers (1).n1 := 1000; l_numbers (1).n2 := 2000; l_cursor := DBMS_SQL.open_cursor (); DBMS_SQL.parse (l_cursor, c_block, DBMS_SQL.native); DBMS_SQL.bind_variable_pkg ( l_cursor, 'nums', l_numbers); l_feedback := DBMS_SQL.execute (l_cursor); DBMS_SQL.close_cursor (l_cursor); END dyn_sql_ibbi; /
Given the power of native dynamic SQL (EXECUTE IMMEDIATE), DBMS_SQL is used only for the most complex dynamic SQL requirements, such as method 4 (variable number of bind variables or elements in the SELECT list). If you do need to use DBMS_SQL in Oracle Database 12.2, you will now find it able to handle even more of those most complicated requirements.New PL/SQL Pragma: DEPRECATE
Software is constantly evolving: bugs are fixed, new features are added, and better ways to do things are discovered and implemented.
A great example of this dynamic from PL/SQL itself is the UTL_CALL_STACK package. This package was first introduced in Oracle Database 12c Release 1, and it improves upon the functionality already provided by the following functions in the DMBS_UTILITY package: FORMAT_CALL_STACK, FORMAT_ERROR_STACK, and FORMAT_ERROR_BACKTRACE.
The same thing happens in PL/SQL code developed by customers. The now-outdated subprograms (or other elements) of one’s API cannot be removed immediately; that would break existing code. But everyone would like to make sure that any new code uses the new API.
The new DEPRECATE pragma (compiler directive) in Oracle Database 12.2 will help you accomplish this transition in a smooth, error-free fashion. It provides a formal way to communicate information about deprecated elements with a power that ordinary external documentation cannot convey.
You can apply this new pragma to a whole unit, to a subprogram within a unit, at any level of nesting, to any definition, and so on. When a unit is compiled that makes a reference to a deprecated element, a warning is displayed (when you have compile time warnings enabled).
Let’s take a look at some examples.
CREATE PACKAGE pkg AUTHID DEFINER ASPRAGMA DEPRECATE(pkg); PROCEDURE proc; FUNCTION func RETURN NUMBER; END; /
CREATE PACKAGE pkg AUTHID DEFINER AS PROCEDURE proc; PRAGMA DEPRECATE ( proc, 'pkg.proc deprecated. Use pkg.new_proc instead.'); PROCEDURE new_proc; END; /
Let’s try using that deprecated procedure, with warnings enabled.
ALTER SESSION SET plsql_warnings = 'enable:all' / CREATE OR REPLACE PROCEDURE use_deprecated AUTHID DEFINER IS BEGIN pkg.proc; END; / Procedure USE_DEPRECATED compiled with warnings PLW-06020: reference to a deprecated entity: PROC declared in unit PKG[4,14]. pkg.proc deprecated. Use pkg.new_proc instead.
PL/SQL in Oracle Database 12.2 has four new warnings to help you utilize the DEPRECATE pragma:
6019. The entity was deprecated and could be removed in a future release. Do not use the deprecated entity.
6020. The referenced entity was deprecated and could be removed in a future release. Do not use the deprecated entity. Follow the specific instructions in the warning if any are given.
6021. Misplaced pragma. The DEPRECATE pragma should follow immediately after the declaration of the entity that is being deprecated. Place the pragma immediately after the declaration of the entity that is being deprecated.
6022. This entity cannot be deprecated. Deprecation only applies to entities that may be declared in a package or type specification as well as to top-level procedure and function definitions. Remove the pragma.New Code Coverage Capabilities
Suppose you’ve built a package with 5,000 lines of mission-critical code. And you’ve even built some unit tests to make sure the subprograms meet requirements. How can you tell if your tests actually execute all the code you’ve written? By performing code coverage analysis.
Prior to Oracle Database 12.2, you could cobble together your own code coverage reports from the data generated by DBMS_PROFILER, but it was difficult to do so with a high degree of accuracy and consistency. That’s because DBMS_PROFILER was designed to report execution times on a line-by-line basis and that data only tangentially addresses code coverage.
With Oracle Database 12.2, PL/SQL now includes a new package, DBMS_PLSQL_CODE_COVERAGE, and supporting tables that enable you to collect data at the basic block level. A basic block is a succession of instructions with no way in except to the first and no way out except from the last.
Because a basic block is a notion that belongs to the program’s executable representation, it isn’t always obvious to the programmer how basic blocks map to the program’s source code, but the compilation system can manage a reasonable approximation.
The DBMS_PLSQL_CODE_COVERAGE package populates a table named DBMSCC_BLOCKS that records each block in every unit into which the execution point passes during a coverage run. Each block is attributed to its unit and run, and the row in DBMSCC_BLOCKS notes its start line and column in the source code and whether or not it was executed (aka “covered”).
Gathering code coverage statistics in Oracle Database 12.2 is very easy. First create the tables that are populated by this code coverage feature:
BEGIN dbms_plsql_code_coverage. create_coverage_tables (); END; /
Then use the start and stop procedures to start coverage, run your tests, and stop your coverage.
BEGIN dbms_plsql_code_coverage.start_coverage ('Test Run 17'); run_tests; dbms_plsql_code_coverage.stop_coverage; END; /
You will then find data in three tables:
DBMSPCC_RUNS. This table includes a row for every coverage run.
DBMSPCC_UNITS. This table includes all the program units for which coverage data was gathered for a given run.
DBMSPCC_BLOCKS. This table includes code coverage data for each basic block.
A future article will explore this feature in much greater detail, but here is an example of the kind of query you can write against these code coverage tables.
select u.Owner, u.Name, u.Type, Round((Sum(b.Covered)*100)/Count(*)) as Pct_Covered from DBMSPCC_runs r inner join DBMSPCC_units u using(Run_id) inner join DBMSPCC_blocks b using(Object_ID) where r.Run_Comment = :r group by u.Owner, u.Name, u.Type order by 1, 2, 3
PL/SQL developers can now employ this widely used, standard approach to test code coverage.Long Identifiers
The moment you’ve all been waiting for has arrived! In Oracle Database 12.2, you can now create a database object with a name of up to 128 bytes, such as
CREATE VIEW all_approved_quizzes_already_taken AS SELECT ...
CREATE PROCEDURE qdb_mark_quiz_closed_when_all_players_finished AS ...
instead of something harder to understand, such as:
CREATE PROCEDURE qdb_close_quiz_all_done_ AS ...
Is it possible to still see the “ORA-00972: identifier is too long” error message? Sure, but now it will most likely mean that it truly is too long, as in: no one wants to have to read that!
Providing longer identifiers gives customers greater flexibility in defining their naming schemes, such as longer and more expressive table names. Having longer identifiers also enables smoother object name migration between databases with different character sets, such as Thai to Unicode.Static PL/SQL Expressions Allowed
Suppose I want to declare a variable to hold a string that could get very large. The maximum length of a VARCHAR2 variable is 32767. I would have to declare the variable as follows:
DECLARE l_big_string VARCHAR2(32767);
because the length specified for VARCHAR2 has to be a literal—up through Oracle Database 12.1.
The problem with this declaration is that the maximum length is now hard-coded. If in some future version of Oracle Database, the maximum size of a VARCHAR2 is increased, I would have to go back into my code and find all occurrences of “32767” and replace them with the new value.
Now in Oracle Database 12.2, I can greatly simply my life and code. I can define a package that contains a constant:
CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER IS c_max_length constant integer := 32767; END; /
I can then declare my variable as follows:
DECLARE l_big_string1 VARCHAR2 (pkg.c_max_length);
I can even use expressions in place of a literal, with the caveat that the expression must be evaluated at compile time.
DECLARE l_half_size VARCHAR2 (pkg.c_max_length / 2);
whereas this does not:
DECLARE l_half_size VARCHAR2 ( pkg.c_max_length / TO_NUMBER (TO_CHAR (SYSDATE, 'MM')));
The ability to use expressions (and not just literals) in a broader context has two primary benefits for PL/SQL developers:
Oracle Database 12.2 is packed full of features that make it easier for Oracle Database developers to write high-quality applications that can be optimized and maintained more easily. And there’s more! In Oracle Database 12.2, you can also work with JSON data directly in PL/SQL. This big and important topic will be covered in the detail it deserves in a future article.
From tighter and cleaner integration with SQL to code coverage analysis, Oracle Database 12.2 offers tremendous opportunities for programmers to better use the database as a powerful platform for development.
LEARN more about Oracle Database 12c Release 2.
MEET the Oracle Developer Advocates team.
TEST your SQL and PL/SQL knowledge.
READ more about PL/Scope.
WATCH Feuerstein’s Practically Perfect PL/SQL videos.
READ more about binding PL/SQL datatypes.
Photography by Meric Dagli, Unsplash
Steven Feuerstein is Oracle Corporation's Developer Advocate for PL/SQL, and an expert on the Oracle PL/SQL language, having written ten books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (all published by O'Reilly Media). Steven has been developing software since 1980, spent five years with Oracle back in the "old days" (1987-1992), and was PL/SQL Evangelist for Quest Software (and then Dell) from January 2001 to February 2014 - at which point he returned joyfully to Oracle Corporation. He was one of the original Oracle ACE Directors and writes regularly for Oracle Magazine, which named him the PL/SQL Developer of the Year in both 2002 and 2006. He is also the first recipient of ODTUG's Lifetime Achievement Award (2009).