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:

FROM   some_package.some_function('<any dynamic query you like>');

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...

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 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 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 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:

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.

  2  (
  3    atype ANYTYPE --<-- transient record type
  5  , STATIC FUNCTION ODCITableDescribe(
  6                    rtype OUT ANYTYPE,
  7                    stmt  IN  VARCHAR2
  8                    ) RETURN NUMBER
 10  , STATIC FUNCTION ODCITablePrepare(
 11                    sctx    OUT dla_ot,
 12                    tf_info IN  sys.ODCITabFuncInfo,
 13                    stmt    IN  VARCHAR2
 14                    ) RETURN NUMBER
 17                    sctx IN OUT dla_ot,
 18                    stmt IN     VARCHAR2
 19                    ) RETURN NUMBER
 22                    SELF  IN OUT dla_ot,
 23                    nrows IN     NUMBER,
 24                    rws   OUT    ANYDATASET
 25                    ) RETURN NUMBER
 28                    SELF IN dla_ot
 29                    ) RETURN NUMBER
 30  );
 31  /

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.

dla package specification: pipelined function, types and state variable

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.

  3     /*
  4     || Pipelined function interface.
  5     */
  6     FUNCTION query_view(
  7              p_stmt IN VARCHAR2
  8              ) RETURN ANYDATASET PIPELINED USING dla_ot;
 10     /*
 11     || Record types for use across multiple DLA_OT methods.
 12     */
 13     TYPE rt_dynamic_sql IS RECORD
 14     ( cursor      INTEGER
 15     , column_cnt  PLS_INTEGER
 16     , description DBMS_SQL.DESC_TAB2
 17     , execute     INTEGER
 18     );
 20     TYPE rt_anytype_metadata IS RECORD
 21     ( precision PLS_INTEGER
 22     , scale     PLS_INTEGER
 23     , length    PLS_INTEGER
 24     , csid      PLS_INTEGER
 25     , csfrm     PLS_INTEGER
 26     , schema    VARCHAR2(30)
 27     , type      ANYTYPE
 28     , name      VARCHAR2(30)
 29     , version   VARCHAR2(30)
 30     , attr_cnt  PLS_INTEGER
 31     , attr_type ANYTYPE
 32     , attr_name VARCHAR2(128)
 33     , typecode  PLS_INTEGER
 34     );
 36     /*
 37     || State variable for use across multiple DLA_OT methods.
 38     */
 39     r_sql rt_dynamic_sql;
 41  END dla_pkg;
 42  /

