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:
SELECT *
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...
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 --<-- transient record type 4 5 , STATIC FUNCTION ODCITableDescribe( 6 rtype OUT ANYTYPE, 7 stmt IN VARCHAR2 8 ) RETURN NUMBER 9 10 , STATIC FUNCTION ODCITablePrepare( 11 sctx OUT dla_ot, 12 tf_info IN sys.ODCITabFuncInfo, 13 stmt IN VARCHAR2 14 ) RETURN NUMBER 15 16 , STATIC FUNCTION ODCITableStart( 17 sctx IN OUT dla_ot, 18 stmt IN VARCHAR2 19 ) RETURN NUMBER 20 21 , MEMBER FUNCTION ODCITableFetch( 22 SELF IN OUT dla_ot, 23 nrows IN NUMBER, 24 rws OUT ANYDATASET 25 ) RETURN NUMBER 26 27 , MEMBER FUNCTION ODCITableClose( 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.
SQL> CREATE PACKAGE dla_pkg AS 2 3 /* 4 || Pipelined function interface. 5 */ 6 FUNCTION query_view( 7 p_stmt IN VARCHAR2 8 ) RETURN ANYDATASET PIPELINED USING dla_ot; 9 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 ); 19 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 ); 35 36 /* 37 || State variable for use across multiple DLA_OT methods. 38 */ 39 r_sql rt_dynamic_sql; 40 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.
SQL> CREATE TYPE BODY dla_ot AS 2 3 STATIC FUNCTION ODCITableDescribe( 4 rtype OUT ANYTYPE, 5 stmt IN VARCHAR2 6 ) RETURN NUMBER IS 7 8 r_sql dla_pkg.rt_dynamic_sql; 9 v_rtype ANYTYPE; 10 11 BEGIN 12 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 ); 20 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 ); 26 27 FOR i IN 1 .. r_sql.column_cnt LOOP 28 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; 69 70 v_rtype.EndCreate; 71 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(); 81 82 RETURN ODCIConst.Success; 83 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.
- 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.
86 STATIC FUNCTION ODCITablePrepare( 87 sctx OUT dla_ot, 88 tf_info IN sys.ODCITabFuncInfo, 89 stmt IN VARCHAR2 90 ) RETURN NUMBER IS 91 92 r_meta dla_pkg.rt_anytype_metadata; 93 94 BEGIN 95 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, r_meta.name 103 ); 104 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); 110 111 RETURN ODCIConst.Success; 112 113 END;
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.
115 STATIC FUNCTION ODCITableStart( 116 sctx IN OUT dla_ot, 117 stmt IN VARCHAR2 118 ) RETURN NUMBER IS 119 120 r_meta dla_pkg.rt_anytype_metadata; 121 122 BEGIN 123 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 ); 133 134 FOR i IN 1 .. dla_pkg.r_sql.column_cnt LOOP 135 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, r_meta.name 142 ); 143 144 CASE r_meta.typecode 145 --<>-- 146 WHEN DBMS_TYPES.TYPECODE_VARCHAR2 147 THEN 148 DBMS_SQL.DEFINE_COLUMN( 149 dla_pkg.r_sql.cursor, i, '', 32767 150 ); 151 --<>-- 152 WHEN DBMS_TYPES.TYPECODE_NUMBER 153 THEN 154 DBMS_SQL.DEFINE_COLUMN( 155 dla_pkg.r_sql.cursor, i, CAST(NULL AS NUMBER) 156 ); 157 --<>-- 158 WHEN DBMS_TYPES.TYPECODE_DATE 159 THEN 160 DBMS_SQL.DEFINE_COLUMN( 161 dla_pkg.r_sql.cursor, i, CAST(NULL AS DATE) 162 ); 163 --<>-- 164 WHEN DBMS_TYPES.TYPECODE_RAW 165 THEN 166 DBMS_SQL.DEFINE_COLUMN_RAW( 167 dla_pkg.r_sql.cursor, i, CAST(NULL AS RAW), r_meta.length 168 ); 169 --<>-- 170 WHEN DBMS_TYPES.TYPECODE_TIMESTAMP 171 THEN 172 DBMS_SQL.DEFINE_COLUMN( 173 dla_pkg.r_sql.cursor, i, CAST(NULL AS TIMESTAMP) 174 ); 175 --<>-- 176 WHEN DBMS_TYPES.TYPECODE_TIMESTAMP_TZ 177 THEN 178 DBMS_SQL.DEFINE_COLUMN( 179 dla_pkg.r_sql.cursor, i, CAST(NULL AS TIMESTAMP WITH TIME ZONE) 180 ); 181 --<>-- 182 WHEN DBMS_TYPES.TYPECODE_TIMESTAMP_LTZ 183 THEN 184 DBMS_SQL.DEFINE_COLUMN( 185 dla_pkg.r_sql.cursor, i, CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE) 186 ); 187 --<>-- 188 WHEN DBMS_TYPES.TYPECODE_INTERVAL_YM 189 THEN 190 DBMS_SQL.DEFINE_COLUMN( 191 dla_pkg.r_sql.cursor, i, CAST(NULL AS INTERVAL YEAR TO MONTH) 192 ); 193 --<>-- 194 WHEN DBMS_TYPES.TYPECODE_INTERVAL_DS 195 THEN 196 DBMS_SQL.DEFINE_COLUMN( 197 dla_pkg.r_sql.cursor, i, CAST(NULL AS INTERVAL DAY TO SECOND) 198 ); 199 --<>-- 200 WHEN DBMS_TYPES.TYPECODE_CLOB 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; 217 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 ); 223 224 RETURN ODCIConst.Success; 225 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.
- 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.
228 MEMBER FUNCTION ODCITableFetch( 229 SELF IN OUT dla_ot, 230 nrows IN NUMBER, 231 rws OUT ANYDATASET 232 ) RETURN NUMBER IS 233 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; 252 253 254 BEGIN 255 256 IF DBMS_SQL.FETCH_ROWS( dla_pkg.r_sql.cursor ) > 0 THEN 257 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.name, r_meta.version, r_meta.attr_cnt 266 ); 267 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(); 276 277 FOR i IN 1 .. dla_pkg.r_sql.column_cnt LOOP 278 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 ); 284 285 CASE r_meta.typecode 286 --<>-- 287 WHEN DBMS_TYPES.TYPECODE_VARCHAR2 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 --<>-- 294 WHEN DBMS_TYPES.TYPECODE_NUMBER 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 --<>-- 301 WHEN DBMS_TYPES.TYPECODE_DATE 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 --<>-- 316 WHEN DBMS_TYPES.TYPECODE_INTERVAL_DS 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 --<>-- 323 WHEN DBMS_TYPES.TYPECODE_INTERVAL_YM 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 --<>-- 330 WHEN DBMS_TYPES.TYPECODE_TIMESTAMP 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 --<>-- 337 WHEN DBMS_TYPES.TYPECODE_TIMESTAMP_TZ 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 --<>-- 344 WHEN DBMS_TYPES.TYPECODE_TIMESTAMP_LTZ 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 --<>-- 351 WHEN DBMS_TYPES.TYPECODE_CLOB 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; 376 377 /* 378 || Our ANYDATASET instance is complete. We end our create session... 379 */ 380 rws.EndCreate(); 381 382 END IF; 383 384 RETURN ODCIConst.Success; 385 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:
- 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.
388 MEMBER FUNCTION ODCITableClose( 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; 396 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:
- 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.
SQL> SELECT * 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 ------------------------------------------------------------------------------ ---------------- TYPE_TEXT ----------------------------------------------------------------------------------------------- OID_TEXT_LENGTH --------------- OID_TEXT ----------------------------------------------------------------------------------------------- VIEW_TYPE_OWNER VIEW_TYPE SUPERVIEW_NAME ------------------------------ ------------------------------ ------------------------------ SYS V_$MAP_LIBRARY 160 select "LIB_IDX","LIB_NAME","VENDOR_NAME","PROTOCOL_NUM","VERSION_NUM","PATH_N AME","MAP_FILE","FILE_CFGID","MAP_ELEM","ELEM_CFGID","MAP_SYNC" from v$map_lib rary 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 ------------------------------ ------------------------------ SYSTPHvttROLoRF+3LWYLkOFoww== COLLECTION SYSTPrH9i9v1OR0KwHDbeEiPKUA== OBJECT 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.
SQL> SELECT * 2 FROM TABLE( 3 dla_pkg.query_view( 4 'SELECT trigger_name, trigger_body 5 FROM dba_triggers' 6 ) ) 7 WHERE ROWNUM = 1;
TRIGGER_NAME TRIGGER_BODY
------------------------------ --------------------------------------------------------
DEF$_PROPAGATOR_TRIG DECLARE
prop_count NUMBER;
BEGIN
SELECT count(*) into prop_count
FROM system.def$_propagator;
IF (prop_count > 0) THEN
-- Raise duplicate propagator error
sys.dbms_sys_error.raise_system_error(-23394);
END IF;
END;
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.
SQL> SELECT * 2 FROM dba_views;
3691 rows selected.
Statistics
----------------------------------------------------------
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.
SQL> SELECT * 2 FROM TABLE( 3 dla_pkg.query_view( 4 'SELECT * FROM dba_views' 5 ));
3691 rows selected.
Statistics
----------------------------------------------------------
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 STAT..no 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 STAT..free 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.
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).
Adrian Billington, August 2007
Back to Top