oracle-developer.net

dynamic sql enhancements in 11g

Oracle has supported dynamic SQL for as long as many developers have been working with the database. Prior to 8i, the primary means of executing dynamic SQL or PL/SQL was via the DBMS_SQL package (which provided a low-level interface to dynamic cursors through its APIs). The introduction of Native Dynamic SQL (NDS) in 8i made it much easier (in most circumstances) to execute generated strings of code and despite some low-level performance issues in versions prior to 10g, NDS is by far the most popular dynamic method today.

Between them, DBMS_SQL and Native Dynamic SQL cater for most development requirements, but neither satisfy all of them. For example:

NDS does not support very large SQL strings (we use an overloaded DBMS_SQL.PARSE that takes a collection of SQL fragments);

we cannot use NDS if we have an unknown number of bind inputs: these must be known at compile-time (we use DBMS_SQL which supports this "Method 4" dynamic SQL scenario);

NDS cannot be used to describe a cursor to understand its composition (we use the DBMS_SQL.DESCRIBE_COLUMNS{2|3} APIs); and

DBMS_SQL does not support user-defined types as bind variable inputs (we must use NDS but ensure we know the nature of the binds at compile-time).

With the release of 11g, Oracle has attempted to complete its dynamic SQL implementation by addressing these issues. This article will describe how.

It is assumed that readers are comfortable with dynamic SQL concepts. Most are reasonably simple, but Method 4 scenarios can be quite complex. For an overview of what is meant by "Method 4 Dynamic SQL", read this short introduction.

summary of dynamic sql new features

The online documentation describes the new dynamic SQL features as providing "functional completeness" for PL/SQL. The following is taken directly from the New Features Guide.

In Oracle Database 11g, native dynamic SQL now supports statements bigger than 32K characters by allowing a CLOB argument. Also included with this feature:

DBMS_SQL.PARSE() gains a CLOB overload

A REF CURSOR can be converted to a DBMS_SQL cursor and vice versa to support interoperability

DBMS_SQL supports the full range of data types (including collections and object types)

DBMS_SQL allows bulk binds using user-define (sic) collection types

[...some text omitted...] Oracle Database 11g removes these and other restrictions to make the support of dynamic SQL from PL/SQL functionally complete.

We will cover each of these points and more in this article.

a short note on the examples

Dynamic SQL is often built from metadata, dictionary information, supplied components and much string concatenation. The resulting SQL statements are usually obscure to read in their PL/SQL "containers" and often there is no SQL statement for us to read until it is executed. With this in mind, and to make the concepts as clear as possible in this article, we will be using SQL statements that do not need to be dynamic. For this reason, readers should assume that the techniques described in this article will only be used when dynamic SQL is absolutely necessary.

dynamic sql stored in clobs

We will begin with a simple new feature. Both Native Dynamic SQL and DBMS_SQL now support SQL strings stored in CLOBs. Without this feature, NDS is able to parse SQL strings of up to 64K (which can be achieved by concatenating two large VARCHAR2s together) and DBMS_SQL has an overload of PARSE that accepts a collection of SQL string fragments. Neither of these is ideal and the CLOB implementation solves any issues we might have had with the previous alternatives.

In the following example, we will build a simple dynamic PL/SQL block of approximately 33,000 bytes. We will store this in a CLOB and execute it with DBMS_SQL, using the new PARSE overload. The dynamic block itself will output a dummy message to show that it has been executed.

We can see that the only change needed to work with a large SQL or PL/SQL block is on line 3, where we declare a CLOB variable. We have been able to manipulate CLOB variables in a similar manner to those of VARCHAR2 since Oracle 9i, so for those developers who need to generate large dynamic SQL strings, this new feature is very useful. It is likely to be even more useful in Native Dynamic SQL where the VARCHAR2 restriction sits at approximately 64K (when two large VARCHAR2 variables are concatenated). In the following example, we will execute the same dynamic PL/SQL block using NDS.

