More on Oracle Database 11g

Our technologist takes another look at Oracle Database 11g.

November/December 2007

Last issue, I wrote about some new Oracle Database 11g features, and I continue that coverage in this issue, focusing on my favorite procedural language, PL/SQL. PL/SQL is a competent language. In my book Effective Oracle by Design , I wrote:

PL/SQL is Oracle's procedural extension to SQL, and it is a true 3GL programming language. It was first introduced way back in version 6 of the database, giving us the ability to code "anonymous blocks" in our client applications and submit them for processing on the database. In Oracle6, there were no stored procedures, no packages, and no triggers. The ability to store PL/SQL in the database came with version 7 in 1992.

Today, PL/SQL is competent, mature, and full-featured, offering everything you expect to find in a 3GL programming language. In general, I find that PL/SQL is underused in Oracle applications and rarely exploited to its full potential.

That was written when Oracle9i Database Release 2 was just going into production—three major releases ago—and it still rings true today. I can honestly say that Oracle Database 11g makes PL/SQL an even more compelling language, and in this issue, I take a look at some of the reasons why.

Have you ever inherited someone else's code? It is a lot like misplacing your keys: annoying. And it is always fully commented, well designed, modular, and easy to understand, right? No, in fact, the opposite is typically true. So you have a couple thousand lines of inherited code, and you need to understand, fix, and enhance it. That is a job easier said than done, but an important new compile option for PL/SQL makes the "understand" component of your task much easier. This new compile option is called PL/Scope, and the documentation describes it this way:

PL/Scope is a compiler-driven tool that collects data about user-defined identifiers from PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, and assignment), and the location of each usage in the source code.

That is a fancy way of saying that all of your variables, procedures, functions, and so on are extracted from the code and made visible in a database table for you (or some tool) to query. I believe that seeing an example is the easiest way to understand something, so a quick demonstration is called for. First, I need to enable this feature, and I do that via a session-settable PLSCOPE_SETTINGS parameter:

SQL> alter session set
  2    PLSCOPE_SETTINGS =
  3   'identifiers:all'
  4    /
Session altered.

That enables the compiler feature that will extract and store this additional information. The other (default) setting for PLSCOPE_SETTINGS is identifiers:none, which disables this collection feature.

Now, once that setting is enabled, any code compiled in that session will have its identifiers extracted and stored in a set of data dictionary tables, exposed via views—including USER_IDENTIFIERS and USER_SOURCE—for my use. So the next thing I need to do is compile some code; for testing, I used my RUNSTATS package. After compiling the package, I was able to run the query in Listing 1.

Code Listing 1: Query on USER_IDENTIFIERS view

SQL> select name, type, usage, line
  2       from user_identifiers
  3     where object_name= 'RUNSTATS_PKG'
  4       and object_type= 'PACKAGE BODY'
  5     order by name, type, line
  6     /
NAME                         TYPE         USAGE       LINE
------------                 --------     ---------  -----
DBMS_OUTPUT                  SYNONYM      REFERENCE    35
DBMS_OUTPUT                  SYNONYM      REFERENCE    37
DBMS_OUTPUT                  SYNONYM      REFERENCE    41
DBMS_OUTPUT                  SYNONYM      REFERENCE    45
DBMS_OUTPUT                  SYNONYM      REFERENCE    50
.
.
.
G_START                      VARIABLE     DECLARATION   4
G_START                      VARIABLE     ASSIGNMENT   16
G_START                      VARIABLE     REFERENCE    22
G_START                      VARIABLE     ASSIGNMENT   26
G_START                      VARIABLE     REFERENCE    33
P_DIFFERENCE_THRESHOLD       FORMAL IN    ASSIGNMENT   30
P_DIFFERENCE_THRESHOLD       FORMAL IN    DECLARATION  30
P_DIFFERENCE_THRESHOLD       FORMAL IN    REFERENCE    67
RS_MIDDLE                    PROCEDURE    DEFINITION   19
RS_START                     PROCEDURE    DEFINITION    8
RS_STOP                      PROCEDURE    DEFINITION   30
RUNSTATS_PKG                 PACKAGE      DEFINITION    1
X                            ITERATOR     DECLARATION  54
X                            ITERATOR     REFERENCE    70
X                            ITERATOR     DECLARATION  80
X                            ITERATOR     REFERENCE    96
39 rows selected.