Package created.

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.

  3     STATIC FUNCTION ODCITableDescribe(
  4                     rtype OUT ANYTYPE,
  5                     stmt  IN  VARCHAR2
  6                     ) RETURN NUMBER IS
  8        r_sql   dla_pkg.rt_dynamic_sql;
  9        v_rtype ANYTYPE;
 11    BEGIN
 13        /*
 14        || Parse the SQL and describe its format and structure.
 15        */
 16        r_sql.cursor := DBMS_SQL.OPEN_CURSOR;
 17        DBMS_SQL.PARSE( r_sql.cursor, stmt, DBMS_SQL.NATIVE );
 18        DBMS_SQL.DESCRIBE_COLUMNS2( r_sql.cursor, r_sql.column_cnt, r_sql.description );
 19        DBMS_SQL.CLOSE_CURSOR( r_sql.cursor );
 21        /*
 22        || Create the ANYTYPE record structure from this SQL structure.
 23        || Replace LONG columns with CLOB...
 24        */
 25        ANYTYPE.BeginCreate( DBMS_TYPES.TYPECODE_OBJECT, v_rtype );
 27        FOR i IN 1 .. r_sql.column_cnt LOOP
 29           v_rtype.AddAttr( r_sql.description(i).col_name,
 30                            CASE
 31                               --<>--
 32                               WHEN r_sql.description(i).col_type IN (1,96,11,208)
 33                               THEN DBMS_TYPES.TYPECODE_VARCHAR2
 34                               --<>--
 35                               WHEN r_sql.description(i).col_type = 2
 36                               THEN DBMS_TYPES.TYPECODE_NUMBER
 37                               ----
 38                               WHEN r_sql.description(i).col_type IN (8,112)
 39                               THEN DBMS_TYPES.TYPECODE_CLOB
 40                               --<>--
 41                               WHEN r_sql.description(i).col_type = 12
 42                               THEN DBMS_TYPES.TYPECODE_DATE
 43                               --<>--
 44                               WHEN r_sql.description(i).col_type = 23
 45                               THEN DBMS_TYPES.TYPECODE_RAW
 46                               --<>--
 47                               WHEN r_sql.description(i).col_type = 180
 48                               THEN DBMS_TYPES.TYPECODE_TIMESTAMP
 49                               --<>--
 50                               WHEN r_sql.description(i).col_type = 181
 51                               THEN DBMS_TYPES.TYPECODE_TIMESTAMP_TZ
 52                               --<>--
 53                               WHEN r_sql.description(i).col_type = 182
 54                               THEN DBMS_TYPES.TYPECODE_INTERVAL_YM
 55                               --<>--
 56                               WHEN r_sql.description(i).col_type = 183
 57                               THEN DBMS_TYPES.TYPECODE_INTERVAL_DS
 58                               --<>--
 59                               WHEN r_sql.description(i).col_type = 231
 60                               THEN DBMS_TYPES.TYPECODE_TIMESTAMP_LTZ
 61                               --<>--
 62                            END,
 63                            r_sql.description(i).col_precision,
 64                            r_sql.description(i).col_scale,
 65                            r_sql.description(i).col_max_len,
 66                            r_sql.description(i).col_charsetid,
 67                            r_sql.description(i).col_charsetform );
 68        END LOOP;
 70        v_rtype.EndCreate;
 72        /*
 73        || Now we can use this transient record structure to create a table type
 74        || of the same. This will create a set of types on the database for use
 75        || by the pipelined function...
 76        */
 77        ANYTYPE.BeginCreate( DBMS_TYPES.TYPECODE_TABLE, rtype );
 78        rtype.SetInfo( NULL, NULL, NULL, NULL, NULL, v_rtype,
 79                       DBMS_TYPES.TYPECODE_OBJECT, 0 );
 80        rtype.EndCreate();
 82        RETURN ODCIConst.Success;
 84     END;

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.

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.

 86     STATIC FUNCTION ODCITablePrepare(
 87                     sctx    OUT dla_ot,
 88                     tf_info IN  sys.ODCITabFuncInfo,
 89                     stmt    IN  VARCHAR2
 90                     ) RETURN NUMBER IS
 92        r_meta dla_pkg.rt_anytype_metadata;
 94    BEGIN
 96        /*
 97        || We prepare the dataset that our pipelined function will return by
 98        || describing the ANYTYPE that contains the transient record structure...
 99        */
100        r_meta.typecode := tf_info.rettype.GetAttrElemInfo(
101                              1, r_meta.precision, r_meta.scale, r_meta.length,
102                              r_meta.csid, r_meta.csfrm, r_meta.type,
103                              );
105        /*
106        || Using this, we initialise the scan context for use in this and
107        || subsequent executions of the same dynamic SQL cursor...
108        */
109        sctx := dla_ot(r_meta.type);
111        RETURN ODCIConst.Success;
113     END;

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

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.