As expected, NDS also handles the dynamic CLOB and generates the same output as the DBMS_SQL example.

dbms_sql support for user-defined types

DBMS_SQL supports a wide range of built-in and packaged types defined by Oracle, but in versions prior to 11g there is no support for user-defined types. In other words, if we need to combine dynamic SQL with bind variables of our own types in previous Oracle versions, we must use Native Dynamic SQL. In most cases this makes it easier for us, but in scenarios where we don't know the number or types of bind variables at compile time, this causes a real problem.

Note that when we talk of DBMS_SQL "supporting" types, we typically mean that the APIs involved with binding and receiving data have a suitable overload for the types we wish to use. In 11g, Oracle has overloaded some of the DBMS_SQL APIs further to allow us to bind and fetch values of user-defined types. We will see a couple of simple examples below, but first we will create some user-defined types. We will start by creating an object type, as follows.

Note that this type has a single member method to output its current attribute values as a string. This is purely for convenience and will be used in the dynamic examples. To complete our setup, we will also create a collection type, based on this object, as follows.

SQL> CREATE TYPE collection_type AS
2 TABLE OF object_type;
3 /

Type created.

For our first example, we will build a simple dynamic PL/SQL block that will accept an instance of our user-defined object type as a bind variable and invoke its PRINT member function to provide some output. The example is as follows.

Current instance of object_type is [1,20080215,X]
PL/SQL procedure successfully completed.

The lines of interest are highlighted above. We have built a simple anonymous PL/SQL block that receives a bind variable of our object type and invokes the bind variable's PRINT method. As stated earlier, prior to 11g we would have needed to bind this variable using Native Dynamic SQL.

We are not limited to user-defined object types. We can also bind user-defined collections with DBMS_SQL in 11g, as follows.

As we can see from this example, the principle for binding objects or collections is the same. Once the bind variable has been provided via DBMS_SQL (or indeed NDS), it is the responsibility of the dynamic SQL or PL/SQL to understand how to make use of it.

For a listing of the types we can use with DBMS_SQL APIs such as BIND_VARIABLE, we can query ALL_ARGUMENTS as follows.

Note that the BIND_ARRAY procedure is missing from the above query filter. This is because the BIND_ARRAY procedures have not been overloaded to accept user-defined types. Recall from the documentation quoted above that:

"DBMS_SQL allows bulk binds using user-define (sic) collection types"

This is a slightly misleading statement. It is true to an extent, because we saw an example of binding a collection type above. What isn't clear, however, is that this statement does not apply to the binding of arrays that DBMS_SQL has always supported with its own packaged types. We can demonstrate this quite easily. In the following example, we will attempt to bind our own collection types using the DBMS_SQL.BIND_ARRAY interface. First we will create a couple of generic collection types, as follows.

We can see clearly that the BIND_ARRAY interface does not support user-defined types (it only supports packaged array types defined in the DBMS_SQL specification). This means that if we wish to combine bulk updates, dynamic SQL and user-defined types, we must use the BIND_VARIABLE mechanism with dynamic PL/SQL constructs, such as in the following example.

dbms_sql support for ref cursors

With 11g, DBMS_SQL and Native Dynamic SQL become interchangeable due to two new APIs to convert between ref cursors and DBMS_SQL cursors. There are two main benefits to this new functionality:

we can easily describe the structure of ref cursors for the first time, making it much easier to work with Method 4 scenarios; and

we can combine the flexibility of DBMS_SQL binding with the ease of Native Dynamic SQL data fetching if we know the structure of the data being fetched.

We will see examples of how we can convert between the two cursor types below, starting with the conversion of DBMS_SQL cursors to ref cursors.

dbms_sql.to_refcursor

