dynamic sql enhancements in 11g
Oracle has supported dynamic SQL for as long as many developers have been working with the database. Prior to 8i, the primary means of executing dynamic SQL or PL/SQL was via the DBMS_SQL package (which provided a low-level interface to dynamic cursors through its APIs). The introduction of Native Dynamic SQL (NDS) in 8i made it much easier (in most circumstances) to execute generated strings of code and despite some low-level performance issues in versions prior to 10g, NDS is by far the most popular dynamic method today.
Between them, DBMS_SQL and Native Dynamic SQL cater for most development requirements, but neither satisfy all of them. For example:
- NDS does not support very large SQL strings (we use an overloaded DBMS_SQL.PARSE that takes a collection of SQL fragments);
- we cannot use NDS if we have an unknown number of bind inputs: these must be known at compile-time (we use DBMS_SQL which supports this "Method 4" dynamic SQL scenario);
- NDS cannot be used to describe a cursor to understand its composition (we use the DBMS_SQL.DESCRIBE_COLUMNS{2|3} APIs); and
- DBMS_SQL does not support user-defined types as bind variable inputs (we must use NDS but ensure we know the nature of the binds at compile-time).
With the release of 11g, Oracle has attempted to complete its dynamic SQL implementation by addressing these issues. This article will describe how.
It is assumed that readers are comfortable with dynamic SQL concepts. Most are reasonably simple, but Method 4 scenarios can be quite complex. For an overview of what is meant by "Method 4 Dynamic SQL", read this short introduction.
summary of dynamic sql new features
The online documentation describes the new dynamic SQL features as providing "functional completeness" for PL/SQL. The following is taken directly from the New Features Guide.
In Oracle Database 11g, native dynamic SQL now supports statements bigger than 32K characters by allowing a CLOB argument. Also included with this feature:[...some text omitted...] Oracle Database 11g removes these and other restrictions to make the support of dynamic SQL from PL/SQL functionally complete.
- DBMS_SQL.PARSE() gains a CLOB overload
- A REF CURSOR can be converted to a DBMS_SQL cursor and vice versa to support interoperability
- DBMS_SQL supports the full range of data types (including collections and object types)
- DBMS_SQL allows bulk binds using user-define (sic) collection types
We will cover each of these points and more in this article.
a short note on the examples
Dynamic SQL is often built from metadata, dictionary information, supplied components and much string concatenation. The resulting SQL statements are usually obscure to read in their PL/SQL "containers" and often there is no SQL statement for us to read until it is executed. With this in mind, and to make the concepts as clear as possible in this article, we will be using SQL statements that do not need to be dynamic. For this reason, readers should assume that the techniques described in this article will only be used when dynamic SQL is absolutely necessary.
dynamic sql stored in clobs
We will begin with a simple new feature. Both Native Dynamic SQL and DBMS_SQL now support SQL strings stored in CLOBs. Without this feature, NDS is able to parse SQL strings of up to 64K (which can be achieved by concatenating two large VARCHAR2s together) and DBMS_SQL has an overload of PARSE that accepts a collection of SQL string fragments. Neither of these is ideal and the CLOB implementation solves any issues we might have had with the previous alternatives.
In the following example, we will build a simple dynamic PL/SQL block of approximately 33,000 bytes. We will store this in a CLOB and execute it with DBMS_SQL, using the new PARSE overload. The dynamic block itself will output a dummy message to show that it has been executed.
SQL> DECLARE
2
3 v_sql CLOB;
4 v_cursor BINARY_INTEGER;
5 v_dummy INTEGER;
6
7 BEGIN
8
9 /* Start the dynamic PL/SQL string... */
10 v_sql := 'DECLARE
11 v_variable VARCHAR2(10);
12 BEGIN
13 v_variable := ''Some Value'';';
14
15 /* Append comments until string is longer than VARCHAR2 maximum... */
16 WHILE LENGTH(v_sql) <= 33000 LOOP
17 v_sql := v_sql ||
18 ' /* comment || RPAD(''x'',4000,''x'') */ ' ||
19 CHR(10);
20 END LOOP;
21
22 /* Add some output to the dynamic block... */
23 v_sql := v_sql ||
24 ' DBMS_OUTPUT.PUT_LINE(
25 ''Value of V_VARIABLE is ['' || v_variable || '']'');
26 END;';
27
28 /* How long is our dynamic PL/SQL block? */
29 DBMS_OUTPUT.PUT_LINE(
30 'Length of CLOB is [' || LENGTH(v_sql) || ']');
31
32 /* Parse and execute with DBMS_SQL... */
33 v_cursor := DBMS_SQL.OPEN_CURSOR;
34 DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
35 v_dummy := DBMS_SQL.EXECUTE(v_cursor);
36 DBMS_SQL.CLOSE_CURSOR(v_cursor);
37
38 END;
39 /
Length of CLOB is [33130] Value of V_VARIABLE is [Some Value] PL/SQL procedure successfully completed.
We can see that the only change needed to work with a large SQL or PL/SQL block is on line 3, where we declare a CLOB variable. We have been able to manipulate CLOB variables in a similar manner to those of VARCHAR2 since Oracle 9i, so for those developers who need to generate large dynamic SQL strings, this new feature is very useful. It is likely to be even more useful in Native Dynamic SQL where the VARCHAR2 restriction sits at approximately 64K (when two large VARCHAR2 variables are concatenated). In the following example, we will execute the same dynamic PL/SQL block using NDS.
SQL> DECLARE
2
3 v_sql CLOB;
4
5 BEGIN
6
7 /* Start the dynamic PL/SQL string... */
8 v_sql := 'DECLARE
9 v_variable VARCHAR2(10);
10 BEGIN
11 v_variable := ''Some Value'';';
12
13 /* Append comments until string is longer than VARCHAR2 maximum... */
14 WHILE LENGTH(v_sql) <= 33000 LOOP
15 v_sql := v_sql ||
16 ' /* comment || RPAD(''x'',4000,''x'') */ ' ||
17 CHR(10);
18 END LOOP;
19
20 /* Add some output to the dynamic block... */
21 v_sql := v_sql ||
22 ' DBMS_OUTPUT.PUT_LINE(
23 ''Value of V_VARIABLE is ['' || v_variable || '']'');
24 END;';
25
26 /* How long is our dynamic PL/SQL block? */
27 DBMS_OUTPUT.PUT_LINE(
28 'Length of CLOB is [' || LENGTH(v_sql) || ']');
29
30 /* Parse and execute dynamic PL/SQL CLOB with NDS... */
31 EXECUTE IMMEDIATE v_sql;
32
33 END;
34 /
Length of CLOB is [33130] Value of V_VARIABLE is [Some Value] PL/SQL procedure successfully completed.
As expected, NDS also handles the dynamic CLOB and generates the same output as the DBMS_SQL example.
dbms_sql support for user-defined types
DBMS_SQL supports a wide range of built-in and packaged types defined by Oracle, but in versions prior to 11g there is no support for user-defined types. In other words, if we need to combine dynamic SQL with bind variables of our own types in previous Oracle versions, we must use Native Dynamic SQL. In most cases this makes it easier for us, but in scenarios where we don't know the number or types of bind variables at compile time, this causes a real problem.
Note that when we talk of DBMS_SQL "supporting" types, we typically mean that the APIs involved with binding and receiving data have a suitable overload for the types we wish to use. In 11g, Oracle has overloaded some of the DBMS_SQL APIs further to allow us to bind and fetch values of user-defined types. We will see a couple of simple examples below, but first we will create some user-defined types. We will start by creating an object type, as follows.
SQL> CREATE TYPE object_type AS OBJECT 2 ( x INT 3 , y DATE 4 , z VARCHAR2(1) 5 , MEMBER FUNCTION print RETURN VARCHAR2 6 ); 7 /
Type created.
SQL> CREATE TYPE BODY object_type AS 2 MEMBER FUNCTION print RETURN VARCHAR2 IS 3 BEGIN 4 RETURN TO_CHAR(SELF.x) || ',' || 5 TO_CHAR(SELF.y,'YYYYMMDD') || ',' || 6 z; 7 END; 8 END; 9 /
Type body created.
Note that this type has a single member method to output its current attribute values as a string. This is purely for convenience and will be used in the dynamic examples. To complete our setup, we will also create a collection type, based on this object, as follows.
SQL> CREATE TYPE collection_type AS 2 TABLE OF object_type; 3 /
Type created.
For our first example, we will build a simple dynamic PL/SQL block that will accept an instance of our user-defined object type as a bind variable and invoke its PRINT member function to provide some output. The example is as follows.
SQL> DECLARE 2 3 v_sql CLOB; 4 v_cursor BINARY_INTEGER; 5 v_dummy INTEGER; 6 v_bind object_type := object_type(1,SYSDATE,'X'); 7 8 BEGIN 9 10 v_sql := 'DECLARE 11 v_variable object_type := :b1; 12 BEGIN 13 DBMS_OUTPUT.PUT_LINE( 14 ''Current instance of object_type is ['' || 15 v_variable.print() || '']''); 16 END;'; 17 18 v_cursor := DBMS_SQL.OPEN_CURSOR; 19 DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE); 20 21 DBMS_SQL.BIND_VARIABLE(v_cursor, 'B1', v_bind); 22 23 v_dummy := DBMS_SQL.EXECUTE(v_cursor); 24 DBMS_SQL.CLOSE_CURSOR(v_cursor); 25 26 END; 27 /
Current instance of object_type is [1,20080215,X] PL/SQL procedure successfully completed.
The lines of interest are highlighted above. We have built a simple anonymous PL/SQL block that receives a bind variable of our object type and invokes the bind variable's PRINT method. As stated earlier, prior to 11g we would have needed to bind this variable using Native Dynamic SQL.
We are not limited to user-defined object types. We can also bind user-defined collections with DBMS_SQL in 11g, as follows.
SQL> DECLARE 2 3 v_sql CLOB; 4 v_cursor BINARY_INTEGER; 5 v_dummy INTEGER; 6 v_bind collection_type := collection_type( 7 object_type(1,SYSDATE,'A'), 8 object_type(2,SYSDATE-1,'B'), 9 object_type(3,SYSDATE-2,'C') 10 ); 11 BEGIN 12 13 v_sql := 'DECLARE 14 v_variable collection_type := :b1; 15 v_index PLS_INTEGER; 16 BEGIN 17 v_index := v_variable.FIRST; 18 WHILE v_index IS NOT NULL LOOP 19 DBMS_OUTPUT.PUT_LINE( 20 ''Element=['' || v_index || 21 '']; Value=['' || v_variable(v_index).print() || 22 '']''); 23 v_index := v_variable.NEXT(v_index); 24 END LOOP; 25 END;'; 26 27 v_cursor := DBMS_SQL.OPEN_CURSOR; 28 DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE); 29 30 DBMS_SQL.BIND_VARIABLE(v_cursor, 'B1', v_bind); 31 32 v_dummy := DBMS_SQL.EXECUTE(v_cursor); 33 DBMS_SQL.CLOSE_CURSOR(v_cursor); 34 35 END; 36 /
Element=[1]; Value=[1,20080219,A] Element=[2]; Value=[2,20080218,B] Element=[3]; Value=[3,20080217,C] PL/SQL procedure successfully completed.
As we can see from this example, the principle for binding objects or collections is the same. Once the bind variable has been provided via DBMS_SQL (or indeed NDS), it is the responsibility of the dynamic SQL or PL/SQL to understand how to make use of it.
For a listing of the types we can use with DBMS_SQL APIs such as BIND_VARIABLE, we can query ALL_ARGUMENTS as follows.
SQL> SELECT data_type
2 , COUNT(*)
3 FROM all_arguments
4 WHERE package_name = 'DBMS_SQL'
5 AND object_name IN ('BIND_VARIABLE','COLUMN_VALUE',
6 'DEFINE_COLUMN','VARIABLE_VALUE')
7 GROUP BY
8 data_type
9 ORDER BY
10 data_type;
DATA_TYPE COUNT(*) ------------------------------ ---------- BFILE 6 BINARY_DOUBLE 6 BINARY_FLOAT 6 BINARY_INTEGER 10 BLOB 6 CHAR 8 CLOB 6 DATE 7 INTERVAL DAY TO SECOND 6 INTERVAL YEAR TO MONTH 6 NUMBER 186 OBJECT 4 PL/SQL TABLE 32 REF 4 TABLE 4 TIME 6 TIME WITH TIME ZONE 6 TIMESTAMP 6 TIMESTAMP WITH LOCAL TIME ZONE 6 TIMESTAMP WITH TIME ZONE 6 UNDEFINED 4 UROWID 6 VARCHAR2 67 VARRAY 4 24 rows selected.
Note that the BIND_ARRAY procedure is missing from the above query filter. This is because the BIND_ARRAY procedures have not been overloaded to accept user-defined types. Recall from the documentation quoted above that:
"DBMS_SQL allows bulk binds using user-define (sic) collection types"
This is a slightly misleading statement. It is true to an extent, because we saw an example of binding a collection type above. What isn't clear, however, is that this statement does not apply to the binding of arrays that DBMS_SQL has always supported with its own packaged types. We can demonstrate this quite easily. In the following example, we will attempt to bind our own collection types using the DBMS_SQL.BIND_ARRAY interface. First we will create a couple of generic collection types, as follows.
SQL> CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000); 2 /
Type created.
SQL> CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER; 2 /
Type created.
Now we will attempt to bulk update the EMP table for a supplied list of jobs. We will attempt to bind in a collection of JOBs and bind out a collection of the affected EMPNOs, as follows.
SQL> DECLARE
2
3 v_sql VARCHAR2(128);
4 v_empnos number_ntt;
5 v_jobs varchar2_ntt := varchar2_ntt('MANAGER','SALESMAN');
6 v_cursor BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
7 v_execute BINARY_INTEGER;
8
9 BEGIN
10
11 /* Prepare an update statement... */
12 v_sql := 'UPDATE emp
13 SET ename = LOWER(ename)
14 WHERE job = :job
15 RETURNING empno INTO :empno';
16
17 /* Parse, bind and execute... */
18 DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
19 DBMS_SQL.BIND_ARRAY(v_cursor, 'job', v_jobs);
20 DBMS_SQL.BIND_ARRAY(v_cursor, 'empno', v_empnos);
21 v_execute := DBMS_SQL.EXECUTE(v_cursor);
22
23 /* Access the out bind collection... */
24 DBMS_SQL.VARIABLE_VALUE(v_cursor, 'empno', v_empnos);
25 DBMS_SQL.CLOSE_CURSOR(v_cursor);
26
27 /* What did we return? */
28 FOR i IN 1 .. v_empnos.COUNT LOOP
29 DBMS_OUTPUT.PUT_LINE(v_empnos(i));
30 END LOOP;
31
32 END;
33 /
DBMS_SQL.BIND_ARRAY(v_cursor, 'job', v_jobs); * ERROR at line 19: ORA-06550: line 19, column 4: PLS-00306: wrong number or types of arguments in call to 'BIND_ARRAY' ORA-06550: line 19, column 4: PL/SQL: Statement ignored ORA-06550: line 20, column 4: PLS-00306: wrong number or types of arguments in call to 'BIND_ARRAY' ORA-06550: line 20, column 4: PL/SQL: Statement ignored
We can see clearly that the BIND_ARRAY interface does not support user-defined types (it only supports packaged array types defined in the DBMS_SQL specification). This means that if we wish to combine bulk updates, dynamic SQL and user-defined types, we must use the BIND_VARIABLE mechanism with dynamic PL/SQL constructs, such as in the following example.
SQL> DECLARE
2
3 v_plsql VARCHAR2(4000);
4 v_empnos number_ntt;
5 v_jobs varchar2_ntt := varchar2_ntt('MANAGER','SALESMAN');
6 v_cursor BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
7 v_execute BINARY_INTEGER;
8
9 BEGIN
10
11 /* Prepare an update statement... */
12 v_plsql := 'BEGIN
13 FORALL i IN INDICES OF :jobs
14 UPDATE emp
15 SET ename = LOWER(ename)
16 WHERE job = :jobs(i)
17 RETURNING empno
18 BULK COLLECT INTO :empnos;
19 END;';
20
21 /* Parse, bind and execute... */
22 DBMS_SQL.PARSE(v_cursor, v_plsql, DBMS_SQL.NATIVE);
23 DBMS_SQL.BIND_VARIABLE(v_cursor, 'jobs', v_jobs);
24 DBMS_SQL.BIND_VARIABLE(v_cursor, 'empnos', v_empnos);
25 v_execute := DBMS_SQL.EXECUTE(v_cursor);
26
27 /* Access the out bind collection... */
28 DBMS_SQL.VARIABLE_VALUE(v_cursor, 'empnos', v_empnos);
29 DBMS_SQL.CLOSE_CURSOR(v_cursor);
30
31 /* What did we return? */
32 FOR i IN 1 .. v_empnos.COUNT LOOP
33 DBMS_OUTPUT.PUT_LINE(v_empnos(i));
34 END LOOP;
35
36 END;
37 /
7566 7698 7782 7499 7521 7654 7844 PL/SQL procedure successfully completed.
dbms_sql support for ref cursors
With 11g, DBMS_SQL and Native Dynamic SQL become interchangeable due to two new APIs to convert between ref cursors and DBMS_SQL cursors. There are two main benefits to this new functionality:
- we can easily describe the structure of ref cursors for the first time, making it much easier to work with Method 4 scenarios; and
- we can combine the flexibility of DBMS_SQL binding with the ease of Native Dynamic SQL data fetching if we know the structure of the data being fetched.
We will see examples of how we can convert between the two cursor types below, starting with the conversion of DBMS_SQL cursors to ref cursors.
dbms_sql.to_refcursor
As its name suggests, this new API converts a DBMS_SQL cursor to a ref cursor (cursor variable). There are certain situations where we might know the structure of a record being fetched, but we don't know the number or types of bind variables being supplied (this is a typical scenario for application search screens, for example). In these cases, we need DBMS_SQL to process the bind variables. Because we know the structure of the resultset records, DBMS_SQL is also reasonably simple to use but if we switch to using a ref cursor, we can write "regular" PL/SQL to process the data. This is significantly easier to code and support.
In the following example, we will use DBMS_SQL to prepare and bind a dynamic SQL statement that gives a fixed return structure. The DBMS_SQL pre-processing is over-simplified to avoid crowding the example, so we have to take a leap-of-faith that this method is actually necessary (i.e. imagine that the statement and number of bind variables is unknown at compile time). Once the DBMS_SQL pre-processing is complete, we will convert to a ref cursor and revert to standard PL/SQL constructs to fetch the data.
SQL> DECLARE
2
3 TYPE emp_aat IS TABLE OF emp%ROWTYPE
4 INDEX BY PLS_INTEGER;
5
6 v_emps emp_aat;
7 v_sql CLOB;
8 v_cursor BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
9 v_rc SYS_REFCURSOR;
10 v_execute BINARY_INTEGER;
11
12 BEGIN
13
14 /* Setup EMP query... */
15 v_sql := 'SELECT * FROM emp WHERE job = :job';
16
17 /* Parse dynamic SQL... */
18 DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
19
20 /* Process bind variable... */
21 DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', '&job');
22
23 /* Execute cursor and convert to ref cursor... */
24 v_execute := DBMS_SQL.EXECUTE(v_cursor);
25 v_rc := DBMS_SQL.TO_REFCURSOR(v_cursor);
26
27 /* Now we can use much simpler code for accessing the data... */
28 LOOP
29 FETCH v_rc BULK COLLECT INTO v_emps LIMIT 100;
30 FOR i IN 1 .. v_emps.COUNT LOOP
31 DBMS_OUTPUT.PUT_LINE('Emp = ' || v_emps(i).ename);
32 END LOOP;
33 EXIT WHEN v_rc%NOTFOUND;
34 END LOOP;
35 CLOSE v_rc;
36
37 END;
38 /
Enter value for job: SALESMAN Emp = ALLEN Emp = WARD Emp = MARTIN Emp = TURNER PL/SQL procedure successfully completed.
Some points to note are:
- Lines 15-21: this is our imaginary complex statement with an unknown set of binds. In actual fact, we have restricted the example to just one fixed bind variable to avoid over-complicating the code at this stage. The bind variable is provided via a sqlplus substitution variable as a proxy for an interactive application;
- Line 24: the dynamic SQL statement must be executed before we can start to fetch from the cursor;
- Line 25: we convert the DBMS_SQL cursor to a ref cursor variable of type SYS_REFCURSOR. Any weak ref cursor type can be used;
- Lines 28-35: we can use simple PL/SQL constructs to fetch and process the data and therefore avoid the more complex and code-intensive DBMS_SQL calls. In this example we have coded a standard bulk fetch loop that we might typically use for efficiency with larger resultsets.
By combining DBMS_SQL with ref cursors, we have achieved greater flexibility. Note, however, that this will not be suitable for situations where the output of the SQL is unknown (i.e. complete Method 4). In these situations we must use DBMS_SQL throughout.
An important point to note is that in 11g Release 1 the ref cursors are only usable in PL/SQL (this restriction is lifted in 11g Release 2 as we will see below). In other words, in 11g Release 1, we cannot convert DBMS_SQL cursors to ref cursors and pass them to client applications in anything other than PL/SQL. We will demonstrate this below. First we will create a simple function to parse, bind and execute a simple EMP query, convert the cursor to a ref cursor and return it to the calling program.
SQL> CREATE FUNCTION emps_as_refcursor( 2 p_job IN emp.job%TYPE 3 ) RETURN SYS_REFCURSOR AS 4 5 v_sql CLOB; 6 v_cursor BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR; 7 v_execute BINARY_INTEGER; 8 9 BEGIN 10 11 v_sql := 'SELECT * FROM emp WHERE job = :job'; 12 DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE); 13 DBMS_SQL.BIND_VARIABLE(v_cursor, 'JOB', p_job); 14 v_execute := DBMS_SQL.EXECUTE(v_cursor); 15 RETURN DBMS_SQL.TO_REFCURSOR(v_cursor); 16 17 END; 18 /
Function created.
This function uses the same DBMS_SQL pre-processing logic as we saw in our previous example and returns a ref cursor to the caller. We will attempt to call this from a client in both 11g Release 1 and 2 below. For this, sqlplus will be our proxy for the client application. We will use a sqlplus ref cursor variable and bind it into our PL/SQL block, starting with 11g Release 1, as follows.
SQL> set autoprint on
SQL> VAR rc REFCURSOR;
SQL> BEGIN
2 :rc := emps_as_refcursor('MANAGER');
3 END;
4 /
PL/SQL procedure successfully completed. ERROR: ORA-01001: invalid cursor no rows selected
We can see that in 11g Release 1, our PL/SQL block succeeds but the client application cannot process the ref cursor. Oracle raises an ORA-01001 exception. This is a major restriction and one which reduces the potential for this new feature, particularly if we consider the best practice of passing ref cursors to client applications.
We will now repeat the example using an 11g Release 2 database, as follows.
SQL> set autoprint on
SQL> VAR rc REFCURSOR;
SQL> BEGIN
2 :rc := emps_as_refcursor('MANAGER');
3 END;
4 /
PL/SQL procedure successfully completed.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- -------- ---------- ------- ------- --------
7566 JONES MANAGER 7839 02/04/1981 2975 20
7698 BLAKE MANAGER 7839 01/05/1981 2850 30
7782 CLARK MANAGER 7839 09/06/1981 2450 10
3 rows selected.
We can see that in 11g Release 2, the client application is able to fetch from the ref cursor. With this support for client ref cursors, the new combination of using DBMS_SQL for unknown binds and ref cursors for data fetching is very powerful. For example, client search screens are often implemented poorly because of a lack of understanding of bind variables and the fact that the client requires a ref cursor to be returned. This new feature caters for both requirements with ease.
Finally, to demonstrate that the 11g Release 1 issue is not simply an issue with using a sqlplus variable, we will use the ref cursor bind variable in a purely PL/SQL context, as follows.
SQL> set autoprint off
SQL> DECLARE
2 TYPE emp_aat IS TABLE OF emp%ROWTYPE
3 INDEX BY PLS_INTEGER;
4 v_emps emp_aat;
5 BEGIN
6
7 /* Get ref cursor... */
8 :rc := emps_as_refcursor('MANAGER');
9
10 /* Process as usual... */
11 LOOP
12 FETCH :rc BULK COLLECT INTO v_emps LIMIT 100;
13 FOR i IN 1 .. v_emps.COUNT LOOP
14 DBMS_OUTPUT.PUT_LINE('Emp = ' || v_emps(i).ename);
15 END LOOP;
16 EXIT WHEN :rc%NOTFOUND;
17 END LOOP;
18 CLOSE :rc;
19
20 END;
21 /
Emp = JONES Emp = BLAKE Emp = CLARK PL/SQL procedure successfully completed.
Providing that we use PL/SQL to fetch from the ref cursor, we can use any cursor variable, as the above demonstrates.
dbms_sql.to_cursor_number
The converse to using DBMS_SQL for binds and ref cursors for fetching is to begin with a ref cursor and convert to DBMS_SQL for data retrieval. The TO_CURSOR_NUMBER API enables us to do this, but interestingly (and this is not made clear in the documentation), we can convert both static and dynamic ref cursors to DBMS_SQL cursors. This means that we can programmatically describe any weak or strong ref cursor (although we already know the structure of a strong ref cursor).
Since Native Dynamic SQL was introduced in 8i, the OPEN FOR syntax has become far more commonly used in PL/SQL programs. What is often overlooked by Oracle developers is the fact that this syntax existed before NDS and was originally used for static SQL cursors. To re-emphasise the origins of the OPEN FOR construct, our first example will describe a static, rather than dynamic, ref cursor by converting it to a DBMS_SQL cursor.
SQL> DECLARE
2
3 v_static_rc SYS_REFCURSOR;
4 v_desc DBMS_SQL.DESC_TAB;
5 v_cols BINARY_INTEGER;
6 v_cursor BINARY_INTEGER;
7
8 BEGIN
9
10 /* Open cursor for a static SQL statement... */
11 OPEN v_static_rc FOR
12 SELECT ename, hiredate, sal
13 FROM emp;
14
15 /* Convert to DBMS_SQL cursor... */
16 v_cursor := DBMS_SQL.TO_CURSOR_NUMBER(v_static_rc);
17
18 /* Describe the cursor... */
19 DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_cols, v_desc);
20
21 /* Simple example so no data fetching. Close the cursor... */
22 DBMS_SQL.CLOSE_CURSOR(v_cursor);
23
24 /* Output some information about the cursor... */
25 FOR i IN 1 .. v_cols LOOP
26 DBMS_OUTPUT.PUT('Column ' || i || ': ' || RPAD(v_desc(i).col_name,10));
27 DBMS_OUTPUT.PUT('; Type: ' || CASE v_desc(i).col_type
28 WHEN 1
29 THEN 'VARCHAR2'
30 WHEN 2
31 THEN 'NUMBER'
32 WHEN 12
33 THEN 'DATE'
34 ELSE 'MANY OTHERS NOT IN THIS SIMPLE EXAMPLE...'
35 END);
36 DBMS_OUTPUT.NEW_LINE;
37 END LOOP;
38
39 END;
40 /
Column 1: ENAME ; Type: VARCHAR2 Column 2: HIREDATE ; Type: DATE Column 3: SAL ; Type: NUMBER PL/SQL procedure successfully completed.
Some points to note about this example are:
- Lines 11-13: we open our ref cursor with a static SQL statement using the original OPEN FOR syntax. There are no bind variables in this example;
- Line 16: we convert the static ref cursor to a DBMS_SQL cursor handle;
- Line 19: we describe the DBMS_SQL cursor that was formerly a ref cursor;
- Lines 25-37: we can make decisions based on the nature of the cursor structure. In this example we have simply output the types of the columns in our SQL statement. We have only catered for the three main datatypes but we could extend our logic to cover all DBMS_SQL-supported types.
We will now look at a slightly more complex example. In the following example, we will create a procedure to accept a ref cursor parameter (remember the restriction that in 11g Release 1 this must be a PL/SQL-managed ref cursor). We will describe this cursor by converting it to a DBMS_SQL cursor and then fetch its data. The cursor structure is completely unknown (although for simplicity we will restrict it to strings, dates and numbers). We create the procedure as follows.
SQL> CREATE PROCEDURE rc_to_dbms_sql(
2 p_refcursor IN OUT SYS_REFCURSOR
3 ) AS
4
5 v_desc DBMS_SQL.DESC_TAB;
6 v_cols BINARY_INTEGER;
7 v_cursor BINARY_INTEGER;
8
9 v_varchar2 VARCHAR2(4000);
10 v_number NUMBER;
11 v_date DATE;
12
13 v_data VARCHAR2(32767);
14
15 BEGIN
16
17 /* Convert refcursor "parameter" to DBMS_SQL cursor... */
18 v_cursor := DBMS_SQL.TO_CURSOR_NUMBER(p_refcursor);
19
20 /* Describe the cursor... */
21 DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_cols, v_desc);
22
23 /* Define columns to be fetched. We're only using V2, NUM, DATE for example... */
24 FOR i IN 1 .. v_cols LOOP
25
26 IF v_desc(i).col_type = 2 THEN
27 DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_number);
28 ELSIF v_desc(i).col_type = 12 THEN
29 DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_date);
30 ELSE
31 DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_varchar2, 4000);
32 END IF;
33
34 END LOOP;
35
36 /* Now output the data, purely for demonstration. Start with header... */
37 DBMS_OUTPUT.NEW_LINE;
38 FOR i IN 1 .. v_cols LOOP
39 v_data := v_data ||
40 CASE v_desc(i).col_type
41 WHEN 2
42 THEN LPAD(v_desc(i).col_name, v_desc(i).col_max_len+1)
43 WHEN 12
44 THEN RPAD(v_desc(i).col_name, 22)
45 ELSE RPAD(v_desc(i).col_name, v_desc(i).col_max_len+1)
46 END || ' ';
47 END LOOP;
48 DBMS_OUTPUT.PUT_LINE(v_data);
49
50 v_data := NULL;
51 FOR i IN 1 .. v_cols LOOP
52 v_data := v_data ||
53 CASE v_desc(i).col_type
54 WHEN 2
55 THEN LPAD('-', v_desc(i).col_max_len+1, '-')
56 WHEN 12
57 THEN RPAD('-', 22, '-')
58 ELSE RPAD('-', v_desc(i).col_max_len+1, '-')
59 END || ' ';
60 END LOOP;
61 DBMS_OUTPUT.PUT_LINE(v_data);
62
63 /* Fetch all data... */
64 WHILE DBMS_SQL.FETCH_ROWS(v_cursor) > 0 LOOP
65
66 v_data := NULL;
67
68 FOR i IN 1 .. v_cols LOOP
69
70 IF v_desc(i).col_type = 2 THEN
71 DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_number);
72 v_data := v_data ||
73 LPAD(v_number, v_desc(i).col_max_len+1) || ' ';
74 ELSIF v_desc(i).col_type = 12 THEN
75 DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_date);
76 v_data := v_data || RPAD(v_date, 22) || ' ';
77 ELSE
78 DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_varchar2);
79 v_data := v_data ||
80 RPAD(v_varchar2, v_desc(i).col_max_len+1) || ' ';
81 END IF;
82
83 END LOOP;
84
85 DBMS_OUTPUT.PUT_LINE(v_data);
86
87 END LOOP;
88 DBMS_SQL.CLOSE_CURSOR(v_cursor);
89
90 END rc_to_dbms_sql;
91 /
Procedure created.
This is quite a long and complicated procedure to use as an example, so some key points to note are as follows:
- Line 2: the ref cursor parameter must be IN OUT else the attempt to convert it to a DBMS_SQL cursor will fail with "PLS-00363: expression 'p_refcursor' cannot be used as an assignment target";
- Lines 9-11: for simplicity, we are restricting the example to support VARCHAR2, DATE and NUMBER types only;
- Line 18: we convert the ref cursor parameter directly to a DBMS_SQL cursor;
- Line 21: we describe the DBMS_SQL cursor and are now able to procedurally prepare and fetch our results;
- Lines 24-34: using the cursor metadata, we call the DBMS_SQL.DEFINE_COLUMN API to define the data we are going to fetch (this is standard DBMS_SQL processing);
- Lines 36-61: for this example, we are simply going to output our data in sqlplus format, so these loops through the cursor metadata are included to output some underlined column names. Each line to be output is prepared using a v_data variable for convenience;
- Lines 68-83: we fetch from our dynamic cursor. For each row, we must loop through the cursor description to understand the nature of each column/expression being fetched. We can then supply the correct variable to fetch the data into. Again, we have used the three main built-in datatypes, but DBMS_SQL supports a much wider range than this;
- Line 85: once each row is completely fetched, we output it and move onto the next record.
This procedure is a simplified example of Method 4 dynamic SQL. We have no prior knowledge of any cursors that will use this procedure (with the noted exception of our self-imposed limited datatype support). Outputting the data in the style of sqlplus is not necessarily what we use Method 4 dynamic SQL for, but it is a useful technique for demonstrating the concepts.
We will now test our procedure twice. We will execute an anonymous block to open a ref cursor and call our procedure. The first example will open a static ref cursor as follows.
SQL> DECLARE 2 v_rc SYS_REFCURSOR; 3 BEGIN 4 OPEN v_rc FOR &any_query_we_like; 5 rc_to_dbms_sql(v_rc); 6 END; 7 /
Enter value for any_query_we_like: SELECT empno, ename, hiredate, sal FROM emp
EMPNO ENAME HIREDATE SAL
----------------------- ----------- ---------------------- -----------------------
7369 SMITH 17/12/1980 880
7499 ALLEN 20/02/1981 1760
7521 WARD 22/02/1981 1375
7566 JONES 02/04/1981 3272.5
7654 MARTIN 28/09/1981 1375
7698 BLAKE 01/05/1981 3135
7782 CLARK 09/06/1981 2695
7788 SCOTT 19/04/1987 3300
7839 KING 17/11/1981 5500
7844 TURNER 08/09/1981 1650
7876 ADAMS 23/05/1987 1210
7900 JAMES 03/12/1981 1045
7902 FORD 03/12/1981 3300
7934 MILLER 23/01/1982 1430
PL/SQL procedure successfully completed.
We have a simple Method 4 implementation that begins with a ref cursor. For completeness, we will test with a dynamic ref cursor (i.e. SQL stored as a string) as follows.
SQL> /
Enter value for any_query_we_like: 'SELECT * FROM dept'
DEPTNO DNAME LOC
----------------------- --------------- --------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
PL/SQL procedure successfully completed.
Our dynamic ref cursor works in the same way as the static version, as described earlier.
conclusion: functionally complete?
The New Features Guide states:
Oracle Database 11g removes [...] restrictions to make the support of dynamic SQL from PL/SQL functionally complete.
We have clearly demonstrated this above, particularly with the extended support for datatypes and Method 4 scenarios. The lack of inter-operability between converted cursors and non-PL/SQL ref cursors in 11g Release 1 appears to be a key restriction for that release. However, this particular issue is fixed in 11g Release 2, making the latest Oracle release rich in dynamic SQL functionality.
further reading
For more information on 11g's dynamic SQL capabilities, read Chapter 7 of the PL/SQL Language Reference. A summary of DBMS_SQL subprograms can be found in the PL/SQL Packages and Types Reference. Finally, read this article for a more detailed description of Method 4 dynamic SQL and its uses.
source code
The source code for the examples in this article can be downloaded from here.
Adrian Billington, February 2008 (updated June 2010)
Back to Top