116                     sctx IN OUT dla_ot,
117                     stmt IN     VARCHAR2
118                     ) RETURN NUMBER IS
120        r_meta dla_pkg.rt_anytype_metadata;
122    BEGIN
124        /*
125        || We now describe the cursor again and use this and the described
126        || ANYTYPE structure to define and execute the SQL statement...
127        */
128        dla_pkg.r_sql.cursor := DBMS_SQL.OPEN_CURSOR;
129        DBMS_SQL.PARSE( dla_pkg.r_sql.cursor, stmt, DBMS_SQL.NATIVE );
130        DBMS_SQL.DESCRIBE_COLUMNS2( dla_pkg.r_sql.cursor,
131                                    dla_pkg.r_sql.column_cnt,
132                                    dla_pkg.r_sql.description );
134        FOR i IN 1 .. dla_pkg.r_sql.column_cnt LOOP
136           /*
137           || Get the ANYTYPE attribute at this position...
138           */
139           r_meta.typecode := sctx.atype.GetAttrElemInfo(
140                                 i, r_meta.precision, r_meta.scale, r_meta.length,
141                                 r_meta.csid, r_meta.csfrm, r_meta.type,
142                                 );
144           CASE r_meta.typecode
145              --<>--
147              THEN
148                 DBMS_SQL.DEFINE_COLUMN(
149                    dla_pkg.r_sql.cursor, i, '', 32767
150                    );
151              --<>--
153              THEN
154                 DBMS_SQL.DEFINE_COLUMN(
155                    dla_pkg.r_sql.cursor, i, CAST(NULL AS NUMBER)
156                    );
157              --<>--
159              THEN
160                 DBMS_SQL.DEFINE_COLUMN(
161                    dla_pkg.r_sql.cursor, i, CAST(NULL AS DATE)
162                    );
163              --<>--
165              THEN
166                 DBMS_SQL.DEFINE_COLUMN_RAW(
167                    dla_pkg.r_sql.cursor, i, CAST(NULL AS RAW), r_meta.length
168                    );
169              --<>--
171              THEN
172                 DBMS_SQL.DEFINE_COLUMN(
173                    dla_pkg.r_sql.cursor, i, CAST(NULL AS TIMESTAMP)
174                    );
175              --<>--
177              THEN
178                 DBMS_SQL.DEFINE_COLUMN(
179                    dla_pkg.r_sql.cursor, i, CAST(NULL AS TIMESTAMP WITH TIME ZONE)
180                    );
181              --<>--
183              THEN
184                 DBMS_SQL.DEFINE_COLUMN(
185                    dla_pkg.r_sql.cursor, i, CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE)
186                    );
187              --<>--
189              THEN
190                 DBMS_SQL.DEFINE_COLUMN(
191                    dla_pkg.r_sql.cursor, i, CAST(NULL AS INTERVAL YEAR TO MONTH)
192                    );
193              --<>--
195              THEN
196                 DBMS_SQL.DEFINE_COLUMN(
197                    dla_pkg.r_sql.cursor, i, CAST(NULL AS INTERVAL DAY TO SECOND)
198                    );
199              --<>--
201              THEN
202                 --<>--
203                 CASE dla_pkg.r_sql.description(i).col_type
204                    WHEN 8
205                    THEN
206                       DBMS_SQL.DEFINE_COLUMN_LONG(
207                          dla_pkg.r_sql.cursor, i
208                          );
209                    ELSE
210                       DBMS_SQL.DEFINE_COLUMN(
211                          dla_pkg.r_sql.cursor, i, CAST(NULL AS CLOB)
212                          );
213                 END CASE;
214              --<>--
215           END CASE;
216        END LOOP;
218        /*
219        || The cursor is prepared according to the structure of the type we wish
220        || to fetch it into. We can now execute it and we are done for this method...
221        */
222        dla_pkg.r_sql.execute := DBMS_SQL.EXECUTE( dla_pkg.r_sql.cursor );
224        RETURN ODCIConst.Success;
226     END;

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.

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.

