Application Development

The Power of Cloud PL/SQL

Oracle Database 12c Release 2 delivers better database development to the cloud.

By Steven Feuerstein Oracle ACE Director

November/December 2016

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

  • Where are all the locations in code in which the value of a specific variable is modified?
  • Did I declare any exceptions that I never raise and can therefore be removed?
  • Which parameters violate naming conventions?

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

  • Where in my code do I perform updates on a given table?
  • Where have I used SQL hints in my PL/SQL code?
  • Where are all possible SQL injection entry points in my code?
  • Where does that slow SQL statement, identified by its SQL ID, appear in my code?

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.

  1. Deprecate an entire package.
    CREATE PACKAGE pkg AUTHID DEFINER
    ASPRAGMA DEPRECATE(pkg);
       PROCEDURE proc;
       FUNCTION func RETURN NUMBER;
    END;
    /
  2. Deprecate a subprogram in a package. Note the comment added to the pragma. This text will be displayed along with the warning/error information.
    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 ...

or

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.

This works:

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:

  1. Programs are much more adaptable and able to deal with changes in their environment.
  2. Programs are more compact, clearer, and substantially easier to understand and maintain.
Easier and Better Programming

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.

Next Steps

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.

 

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.