As its name suggests, this new API converts a DBMS_SQL cursor to a ref cursor (cursor variable). There are certain situations where we might know the structure of a record being fetched, but we don't know the number or types of bind variables being supplied (this is a typical scenario for application search screens, for example). In these cases, we need DBMS_SQL to process the bind variables. Because we know the structure of the resultset records, DBMS_SQL is also reasonably simple to use but if we switch to using a ref cursor, we can write "regular" PL/SQL to process the data. This is significantly easier to code and support.

In the following example, we will use DBMS_SQL to prepare and bind a dynamic SQL statement that gives a fixed return structure. The DBMS_SQL pre-processing is over-simplified to avoid crowding the example, so we have to take a leap-of-faith that this method is actually necessary (i.e. imagine that the statement and number of bind variables is unknown at compile time). Once the DBMS_SQL pre-processing is complete, we will convert to a ref cursor and revert to standard PL/SQL constructs to fetch the data.

Lines 15-21: this is our imaginary complex statement with an unknown set of binds. In actual fact, we have restricted the example to just one fixed bind variable to avoid over-complicating the code at this stage. The bind variable is provided via a sqlplus substitution variable as a proxy for an interactive application;

Line 24: the dynamic SQL statement must be executed before we can start to fetch from the cursor;

Line 25: we convert the DBMS_SQL cursor to a ref cursor variable of type SYS_REFCURSOR. Any weak ref cursor type can be used;

Lines 28-35: we can use simple PL/SQL constructs to fetch and process the data and therefore avoid the more complex and code-intensive DBMS_SQL calls. In this example we have coded a standard bulk fetch loop that we might typically use for efficiency with larger resultsets.

By combining DBMS_SQL with ref cursors, we have achieved greater flexibility. Note, however, that this will not be suitable for situations where the output of the SQL is unknown (i.e. complete Method 4). In these situations we must use DBMS_SQL throughout.

An important point to note is that in 11g Release 1 the ref cursors are only usable in PL/SQL (this restriction is lifted in 11g Release 2 as we will see below). In other words, in 11g Release 1, we cannot convert DBMS_SQL cursors to ref cursors and pass them to client applications in anything other than PL/SQL. We will demonstrate this below. First we will create a simple function to parse, bind and execute a simple EMP query, convert the cursor to a ref cursor and return it to the calling program.

This function uses the same DBMS_SQL pre-processing logic as we saw in our previous example and returns a ref cursor to the caller. We will attempt to call this from a client in both 11g Release 1 and 2 below. For this, sqlplus will be our proxy for the client application. We will use a sqlplus ref cursor variable and bind it into our PL/SQL block, starting with 11g Release 1, as follows.

We can see that in 11g Release 1, our PL/SQL block succeeds but the client application cannot process the ref cursor. Oracle raises an ORA-01001 exception. This is a major restriction and one which reduces the potential for this new feature, particularly if we consider the best practice of passing ref cursors to client applications.

We will now repeat the example using an 11g Release 2 database, as follows.

We can see that in 11g Release 2, the client application is able to fetch from the ref cursor. With this support for client ref cursors, the new combination of using DBMS_SQL for unknown binds and ref cursors for data fetching is very powerful. For example, client search screens are often implemented poorly because of a lack of understanding of bind variables and the fact that the client requires a ref cursor to be returned. This new feature caters for both requirements with ease.

Finally, to demonstrate that the 11g Release 1 issue is not simply an issue with using a sqlplus variable, we will use the ref cursor bind variable in a purely PL/SQL context, as follows.

Providing that we use PL/SQL to fetch from the ref cursor, we can use any cursor variable, as the above demonstrates.

dbms_sql.to_cursor_number

The converse to using DBMS_SQL for binds and ref cursors for fetching is to begin with a ref cursor and convert to DBMS_SQL for data retrieval. The TO_CURSOR_NUMBER API enables us to do this, but interestingly (and this is not made clear in the documentation), we can convert both static and dynamic ref cursors to DBMS_SQL cursors. This means that we can programmatically describe any weak or strong ref cursor (although we already know the structure of a strong ref cursor).

