Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
EAN: 2147483647
Year: 2005
Pages: 41
Pages: 41
Authors: Richard Laermer, Michael Prichinello
Normally, a program is developed with all of the SQL it uses hard coded into it. This is typically referred to as static SQL. Many useful programs however, do not know what SQL they will be executing until run-time. This is where dynamic SQL comes in - it is SQL your program executes at run-time, which was not known at compile-time. Maybe your program generates queries on the fly based on user input; maybe it is a data load program you have developed. SQL*PLUS is a great example of this sort of program, as is any ad-hoc query or reporting tool. SQL*PLUS can execute and display the results of any SQL statement, and it definitely was not compiled knowing all of the SQL you would want to execute.
In this chapter, we will discuss how and why you may want to use dynamic SQL in your programs and the kinds of scenarios where this would be of benefit to your application. We will concentrate on dynamic SQL in PL/SQL, as this is the environment in which most people will be using dynamic SQL in a pre-compiler format. Since dynamic SQL is the norm in Java via JDBC (and in order to perform dynamic SQL in SQLJ you must use JDBC) and in C via OCI, it would not be relevant to discuss them in that context - there is only dynamic SQL and no such thing as static SQL, so discussion of this as a special topic would be pointless. Specifically, we will:
Demonstrate the different implications of the use of dynamic or static SQL.
Look in detail at how you can use dynamic SQL in your programs through the DBMS_SQL supplied package.
Examine the native dynamic SQL feature.
Deal with some of the problems that you may face when using dynamic SQL in your applications. These include the fact that it breaks the dependency chain and it also makes the code more fragile and difficult to tune.
In order to perform all of the dynamic SQL examples in this section, you will need to have Oracle 8.1.5 or higher. Native dynamic SQL was introduced in that release and is a core feature of all subsequent Oracle editions. The majority of the DBMS_SQL examples require only Oracle 7.1 and up, with the exception of functions that employ array processing - a feature that was added to DBMS_SQL in version 8.0.
Dynamic SQL is a natural choice in API approaches to database programming, such as ODBC, JDBC, and OCI. Static SQL on the other hand is more natural in pre-compiler environments such as Pro*C, SQLJ, and PL/SQL (yes, it is fair to think of the PL/SQL compiler as a pre-compiler). In API-based approaches, only dynamic SQL is supported. In order to execute a query you, the programmer, build the query in a string, parse the string, bind inputs into it, execute it, fetch rows if need be, and then finally close the statement. In a static SQL environment almost all of that work will be performed for you. By way of comparison, let's create two PL/SQL programs that do the same thing, but where one uses dynamic SQL and the other static SQL. Here's the dynamic SQL version:
scott@TKYTE816> create or replace procedure DynEmpProc( p_job in varchar2 ) 2 as 3 type refcursor is ref cursor; 4 5 -- We must allocate our own host 6 -- variables and resources using dynamic sql. 7 l_cursor refcursor; 8 l_ename emp.ename%type; 9 begin 10 11 -- We start by parsing the query 12 open l_cursor for 13 'select ename 14 from emp 15 where job = :x' USING in p_job; 16 17 loop 18 -- and explicitly FETCHING from the cursor. 19 fetch l_cursor into l_ename; 20 21 -- We have to do all error handling 22 -- and processing logic ourselves. 23 exit when l_cursor%notfound; 24 25 dbms_output.put_line( l_ename ); 26 end loop; 27 28 -- Make sure to free up resources 29 close l_cursor; 30 exception 31 when others then 32 -- and catch and handle any errors so 33 -- as to not 'leak' resources over time 34 -- when errors occur. if ( l_cursor%isopen ) 36 then 37 close l_cursor; 38 end if; 39 RAISE; 40 end; 41 / Procedure created.
and here is the static SQL counterpart:
scott@TKYTE816> create or replace procedure StaticEmpProc(p_job in varchar2) 2 as 3 begin 4 for x in ( select ename from emp where job = p_job ) 5 loop 6 dbms_output.put_line( x.ename ); 7 end loop; 8 end; 9 / Procedure created.
The two procedures do exactly the same thing:
scott@TKYTE816> set serveroutput on size 1000000 scott@TKYTE816> exec DynEmpProc( 'CLERK' ) SMITH ADAMS JAMES MILLER PL/SQL procedure successfully completed. scott@TKYTE816> exec StaticEmpProc( 'CLERK' ) SMITH ADAMS JAMES MILLER PL/SQL procedure successfully completed.
However, it is clear that dynamic SQL will require significantly more coding on your part. I find that, from a programming standpoint, static SQL is more efficient while coding (I can develop an application faster using static SQL) but dynamic SQL is more flexible at run-time (my program can do things I did not explicitly code into it at run-time). Also, static SQL, especially static SQL in PL/SQL, will execute much more efficiently than dynamic SQL. Using static SQL, the PL/SQL engine can do in a single interpreted line of code what might take five or six lines of interpreted code with dynamic SQL. For this reason, I use static SQL whenever possible and drop down to dynamic SQL only when I have to. Both methods are useful; one is not inherently better than the other and there are efficiencies and features to be gained from both.
There are many reasons for using dynamic SQL in PL/SQL. Some of the more common ones are:
To develop generic routines to perform common operations such as unloading data to flat files. In Chapter 9 on Data Loading, we demonstrate such a routine.
To develop generic routines to load data into as yet unknown tables. We will explore the use of dynamic SQL to load any table.
To dynamically invoke other PL/SQL routines at run-time. We discuss this topic in Chapter 23 on Invoker and Definer Rights procedures. We'll investigate this further in this chapter.
To generate predicates (for example the where clause) on the fly at run-time based on inputs. This is perhaps the number one reason people use dynamic SQL. We will explore how to do this (and how not to do this!) in this chapter.
To execute DDL. Since PL/SQL does not permit you to code static DDL statements in your application, dynamic SQL is the only way to achieve this. This will permit us to issue statements that begin with CREATE, ALTER, GRANT, DROP, and so on.
We will explore the above using two main PL/SQL constructs. Firstly, the DBMS_SQL supplied package. This package has been around for a while C since version 7.1 of Oracle. It supplies a procedural method for executing dynamic SQL that is similar to an API-based approach (such as JDBC or ODBC). Secondly, native dynamic SQL (this is the EXECUTE IMMEDIATE verb in PL/SQL). This is a declarative method of performing dynamic SQL in PL/SQL and in most cases, is syntactically easier than DBMS_SQL as well as being faster.
Note that most, but not all, of the procedural DBMS_SQL package still represents a vital and important approach in PL/SQL. We will compare and contrast the two methods and try to spell out when you might want to use one over the other. Once you determine that you need dynamic SQL (static SQL would be best in most cases), you will choose DBMS_SQL or native dynamic SQL after determining the following.
DBMS_SQL will be used when:
You do not know the number or types of columns with which you will be working. DBMS_SQL includes procedures to 'describe' a resultset. Native dynamic SQL does not. With native dynamic SQL, you need to know what the resultset will look like at compile-time if PL/SQL is to process the results.
You do not know the number or types of possible bind variables with which you will be working. DBMS_SQL allows us to procedurally bind inputs into our statements. Native dynamic SQL requires us to be aware of the number and types of bind variables at compile-time (but we will investigate an interesting workaround to this issue).
You will be fetching or inserting thousands of rows and can employ array processing. DBMS_SQL permits array processing, the ability to fetch N rows in a single call instead of a row at a time. Native dynamic SQL in general does not, but there is a workaround demonstrated below.
You will be executing the same statement many times in the same session. DBMS_SQL will allow us to parse the statement once and execute it many times over. Native dynamic SQL will cause a soft parse with each and every execution. See Chapter 10 on Tuning Strategies and Tools for why this extra parsing is not desirable.
Native dynamic SQL should be used when:
You know the number and types of columns with which you will be working.
You know the number and types of bind variables (we can also use application contexts to use the easier native dynamic SQL to get around the fact we don't know the number and types).
You will be doing DDL.
You will be executing the statement very few (one optimally) times.
We will look at the basics involved in using both the supplied DBMS_SQL database package as well as the native dynamic SQL feature.
The DBMS_SQL package is a supplied built-in package with the database. It is installed by default into the SYS schema and the privilege to execute this package is granted to PUBLIC. That means you should have no trouble accessing it or building compiled stored objects that reference it C no additional or special grants need to be made. One of the nice things about this package is that the documentation is always just a couple of keystrokes away. If you are using DBMS_SQL and need a quick refresher, you can simply execute the following script:
scott@TKYTE816> set pagesize 30 scott@TKYTE816> set pause on scott@TKYTE816> prompt remember to hit ENTER to start reading remember to hit ENTER to start reading scott@TKYTE816> select text 2 from all_source 3 where name = 'DBMS_SQL' 4 and type = 'PACKAGE' 5 order by line 6 / TEXT -------------------------------------------------------------------------- package dbms_sql is ------------ -- OVERVIEW -- -- This package provides a means to use dynamic SQL to access the database. -- ------------------------- -- RULES AND LIMITATIONS ...
| Note | In fact, if you need an overview and examples, the above trick works nicely for any of the supplied DBMS_ or UTL_ packages. |
DBMS_SQL is a procedural approach to dynamic SQL. It represents a very similar approach to that used in any other language, such as Java using JDBC or C using OCI. In general, a process using DBMS_SQL will have the following structure:
Call OPEN_CURSOR to obtain a cursor handle.
Call PARSE to parse a statement. A single cursor handle may be used with many different parsed statements. Only one statement at a time will be in effect however.
Call BIND_VARIABLE or BIND_ARRAY to supply any input to the statement.
If it is a query (SELECT statement) you will call DEFINE_COLUMN or DEFINE_ARRAY to tell Oracle how you want the output (as arrays or scalars and of what types).
Call EXECUTE to have the statement run and do its work.
If it was a query, you will call FETCH_ROWS to have Oracle fetch data. You will use COLUMN_VALUE to retrieve these values by position in the select list.
Otherwise, if it was a PL/SQL block of code or a DML statement with a RETURN clause, you can call VARIABLE_VALUE to retrieve OUT values from the block by name.
Call CLOSE_CURSOR.
The pseudo-code steps for dynamically processing a query are:
1) Open a cursor 2) Parse a statement 3) Optionally describe the statement to discover the outputs 4) For I in number of bind variables (inputs) Bind the I'th input to the statement 5) For I in number of output columns Define the I'th column, tell Oracle what type of variable you will be fetching into 6) Execute the statement 7) While Fetch Rows succeeds Loop 8) For I in number of output columns Call column value to retrieve the value of the I'th column End while loop 9) Close cursor
And the pseudo-code steps for a PL/SQL block or DML statement are:
1) Open a cursor 2) Parse a statement 3) For I in number of bind variables (inputs and outputs) Bind the I'th input to the statement 4) Execute the statement 5) For I in number of output bind variables Call variable value to retrieve the value of the I'th output 6) Close cursor
Lastly, when simply executing DDL (which can have no bind variables) or PL/SQL or DML statements that need no bind variables, the above set of steps is simplified to (although, for this type of statement, I would prefer not to use DBMS_SQL but rather to use native dynamic SQL in all such cases):
1) Open a cursor 2) Parse a statement 3) Execute the statement 4) Close cursor
Consider the following example of using DBMS_SQL with a query, which can count the number of rows in any database table to which you have access:
scott@TKYTE816> create or replace 2 function get_row_cnts( p_tname in varchar2 ) return number 3 as 4 l_theCursor integer; 5 l_columnValue number default NULL; 6 l_status integer; 7 begin 8 9 -- Step 1, open the cursor. 10 l_theCursor := dbms_sql.open_cursor;
We begin a block with an exception handler. If we get an error in this next block of code, we need to have the exception handler close our cursor that we just opened, in order to avoid a 'cursor leak' whereby an opened cursor handle is lost when an error throws us out of this routine.
11 begin 12 13 -- Step 2, parse the query. 14 dbms_sql.parse( c => l_theCursor, 15 statement => 'select count(*) from ' || p_tname, 16 language_flag => dbms_sql.native ); 17
Notice the language flag is set to the constant supplied in the DBMS_SQL package named NATIVE. This causes the query to be parsed using the rules of the database that the code is executing in. It could be set to DBMS_SQL.V6 or DBMS_SQL.V7 as well. I use NATIVE in all cases.
We did not need step 3 or 4 from the pseudo-code above, as we know the outputs and there are no bind variables in this simple example.
18 -- Step 5, define the output of this query as a NUMBER. 19 dbms_sql.define_column ( c => l_theCursor, 20 position => 1, 21 column => l_columnValue ); 22
DEFINE does this by being an overloaded procedure so it can tell when you defined a number, DATE, or VARCHAR.
23 -- Step 6, execute the statement. 24 l_status := dbms_sql.execute(l_theCursor); 25
If this were a DML statement, L_STATUS would be the number of rows returned. For a SELECT, the return value is not meaningful.
26 -- Step 7, fetch the rows. 27 if ( dbms_sql.fetch_rows( c => l_theCursor) > 0 ) 28 then 29 -- Step 8, retrieve the outputs. 30 dbms_sql.column_value( c => l_theCursor, 31 position => 1, 32 value => l_columnValue ); 33 end if; 34 35 -- Step 9, close the cursor. 36 dbms_sql.close_cursor( c => l_theCursor ); 37 return l_columnValue; 38 exception 39 when others then 40 dbms_output.put_line( '===> ' || sqlerrm ); 41 dbms_sql.close_cursor( c => l_theCursor ); 42 RAISE; 43 end; 44 end; 45 / Function created. scott@TKYTE816> set serveroutput on scott@TKYTE816> begin 2 dbms_output.put_line('Emp has this many rows ' || 3 get_row_cnts('emp')); 4 end; 5 / Emp has this many rows 14 PL/SQL procedure successfully completed. scott@TKYTE816> begin 2 dbms_output.put_line('Not a table has this many rows ' || 3 get_row_cnts('NOT_A_TABLE')); 4 end; 5 / ===> ORA-00942: table or view does not exist begin * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at "SCOTT.GET_ROW_CNTS", line 60 ORA-06512: at line 2
In the above example, we started by allocating a cursor via the call to DBMS_SQL.OPEN_CURSOR. It should be noted that this cursor is specific to DBMS_SQL C it cannot be returned to a VB application to be fetched from and it cannot be used as a PL/SQL cursor. In order to retrieve data from this cursor we must use DBMS_SQL. We then parsed a query SELECT COUNT(*) FROM TABLE, where the value of TABLE is supplied by the caller at run-time C it is concatenated into the query. We must 'glue' the name of the table into the query and not use a bind variable, since bind variables can never be used where an identifier (table name or column name for example) is needed. After parsing the query, we used DBMS_SQL.DEFINE_COLUMN to specify that we would like the first (and in this case only) output column in the SELECT list to be retrieved as a NUMBER. The fact we want it retrieved as a number is implicit here C DBMS_SQL.DEFINE_COLUMN is an overloaded procedure with entry points for VARCHARs, NUMBERs, DATEs, BLOB, CLOB, and so on. The output data type we want is implied by the entry point we use, since L_COLUMNVALUE above is a number, the number version of DEFINE_COLUMN is invoked. Next, we call DBMS_SQL.EXECUTE. If we were performing an INSERT, UPDATE, or DELETE type of statement, EXECUTE would return the number of rows affected. In the case of a query, the return value is not defined and may just be ignored. After executing the statement, we call DBMS_SQL.FETCH_ROWS. FETCH_ROWS returns the number of rows actually fetched. In our case above, since we bound to scalar types (not arrays) FETCH_ROWS will return 1 until no more data exists at which point it will return 0. After each row we fetch, we call DBMS_SQL.COLUMN_VALUE for each column in the select list to retrieve its value. Lastly, we finish the routine by closing the cursor via DBMS_SQL.CLOSE_CURSOR.
Next, we will demonstrate how to use DBMS_SQL to process parameterized PL/SQL blocks or DML statements dynamically. I find this functionality very useful when loading an arbitrary file from the operating system using UTL_FILE (an API to allow PL/SQL to read text files) for example. We provide such a utility as an example in Chapter 9 on Data Loading. There we use DBMS_SQL to dynamically build an INSERT statement with some number of columns, the number of which is only known at run-time and varies from call to call. It would not be possible to use native dynamic SQL to load an arbitrary table with an arbitrary number of columns, since it needs to know the number of bind variables at compile-time. This particular example is designed to show the mechanics of DBMS_SQL with regards to PL/SQL blocks and DML (it would actually be easier to code the following example using native dynamic SQL because we obviously know the number of bind variables at compile-time in this case):
scott@TKYTE816> create or replace 2 function update_row( p_owner in varchar2, 3 p_newDname in varchar2, 4 p_newLoc in varchar2, 5 p_deptno in varchar2, 6 p_rowid out varchar2 ) 7 return number 8 is 9 l_theCursor integer; 10 l_columnValue number default NULL; 11 l_status integer; 12 l_update long; 13 begin 14 l_update := 'update ' || p_owner || '.dept 15 set dname = :bv1, loc = :bv2 16 where deptno = to_number(:pk) 17 returning rowid into :out'; 18 19 -- Step 1, open the cursor. 20 l_theCursor := dbms_sql.open_cursor; 21
We begin a subblock with an exception handler. If we get an error in this block of code, we need to have the exception handler close our cursor we just opened in order to avoid a 'cursor leak' whereby an opened cursor handle is lost when an error throws us out of this routine.
22 begin 23 -- Step 2, parse the query. 24 dbms_sql.parse( c => l_theCursor, 25 statement => l_update, 26 language_flag => dbms_sql.native ); 27 28 -- Step 3, bind all of the INPUTS and OUTPUTS. 29 dbms_sql.bind_variable( c => l_theCursor, 30 name => ':bv1', 31 value => p_newDname ); 32 dbms_sql.bind_variable( c => l_theCursor, 33 name => ':bv2', 34 value => p_newLoc ); 35 dbms_sql.bind_variable( c => l_theCursor, 36 name => ':pk', 37 value => p_deptno ); 38 dbms_sql.bind_variable( c => l_theCursor, 39 name => ':out', 40 value => p_rowid, 41 out_value_size => 4000 ); 42
Note that even though the returning variables are all OUT only parameters, we must 'bind' them on input. We also must send the maximum size that we expect them to be on the way out as well (OUT_VALUE_SIZE) so that Oracle will set aside the space for them.
43 -- Step 4, execute the statement. Since this is a DML 44 -- statement, L_STATUS is be the number of rows updated. 45 -- This is what we'll return. 46 47 l_status := dbms_sql.execute(l_theCursor); 48 49 -- Step 5, retrieve the OUT variables from the statement. 50 dbms_sql.variable_value( c => l_theCursor, 51 name => ':out', 52 value => p_rowid ); 53 54 -- Step 6, close the cursor. 55 dbms_sql.close_cursor( c => l_theCursor ); 56 return l_columnValue; 57 exception 58 when dup_val_on_index then 59 dbms_output.put_line( '===> ' || sqlerrm ); 60 dbms_sql.close_cursor( c => l_theCursor ); 61 RAISE; 62 end; 63 end; 64 / Function created. scott@TKYTE816> set serveroutput on scott@TKYTE816> declare 2 l_rowid varchar(50); 3 l_rows number; 4 begin 5 l_rows := update_row('SCOTT','CONSULTING','WASHINGTON', '10',l_rowid ); 6 7 dbms_output.put_line( 'Updated ' || l_rows || ' rows' ); 8 dbms_output.put_line( 'its rowid was ' || l_rowid ); 9 end; 10 / Updated 1 rows its rowid was AAAGnuAAFAAAAESAAA PL/SQL procedure successfully completed.
So, this shows the mechanics of using DBMS_SQL to execute a block of code providing inputs and retrieving outputs. Let me reiterate C the above block of code would typically be implemented using native dynamic SQL (we'll implement the above routine in native dynamic SQL in a moment). We used DBMS_SQL merely to show how the API worked. In other sections of the book, specifically Chapter 9 on Data Loading, we show why DBMS_SQL is still extremely useful. In that chapter we examine the code for a PL/SQL 'data loader' and 'data unloader'. There, DBMS_SQL shows its true power C the ability to process an unknown number of columns of different types, either on input (INSERTs) or output (SELECTs).
We have now covered perhaps 75 percent of the functionality of the DBMS_SQL package. A little later, when we take a look at executing the same statement, dynamically, many times, we'll take a look at the array interface and compare the use of DBMS_SQL to native dynamic SQL in this scenario. However, for the time being, this concludes our overview of DBMS_SQL. For a complete list of the subroutines available and their inputs/outputs, I would refer you to the Oracle8i Supplied PL/SQL Packages Reference, where each routine is enumerated.
Native dynamic SQL was introduced in Oracle 8i. It introduces to PL/SQL a declarative method for executing dynamic SQL. Most of the work is done with one clause, EXECUTE IMMEDIATE, but with a little help from OPEN FOR. The syntax for EXECUTE IMMEDIATE is as follows:
EXECUTE IMMEDIATE 'some statement' [INTO {variable1, variable2, ... variableN | record}] [USING [IN | OUT | IN OUT] bindvar1, ... bindvarN] [{RETURNING | RETURN} INTO output1 [, ..., outputN]...];
where:
some statement is any SQL or PL/SQL statement.
variable1, variable2, ... variableN or record is a PL/SQL variable to be fetched into (as the result of single row SELECT).
bindvar1, ... bindvarN is a set of PL/SQL variables to be used for input and/or output.
output1, ... outputN is a set of PL/SQL variables to be used for output from a RETURN clause in DML.
By way of an example, I will implement the code for the GET_ROW_CNTS and UPDATE_ROW functions, that we previously implemented using DBMS_SQL, using EXECUTE IMMEDIATE. First, the GET_ROW_CNTS function:
scott@TKYTE816> create or replace 2 function get_row_cnts( p_tname in varchar2 ) return number 3 as 4 l_cnt number; 5 begin 6 execute immediate 7 'select count(*) from ' || p_tname 8 into l_cnt; 9 10 return l_cnt; 11 end; 12 / Function created. scott@TKYTE816> set serveroutput on scott@TKYTE816> exec dbms_output.put_line( get_row_cnts('emp') ); 14 PL/SQL procedure successfully completed.
Using a simple SELECT...INTO...with EXECUTE IMMEDIATE, you can see that we have drastically cut down the amount of code we needed to write. The nine procedural steps needed by DBMS_SQL are rolled into one step using native dynamic SQL. It is not always just one step C sometimes it is three, as we'll see below C but you get the point. Native dynamic SQL is much more efficient from a coding perspective in this scenario (we'll investigate it from a performance perspective in a moment). You may also have noticed the removal of the EXCEPTION block here C it is not necessary, since everything is implicit. There is no cursor for me to close, no cleanup necessary. Oracle does all of the work.
Now, we will implement UPDATE_ROW using native dynamic SQL:
scott@TKYTE816> create or replace 2 function update_row( p_owner in varchar2, 3 p_newDname in varchar2, 4 p_newLoc in varchar2, 5 p_deptno in varchar2, 6 p_rowid out varchar2 ) 7 return number 8 is 9 begin 10 execute immediate 11 'update ' || p_owner || '.dept 12 set dname = :bv1, loc = :bv2 13 where deptno = to_number(:pk) 14 returning rowid into :out' 15 using p_newDname, p_newLoc, p_deptno 16 returning into p_rowid; 17 18 return sql%rowcount; 19 end; 20 / Function created. scott@TKYTE816> set serveroutput on scott@TKYTE816> declare 2 l_rowid varchar(50); 3 l_rows number; 4 begin 5 l_rows := update_row( 'SCOTT', 'CONSULTING', 6 'WASHINGTON', '10', l_rowid ); 7 8 dbms_output.put_line( 'Updated ' || l_rows || ' rows' ); 9 dbms_output.put_line( 'its rowid was ' || l_rowid ); 10 end; 11 / Updated 1 rows its rowid was AAAGnuAAFAAAAESAAA PL/SQL procedure successfully completed.
Once again, the reduction in code is tremendous C one step instead of six; easier to read, easier to maintain. In these two cases, native dynamic SQL is clearly superior to DBMS_SQL.
In addition to the EXECUTE IMMEDIATE, native dynamic SQL supports the dynamic processing of a REF CURSOR, also known as a cursor variable. Cursor variables have been around for a while in Oracle (since version 7.2, in fact). Initially, they allowed a stored procedure to OPEN a query (resultset) and return it to a client. They are the methods by which a stored procedure returns a resultset to a client when using a VB, JDBC, ODBC, or OCI program. Later, in version 7.3, they were generalized so that PL/SQL could use a cursor variable not only in the OPEN statement, but in a FETCH statement as well (the client could be another PL/SQL routine). This allowed a PL/SQL routine to accept a resultset as input and process it. This gave us a way to centralize certain processing of different queries C a single routine could FETCH from many different queries (resultsets) for the first time. Until Oracle 8i, however, REF CURSORS were purely static in nature. You had to know at compile-time (stored procedure creation time) exactly what the SQL query was going to be. This was very limiting, as you could not dynamically change the predicate, change the table(s) being queried, and so on. Starting with Oracle 8i, native dynamic SQL allows us to dynamically open a REF CURSOR using any arbitrary query. The syntax for this is simply:
OPEN ref_cursor_variable FOR 'select ...' USING bind_variable1, bind_variabl2, ...;
So, using a ref cursor with dynamic SQL, we can implement a generic procedure that queries a table differently given different inputs, and returns the resultset to the client for further processing:
scott@TKYTE816> create or replace package my_pkg 2 as 3 type refcursor_Type is ref cursor; 4 5 procedure get_emps( p_ename in varchar2 default NULL, 6 p_deptno in varchar2 default NULL, 7 p_cursor in out refcursor_type ); 8 end; 9 / Package created. scott@TKYTE816> create or replace package body my_pkg 2 as 3 procedure get_emps( p_ename in varchar2 default NULL, 4 p_deptno in varchar2 default NULL, 5 p_cursor in out refcursor_type ) 6 is 7 l_query long; 8 l_bind varchar2(30); 9 begin 10 l_query := 'select deptno, ename, job from emp'; 11 12 if ( p_ename is not NULL ) 13 then 14 l_query := l_query || ' where ename like :x'; 15 l_bind := '%' || upper(p_ename) || '%'; 16 elsif ( p_deptno is not NULL ) 17 then 18 l_query := l_query || ' where deptno = to_number(:x)'; 19 l_bind := p_deptno; 20 else 21 raise_application_error(-20001,'Missing search criteria'); 22 end if; 23 24 open p_cursor for l_query using l_bind; 25 end; 26 end; 27 / Package body created. scott@TKYTE816> variable C refcursor scott@TKYTE816> set autoprint on scott@TKYTE816> exec my_pkg.get_emps( p_ename => 'a', p_cursor => :C ) PL/SQL procedure successfully completed. DEPTNO ENAME JOB ---------- ---------- --------- 20 ADAMS CLERK 30 ALLEN SALESMAN 30 BLAKE MANAGER 10 CLARK MANAGER 30 JAMES CLERK 30 MARTIN SALESMAN 30 WARD SALESMAN 7 rows selected. scott@TKYTE816> exec my_pkg.get_emps( p_deptno=> '10', p_cursor => :C ) PL/SQL procedure successfully completed. DEPTNO ENAME JOB ---------- ---------- --------- 10 CLARK MANAGER 10 KING PRESIDENT 10 MILLER CLERK
Any time you have more than one row returned from a dynamic query, you must use the above method instead of EXECUTE IMMEDIATE.
So, compared to the DBMS_SQL routines above, this EXECUTE IMMEDIATE and OPEN method is trivial to code and to implement. Does that mean we should never use DBMS_SQL again? The answer to that is definitely no. The above examples show how easy dynamic SQL can be when we know the number of bind variables at compile-time. If we did not know this, we could not use EXECUTE IMMEDIATE as easily as we did above. It needs to know the number of bind variables beforehand. DBMS_SQL is more flexible in that respect. In addition to the bind variables, there is the issue of defined columns C columns that are output from a SQL SELECT statement. If you do not know the number and types of these columns, you again cannot use EXECUTE IMMEDIATE. You may be able to use OPEN FOR if the client that will receive the REF CURSOR is not another PL/SQL routine.
In terms of performance, EXECUTE IMMEDIATE will outperform DBMS_SQL for all statements that are parsed and executed only once (all of our examples so far have been of the 'execute once' type). DBMS_SQL has more overhead in this regard, simply because we must make five or six procedure calls to accomplish what is done in one EXECUTE IMMEDIATE.
However, DBMS_SQL makes a comeback in the performance arena when you use it to execute a parsed statement over and over again. EXECUTE IMMEDIATE has no mechanism to 'reuse' parsed statements. It must always parse them, and the overhead of doing that over and over again soon outweighs the benefits of making fewer procedure calls. This is especially relevant in a multi-user environment. Lastly, EXECUTE IMMEDIATE and OPEN cannot utilize array processing as easily as DBMS_SQL and, as we shall see, this alone can have a huge impact on performance.
Now that we've discussed how to implement various routines using either DBMS_SQL or native dynamic SQL, we'll investigate when you should use one over the other. It boils down to some very clear cases. The things that will impact your choice are:
Whether you know the bind variables at compile-time or not. If you do not, DBMS_SQL will most likely be the correct choice.
Whether you know all of the outputs at compile-time or not. If you do not, DBMS_SQL most likely will be the correct choice.
Whether you need to use a REF CURSOR to return a resultset from a stored procedure. If you do, you will have to use OPEN FOR.
Whether you will be executing a given statement once, or many times in a session. If you find you will dynamically execute the same statement many times, DBMS_SQL will give better performance.
Whether you need to employ array processing dynamically.
We'll investigate three of these cases below (in fact, four really C because we will look at examples that execute a statement many times, both with and without array processing.
Bind variables have a great impact on the performance of your system. Without them, performance will be terrible. With them, performance will be enhanced. It is as simple as that. We have looked at methods to perform auto binding in Chapter 10 on Tuning Strategies and Tools (via the CURSOR_SHARING parameter). This helps immensely but still adds overhead as the database must rewrite your query and remove information that could be vital to the optimizer, instead of you doing it right in your code yourself.
So, let's say you wanted to create a procedure that creates a query dynamically, based on user inputs. The query will always have the same outputs C the same SELECT list C but the WHERE clause will vary depending on user input. We need to use bind variables for performance reasons. How can we do this using native dynamic SQL and DBMS_SQL? To see the methods, we'll start with a sample routine specification. The procedure we will develop will look like this:
scott@TKYTE816> create or replace package dyn_demo 2 as 3 type array is table of varchar2(2000); 4 5 6 /* 7 * DO_QUERY will dynamically query the emp 8 * table and process the results. You might 9 * call it like this: 10 * 11 * dyn_demo.do_query( array( 'ename', 'job' ), 12 array( 'like', '=' ), 13 array( '%A%', 'CLERK' ) ); 14 * 15 * to have it query up: 16 * 17 * select * from emp where ename like '%A%' and job = 'CLERK' 18 * 19 * for example. 20 */ 21 procedure do_query( p_cnames in array, 22 p_operators in array, 23 p_values in array ); 24 25 end; 26 / Package created.
It is natural to do this with DBMS_SQL C the package was built for this type of situation. We can procedurally loop through our arrays of columns and values, and build the WHERE clause. We can parse that query and then loop through the arrays again to bind the values to the placeholders. Then we can execute the statement, fetch the rows and process them. It could be coded as follows:
scott@TKYTE816> create or replace package body dyn_demo 2 as 3 4 /* 5 * DBMS_SQL-based implementation of dynamic 6 * query with unknown bind variables 7 */ 8 g_cursor int default dbms_sql.open_cursor; 9 10 11 procedure do_query( p_cnames in array, 12 p_operators in array, 13 p_values in array ) 14 is 15 l_query long; 16 l_sep varchar2(20) default ' where '; 17 l_comma varchar2(1) default ''; 18 l_status int; 19 l_colValue varchar2(4000); 20 begin 21 /* 22 * This is our constant SELECT list - we'll always 23 * get these three columns. The predicate is what 24 * changes. 25 */ 26 l_query := 'select ename, empno, job from emp'; 27 28 /* 29 * We build the predicate by putting: 30 * 31 * cname operator :bvX 32 * 33 * into the query first. 34 */ 35 for i in 1 .. p_cnames.count loop 36 l_query := l_query || l_sep || p_cnames(i) || ' ' || 37 p_operators(i) || ' ' || 38 ':bv' || i; 39 l_sep := ' and '; 40 end loop; 41 42 /* 43 * Now we can parse the query 44 */ 45 dbms_sql.parse(g_cursor, l_query, dbms_sql.native); 46 47 /* 48 * and then define the outputs. We fetch all three 49 * columns into a VARCHAR2 type. 50 */ 51 for i in 1 .. 3 loop 52 dbms_sql.define_column( g_cursor, i, l_colValue, 4000 ); 53 end loop; 54 55 /* 56 * Now, we can bind the inputs to the query 57 */ 58 for i in 1 .. p_cnames.count loop 59 dbms_sql.bind_variable( g_cursor, ':bv'||i, p_values(i), 4000); 60 end loop; 61 62 /* 63 * and then execute it. This defines the resultset 64 */ 65 l_status := dbms_sql.execute(g_cursor); 66 67 /* 68 * and now we loop over the rows and print out the results. 69 */ 70 while( dbms_sql.fetch_rows( g_cursor ) > 0 ) 71 loop 72 l_comma := ''; 73 for i in 1 .. 3 loop 74 dbms_sql.column_value( g_cursor, i, l_colValue ); 75 dbms_output.put( l_comma || l_colValue ); 76 l_comma := ','; 77 end loop; 78 dbms_output.new_line; 79 end loop; 80 end; 81 82 end dyn_demo; 83 / Package body created. scott@TKYTE816> set serveroutput on scott@TKYTE816> begin 2 dyn_demo.do_query( dyn_demo.array( 'ename', 'job' ), 3 dyn_demo.array( 'like', '=' ), 4 dyn_demo.array( '%A%', 'CLERK' ) ); 5 end; 6 / ADAMS,7876,CLERK JAMES,7900,CLERK PL/SQL procedure successfully completed.
As you can see, it is very straightforward and it follows the steps for DBMS_SQL outlined in the beginning. Now, we would like to implement the same thing using native dynamic SQL. Here we run into a snag however. The syntax for opening a query dynamically with bind variables using native dynamic SQL is:
OPEN ref_cursor_variable FOR 'select ...' USING variable1, variable2, variable3, ...;
The problem here is that we do not know at compile-time how large our USING list is C will there be one variable, two variables, no variables? The answer is: we do not know. So, we need to parameterize our query but cannot use bind variables in the traditional sense. We can however, borrow a feature intended for use elsewhere. When we investigate Fine Grained Access Control in Chapter 21, we review what an application context is and how to use it. An application context is basically a method to place a variable/value pair into a namespace. This variable/value pair may be referenced in SQL using the built-in SYS_CONTEXT function. We can use this application context to parameterize a query by placing our bind values into a namespace and referencing them via the built-in SYS_CONTEXT routine in the query.
So, instead of building a query like:
select ename, empno, job from emp where ename like :bv1 and job = :bv2;
as we did above, we will build a query that looks like:
select ename, empno, job from emp where ename like SYS_CONTEXT('namespace','ename') and job = SYS_CONTEXT('namespace','job');
The code to implement this could look like this:
scott@TKYTE816> REM SCOTT must have GRANT CREATE ANY CONTEXT TO SCOTT; scott@TKYTE816> REM or a role with that for this to work scott@TKYTE816> create or replace context bv_context using dyn_demo 2 / Context created. scott@TKYTE816> create or replace package body dyn_demo 2 as 3 4 procedure do_query( p_cnames in array, 5 p_operators in array, 6 p_values in array ) 7 is 8 type rc is ref cursor; 9 10 l_query long; 11 l_sep varchar2(20) default ' where '; 12 l_cursor rc; 13 l_ename emp.ename%type; 14 l_empno emp.empno%type; 15 l_job emp.job%type; 16 begin 17 /* 18 * This is our constant SELECT list - we'll always 19 * get these three columns. The predicate is what 20 * changes. 21 */ 22 l_query := 'select ename, empno, job from emp'; 23 24 for i in 1 .. p_cnames.count loop 25 l_query := l_query || l_sep || 26 p_cnames(i) || ' ' || 27 p_operators(i) || ' ' || 28 'sys_context( ''BV_CONTEXT'', ''' || 29 p_cnames(i) || ''' )'; 30 l_sep := ' and '; 31 dbms_session.set_context( 'bv_context', 32 p_cnames(i), 33 p_values(i) ); 34 end loop; 35 36 open l_cursor for l_query; 37 loop 38 fetch l_cursor into l_ename, l_empno, l_job; 39 exit when l_cursor%notfound; 40 dbms_output.put_line( l_ename ||','|| l_empno ||','|| l_job ); 41 end loop; 42 close l_cursor; 43 end; 44 45 end dyn_demo; 46 / Package body created. scott@TKYTE816> set serveroutput on scott@TKYTE816> begin 2 dyn_demo.do_query( dyn_demo.array( 'ename', 'job' ), 3 dyn_demo.array( 'like', '=' ), 4 dyn_demo.array( '%A%', 'CLERK' ) ); 5 end; 6 / ADAMS,7876,CLERK JAMES,7900,CLERK PL/SQL procedure successfully completed.
So, it is not as straightforward as using DBMS_SQL with regards to bind variables C it requires a trick. Once you understand how to do this, you should opt for using native dynamic SQL over DBMS_SQL as long as the query has a fixed number of outputs and you use an application context. You must create and use an application context to support bind variables in order to do the above type of work efficiently. In the end, you will find the above example, using REF CURSORS with native dynamic SQL, to be faster. On simple queries, where the processing of the query itself could be ignored, native dynamic SQL is almost twice as fast at fetching the data as DBMS_SQL.
Here the answer is cut and dry C if the client that will fetch and process the data is PL/SQL, you must use DBMS_SQL. If the client that will fetch and process the data is a 3GL application using ODBC, JDBC, OCI, and so on, you will use native dynamic SQL.
The situation we will look at is that you are given a query at run-time and you have no idea how many columns are in the select list. You need to process this in PL/SQL. We will find we cannot use native dynamic SQL since we would need to code the following:
FETCH cursor INTO variable1, variable2, variable3, ...; at some point in our code, but we cannot do this since we do not know how many variables to place there until run-time. This is one case where DBMS_SQL's use will be mandatory, since it gives us the ability to use constructs such as:
41 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) 42 loop 43 /* Build up a big output line, this is more efficient than calling 44 * DBMS_OUTPUT.PUT_LINE inside the loop. 45 */ 46 l_cnt := l_cnt+1; 47 l_line := l_cnt; 48 /* Step 8 - get and process the column data. */ 49 for i in 1 .. l_colCnt loop 50 dbms_sql.column_value( l_theCursor, i, l_columnValue ); 51 l_line := l_line || ',' || l_columnValue; 52 end loop; 53 54 /* Now print out this line. */ 55 dbms_output.put_line( l_line ); 56 end loop;
We can procedurally iterate over the columns, indexing each one as if it were an array. The above construct comes from the following piece of code:
scott@TKYTE816> create or replace 2 procedure dump_query( p_query in varchar2 ) 3 is 4 l_columnValue varchar2(4000); 5 l_status integer; 6 l_colCnt number default 0; 7 l_cnt number default 0; 8 l_line long; 9 10 /* We'll be using this to see how many columns 11 * we have to fetch so we can define them and 12 * then retrieve their values. 13 */ 14 l_descTbl dbms_sql.desc_tab; 15 16 17 /* Step 1 - open cursor. */ 18 l_theCursor integer default dbms_sql.open_cursor; 19 begin 20 21 /* Step 2 - parse the input query so we can describe it. */ 22 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native ); 23 24 /* Step 3 - now, describe the outputs of the query. */ 25 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl ); 26 27 /* Step 4 - we do not use in this example, no BINDING needed. 28 * Step 5 - for each column, we need to define it, tell the database 29 * what we will fetch into. In this case, all data is going 30 * to be fetched into a single varchar2(4000) variable. 31 */ 32 for i in 1 .. l_colCnt 33 loop 34 dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 ); 35 end loop; 36 37 /* Step 6 - execute the statement. */ 38 l_status := dbms_sql.execute(l_theCursor); 39 40 /* Step 7 - fetch all rows. */ 41 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) 42 loop 43 /* Build up a big output line, this is more efficient than calling 44 * DBMS_OUTPUT.PUT_LINE inside the loop. 45 */ 46 l_cnt := l_cnt+1; 47 l_line := l_cnt; 48 /* Step 8 - get and process the column data. */ 49 for i in 1 .. l_colCnt loop 50 dbms_sql.column_value( l_theCursor, i, l_columnValue ); 51 l_line := l_line || ',' || l_columnValue; 52 end loop; 53 54 /* Now print out this line. */ 55 dbms_output.put_line( l_line ); 56 end loop; 57 58 /* Step 9 - close cursor to free up resources.. */ 59 dbms_sql.close_cursor(l_theCursor); 60 exception 61 when others then 62 dbms_sql.close_cursor( l_theCursor ); 63 raise; 64 end dump_query; 65 / Procedure created.
Additionally, DBMS_SQL gives us an API called DBMS_SQL.DESCRIBE_COLUMNS, which will tell us the number of columns in a query, their data types, and their names among other pieces of information. As an example of this, we'll look at a generic routine to dump the results of an arbitrary query to a flat file. It differs from the other example we have of this in Chapter 9 on Data Loading, where we look at a SQL-Unloader tool. This example dumps data to a fixed width file, where each column always appears in the same position in the output file. It does this by inspecting the output of DBMS_SQL.DESCRIBE_COLUMNS, which tells us the maximum width of a column, in addition to the number of columns we have selected. Before we look at the full example, we'll take a look at the DESCRIBE_COLUMNS routine first. After we parse a SELECT query, we can use this routine to interrogate the database as to what we can expect when we fetch from the query. This routine will populate an array of records with information regarding the column name, data type, max length, and so on.
Here is an example that shows how to use DESCRIBE_COLUMNS and dumps out the data it returns for an arbitrary query so we can see what sort of information is available:
scott@TKYTE816> create or replace 2 procedure desc_query( p_query in varchar2 ) 3 is 4 l_columnValue varchar2(4000); 5 l_status integer; 6 l_colCnt number default 0; 7 l_cnt number default 0; 8 l_line long; 9 10 /* We'll be using this to see what our query SELECTs 11 */ 12 l_descTbl dbms_sql.desc_tab; 13 14 15 /* Step 1 - open cursor. */ 16 l_theCursor integer default dbms_sql.open_cursor; 17 begin 18 19 /* Step 2 - parse the input query so we can describe it. */ 20 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native ); 21 22 /* Step 3 - now, describe the outputs of the query. 23 * L_COLCNT will contain the number of columns selected 24 * in the query. It will be equal to L_DESCTBL.COUNT 25 * actually and so it is redundant really. L_DESCTBL 26 * contains the useful data about our SELECTed columns. 27 */ 28 29 dbms_sql.describe_columns( c => l_theCursor, 30 col_cnt => l_colCnt, 31 desc_t => l_descTbl ); 32 33 for i in 1 .. l_colCnt 34 loop 35 dbms_output.put_line 36 ( 'Column Type...........' || l_descTbl(i).col_type ); 37 dbms_output.put_line 38 ( 'Max Length............' || l_descTbl(i).col_max_len ); 39 dbms_output.put_line 40 ( 'Name..................' || l_descTbl(i).col_name ); 41 dbms_output.put_line 42 ( 'Name Length...........' || l_descTbl(i).col_name_len ); 43 dbms_output.put_line 44 ( 'ObjColumn Schema Name.' || l_descTbl(i).col_schema_name ); 45 dbms_output.put_line 46 ( 'Schema Name Length....' || l_descTbl(i).col_schema_name_len ); 47 dbms_output.put_line 48 ( 'Precision.............' || l_descTbl(i).col_precision ); 49 dbms_output.put_line 50 ( 'Scale.................' || l_descTbl(i).col_scale ); 51 dbms_output.put_line 52 ( 'Charsetid.............' || l_descTbl(i).col_Charsetid ); 53 dbms_output.put_line 54 ( 'Charset Form..........' || l_descTbl(i).col_charsetform ); 55 if ( l_desctbl(i).col_null_ok ) then 56 dbms_output.put_line( 'Nullable..............Y' ); 57 else 58 dbms_output.put_line( 'Nullable..............N' ); 59 end if; 60 dbms_output.put_line( '------------------------' ); 61 end loop; 62 63 /* Step 9 - close cursor to free up resources. */ 64 dbms_sql.close_cursor(l_theCursor); 65 exception 66 when others then 67 dbms_sql.close_cursor( l_theCursor ); 68 raise; 69 end desc_query; 70 / Procedure created. scott@TKYTE816> set serveroutput on scott@TKYTE816> exec desc_query( 'select rowid, ename from emp' ); Column Type...........11 Max Length............16 Name..................ROWID Name Length...........5 ObjColumn Schema Name. Schema Name Length....0 Precision.............0 Scale.................0 Charsetid.............0 Charset Form..........0 Nullable..............Y ------------------------ Column Type...........1 Max Length............10 Name..................ENAME Name Length...........5 ObjColumn Schema Name. Schema Name Length....0 Precision.............0 Scale.................0 Charsetid.............31 Charset Form..........1 Nullable..............Y ------------------------ PL/SQL procedure successfully completed.
Unfortunately, the COLUMN TYPE is simply a number, not the name of the data type itself, so unless you know that Column Type 11 is a ROWID and 1 is a VARCHAR2, you won't be able to decode these. The Oracle Call Interface Programmer's Guide has a complete list of all internal data type codes and their corresponding data type names. This is a copy of that list:
| Data type Name | Code |
|---|---|
| VARCHAR2, NVARCHAR2 | 1 |
| NUMBER | 2 |
| LONG | 8 |
| ROWID | 11 |
| DATE | 12 |
| RAW | 23 |
| LONG RAW | 24 |
| CHAR, NCHAR | 96 |
| User-defined type (object type, VARRAY, nested table) | 108 |
| REF | 111 |
| CLOB, NCLOB | 112 |
| BLOB | 113 |
| BFILE | 114 |
| UROWID | 208 |
Now we are ready for the full routine that can take almost any query and dump the results to a flat file (assuming you have setup UTL_FILE, see the Appendix A on Necessary Supplied Packages for more details on this):
scott@TKYTE816> create or replace 2 function dump_fixed_width( p_query in varchar2, 3 p_dir in varchar2 , 4 p_filename in varchar2 ) 5 return number 6 is 7 l_output utl_file.file_type; 8 l_theCursor integer default dbms_sql.open_cursor; 9 l_columnValue varchar2(4000); 10 l_status integer; 11 l_colCnt number default 0; 12 l_cnt number default 0; 13 l_line long; 14 l_descTbl dbms_sql.desc_tab; 15 l_dateformat nls_session_parameters.value%type; 16 begin 17 select value into l_dateformat 18 from nls_session_parameters 19 where parameter = 'NLS_DATE_FORMAT'; 20 21 /* Use a date format that includes the time. */ 22 execute immediate 23 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' '; 24 l_output := utl_file.fopen( p_dir, p_filename, 'w', 32000 ); 25 26 /* Parse the input query so we can describe it. */ 27 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native ); 28 29 /* Now, describe the outputs of the query. */ 30 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl ); 31 32 /* For each column, we need to define it, to tell the database 33 * what we will fetch into. In this case, all data is going 34 * to be fetched into a single varchar2(4000) variable. 35 * 36 * We will also adjust the max width of each column. We do 37 * this so when we OUTPUT the data. Each field starts and 38 * stops in the same position for each record. 39 */ 40 for i in 1 .. l_colCnt loop 41 dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 ); 42 43 if ( l_descTbl(i).col_type = 2 ) /* number type */ 44 then 45 L_descTbl(i).col_max_len := l_descTbl(i).col_precision+2; 46 elsif ( l_descTbl(i).col_type = 12 ) /* date type */ 47 then 48 /* length of my format above */ 49 l_descTbl(i).col_max_len := 20; 50 end if; 51 end loop; 52 53 l_status := dbms_sql.execute(l_theCursor); 54 55 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) 56 loop 57 /* Build up a big output line. This is more efficient than 58 * calling UTL_FILE.PUT inside the loop. 59 */ 60 l_line := null; 61 for i in 1 .. l_colCnt loop 62 dbms_sql.column_value( l_theCursor, i, l_columnValue ); 63 l_line := l_line || 64 rpad( nvl(l_columnValue,' '), 65 l_descTbl(i).col_max_len ); 66 end loop; 67 68 /* Now print out that line and increment a counter. */ 69 utl_file.put_line( l_output, l_line ); 70 l_cnt := l_cnt+1; 71 end loop; 72 73 /* Free up resources. */ 74 dbms_sql.close_cursor(l_theCursor); 75 utl_file.fclose( l_output ); 76 77 /* Reset the date format ... and return. */ 78 execute immediate 79 'alter session set nls_date_format=''' || l_dateformat || ''' '; 80 return l_cnt; 81 exception 82 when others then 83 dbms_sql.close_cursor( l_theCursor ); 84 execute immediate 85 'alter session set nls_date_format=''' || l_dateformat || ''' '; 86 87 end dump_fixed_width; 88 / Function created.
So, this function uses the DBMS_SQL.DESCRIBE_COLUMNS routine to find the number of columns based on their data type. I have modified some of the maximum length settings to adjust for the size of the date format I am using and decimals/signs in numbers. As currently coded, the above routine would not unload LONGs, LONG RAW, CLOBs, and BLOBs. It could be modified easily to handle CLOBs and even LONGs. You would need to bind specifically for those types and use DBMS_CLOB to retrieve the CLOB data and DBMS_SQL.COLUMN_VALUE_LONG for the LONG data. It should be noted that you quite simply would not be able to achieve the above using native dynamic SQL C it just is not possible when the SELECT list is not known in PL/SQL.
This will be a trade-off between DBMS_SQL and native dynamic SQL. The trade-off is one of code and complexity versus performance. To demonstrate this, I will develop a routine that inserts many rows into a table dynamically. We are using dynamic SQL since we don't know the name of the table in to which we are inserting until run-time. We'll set up four routines to compare and contrast:
| Routine | Meaning |
|---|---|
| DBMSSQL_ARRAY | Uses array processing in PL/SQL to bulk insert rows |
| NATIVE_DYNAMIC_ARRAY | Uses simulated array processing with object type tables |
| DBMSSQL_NOARRAY | Uses row at a time processing to insert records |
| NATIVE_DYNAMIC_NOARRAY | Uses row at a time processing to insert records |
The first method will be the most scalable and best performing. In my tests on various platforms, methods one and two were basically tied in a single user test C given a machine with no other users, they were more or less equivalent. On some platforms, native dynamic SQL was marginally faster C on others DBMS_SQL was the quicker of the two. In a multi-user environment, however, due to the fact that native dynamic SQL is parsed every time it is executed, the DBMS_SQL array approach will be more scalable. It removes the need to soft parse the query for each and every execution. Another thing to consider is that in order to simulate array processing in native dynamic SQL, we had to resort to a trick. The code is no easier to write in either case. Normally, native dynamic SQL is much easier to code than DBMS_SQL but not in this case.
The only clear conclusion we will come to from this is that methods three and four are much slower than one and two C many times slower in fact. The following results were obtained on a single user Solaris platform although the results on Windows were similar. You should test on your platform to see which makes the most sense for you.
scott@TKYTE816> create or replace type vcArray as table of varchar2(400) 2 / Type created. scott@TKYTE816> create or replace type dtArray as table of date 2 / Type created. scott@TKYTE816> create or replace type nmArray as table of number 2 / Type created.
These types are needed in order to simulate array processing with native dynamic SQL. They will be our array types C native dynamic SQL cannot be used with PL/SQL table types at all. Now, here is the specification of the package we'll use to test with:
scott@TKYTE816> create or replace package load_data 2 as 3 4 procedure dbmssql_array( p_tname in varchar2, 5 p_arraysize in number default 100, 6 p_rows in number default 500 ); 7 8 procedure dbmssql_noarray( p_tname in varchar2, 9 p_rows in number default 500 ); 10 11 12 procedure native_dynamic_noarray( p_tname in varchar2, 13 p_rows in number default 500 ); 14 15 procedure native_dynamic_array( p_tname in varchar2, 16 p_arraysize in number default 100, 17 p_rows in number default 500 ); 18 end load_data; 19 / Package created.
Each of the above procedures will dynamically insert into some table specified by P_TNAME. The number of rows inserted is controlled by P_ROWS and, when using array processing, the array size used is dictated by the P_ARRAYSIZE parameter. Now for the implementation:
scott@TKYTE816> create or replace package body load_data 2 as 3 4 procedure dbmssql_array( p_tname in varchar2, 5 p_arraysize in number default 100, 6 p_rows in number default 500 ) 7 is 8 l_stmt long; 9 l_theCursor integer; 10 l_status number; 11 l_col1 dbms_sql.number_table; 12 l_col2 dbms_sql.date_table; 13 l_col3 dbms_sql.varchar2_table; 14 l_cnt number default 0; 15 begin 16 l_stmt := 'insert into ' || p_tname || 17 ' q1 ( a, b, c ) values ( :a, :b, :c )'; 18 19 l_theCursor := dbms_sql.open_cursor; 20 dbms_sql.parse(l_theCursor, l_stmt, dbms_sql.native); 21 /* 22 * We will make up data here. When we've made up ARRAYSIZE 23 * rows, we'll bulk insert them. At the end of the loop, 24 * if any rows remain, we'll insert them as well. 25 */ 26 for i in 1 .. p_rows 27 loop 28 l_cnt := l_cnt+1; 29 l_col1( l_cnt ) := i; 30 l_col2( l_cnt ) := sysdate+i; 31 l_col3( l_cnt ) := to_char(i); 32 33 if (l_cnt = p_arraysize) 34 then 35 dbms_sql.bind_array( l_theCursor, ':a', l_col1, 1, l_cnt ); 36 dbms_sql.bind_array( l_theCursor, ':b', l_col2, 1, l_cnt ); 37 dbms_sql.bind_array( l_theCursor, ':c', l_col3, 1, l_cnt ); 38 l_status := dbms_sql.execute( l_theCursor ); 39 l_cnt := 0; 40 end if; 41 end loop; 42 if (l_cnt > 0 ) 43 then 44 dbms_sql.bind_array( l_theCursor, ':a', l_col1, 1, l_cnt ); 45 dbms_sql.bind_array( l_theCursor, ':b', l_col2, 1, l_cnt ); 46 dbms_sql.bind_array( l_theCursor, ':c', l_col3, 1, l_cnt ); 47 l_status := dbms_sql.execute( l_theCursor ); 48 end if; 49 dbms_sql.close_cursor( l_theCursor ); 50 end; 51
So, this is the routine that uses DBMS_SQL to array-insert N rows at a time. We use the overloaded BIND_VARIABLE routine that allows us to send in a PL/SQL table type with the data to be loaded. We send in the array bounds telling Oracle where to start and stop in our PL/SQL table C in this case, we always start at index 1 and end at index L_CNT. Notice that the table name in the INSERT statement has a correlation name Q1 associated with it. I did this so that when we go to analyze the performance using TKPROF, we'll be able to identify which INSERT statements were used by particular routines. Overall the code is fairly straightforward. Next, we implement the DBMS_SQL that does not use array processing:
52 procedure dbmssql_noarray( p_tname in varchar2, 53 p_rows in number default 500 ) 54 is 55 l_stmt long; 56 l_theCursor integer; 57 l_status number; 58 begin 59 l_stmt := 'insert into ' || p_tname || 60 ' q2 ( a, b, c ) values ( :a, :b, :c )'; 61 62 l_theCursor := dbms_sql.open_cursor; 63 dbms_sql.parse(l_theCursor, l_stmt, dbms_sql.native); 64 /* 65 * We will make up data here. When we've made up ARRAYSIZE 66 * rows, we'll bulk insert them. At the end of the loop, 67 * if any rows remain, we'll insert them as well. 68 */ 69 for i in 1 .. p_rows 70 loop 71 dbms_sql.bind_variable( l_theCursor, ':a', i ); 72 dbms_sql.bind_variable( l_theCursor, ':b', sysdate+i ); 73 dbms_sql.bind_variable( l_theCursor, ':c', to_char(i) ); 74 l_status := dbms_sql.execute( l_theCursor ); 75 end loop; 76 dbms_sql.close_cursor( l_theCursor ); 77 end; 78
This is very similar in appearance to the previous routine, with just the arrays missing. If you find yourself coding a routine that looks like the above logic, you should give serious consideration to using array processing. As we'll see in a moment, it can make a big difference in the performance of your application. Now for the native dynamic SQL routine:
79 procedure native_dynamic_noarray( p_tname in varchar2, 80 p_rows in number default 500 ) 81 is 82 begin 83 /* 84 * Here, we simply make up a row and insert it. 85 * A trivial amount of code to write and execute. 86 */ 87 for i in 1 .. p_rows 88 loop 89 execute immediate 90 'insert into ' || p_tname || 91 ' q3 ( a, b, c ) values ( :a, :b, :c )' 92 using i, sysdate+i, to_char(i); 93 end loop; 94 end; 95
This is without array processing. Very simple, very small C easy to code but among the worst performing due to the excessive amount of parsing that must take place. Lastly, an example of simulating array inserts using native dynamic SQL:
96 procedure native_dynamic_array( p_tname in varchar2, 97 p_arraysize in number default 100, 98 p_rows in number default 500 ) 99 is 100 l_stmt long; 101 l_theCursor integer; 102 l_status number; 103 l_col1 nmArray := nmArray(); 104 l_col2 dtArray := dtArray(); 105 l_col3 vcArray := vcArray(); 106 l_cnt number := 0; 107 begin 108 /* 109 * We will make up data here. When we've made up ARRAYSIZE 110 * rows, we'll bulk insert them. At the end of the loop, 111 * if any rows remain, we'll insert them as well. 112 */ 113 l_col1.extend( p_arraysize ); 114 l_col2.extend( p_arraysize ); 115 l_col3.extend( p_arraysize ); 116 for i in 1 .. p_rows 117 loop 118 l_cnt := l_cnt+1; 119 l_col1( l_cnt ) := i; 120 l_col2( l_cnt ) := sysdate+i; 121 l_col3( l_cnt ) := to_char(i); 122 123 if (l_cnt = p_arraysize) 124 then 125 execute immediate 126 'begin 127 forall i in 1 .. :n 128 insert into ' || p_tname || ' 129 q4 ( a, b, c ) values ( :a(i), :b(i), :c(i) ); 130 end;' 131 USING l_cnt, l_col1, l_col2, l_col3; 132 l_cnt := 0; 133 end if; 134 end loop; 135 if (l_cnt > 0 ) 136 then 137 execute immediate 138 'begin 139 forall i in 1 .. :n 140 insert into ' || p_tname || ' 141 q4 ( a, b, c ) values ( :a(i), :b(i), :c(i) ); 142 end;' 143 USING l_cnt, l_col1, l_col2, l_col3; 144 end if; 145 end; 146 147 end load_data; 148 / Package body created.
As you can see, this is a little obscure. Our code is writing code that will be dynamically executed. This dynamic code uses the FORALL syntax to bulk insert arrays. Since the EXECUTE IMMEDIATE statement can only use SQL types, we had to define types for it to use. Then we had to dynamically execute a statement:
begin forall i in 1 .. :n insert into t (a,b,c) values (:a(I), :b(I), :c(I)); end;
binding in the number of rows to insert and the three arrays. As we will see below, the use of array processing speeds up the inserts many times. You have to trade this off with the ease of coding the native dynamic SQL routine without arrays, however C it is hard to beat one line of code! If this was a one-time program where performance was not crucial, I might go down that path. If this was a reusable routine, one that would be around for a while, I would choose DBMS_SQL when the need for speed was present and the number of bind variables unknown and native dynamic SQL when the performance was acceptable and the number of bind variables was well known.
Lastly, we cannot forget about the discussion from Chapter 10, Tuning Strategies and Tools, where we saw that avoiding soft parses is desirable C DBMS_SQL can do this, native dynamic SQL cannot. You need to look at what you are doing and chose the appropriate approach. If you are writing a data loader that will be run once a day and parse the queries a couple of hundred times, native dynamic SQL would work great. On the other hand, if you are writing a routine that uses the same dynamic SQL statement dozens of times by many dozens of users concurrently, you'll want to use DBMS_SQL so you can parse once and run many times.
I ran the above routines using this test block of code (a single user system remember!):
create table t (a int, b date, c varchar2(15)); alter session set sql_trace=true; truncate table t; exec load_data.dbmssql_array('t', 50, 10000); truncate table t; exec load_data.native_dynamic_array('t', 50, 10000); truncate table t; exec load_data.dbmssql_noarray('t', 10000) truncate table t; exec load_data.native_dynamic_noarray('t', 10000)
What we find from the TKPROF report is this:
BEGIN load_data.dbmssql_array( 't', 50, 10000 ); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ----- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 2.58 2.83 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ----- ---------- ---------- ---------- total 2 2.59 2.83 0 0 0 1 BEGIN load_data.native_dynamic_array( 't', 50, 10000 ); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ----- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 2.39 2.63 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ----- ---------- ---------- ---------- total 2 2.39 2.63 0 0 0 1
So overall, the execution profiles were very similar, 2.59 CPU seconds and 2.30 CPU seconds. The devil is in the details here, however. If you look at the code above, I make sure each and every insert was a little different from the other inserts by sticking a Q1, Q2, Q3, and Q4 correlation name in them. In this fashion, we can see how many parses took place. The DBMS_SQL array routine used Q1 and the native dynamic SQL routine used Q4. The results are:
insert into t q1 ( a, b, c ) values ( :a, :b, :c ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ----- ---------- ---------- ---------- Parse 1 0.00 0.01 0 0 0 0
and:
begin forall i in 1 .. :n insert into t q4 ( a, b, c ) values ( :a(i), :b(i), :c(i) ); end; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ----- ---------- ---------- ---------- Parse 200 0.10 0.07 0 0 0 0 INSERT INTO T Q4 ( A,B,C ) VALUES ( :b1,:b2,:b3 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ----- ---------- ---------- ---------- Parse 200 0.07 0.04 0 0 0 0
As you can see, the DBMS_SQL routine was able to get away with a single parse, but the native dynamic SQL had to parse 400 times. On a heavily loaded system, with lots of concurrent users, this may really affect performance and is something to consider. Since it can be avoided and the DBMS_SQL code is not significantly harder to code in this particular case, I would give the nod to DBMS_SQL as the correct implementation for this type of work. It is a close call but for scalability reasons, I would go with it.
The results of the non-array processing routines were terrible, relatively speaking:
BEGIN load_data.dbmssql_noarray( 't', 10000 ); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ----- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 7.66 7.68 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ----- ---------- ---------- ---------- total 2 7.66 7.68 0 0 0 1 BEGIN load_data.native_dynamic_noarray( 't', 10000 ); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ----- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 6.15 6.25 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ----- ---------- ---------- ---------- total 2 6.15 6.25 0 0 0 1
Here, it looks like the native dynamic SQL would be the way to go. However, I would still probably go with DBMS_SQL if I were not to implement array processing. It is purely because of this:
insert into t q2 ( a, b, c ) values ( :a, :b, :c ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ----- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 insert into t q3 ( a, b, c ) values ( :a, :b, :c ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ----- ---------- ---------- --------- Parse 10000 1.87 1.84 0 0 0 0
This shows 10,000 soft parses using native dynamic SQL and one soft parse using DBMS_SQL. In a multi-user environment, the DBMS_SQL implementation will scale better.
We see similar results when processing lots of rows from a dynamically executed query. Normally, you can array-fetch from a REF CURSOR, but only a strongly typed REF CURSOR. That is, a REF CURSOR whose structure is known by the compiler at compile-time. Native dynamic SQL only supports weakly typed REF CURSORS and hence, does not support the BULK COLLECT. If you attempt to BULK COLLECT a dynamically opened REF CURSOR you'll receive a:
ORA-01001: Invalid Cursor Here is a comparison of two routines, both of which fetch all of the rows from ALL_OBJECTS and count them. The routine that utilizes DBMS_SQL with array processing is almost twice as fast:
scott@TKYTE816> create or replace procedure native_dynamic_select 2 as 3 type rc is ref cursor; 4 l_cursor rc; 5 l_oname varchar2(255); 6 l_cnt number := 0; 7 l_start number default dbms_utility.get_time; 8 begin 9 open l_cursor for 'select object_name from all_objects'; 10 11 loop 12 fetch l_cursor into l_oname; 13 exit when l_cursor%notfound; 14 l_cnt := l_cnt+1; 15 end loop; 16 17 close l_cursor; 18 dbms_output.put_line( L_cnt || ' rows processed' ); 19 dbms_output.put_line 20 ( round( (dbms_utility.get_time-l_start)/100, 2 ) || ' seconds' ); 21 exception 22 when others then 23 if ( l_cursor%isopen ) 24 then 25 close l_cursor; 26 end if; 27 raise; 28 end; 29 / Procedure created. scott@TKYTE816> create or replace procedure dbms_sql_select 2 as 3 l_theCursor integer default dbms_sql.open_cursor; 4 l_columnValue dbms_sql.varchar2_table; 5 l_status integer; 6 l_cnt number := 0; 7 l_start number default dbms_utility.get_time; 8 begin 9 10 dbms_sql.parse( l_theCursor, 11 'select object_name from all_objects', 12 dbms_sql.native ); 13 14 dbms_sql.define_array( l_theCursor, 1, l_columnValue, 100, 1 ); 15 l_status := dbms_sql.execute( l_theCursor ); 16 loop 17 l_status := dbms_sql.fetch_rows(l_theCursor); 18 dbms_sql.column_value(l_theCursor,1,l_columnValue); 19 20 l_cnt := l_status+l_cnt; 21 exit when l_status <> 100; 22 end loop; 23 dbms_sql.close_cursor( l_theCursor ); 24 dbms_output.put_line( L_cnt || ' rows processed' ); 25 dbms_output.put_line 26 ( round( (dbms_utility.get_time-l_start)/100, 2 ) || ' seconds' ); 27 exception 28 when others then 29 dbms_sql.close_cursor( l_theCursor ); 30 raise; 31 end; 32 / Procedure created. scott@TKYTE816> set serveroutput on scott@TKYTE816> exec native_dynamic_select 19695 rows processed 1.85 seconds PL/SQL procedure successfully completed. scott@TKYTE816> exec native_dynamic_select 19695 rows processed 1.86 seconds PL/SQL procedure successfully completed. scott@TKYTE816> exec dbms_sql_select 19695 rows processed 1.03 seconds PL/SQL procedure successfully completed. scott@TKYTE816> exec dbms_sql_select 19695 rows processed 1.07 seconds PL/SQL procedure successfully completed.
Again, it is a trade-off of performance versus coding effort. Utilizing array processing in DBMS_SQL takes a considerable amount more coding then native dynamic SQL but the pay off is greatly increased performance.
As with any feature, there are some nuances that need to be noted in the way this feature functions. This section attempts to address them each in turn. There are three major caveats that come to mind with dynamic SQL in stored procedures. They are:
It breaks the dependency chain.
It makes the code more 'fragile'.
It is much harder to tune to have predictable response times.
Normally, when you compile a procedure into the database, everything it references and everything that references it is recorded in the data dictionary. For example, I create a procedure:
ops$tkyte@DEV816> create or replace function count_emp return number 2 as 3 l_cnt number; 4 begin 5 select count(*) into l_cnt from emp; 6 return l_cnt; 7 end; 8 / Function created. ops$tkyte@DEV816> select referenced_name, referenced_type 2 from user_dependencies 3 where name = 'COUNT_EMP' 4 and type = 'FUNCTION' 5 / REFERENCED_NAME REFERENCED_T -------------------------------------------------------------- ------------ STANDARD PACKAGE SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE EMP TABLE 3 rows selected.
Now, let's compare the last iteration of our native dynamic SQL function GET_ROW_CNTS from above to the COUNT_EMP procedure:
ops$tkyte@DEV816> select referenced_name, referenced_type 2 from user_dependencies 3 where name = 'GET_ROW_CNTS' 4 and type = 'FUNCTION' 5 / REFERENCED_NAME REFERENCED_T -------------------------------------------------------------- ------------ STANDARD PACKAGE SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE 2 rows selected.
The function with a static reference to EMP shows this reference in the dependency table. The other function does not, because it is not dependent on the EMP table. In this case, it is perfectly OK because we derive much added value from the use of dynamic SQL C the ability to generically get the row count of any table. In the above, we were using dynamic SQL just for the sake of it, however C this broken dependency would be a bad thing. It is extremely useful to find out what your procedures reference and what references them. Dynamic SQL will obscure that relationship.
When using static SQL only, I can be assured that when I compile the program, the SQL I have embedded in there will in fact be syntactically correct C it has to be, we verified that completely at compile-time. When using dynamic SQL, I'll only be able to tell at run-time if the SQL is OK. Further, since we build the SQL on the fly, we need to test every possible code path to check that all of the SQL we generate is OK. This means that a given set of inputs, which cause the procedure to generate SQL in one way, might not work while a different set of inputs will. This is true of all code but the use of dynamic SQL introduces another way for your code to 'break'.
Dynamic SQL makes it possible to do many things that you could not do otherwise, but static SQL should be used whenever possible. Static SQL will be faster, cleaner, and less fragile.
This is not as obvious, but an application that dynamically builds queries is hard to tune. Normally, we can look at the exhaustive set of queries an application will use, identify the ones that will be potential performance issues and tune them to death. If the set of queries the application will use isn't known until after the application actually executes, we have no way of knowing how it will perform. Suppose you create a stored procedure that dynamically builds a query based on user inputs on a web screen. Unless you test each and every query that may be generated by your routine you'll never know if you have all of the correct indexes in place and so on to have a well tuned system. With just a low number of columns (say five) there are already dozens of combinations of predicates that you could come up with. This does not mean 'never use dynamic SQL', it means be prepared to be on the lookout for these types of issues C queries you never anticipated being generated by the system.
In this chapter we thoroughly explored dynamic SQL in stored procedures. We have seen the differences between native dynamic SQL and DBMS_SQL, showing when to use one over the other. Both implementations have their time and place.
Dynamic SQL allows you to write procedures that are otherwise impossible - generic utilities to dump data, to load data, and so on. Further examples of dynamic SQL-based routines can be found on the Apress web site, such as a utility to load dBASE III files into Oracle via PL/SQL, printing the results of a query down the page in SQL*PLUS (see Invoker and Definer Rights, Chapter 23), pivoting resultsets (see Analytic Functions, Chapter 12 for details on this), and much more.