229                     SELF   IN OUT dla_ot,
230                     nrows  IN     NUMBER,
231                     rws    OUT    ANYDATASET
232                     ) RETURN NUMBER IS
234        TYPE rt_fetch_attributes IS RECORD
235        ( v2_column    VARCHAR2(32767)
236        , num_column   NUMBER
237        , date_column  DATE
238        , clob_column  CLOB
239        , raw_column   RAW(32767)
240        , raw_error    NUMBER
241        , raw_length   INTEGER
242        , ids_column   INTERVAL DAY TO SECOND
243        , iym_column   INTERVAL YEAR TO MONTH
244        , ts_column    TIMESTAMP
245        , tstz_column  TIMESTAMP WITH TIME ZONE
246        , tsltz_column TIMESTAMP WITH LOCAL TIME ZONE
247        , cvl_offset   INTEGER := 0
248        , cvl_length   INTEGER
249        );
250        r_fetch rt_fetch_attributes;
251        r_meta  dla_pkg.rt_anytype_metadata;
254     BEGIN
256        IF DBMS_SQL.FETCH_ROWS( dla_pkg.r_sql.cursor ) > 0 THEN
258           /*
259           || First we describe our current ANYTYPE instance (SELF.A) to determine
260           || the number and types of the attributes...
261           */
262           r_meta.typecode := SELF.atype.GetInfo(
263                                 r_meta.precision, r_meta.scale, r_meta.length,
264                                 r_meta.csid, r_meta.csfrm, r_meta.schema,
265                       , r_meta.version, r_meta.attr_cnt
266                                 );
268           /*
269           || We can now begin to piece together our returning dataset. We create an
270           || instance of ANYDATASET and then fetch the attributes off the DBMS_SQL
271           || cursor using the metadata from the ANYTYPE. LONGs are converted to CLOBs...
272           */
273           ANYDATASET.BeginCreate( DBMS_TYPES.TYPECODE_OBJECT, SELF.atype, rws );
274           rws.AddInstance();
275           rws.PieceWise();
277           FOR i IN 1 .. dla_pkg.r_sql.column_cnt LOOP
279              r_meta.typecode := SELF.atype.GetAttrElemInfo(
280                                    i, r_meta.precision, r_meta.scale, r_meta.length,
281                                    r_meta.csid, r_meta.csfrm, r_meta.attr_type,
282                                    r_meta.attr_name
283                                    );
285              CASE r_meta.typecode
286                 --<>--
288                 THEN
289                    DBMS_SQL.COLUMN_VALUE(
290                       dla_pkg.r_sql.cursor, i, r_fetch.v2_column
291                       );
292                    rws.SetVarchar2( r_fetch.v2_column );
293                 --<>--
295                 THEN
296                    DBMS_SQL.COLUMN_VALUE(
297                       dla_pkg.r_sql.cursor, i, r_fetch.num_column
298                       );
299                    rws.SetNumber( r_fetch.num_column );
300                 --<>--
302                 THEN
303                    DBMS_SQL.COLUMN_VALUE(
304                       dla_pkg.r_sql.cursor, i, r_fetch.date_column
305                       );
306                    rws.SetDate( r_fetch.date_column );
307                 --<>--
308                 WHEN DBMS_TYPES.TYPECODE_RAW
309                 THEN
310                    DBMS_SQL.COLUMN_VALUE_RAW(
311                       dla_pkg.r_sql.cursor, i, r_fetch.raw_column,
312                       r_fetch.raw_error, r_fetch.raw_length
313                       );
314                    rws.SetRaw( r_fetch.raw_column );
315                 --<>--
317                 THEN
318                    DBMS_SQL.COLUMN_VALUE(
319                       dla_pkg.r_sql.cursor, i, r_fetch.ids_column
320                       );
321                    rws.SetIntervalDS( r_fetch.ids_column );
322                 --<>--
324                 THEN
325                    DBMS_SQL.COLUMN_VALUE(
326                       dla_pkg.r_sql.cursor, i, r_fetch.iym_column
327                       );
328                    rws.SetIntervalYM( r_fetch.iym_column );
329                 --<>--
331                 THEN
332                    DBMS_SQL.COLUMN_VALUE(
333                       dla_pkg.r_sql.cursor, i, r_fetch.ts_column
334                       );
335                    rws.SetTimestamp( r_fetch.ts_column );
336                 --<>--
338                 THEN
339                    DBMS_SQL.COLUMN_VALUE(
340                       dla_pkg.r_sql.cursor, i, r_fetch.tstz_column
341                       );
342                    rws.SetTimestampTZ( r_fetch.tstz_column );
343                 --<>--
345                 THEN
346                    DBMS_SQL.COLUMN_VALUE(
347                       dla_pkg.r_sql.cursor, i, r_fetch.tsltz_column
348                       );
349                    rws.SetTimestamplTZ( r_fetch.tsltz_column );
350                 --<>--
352                 THEN
353                    --<>--
354                    CASE dla_pkg.r_sql.description(i).col_type
355                       WHEN 8
356                       THEN
357                          LOOP
358                             DBMS_SQL.COLUMN_VALUE_LONG(
359                                dla_pkg.r_sql.cursor, i, 32767, r_fetch.cvl_offset,
360                                r_fetch.v2_column, r_fetch.cvl_length
361                                );
362                             r_fetch.clob_column := r_fetch.clob_column ||
363                                                    r_fetch.v2_column;
364                             r_fetch.cvl_offset := r_fetch.cvl_offset + 32767;
365                             EXIT WHEN r_fetch.cvl_length < 32767;
366                          END LOOP;
367                       ELSE
368                          DBMS_SQL.COLUMN_VALUE(
369                             dla_pkg.r_sql.cursor, i, r_fetch.clob_column
370                             );
371                       END CASE;
372                       rws.SetClob( r_fetch.clob_column );
373                 --<>--
374              END CASE;
375           END LOOP;
377           /*
378           || Our ANYDATASET instance is complete. We end our create session...
379           */
380           rws.EndCreate();
382        END IF;
384        RETURN ODCIConst.Success;
386     END;

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:

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.