Since Native Dynamic SQL was introduced in 8i, the OPEN FOR syntax has become far more commonly used in PL/SQL programs. What is often overlooked by Oracle developers is the fact that this syntax existed before NDS and was originally used for static SQL cursors. To re-emphasise the origins of the OPEN FOR construct, our first example will describe a static, rather than dynamic, ref cursor by converting it to a DBMS_SQL cursor.

Lines 11-13: we open our ref cursor with a static SQL statement using the original OPEN FOR syntax. There are no bind variables in this example;

Line 16: we convert the static ref cursor to a DBMS_SQL cursor handle;

Line 19: we describe the DBMS_SQL cursor that was formerly a ref cursor;

Lines 25-37: we can make decisions based on the nature of the cursor structure. In this example we have simply output the types of the columns in our SQL statement. We have only catered for the three main datatypes but we could extend our logic to cover all DBMS_SQL-supported types.

We will now look at a slightly more complex example. In the following example, we will create a procedure to accept a ref cursor parameter (remember the restriction that in 11g Release 1 this must be a PL/SQL-managed ref cursor). We will describe this cursor by converting it to a DBMS_SQL cursor and then fetch its data. The cursor structure is completely unknown (although for simplicity we will restrict it to strings, dates and numbers). We create the procedure as follows.

This is quite a long and complicated procedure to use as an example, so some key points to note are as follows:

Line 2: the ref cursor parameter must be IN OUT else the attempt to convert it to a DBMS_SQL cursor will fail with "PLS-00363: expression 'p_refcursor' cannot be used as an assignment target";

Lines 9-11: for simplicity, we are restricting the example to support VARCHAR2, DATE and NUMBER types only;

Line 18: we convert the ref cursor parameter directly to a DBMS_SQL cursor;

Line 21: we describe the DBMS_SQL cursor and are now able to procedurally prepare and fetch our results;

Lines 24-34: using the cursor metadata, we call the DBMS_SQL.DEFINE_COLUMN API to define the data we are going to fetch (this is standard DBMS_SQL processing);

Lines 36-61: for this example, we are simply going to output our data in sqlplus format, so these loops through the cursor metadata are included to output some underlined column names. Each line to be output is prepared using a v_data variable for convenience;

Lines 68-83: we fetch from our dynamic cursor. For each row, we must loop through the cursor description to understand the nature of each column/expression being fetched. We can then supply the correct variable to fetch the data into. Again, we have used the three main built-in datatypes, but DBMS_SQL supports a much wider range than this;

Line 85: once each row is completely fetched, we output it and move onto the next record.

This procedure is a simplified example of Method 4 dynamic SQL. We have no prior knowledge of any cursors that will use this procedure (with the noted exception of our self-imposed limited datatype support). Outputting the data in the style of sqlplus is not necessarily what we use Method 4 dynamic SQL for, but it is a useful technique for demonstrating the concepts.

We will now test our procedure twice. We will execute an anonymous block to open a ref cursor and call our procedure. The first example will open a static ref cursor as follows.

Our dynamic ref cursor works in the same way as the static version, as described earlier.

conclusion: functionally complete?

The New Features Guide states:

Oracle Database 11g removes [...] restrictions to make the support of dynamic SQL from PL/SQL functionally complete.

We have clearly demonstrated this above, particularly with the extended support for datatypes and Method 4 scenarios. The lack of inter-operability between converted cursors and non-PL/SQL ref cursors in 11g Release 1 appears to be a key restriction for that release. However, this particular issue is fixed in 11g Release 2, making the latest Oracle release rich in dynamic SQL functionality.

further reading

For more information on 11g's dynamic SQL capabilities, read Chapter 7 of the PL/SQL Language Reference. A summary of DBMS_SQL subprograms can be found in the PL/SQL Packages and Types Reference. Finally, read this article for a more detailed description of Method 4 dynamic SQL and its uses.

source code

The source code for the examples in this article can be downloaded from here.