The listing shows me all referenced identifiers (the package DBMS_OUTPUT that I invoke many times, for example) as well as all local, global, and parameter variables in my code. It shows me not only the variables but also how they are used, where they are defined, where I reference them, and where I assign to them. Taking this one step further, I can run the query in Listing 2.

Code Listing 2: Query on USER_IDENTIFIERS and USER_SOURCE

SQL> select a.line, a.usage,
  2         b.text
  3       from user_identifiers a,
  4         user_source b
  5    where a.line = b.line
  6       and b.name = a.object_name
  7       and b.type = a.object_type
  8       and a.object_name= 'RUNSTATS_PKG'
  9       and a.object_type= 'PACKAGE BODY'
 10      and a.name  = 'G_START'
 11    /
      LINE    USAGE             TEXT
 ----------   -----------       ------------------------------------------
         4    DECLARATION       g_start number;
        16    ASSIGNMENT        g_start := dbms_utility.get_time;
        22    REFERENCE         g_run1 := (dbms_utility.get_time-g_start);
        26    ASSIGNMENT        g_start := dbms_utility.get_time;
        33    REFERENCE         g_run2 := (dbms_utility.get_time-g_start);

Note how I can focus right in on where and how the G_START variable is used.

By itself, this new feature is really cool, but it will be even more useful when the tools I use to access the database become aware of it. Fortunately, I don't have to wait long—not at all, really—because Oracle SQL Developer is already aware of this new capability. I had Kris Rice, the director of development for Oracle SQL Developer, do exactly what I just demonstrated, only by using Oracle SQL Developer, instead of SQL*Plus, as the front end. In 15 seconds, he did what took me a bit of time and SQL to accomplish. Figure 1 shows his results. So, with the PLScope Identifier Lookup, just by clicking a line in the right pane of Oracle SQL Developer, you can go right to the source code in the left pane, and the pop-up bubble help displays the relevant information as you hover over the line.

