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.Now, Where Did I Leave My Keys?
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.Improved Dynamic SQL
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.
READ more about Oracle Database 11g
DOWNLOAD Oracle Database 11g
Photography by Meric Dagli, Unsplash