Vyoms OneStopTesting.com - Testing EBooks, Tutorials, Articles, Jobs, Training Institutes etc.
OneStopGate.com - Gate EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopMBA.com - MBA EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopIAS.com - IAS EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopSAP.com - SAP EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopGRE.com - of GRE EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
Bookmark and Share Rss Feeds

dynamic sql enhancements in 11g | Articles | Recent Articles | News Article | Interesting Articles | Technology Articles | Articles On Education | Articles On Corporate | Company Articles | College Articles | Articles on Recession
Sponsored Ads
Hot Jobs
Fresher Jobs
Experienced Jobs
Government Jobs
Walkin Jobs
Placement Section
Company Profiles
Interview Questions
Placement Papers
Resources @ VYOMS
Companies In India
Consultants In India
Colleges In India
Exams In India
Latest Results
Notifications In India
Call Centers In India
Training Institutes In India
Job Communities In India
Courses In India
Jobs by Keyskills
Jobs by Functional Areas
Learn @ VYOMS
GATE Preparation
GRE Preparation
GMAT Preparation
IAS Preparation
SAP Preparation
Testing Preparation
MBA Preparation
News @ VYOMS
Freshers News
Job Articles
Latest News
India News Network
Interview Ebook
Get 30,000+ Interview Questions & Answers in an eBook.
Interview Success Kit - Get Success in Job Interviews
  • 30,000+ Interview Questions
  • Most Questions Answered
  • 5 FREE Bonuses
  • Free Upgrades

VYOMS TOP EMPLOYERS

Wipro Technologies
Tata Consultancy Services
Accenture
IBM
Satyam
Genpact
Cognizant Technologies

Home » Articles » dynamic sql enhancements in 11g

dynamic sql enhancements in 11g








Article Posted On Date : Wednesday, March 21, 2012


dynamic sql enhancements in 11g
Advertisements

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:

        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

    [...some text omitted...] Oracle Database 11g removes these and other restrictions to make the support of dynamic SQL from PL/SQL functionally complete.

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.






Sponsored Ads



Interview Questions
HR Interview Questions
Testing Interview Questions
SAP Interview Questions
Business Intelligence Interview Questions
Call Center Interview Questions

Databases

Clipper Interview Questions
DBA Interview Questions
Firebird Interview Questions
Hierarchical Interview Questions
Informix Interview Questions
Microsoft Access Interview Questions
MS SqlServer Interview Questions
MYSQL Interview Questions
Network Interview Questions
Object Relational Interview Questions
PL/SQL Interview Questions
PostgreSQL Interview Questions
Progress Interview Questions
Relational Interview Questions
SQL Interview Questions
SQL Server Interview Questions
Stored Procedures Interview Questions
Sybase Interview Questions
Teradata Interview Questions

Microsof Technologies

.Net Database Interview Questions
.Net Deployement Interview Questions
ADO.NET Interview Questions
ADO.NET 2.0 Interview Questions
Architecture Interview Questions
ASP Interview Questions
ASP.NET Interview Questions
ASP.NET 2.0 Interview Questions
C# Interview Questions
Csharp Interview Questions
DataGrid Interview Questions
DotNet Interview Questions
Microsoft Basics Interview Questions
Microsoft.NET Interview Questions
Microsoft.NET 2.0 Interview Questions
Share Point Interview Questions
Silverlight Interview Questions
VB.NET Interview Questions
VC++ Interview Questions
Visual Basic Interview Questions

Java / J2EE

Applet Interview Questions
Core Java Interview Questions
Eclipse Interview Questions
EJB Interview Questions
Hibernate Interview Questions
J2ME Interview Questions
J2SE Interview Questions
Java Interview Questions
Java Beans Interview Questions
Java Patterns Interview Questions
Java Security Interview Questions
Java Swing Interview Questions
JBOSS Interview Questions
JDBC Interview Questions
JMS Interview Questions
JSF Interview Questions
JSP Interview Questions
RMI Interview Questions
Servlet Interview Questions
Socket Programming Interview Questions
Springs Interview Questions
Struts Interview Questions
Web Sphere Interview Questions

Programming Languages

C Interview Questions
C++ Interview Questions
CGI Interview Questions
Delphi Interview Questions
Fortran Interview Questions
ILU Interview Questions
LISP Interview Questions
Pascal Interview Questions
Perl Interview Questions
PHP Interview Questions
Ruby Interview Questions
Signature Interview Questions
UML Interview Questions
VBA Interview Questions
Windows Interview Questions
Mainframe Interview Questions


Copyright © 2001-2017 Vyoms.com. All Rights Reserved. Home | About Us | Advertise With Vyoms.com | Jobs | Contact Us | Feedback | Link to Us | Privacy Policy | Terms & Conditions
Placement Papers | Get Your Free Website | IAS Preparation | C++ Interview Questions | C Interview Questions | Report a Bug | Romantic Shayari | CAT 2017

Fresher Jobs | Experienced Jobs | Government Jobs | Walkin Jobs | Company Profiles | Interview Questions | Placement Papers | Companies In India | Consultants In India | Colleges In India | Exams In India | Latest Results | Notifications In India | Call Centers In India | Training Institutes In India | Job Communities In India | Courses In India | Jobs by Keyskills | Jobs by Functional Areas

Testing Articles | Testing Books | Testing Certifications | Testing FAQs | Testing Downloads | Testing Interview Questions | Testing Jobs | Testing Training Institutes

Gate Articles | Gate Books | Gate Colleges | Gate Downloads | Gate Faqs | Gate Jobs | Gate News | Gate Sample Papers | Gate Training Institutes

MBA Articles | MBA Books | MBA Case Studies | MBA Business Schools | MBA Current Affairs | MBA Downloads | MBA Events | MBA Notifications | MBA FAQs | MBA Jobs
MBA Job Consultants | MBA News | MBA Results | MBA Courses | MBA Sample Papers | MBA Interview Questions | MBA Training Institutes

GRE Articles | GRE Books | GRE Colleges | GRE Downloads | GRE Events | GRE FAQs | GRE News | GRE Training Institutes | GRE Sample Papers

IAS Articles | IAS Books | IAS Current Affairs | IAS Downloads | IAS Events | IAS FAQs | IAS News | IAS Notifications | IAS UPSC Jobs | IAS Previous Question Papers
IAS Results | IAS Sample Papers | IAS Interview Questions | IAS Training Institutes | IAS Toppers Interview

SAP Articles | SAP Books | SAP Certifications | SAP Companies | SAP Study Materials | SAP Events | SAP FAQs | SAP Jobs | SAP Job Consultants
SAP Links | SAP News | SAP Sample Papers | SAP Interview Questions | SAP Training Institutes |


Copyright ©2001-2017 Vyoms.com, All Rights Reserved.
Disclaimer: VYOMS.com has taken all reasonable steps to ensure that information on this site is authentic. Applicants are advised to research bonafides of advertisers independently. VYOMS.com shall not have any responsibility in this regard.