oracle-developer.net

method 4 dynamic sql in pl/sql

Dynamic SQL and PL/SQL has been supported in Oracle for many versions. Before Oracle 8i, we used the low-level DBMS_SQL package and more recently we have used Native Dynamic SQL to parse, bind and execute dynamic statements. Method 4 dynamic SQL, however, has never been truly possible natively in Oracle (i.e. in PL/SQL). For example, consider the following pseudo-statements:

How do we deal with "any dynamic query you like"? What is it? What does it look like? How do we fetch it? How do we display it? We will answer these questions in this article and demonstrate how to fetch data dynamically using the following SQL statement...

SELECT *
FROM TABLE( dla_pkg.query_view( p_select_string ) );

...where p_select_string is an unknown SELECT statement.

an overview of method 4 dynamic sql

A Method 4 scenario is one in which any dynamic statement can be programmatically understood, executed and fetched without knowing any of the structures involved at the time of compilation.

Imagine the various IDEs and middle-tier applications we use to query data from Oracle. Whether they be sqlplus, TOAD, SQL Developer or home-grown applications in Java, PHP, C++ etc, one thing that these tools and languages have in common is the ability to take a cursor or ref cursor and fully display its associated resultset, regardless of the structure of the SQL statement we execute. These tools and languages are able to understand the record structures that are being returned. This is possible due to the various protocols used to access Oracle (OCI, OCCI, JDBC, ODBC etc).

Now consider how we might handle Method 4 dynamic SQL in PL/SQL. We can easily prepare and parse a dynamic statement. We can also programmatically handle bind variables without knowing how many we are going to bind (if we use DBMS_SQL). We can execute this statement without needing to know its form and structure. But what do we fetch the returning data into? In PL/SQL we regularly fetch into variables, records and collections of records, but regardless of which variable type we use, we need to know its structure at compile time. For this reason, true Method 4 dynamic SQL is not possible in native, static PL/SQL.

There are ways of achieving this however, but they are complicated and involve DBMS_SQL describe APIs and PL/SQL to build and execute a dynamic anonymous PL/SQL block. For an example of this, see this oracle-developer.net utility. With the Oracle Data Cartridge framework, we have an alternative method of achieving Method 4 scenarios for dynamic statements that return datasets.

an overview of the data cartridge framework

One of the lesser-known features of Oracle is the Data Cartridge framework which, according to the documentation, provides "the mechanism for extending the capabilities of the Oracle server". What this actually means is that we can create a set of processing rules inside one or more object types and "plug them into" the Oracle server to use in various scenarios. One of these scenarios comprises pipelined functions that return instances of ANYDATASET (a generic type introduced in Oracle 9i). For this, Oracle provides the development framework for us to create a pipelined function, implemented as a Data Cartridge, that will return any record structure we request of it. In other words, we can exploit the Data Cartridge framework and combine it with pipelined functions to create a true Method 4 "engine" inside the database, as we will see in this article.

For readers who wish to familiarise themselves with the Oracle Data Cartridge framework, this oracle-developer.net article describes its most common and simple use: user-defined aggregate functions. Many readers will be familiar with Tom Kyte's "STRAGG" function which is built on the same principles and framework. Note that the techniques we will use below will be more complicated than aggregate function implementations, so a solid grounding in the principles of building Data Cartridges is advised.

introducing the dictionary long application

We are going to use an existing oracle-developer.net application of Data Cartridge and pipelined ANYDATASET functions to demonstrate native Method 4 capabilities. This application is named (not particularly creatively) the "Dictionary Long Application". It is built as a Data Cartridge with a single purpose: to query any dictionary view with LONG column(s) and return CLOB(s) instead. Examples are DBA_VIEWS and DBA_TAB_PARTITIONS, where we often wish to search inside the LONG columns but cannot due to their inflexibility. As CLOBs, these columns are as SQL-friendly as a regular VARCHAR2 column (since Oracle 9i of course). The Dictionary Long Application (we will call it DLA from now on) will accept any dynamic SQL statement and return the relevant dataset, as though we were querying statically in sqlplus.

As an aside, the DLA should technically work from all versions of 10g upwards. However, due to a bug in Oracle's CLOB-handling, the version of the DLA we will see in this article will only work from 10g Release 2 (10.2) onwards. For 9i and 10g Release 1 (10.1), there is an alternative version of the DLA (available in the download at the end of this article).

elements of a data cartridge

A native Data Cartridge of the type we require is built of two components:

an object type that implements the rules of the application using well-defined APIs (contained in Oracle's Data Cartridge development framework); and

a PL/SQL function that declares itself as implemented by the object type.

The well-defined APIs referenced above are built-in static and member methods, prefixed with ODCI*. Some of these methods are mandatory and some are optional based on functionality we may or may not wish to implement. There are several types of Data Cartridge and each varies in the built-in methods they need to include, but we will be using the ODCITable* static and member functions.

In addition to an object type based on ODCITable* functions, we will also create a pipelined function. Unlike a "regular" pipelined function, this function will be fully implemented by the object type and not created in PL/SQL. We will see the details later in this article.

building the method 4 dictionary long application

With reference to the DLA, we will demonstrate how to build a Data Cartridge for a Method 4 dynamic SQL application. The implementing type for the DLA is quite complicated, but remember that in any Method 4 application we are trying to develop a program that can parse, describe, bind and fetch data for any SQL statement. The added twist with the DLA is that it converts LONGs to CLOBs, but this doesn't detract from its Method 4 capability.

dla type specification

We will build the implementing type for the DLA in small stages, breaking to describe certain elements of the syntax and logic. We will begin with the type specification, which provides a good overview of the methods we will need to implement. The implementing type is named DLA_OT and is defined as follows.

SQL> CREATE TYPE dla_ot AS OBJECT
2 (
3 atype ANYTYPE --

Type created.

The names of the static and member functions provide a good summary of Method 4 requirements. The most interesting method is the ODCITableFetch function because this is the area where PL/SQL traditionally breaks down in Method 4 scenarios. Note how this function is passing out an instance of ANYDATASET (i.e. any record or data structure). The individual ANYTYPE attribute will describe the structure of the records that the pipelined function will stream.

Before we build our implementing type body, we will create a package specification to wrap our pipelined function. This is unnecessary of course (the function can be standalone), but we will also make use of packaged types and state variables in our type body to avoid repetition and unnecessary work in the object type. The DLA_PKG package specification is as follows.

Note how we define our QUERY_VIEW pipelined function. Firstly, we are returning an instance of ANYDATASET as our data type. This will contain arrays of whatever record structures we need to return (depending on the statement passed into the function). This gives it a Method 4 capability. We also declare this function to be implemented by our DLA_OT type with the USING clause. This is known as an interface method pipelined function.

dla type body: describe phase

We will now build the implementing type body for the DLA_OT type. We will break it down into smaller chunks, based on each of the methods we need to implement, beginning with the OCITableDescribe static function, as follows.

We do a lot of setup work in the ODCITableDescribe static function. For efficiency, this method is only executed when a dynamic query is hard-parsed for the first time. At this stage, Oracle will create two types in our schema (one object type and one collection type), based on the structures being described (they are created by the ANYTYPE.BeginCreate static method calls). The ODCITableDescribe function is a new feature of 10g that enables us to develop Method 4 applications.

The main elements of this function are as follows.

Lines 16-19: using DBMS_SQL, we first describe the dynamic SQL cursor that we will ultimately be trying to execute. This gives us an array of information on the columns in the cursor's resultset;

Lines 25-70: using the cursor description, we create a transient instance of ANYTYPE. The structure of this type instance matches the described SQL cursor, with one exception described in the next bullet-point. The ANYTYPE instance will be the defining record structure of the ANYDATASET pipelined function for a given dynamic SQL statement;

Lines 38-39: the purpose of the DLA is to convert LONGs to CLOBs to make dictionary views easier to use. A LONG is of typecode 8, so when we are dealing with a cursor attribute of this type, we set the ANYTYPE attribute to CLOB instead. This is the only point at which the incoming SQL cursor and the ANYTYPE instance records differ; and

Lines 77-80: we create a transient collection type based on the transient object type created above. As with any pipelined function, we must always create an object type to define a record, followed by a collection type of this object.

The fact that ODCITableDescribe is only called once per unique query means we can repeat the dynamic SQL call in and across database sessions and never have this method called again. For this reason, we have not made use of the DLA_PKG state variable for the DBMS_SQL elements of this function, as it will not be available to the other methods in the DLA_OT type.

It has been stated already that the return record type is where PL/SQL's Method 4 capabilities fall down. The ANYTYPE built-in type used in the ODCITableDescribe static function above overcomes this by enabling us to create transient data structures that match the incoming dynamic SQL cursor (based on the information we can retrieve with DBMS_SQL).

dla type body: prepare phase

The ODCITablePrepare static function is where we initialise an instance of DLA_OT for use in other methods (and ultimately in the generation of a resultset). For example, we store the instance of ANYTYPE that was passed out of the ODCITableDescribe function in our DLA_OT instance. The well-defined interface of the ODCITablePrepare function requires that the ANYTYPE instance is passed in as a parameter, as below.

This static function is quite simple and performs the following actions.

Lines 117-124: the ANYTYPE.GetAttrElemInfo method provides us with a range of information about our transient type, including the type instance itself, which is the data we require; and

Line 129: we initialise an instance of our DLA_OT, setting the transient ANYTYPE attribute for the current dynamic cursor.

Like the ODCITableDescribe method, the ODCITablePrepare function is executed only at query compilation (hard-parse) time. This means that the scan context we created above (the instance of DLA_OT containing the ANYTYPE definition) is available across repeated calls of the same dynamic SQL statement. This reduces the time we spend executing a query as some of the time-intensive setup work is already done for us. Without a prepare phase, the scan context initialisation would be needed on every execution of a given SQL statement.

dla type body: start phase

The ODCITableStart static function is where we return to DBMS_SQL to define and execute our dynamic SQL cursor. There are some important points to note about the techniques used in the following function, which will be described after the code listing.

At first glance, we can see that we are defining and executing a dynamic SQL cursor, which will be familiar to many developers. There are some interesting points to note about the implementation of this, however, as follows.

Lines 128-132: we open, parse and describe the dynamic SQL statement again. We need to describe the cursor again because we cannot guarantee that the preceding ODCITableDescribe function will be invoked (remember the describe step is only invoked the first time a query is parsed). Describing the cursor again seems wasteful but it is necessary. The ODCITableStart function will be invoked on every query execution, so at this point we can make use of package state by using the record variable in DLA_PKG;

Lines 134-216: we loop through the cursor description (i.e. the array of projected columns). For each element in the array, we extract the corresponding ANYTYPE attribute (remember that we set these in the ODCITableDescribe function) and use their typecodes to define each column for fetch. We use the ANYTYPE typecodes rather than the DBMS_SQL versions because these correspond with named constants in DBMS_TYPES, which makes it easier to understand. By decoding the typecodes, we can call the correct DBMS_SQL.DEFINE_XXX API to setup the output placeholders for fetching into later;

Lines 200-212: for any LONG columns in the incoming dynamic SQL cursor, the ODCITableDescribe method sets the corresponding ANYTYPE attribute to CLOB. When extracting the attribute metadata from the ANYTYPE instance in the ODCITableStart method above, we need to know whether the attribute was always a CLOB or was originally a LONG. This is because we need to use the specific DBMS_SQL.DEFINE_COLUMN_LONG procedure to setup the LONG column for fetch. This is where having both the original cursor description and the ANYTYPE metadata becomes essential; and

Line 222: we execute the dynamic SQL cursor and are ready to fetch data.

We can see that much of the subtlety of the DLA conversion logic exists in this function, as described above. At this stage, however, we have reached the end of PL/SQL's native Method 4 capabilities, as we now need to fetch data. As stated earlier, to achieve Method 4 dynamic SQL in "straight PL/SQL" requires us to write dynamic PL/SQL. The DBMS_SQL APIs we have seen so far enable us to do this by defining dynamic variable names, types, fetch structures etc at runtime (i.e. using PL/SQL to write PL/SQL). Oracle Data Cartridge, together with ANYDATASET and particularly ANYTYPE, enables us to avoid dynamic PL/SQL by fetching into a transient type, as we will see below.

dla type body: fetch phase

In the preceding type functions, we have parsed, described, defined and executed our dynamic SQL cursor. Based on the description of this cursor, we have also instantiated two transient types using ANYTYPE (an object type and a collection type: the pre-requisites for pipelined functions). We are now ready to implement Method 4 by fetching the data from the cursor, which we do using the ODCITableFetch member function below.

We can see a pattern in how we handle the cursor and ANYTYPE attributes. As in the describe and start phases, the underlying data type of each attribute dictates the DBMS_SQL API and ANYTYPE method that we need to use. The ODCITableFetch member function above is no different, except this time we are setting the final data structure for piping to the end-user. Note in particular the following:

Lines 234-249: we define a record structure for our data fetches. This record type includes an attribute for each data type we might need to fetch from the SQL cursor;

Lines 262-266: we retrieve the metadata relating to our ANYTYPE instance as this will be used to drive the fetching and the use of the correct DBMS_SQL.COLUMN_VALUE(_XXX) API. It will also be used to add data into the ANYDATASET instance that our pipelined function will return, as we will describe below;

Lines 273-275: we instantiate an ANYDATASET, based on the record structure in our ANYTYPE instance. We call the Piecewise member function to enable us to add data elements to our ANYDATASET instance one at a time, as we fetch them off the SQL cursor;

Lines 277-375: we loop through the attributes in our ANYTYPE instance and fetch data off the SQL cursor using the appropriate DBMS_SQL procedures. In addition, we add each fetched column data into our ANYDATASET instance use the relevant type-specific method;

Lines 354-372: when we fetch a CLOB, we need to know whether it was originally a LONG. Remember that we saved the corresponding cursor description using a state variable in DLA_PKG. If the SQL cursor attribute is a LONG, we fetch it piecewise using the DBMS_SQL.COLUMN_VALUE_LONG procedure, adding it to our ANYDATASET instance as a CLOB on completion; and

Line 380: we complete the fetch into our ANYDATASET instance, by which stage the pipelined function that is implemented via the DLA_OT type will have piped most of this data.

dla type body: close phase

The remaining method we need to code is the ODCITableClose member function. We can see below that all we need to do in this function is close the dynamic SQL cursor and reset our package state.

This completes our type implementation and we are now able to test our pipelined function. Before we do this, however, we can summarise the type's processing phases as follows:

describe: we describe the incoming cursor and create object and collection instances of ANYTYPE based on this information. This is executed once-only for a new SQL statement, at which point Oracle creates two types to support the pipelined function implementation;

prepare: we initialise a scan context once for a unique cursor. This method is invoked once at query compile (parse) time and this prevents Oracle from initialising an instance of DLA_OT every time a particular query is restarted (i.e. executed again);

start: we describe the dynamic cursor again, using the metadata to define the API calls to the DBMS_SQL package. The cursor is executed and is ready for fetch. We store the cursor information in a package variable for sharing across methods;

fetch: using the cursor description and ANYTYPE metadata, we fetch data off the dynamic SQL cursor into a relevant type variable. We create an instance of ANYDATASET and assign the fetched data piecewise; and

close: we cleanup our operation by closing the cursor and resetting package state.

testing the function

We can now test our QUERY_VIEW pipelined function. As the DLA is designed as a Method 4 application to convert LONGs to CLOBs, we will execute a dynamic query against DBA_VIEWS. We will fetch a single row for simplicity. Remember that the DLA (or any Method 4 application that uses Data Cartridge and ANYDATASET in this way) will run any query against it within the domain of datatypes we support.

We can see that the DLA has a true Method 4 capability. It has described and understood the incoming dynamic SQL statement and fetched it into a structure of its own creation. If we examine the data dictionary, we can see that Oracle has created two physical types to support this particular cursor.

Despite the complex initial setup, we can see that interface-method pipelined functions (using Oracle Data Cartridge in 10.2 and ANYTYPE/ANYDATASET generic types) provide a good means to produce Method 4 SQL applications.

performance considerations

Having such flexibility comes at a cost, as we will see below. In the following example, we will compare a query against DBA_VIEWS with a synonymous query using the DLA. Oracle's object implementation, combined with the fact that the DLA converts the LONG column to a CLOB, increases the time and resources that Oracle must spend to satisfy this Method 4 implementation. We will use autotrace to reduce the output and also a variation of Tom Kyte's RUNSTATS utility to compare the two queries.

From the autotrace output alone we can see that the Data Cartridge application incurs a large amount of I/O when compared with the static query. This is due to the CLOB implementation which is specific to the DLA (and will not necessarily be present in a more general-purpose application of the Data Cartridge framework). In addition, the DLA also generates a high volume of recursive SQL, required to support such a metadata-driven application.

The RUNSTATS output provides more information on the resource usage of Oracle's Data Cartridge framework, in particular the latching. Oracle's object type implementation seems to use proportionately high numbers of latches (and this can be seen in most applications that make use of object types). The DLA is no different and uses far more latches than the static query (the static query uses just 5% of the latches required by the DLA). The initial setup work involved in a Data Cartridge application, such as the creation of types, appears to have little impact on the runtimes or latches used (i.e. the resource usage is similar for subsequent executions of the same cursor).

To reduce the cost of executing Method 4 dynamic queries, the full DLA application has some additional features over those described in this article. Full details are available in the download file (available below) and include a set of pre-defined views (e.g. V_DBA_VIEWS, V_DBA_TAB_PARTITIONS and so on) and, more critically, the ability to limit the volume of data being generated and returned, using application context.

further reading

downloads

The Dictionary Long Application can be downloaded from here. The oracle-developer.net variation on RUNSTATS is available here.

acknowledgements

Many thanks to Jonathan Heller for pointing out a typecode bug for CHAR in the original DLA_OT implementation. Jonathan also noted that pseudo-columns such as ROWNUM, USER etc require an alias to work with this application. I've added these instructions to the usage notes in the DLA_PKG specification (available in the download file).