389                     SELF IN dla_ot
390                     ) RETURN NUMBER IS
391     BEGIN
392        DBMS_SQL.CLOSE_CURSOR( dla_pkg.r_sql.cursor );
393        dla_pkg.r_sql := NULL;
394        RETURN ODCIConst.Success;
395     END;
397  END;
398  /

Type body created.

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:

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.

  2  FROM   TABLE(
  3            dla_pkg.query_view(
  4               'SELECT *
  5                FROM   dba_views'
  6                ) )
  7  WHERE  ROWNUM = 1;

OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
TEXT                                                                           TYPE_TEXT_LENGTH
------------------------------------------------------------------------------ ----------------
VIEW_TYPE_OWNER                VIEW_TYPE                      SUPERVIEW_NAME
------------------------------ ------------------------------ ------------------------------
SYS                            V_$MAP_LIBRARY                         160

1 row selected.

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.

SQL> SELECT type_name, typecode
  2  FROM   user_types
  3  WHERE  type_name LIKE 'SYS%';

TYPE_NAME                      TYPECODE
------------------------------ ------------------------------

2 rows selected.

We can also query the structure of the object type created to support this particular query, as follows.

SQL> SELECT attr_no
  2  ,      attr_name
  3  ,      attr_type_name
  4  FROM   user_type_attrs
  5  WHERE  type_name IN ( SELECT type_name
  6                        FROM   user_types
  7                        WHERE  type_name LIKE 'SYS%' )
  8  ORDER  BY
  9         attr_no;

   ATTR_NO ATTR_NAME                      ATTR_TYPE_NAME
---------- ------------------------------ --------------------------
         1 OWNER                          VARCHAR2
         2 VIEW_NAME                      VARCHAR2
         3 TEXT_LENGTH                    NUMBER
         4 TEXT                           CLOB
         5 TYPE_TEXT_LENGTH               NUMBER
         6 TYPE_TEXT                      VARCHAR2
         7 OID_TEXT_LENGTH                NUMBER
         8 OID_TEXT                       VARCHAR2
         9 VIEW_TYPE_OWNER                VARCHAR2
        10 VIEW_TYPE                      VARCHAR2
        11 SUPERVIEW_NAME                 VARCHAR2

11 rows selected.