For more information on this feature and how to use PL/Scope in its entirety (I've just touched on the capabilities here!), see chapter 8 of Oracle Database Advanced Application Developer's Guide 11g Release 1.

PL/SQL has supported dynamic SQL for a long time—since Oracle 7, in fact. Early releases provided support for dynamic SQL in PL/SQL via the DBMS_SQL built-in package. Later, with Oracle8i Database Release 1, PL/SQL added native dynamic SQL, which enabled us to dynamically open REF CURSORS and execute arbitrary SQL. At that time, many people said, "DBMS_SQL is dead; native dynamic SQL is so much easier that no one will ever use DBMS_SQL."

Well, that turned out not to be the case—for many reasons. First, to use native dynamic SQL, you needed to know at compile time the number of inputs (bind variables) to the SQL being executed as well as the number of types of the outputs. But that frequently defeated the purpose of dynamic SQL, because often you do not know the number of inputs and outputs (let alone their datatypes) at compile time—the SQL isn't known until runtime. When that is the case, REF CURSORS are not very useful but DBMS_SQL—with its procedural API approach—is.

So DBMS_SQL still lives. For example, a popular download from AskTom is my DUMP_CSV routine (asktom.oracle.com/tkyte/dump_csv.html). It takes a SQL statement (not a cursor, but a query in a string) as input and produces a comma-delimited file as output. This is something that would be impossible to do in PL/SQL with a REF CURSOR in Oracle Database 10g and earlier releases, because PL/SQL would not and could not know the number of items in the select list.

But DBMS_SQL is somewhat limiting—it is designed purely for PL/SQL, not for client applications. In fact, if you use PL/SQL to open a cursor with DBMS_SQL and return it to a client application, there is nothing that client application can do with the cursor except send it back to PL/SQL for processing. Additionally, if you have an existing stored procedure that returns a REF CURSOR to a client application and you try to call that from a PL/SQL routine, you'll likely find that the PL/SQL routine is not able to make heads or tails of the REF CURSOR—because PL/SQL needs to know the number and types of outputs at compile time.

Well, in Oracle Database 11g Release 1, these limitations are removed. Two new functions have been added to the DBMS_SQL package:

• DBMS_SQL.TO_NUMBER_CURSOR takes a REF CURSOR as input and returns a DBMS_SQL-friendly cursor handle that can be used in any call to DBMS_SQL—as if DBMS_SQL had created the cursor itself.

• DBMS_SQL.TO_REFCURSOR takes a DBMS_SQL cursor handle as input and returns a REF CURSOR suitable for returning to a client application.

This greatly extends the use cases of DBMS_SQL for PL/SQL—especially in environments such as Oracle Application Express. One of the issues with Oracle Application Express in the past was that the environment demanded that you use DBMS_SQL cursors so that it could procedurally process the results. Because Oracle Application Express is written in PL/SQL, REF CURSORS would not work in the past. But that meant that if you had already written stored procedures that returned result sets, repurposing them in an Oracle Application Express environment was difficult, if not impossible, because these stored procedures would have been returning REF CURSORS to existing client applications. Vice versa, if you wrote an Oracle Application Express application, you would find having a Java client access the stored procedures you wrote for Oracle Application Express to be cumbersome at best, because the stored procedures you wrote would be returning DBMS_SQL cursors, not REF CURSORS.

In Oracle Database 11g, this is no longer a problem. We can convert easily and rapidly between the two cursor types now. By way of example, let's look at the DUMP_CSV routine referenced above. I won't reproduce all of the code here, but I will show the interface to it:

create or replace
function dump_csv(
   p_query       in varchar2,
   p_separator  in varchar2 default ' , ' ,
   p_dir          in varchar2 ,
   p_filename   in varchar2 )
return number
AUTHID CURRENT_USER
is
.
.
.

And you might call the routine as shown in Listing 3.

Code Listing 3: Calling DUMP_CSV routine

SQL> create or replace directory TMP as '/tmp'
  2    /
Directory created.
SQL> declare
  2       l_rows  number;
  3    begin
  4       l_rows := dump_csv( 'select *
  5                                        from all_users
  6                                      where rownum < 5',
  7                                    ' , ' , 'TMP', 'test.dat' );
  8    end;
  9    /
PL/SQL procedure successfully completed.
SQL> !cat /tmp/test.dat
SYS,0,05-JUN-07
SYSTEM,5,05-JUN-07
OUTLN,9,05-JUN-07
DIP,13,05-JUN-07

Now, what if you wanted to call this by using a REF CURSOR as input? To use a REF CURSOR, DUMP_CSV needs to know how many columns the SELECT statement fetches at compile time , because the syntax for fetching from a REF CURSOR is

fetch ref_cursor into host_variable1,
host_variable2, ..., host_variableN;

But this generic utility cannot do that—it cannot fetch into a fixed number of columns, because the query might select 5 columns one time and 500 columns the next. That is why I used a string in the past—so I could open it with DBMS_SQL and process it. Now that we can convert a REF CURSOR into a DBMS_SQL cursor and back, we can utilize REF CURSORS (or DBMS_SQL cursors). Listing 4 shows the revised interface, and it uses a package (called UNLOAD) so we can overload the function to accept a query in a string; an opened REF CURSOR; or an opened, executed DBMS_SQL cursor type.

Code Listing 4: New UNLOAD package specification

SQL> create or replace package unload
  2    as
  3    function csv
  4    ( p_query       in out sys_refcursor,
  5      p_separator in varchar2 default ' , ' ,
  6      p_dir           in varchar2 ,
  7      p_filename   in varchar2 )
  8    return number;
  9    function csv
 10   ( p_query       in out integer,
 11     p_separator  in varchar2 default ' , ' ,
 12     p_dir           in varchar2 ,
 13     p_filename   in varchar2 )
 14   return number;
 15   function csv
 16   ( p_query       in varchar2,
 17     p_separator  in varchar2 default ' , ' ,
 18     p_dir           in varchar2 ,
 19     p_filename   in varchar2 )
 20   return number;
 21   end;
 22   /
Package created.

So now we have an API that supports three different invocations for the query—a string; an opened REF CURSOR; and an opened, executed DBMS_SQL cursor. The advantage of the REF CURSOR input is that it allows a PL/SQL routine to easily open a cursor by using bind variables (and that is a good thing) or reuse an existing routine that returns a result set and repurpose that result set (unloading it, instead of doing whatever the original client application used to do with it).

The modifications to the generic code referenced above (the DUMP_CSV routine) were minor. Basically, all I did was remove the DBMS_SQL.OPEN_CURSOR call and the DBMS_SQL.PARSE/EXECUTE calls. That was it—none of the remaining code was modified. The routine accepted the DBMS_SQL cursor (instead of a string) as input and instead of calling OPEN_CURSOR, PARSE, and EXECUTE, it will process whatever cursor was sent to it as before. So the last version of the CSV function above (defined in lines 10 through 13 in Listing 4) will be implemented in the package body by use of the existing code, minus the OPEN_CURSOR, PARSE, and EXECUTE calls.

The other two variants of the CSV function above were fairly simple to implement. Listing 5 shows the SYS_REFCURSOR variant.

Code Listing 5: CSV function, SYS_REFCURSOR variant

SQL>
.
.
.
  4   function csv
  5   ( p_query in out sys_refcursor,
  6     p_separator  in varchar2 default ' , ' ,
  7     p_dir           in varchar2 ,
  8     p_filename   in varchar2 )
  9   return number
 10   is
 11       l_cursor integer :=  dbms_sql.to_cursor_number(p_query);
 12   begin
 13       return csv( l_cursor, p_separator, p_dir,p_filename);
 14   end;

As you can see, all I need to do here is convert the SYS_REFCURSOR into a DBMS_SQL cursor and invoke the original code (minus the OPEN_CURSOR, PARSE, and EXECUTE calls). The second variant of the CSV function was a little more involved but really was the result of the code I removed. I just cut and pasted it, as shown in Listing 6.

Code Listing 6: CSV function, DBMS_SQL cursor variant

SQL>
.
.
.
 16   function csv
 17   ( p_query        in varchar2,
 18     p_separator   in varchar2 default ' , ' ,
 19     p_dir            in varchar2 ,
 20     p_filename    in varchar2 )
 21   return number
 22   as
 23       l_theCursor     integer default dbms_sql.open_cursor;
 24       l_status         number;
 25   begin
 26       dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
 27       l_status := dbms_sql.execute(  l_theCursor );
 28       return csv( l_theCursor, p_separator, p_dir, p_filename );
 29   end;

Figure 1: Oracle SQL Developer PLScope Identifier Lookup

Now I can invoke our routine by using three methods: string, using a DBMS_SQL cursor, or using a REF CURSOR. Listing 7 shows an example for invoking the routine.

Code Listing 7: Invoking UNLOAD.CSV

SQL> declare
  2       l_rows  number;
  3       l_cursor sys_refcursor;
  4    begin
  5        open l_cursor
  6        for
  7        select *
  8           from all_users
  9          where rownum < 5;
 10
 11       l_rows := unload.csv
 12                    ( l_cursor, ' , ' ,
 13                       'TMP', 'test.dat' );
 14
 15       l_rows := unload.csv
 16                    ( 'select rownum, sysdate
 17                          from dual',
 18                      ' , ' , 'TMP', 'dual.dat' );
 19    end;
 20    /
PL/SQL procedure successfully completed.
SQL> !cat /tmp/test.dat
SYS,0,05-JUN-07
SYSTEM,5,05-JUN-07
OUTLN,9,05-JUN-07
DIP,13,05-JUN-07
SQL> !cat /tmp/dual.dat
1,26-JUL-07

In addition to this nice enhancement for dynamic SQL, we also have the following enhancements in Oracle Database 11g:

• DBMS_SQL.PARSE allows for a CLOB, and the 32K limit that applied before on the length of a SQL string has been removed.

• Native dynamic SQL permits statements of any length via a CLOB.

• DBMS_SQL now supports binding of object types in addition to the scalar types it always supported.