As expected, this "record structure" tallies with the DBA_VIEW column description, with the exception that the TEXT column is a CLOB, rather than a LONG.

We will test the DLA's Method 4 capability with another simple query, as follows.

  2  FROM   TABLE(
  3            dla_pkg.query_view(
  4               'SELECT trigger_name, trigger_body
  5                FROM   dba_triggers'
  6                ) )
  7  WHERE  ROWNUM = 1;

------------------------------ --------------------------------------------------------
                                 prop_count  NUMBER;
                                 SELECT count(*) into prop_count
                                   FROM system.def$_propagator;

                                 IF (prop_count > 0) THEN
                                   -- Raise duplicate propagator error
                                 END IF;

1 row selected.

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.

SQL> set autotrace traceonly statistics

SQL> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

  2  FROM   dba_views;

3691 rows selected.

        301  recursive calls
          0  db block gets
       8546  consistent gets
          0  physical reads
          0  redo size
    3175717  bytes sent via SQL*Net to client
      41118  bytes received via SQL*Net from client
       3693  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
       3691  rows processed
SQL> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

  2  FROM   TABLE(
  3            dla_pkg.query_view(
  4               'SELECT * FROM dba_views'
  5               ));

3691 rows selected.

       7457  recursive calls
      89270  db block gets
      28634  consistent gets
          0  physical reads
          0  redo size
    4042814  bytes sent via SQL*Net to client
    2381976  bytes received via SQL*Net from client
      19819  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       3691  rows processed

SQL> exec runstats_pkg.rs_stop(1000);

SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 268 hsecs
Run2 ran in 447 hsecs
Run1 ran in 59.96% of the time

Name                                  Run1        Run2        Diff work - consistent rea       8,498      12,204       3,706
STAT..table fetch by rowid           3,709       7,477       3,768
STAT..recursive calls                1,035       8,191       7,156
STAT..lob writes                         0       7,384       7,384
STAT..lob writes unaligned               0       7,384       7,384 buffer requested              0       7,443       7,443
STAT..buffer is not pinned cou       7,934      15,499       7,565
STAT..lob reads                          0      12,435      12,435
LATCH.cache buffers lru chain           36      14,886      14,850
LATCH.object queue header oper          34      14,887      14,853
LATCH.simulator hash latch             638      15,751      15,113
LATCH.simulator lru latch              638      15,751      15,113
STAT..SQL*Net roundtrips to/fr       3,700      19,826      16,126
STAT..user calls                     3,704      19,830      16,126
STAT..consistent gets                8,549      28,640      20,091
STAT..consistent gets from cac       8,549      28,640      20,091
STAT..consistent changes                 0      29,765      29,765
STAT..db block changes                   0      29,765      29,765
LATCH.session idle bit               7,425      39,677      32,252
STAT..calls to get snapshot sc          27      38,372      38,345
STAT..session pga memory max             0      65,536      65,536
LATCH.library cache pin                 95      74,019      73,924
STAT..db block gets                      0      89,270      89,270
STAT..db block gets from cache           0      89,270      89,270
STAT..session logical reads          8,549     117,910     109,361
LATCH.library cache                    182     111,131     110,949
STAT..session uga memory max             0     130,928     130,928
LATCH.cache buffers chains          17,117     295,027     277,910
STAT..bytes sent via SQL*Net t   3,176,380   4,043,477     867,097
STAT..session pga memory           327,680    -786,432  -1,114,112
STAT..bytes received via SQL*N      42,105   2,382,967   2,340,862

Run1 latches total versus run2 -- difference and pct
Run1       Run2        Diff         Pct
27,376     582,312     554,936      4.70%

PL/SQL procedure successfully completed.

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

For more information on Oracle Data Cartridge, read the Data Cartridge Developer's Guide. In particular, read this section on interface pipelined functions.


The Dictionary Long Application can be downloaded from here. The variation on RUNSTATS is available here.


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).

Adrian Billington, August 2007

Back to Top