Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Wednesday, March 6, 2013

anydata and anytype in 9i


In Oracle 9i Release 1 (9.0), Oracle has introduced three types for use in developing generic or self-describing applications. These types are:
  • ANYDATA;
  • ANYTYPE; and
  • ANYDATASET.
These types enable us to encapsulate and work with datatypes of any known type, but within a single type instance (for example, a single parameter or column). The generic types can be used as storage or variables for built-in types (such as VARCHAR2) or user-defined types (such as objects or collections). The types contain numerous methods for "get, set and describe" and even methods to build and reference transient types "on the fly". As a basic implementation, they enable us to build generic applications and utilities without overloading.

what's covered in this article?

This article will introduce the ANYDATA type with passing reference to ANYTYPE where relevant. The ANYDATASET type is more complex and beyond the scope of this article. It can be used with Oracle's Data Cartridge (extensibility framework) to implement pipelined functions to return transient datasets and this will be the subject of a future article.
As stated, ANYDATA is a generic built-in type that allows any known user-defined or built-in type to be assigned to it. ANYDATA can be used as a PL/SQL variable/parameter or as a column in a table or view. All of the examples in this article will be PL/SQL-based. We will not use ANYDATA in tables or views.
There are numerous methods available in the ANYDATA specification to assign/retrieve data to/from an instance of the type. These methods enable us to perform these operations either in full or piecewise. For simplicity, this article will concentrate on full assignments and retrievals of data only. Built-in and user-defined types will be examined separately.

built-in datatypes and anydata

In this section we will look at how to assign, access and describe "regular" data (i.e. based on built-in datatypes such as VARCHAR2) using instances of ANYDATA.

assigning data

We will begin with some simple data assignments to an instance of ANYDATA. Assignments are made using ANYDATA's various ConvertXXXstatic functions, where XXX is the name of a built-in datatype. In the following example, we will assign some standard VARCHAR2, NUMBER and DATE data to a variable of ANYDATA. Each assignment overwrites the previous one but shows simply how the methods are called.
SQL> DECLARE
  2     v_anydata ANYDATA;
  3  BEGIN
  4     v_anydata := ANYDATA.ConvertVarchar2('Some string data');
  5     v_anydata := ANYDATA.ConvertNumber(12345678);
  6     v_anydata := ANYDATA.ConvertDate(SYSDATE);
  7  END;
  8  /


PL/SQL procedure successfully completed.
There are many ConvertXXX methods (see the type specification for details) for a wide range of datatypes.

retrieving data

For every ConvertXXX static function, ANYDATA has a corresponding AccessXXX member function. These functions are used to retrieve data from an instance of ANYDATA. In the following example, we will convert and access a simple string using ANYDATA functions.
SQL> DECLARE
  2     v_anydata ANYDATA;
  3     v_string  VARCHAR2(30);
  4  BEGIN
  5     v_anydata := ANYDATA.ConvertVarchar2('some string data');
  6     v_string  := v_anydata.AccessVarchar2();
  7     DBMS_OUTPUT.PUT_LINE( 'String value is [' || v_string || ']' );
  8  END;
  9  /

String value is [some string data]

PL/SQL procedure successfully completed.

type metadata

If we need to work with multiple base datatypes, we require a bit more flexibility in the code we write. In the examples so far, we have known exactly what types and data we are assigning and accessing. There are methods available that enable us to interrogate an instance of ANYDATA to determine its underlying structure. The following example shows the simplest way of determining a type's name.
SQL> DECLARE
  2     v_anydata  ANYDATA := ANYDATA.ConvertVarchar2('String');
  3     v_typename VARCHAR2(128);
  4  BEGIN
  5     v_typename := v_anydata.GetTypeName();
  6     DBMS_OUTPUT.PUT_LINE('Type of ANYDATA instance is [' || v_typename || ']');
  7  END;
  8  /

Type of ANYDATA instance is [SYS.VARCHAR2]

PL/SQL procedure successfully completed.
We can see that the GetTypeName member function returns the type's name, but there is also a GetType method that provides us with slightly different metadata. The GetType member function returns the ANYDATA instance's typecode, which can be compared with a set of constant typecodes in the new DBMS_TYPES package. This function also returns an instance of ANYTYPE (as an OUT parameter). The GetType member function is used as follows.
SQL> DECLARE
  2     v_anydata  ANYDATA := ANYDATA.ConvertVarchar2('String');
  3     v_anytype  ANYTYPE;
  4     v_typecode PLS_INTEGER;
  5     v_typename VARCHAR2(128);
  6  BEGIN
  7     v_typecode := v_anydata.GetType(v_anytype);
  8     DBMS_OUTPUT.PUT_LINE('Typecode of instance is [' || v_typecode || ']');
  9     DBMS_OUTPUT.PUT_LINE('Typecode of DBMS_TYPES.TYPECODE_VARCHAR2 is [' ||
 10                           DBMS_TYPES.TYPECODE_VARCHAR2 || ']');
 11  END;
 12  /

Typecode of instance is [9]
Typecode of DBMS_TYPES.TYPECODE_VARCHAR2 is [9]

PL/SQL procedure successfully completed.

a note on anytype

While the ANYDATA type is used for generic storage of data, ANYTYPE is used to describe the structure of any user-defined type. It is also used to create and describe unnamed transient types (i.e. we can use it to create a type "on the fly" or dynamically). However, the ANYTYPE instance returned by ANYDATA's GetTypeName method is only populated for user-defined types, so we will describe this later when we use our own types.

a more generic example

So far we have used a variable of ANYDATA in the examples and assigned and accessed data that is known to us (i.e. we have known the type of data we have used). Using the information we have seen so far, we can easily derive the syntax for situations where the underlying type of data is unknown to us.
In the following example, we will forward-declare a "generic" procedure that accepts a single parameter of ANYDATA. Using ANYDATA's GetType method, we will determine the parameter's type and value and output it accordingly. At this stage, we will restrict the example to VARCHAR2, NUMBER and DATE base types only. This can easily be used as a template to create a version that includes all of the built-in datatypes that ANYDATA and DBMS_TYPES support.
SQL> DECLARE
  2
  3     PROCEDURE generic_procedure ( p_anydata IN ANYDATA ) IS
  4
  5        v_typeinfo  ANYTYPE;
  6        v_typecode  PLS_INTEGER;
  7        v_typevalue VARCHAR2(128);
  8        v_typename  VARCHAR2(128);
  9
 10     BEGIN
 11
 12        /* Get typecode... */
 13        v_typecode := p_anydata.GetType(v_typeinfo);
 14
 15        /* Determine typecode and access accordingly... */
 16        CASE v_typecode
 17
 18           WHEN DBMS_TYPES.TYPECODE_VARCHAR2
 19           THEN
 20              v_typevalue := p_anydata.AccessVarchar2();
 21              v_typename  := 'VARCHAR2';
 22
 23           WHEN DBMS_TYPES.TYPECODE_NUMBER
 24           THEN
 25              v_typevalue := TO_CHAR(p_anydata.AccessNumber());
 26              v_typename  := 'NUMBER';
 27
 28           WHEN DBMS_TYPES.TYPECODE_DATE
 29           THEN
 30              v_typevalue := TO_CHAR(p_anydata.AccessDate(),'DD-MON-YYYY');
 31              v_typename  := 'DATE';
 32
 33        END CASE;
 34
 35        /* Output... */
 36        DBMS_OUTPUT.PUT_LINE('--------------------------------------');
 37        DBMS_OUTPUT.PUT_LINE('Type name : ' || v_typename);
 38        DBMS_OUTPUT.PUT_LINE('Value     : ' || v_typevalue);
 39
 40     END generic_procedure;
 41
 42  BEGIN
 43
 44     /* Test some inputs... */
 45     generic_procedure( ANYDATA.ConvertVarchar2('String value') );
 46     generic_procedure( ANYDATA.ConvertNumber(1234567890) );
 47     generic_procedure( ANYDATA.ConvertDate(DATE '1900-01-01') );
 48
 49  END;
 50  /

--------------------------------------
Type name : VARCHAR2
Value     : String value
--------------------------------------
Type name : NUMBER
Value     : 1234567890
--------------------------------------
Type name : DATE
Value     : 01-JAN-1900

PL/SQL procedure successfully completed.
The DBMS_TYPES package and ANYDATA type specifications provide a comprehensive list of the built-in types that can be used in this way. They also cater for user-defined types such as objects and collections and we will look at these below.

user-defined types and anydata

In addition to built-in types, the ANYDATA specification allows us to include our own types such as object types and collections (nested tables and VARRAYs). With user-defined types, the power and flexibility of ANYDATA, ANYTYPE and ANYDATASET becomes apparent, especially for producing truly generic applications (we can even create structured object and collection types "on the fly" using these types). We will not venture too far down this path in this article, however. Instead, we will see some simple examples of how to work with ANYDATA, ANYTYPE and our own types.

setup

To begin, we will create a simple object type and corresponding collection type, as follows.
SQL> CREATE TYPE emp_ot AS OBJECT
  2  ( empno    NUMBER(4)
  3  , ename    VARCHAR2(10)
  4  , job      VARCHAR2(9)
  5  , mgr      NUMBER(4)
  6  , hiredate DATE
  7  , sal      NUMBER(7,2)
  8  , comm     NUMBER(7,2)
  9  , deptno   NUMBER(2)
 10  );
 11  /


Type created.


SQL> CREATE TYPE emp_ntt AS TABLE OF emp_ot;
  2  /


Type created.

assigning user-defined data to anydata

We will begin by assigning a variable of our new EMP_OT type to an instance of ANYDATA. We do this with the single "catch-all"ConvertObject static function, as follows. We will also retrieve the ANYDATA instance's type name, in the same manner as before.
SQL> DECLARE
  2
  3     v_anydata  ANYDATA;
  4     v_typename VARCHAR2(128);
  5     v_emp      emp_ot;
  6
  7  BEGIN
  8
  9     /* Fetch an EMP record into our EMP_OT object... */
 10     SELECT emp_ot(empno, ename, job, mgr, hiredate, sal, comm, deptno)
 11     INTO   v_emp
 12     FROM   emp
 13     WHERE  ROWNUM = 1;
 14
 15     /* Assign it to our ANYDATA instance... */
 16     v_anydata := ANYDATA.ConvertObject(v_emp);
 17
 18     /* Determine the typename... */
 19     v_typename := v_anydata.GetTypeName();
 20     DBMS_OUTPUT.PUT_LINE('Type name is [' || v_typename || ']');
 21
 22  END;
 23  /

Type name is [SCOTT.EMP_OT]

PL/SQL procedure successfully completed.
The same principle applies to collections, using the ConvertCollection static function, as follows.
SQL> DECLARE
  2
  3     v_anydata  ANYDATA;
  4     v_emps     emp_ntt;
  5
  6  BEGIN
  7
  8     /* Fetch all EMP records into our EMP_NTT collection of EMP_OT... */
  9     SELECT emp_ot(empno, ename, job, mgr, hiredate, sal, comm, deptno)
 10     BULK COLLECT INTO v_emps
 11     FROM emp;
 12
 13     /* Assign the collection to our ANYDATA instance... */
 14     v_anydata := ANYDATA.ConvertCollection(v_emps);
 15
 16  END;
 17  /


PL/SQL procedure successfully completed.

accessing user-defined data from anydata

If we know the underlying type of the ANYDATA instance, access is straightforward, as with built-in types. In the following example, we will access data of known object and collection types using GetXXX member functions. These are equivalent to the AccessXXX methods we saw earlier, but for user-defined types. Note also that the GetXXX member functions for objects and collections return a success integer, with the actual data being retrieved as an OUT parameter.
SQL> DECLARE
  2
  3     v_anydata ANYDATA;
  4     v_emps    emp_ntt;
  5     v_emp     emp_ot;
  6     v_dummy   PLS_INTEGER;
  7
  8  BEGIN
  9
 10     /* Fetch data into collection... */
 11     SELECT emp_ot(empno, ename, job, mgr, hiredate, sal, comm, deptno)
 12     BULK COLLECT INTO v_emps
 13     FROM emp;
 14
 15     /* Assign and access an object... */
 16     v_anydata := ANYDATA.ConvertObject(v_emps(v_emps.FIRST));
 17     v_dummy := v_anydata.GetObject(v_emp);
 18     DBMS_OUTPUT.PUT_LINE('Accessed emp ' || v_emp.ename);
 19
 20     /* Assign and access a collection (access will overwrite original v_emps)... */
 21     v_anydata := ANYDATA.ConvertCollection(v_emps);
 22     v_dummy := v_anydata.GetCollection(v_emps);
 23     DBMS_OUTPUT.PUT_LINE('Accessed ' || v_emps.COUNT || ' emps');
 24
 25  END;
 26  /

Accessed emp SMITH
Accessed 14 emps

PL/SQL procedure successfully completed.
If we do not know the underlying datatype, access becomes a little more difficult because we need to dynamically access the data. In the following example, we will have a "generic" procedure that will determine the object type of an instance of ANYDATA and invoke a member PRINT function accordingly. For this, we require a modification to our EMP_OT (to add a PRINT method), as follows.
SQL> ALTER TYPE emp_ot ADD
  2     MEMBER FUNCTION print RETURN VARCHAR2
  3     CASCADE;


Type altered.


SQL> CREATE TYPE BODY emp_ot AS
  2     MEMBER FUNCTION print RETURN VARCHAR2 IS
  3     BEGIN
  4        RETURN TO_CHAR(SELF.empno);
  5     END;
  6  END;
  7  /


Type body created.
To demonstrate how to distinguish between user-defined types, we will also create a second object type, DEPT_OT, as follows.
SQL> CREATE TYPE dept_ot AS OBJECT
  2  ( deptno NUMBER(2)
  3  , dname  VARCHAR2(14)
  4  , loc    VARCHAR2(13)
  5  , MEMBER FUNCTION print RETURN VARCHAR2
  6  );
  7  /


Type created.


SQL> CREATE TYPE BODY dept_ot AS
  2     MEMBER FUNCTION print RETURN VARCHAR2 IS
  3     BEGIN
  4        RETURN TO_CHAR(SELF.deptno);
  5     END;
  6  END;
  7  /


Type body created.
Note how we have added a PRINT member function to both types. This will be used to access the key attributes of the type in our generic procedure as follows.
SQL> DECLARE
  2
  3     v_anydata ANYDATA;
  4
  5     PROCEDURE generic_procedure( p_anydata IN ANYDATA ) IS
  6
  7        v_typename VARCHAR2(128);
  8        v_value    VARCHAR2(32767);
  9
 10     BEGIN
 11
 12        v_typename := p_anydata.GetTypeName;
 13
 14        EXECUTE IMMEDIATE 'DECLARE
 15                              v_object ' || v_typename || ';
 16                              v_anydata     ANYDATA := :bv1_in;
 17                              v_dummy       PLS_INTEGER;
 18                           BEGIN
 19                              v_dummy := v_anydata.GetObject(v_object);
 20                              :bv2_out := v_object.print();
 21                           END;'
 22                    USING  IN  p_anydata,
 23                           OUT v_value;
 24
 25        DBMS_OUTPUT.PUT_LINE('-------------------------------------------------');
 26        DBMS_OUTPUT.PUT_LINE('Type name  : ' || v_typename);
 27        DBMS_OUTPUT.PUT_LINE('Type value : ' || v_value);
 28
 29     END generic_procedure;
 30
 31  BEGIN
 32
 33     /*
 34     || Test an EMP record...
 35     */
 36     SELECT ANYDATA.ConvertObject(
 37               emp_ot(empno, ename, job, mgr, hiredate, sal, comm, deptno)
 38               )
 39     INTO   v_anydata
 40     FROM   emp
 41     WHERE  ROWNUM = 1;
 42
 43     generic_procedure(v_anydata);
 44
 45     /*
 46     || Test a DEPT record...
 47     */
 48     SELECT ANYDATA.ConvertObject(
 49               dept_ot(deptno, dname, loc)
 50               )
 51     INTO   v_anydata
 52     FROM   dept
 53     WHERE  ROWNUM = 1;
 54
 55     generic_procedure(v_anydata);
 56
 57  END;
 58  /

-------------------------------------------------
Type name  : SCOTT.EMP_OT
Type value : 7369
-------------------------------------------------
Type name  : SCOTT.DEPT_OT
Type value : 10

PL/SQL procedure successfully completed.
Some notes on the above are as follows.
  • Lines 5-29: we have forward-declared a "generic" procedure to receive an ANYDATA instance and process it dynamically;
  • Lines 14-21: we execute a simple dynamic PL/SQL block to access the unknown object data. The type of the return object variable has to be concatenated in the declaration;
  • Lines 16 & 20: we use bind variables to pass in an instance of ANYDATA and receive a string of key values, generated via the unknown type's PRINT member function;
  • Lines 43 & 45: we call the generic procedure with ANYDATA instances that have different underlying datatypes (i.e. EMP_OT or DEPT_OT).
This example is a simple representation of how we can use ANYDATA with unknown object types. The inclusion of a PRINT method in each type made this easy. To take this example any further (for example, to access all attribute values and populate a table at runtime), we would need to access the data dictionary to build a list of type attributes and target columns, accordingly. This is beyond the scope of this article.
For generic procedures that might need to process objects or collections, we need to return to typecodes to determine the relevant ANYDATA access method and code path to use. The DBMS_TYPES.TYPECODE_OBJECT and DBMS_TYPES.TYPECODE_NAMEDCOLLECTION constants can be used for comparison with the ANYDATA's underlying typecodes, in a similar manner to our earlier examples with built-in types.

a return to anytype

Finally, we will return to ANYTYPE to demonstrate some of what it provides. Remember that the GetType member function of ANYDATA returns an instance of ANYTYPE, but that this is only populated for user-defined types. We will now see the information available in such an ANYTYPE instance. In the following example, we will use ANYDATA and ANYTYPE "metadata methods" to describe an underlying variable of DEPT_OT.
SQL> DECLARE
  2
  3     v_anydata     ANYDATA;
  4     v_anytype     ANYTYPE;
  5     v_typecode    PLS_INTEGER;
  6
  7     TYPE rt_typeinfo IS RECORD
  8     ( prec        PLS_INTEGER
  9     , scale       PLS_INTEGER
 10     , len         PLS_INTEGER
 11     , csid        PLS_INTEGER
 12     , csfrm       PLS_INTEGER
 13     , schema_name VARCHAR2(30)
 14     , type_name   VARCHAR2(30)
 15     , version     VARCHAR2(30)
 16     , count       PLS_INTEGER
 17     );
 18     r_typeinfo rt_typeinfo;
 19
 20     TYPE rt_attrinfo IS RECORD
 21     ( prec           PLS_INTEGER
 22     , scale          PLS_INTEGER
 23     , len            PLS_INTEGER
 24     , csid           PLS_INTEGER
 25     , csfrm          PLS_INTEGER
 26     , attr_elt_type  ANYTYPE
 27     , aname          VARCHAR2(30)
 28     );
 29     r_attrinfo rt_attrinfo;
 30
 31  BEGIN
 32
 33     /* Demonstration ANYDATA instance using DEPT_OT... */
 34     v_anydata := ANYDATA.ConvertObject(dept_ot(10,'SALES','DALLAS'));
 35
 36     /* Get the ANYTYPE information for the ANYDATA instance... */
 37     v_typecode := v_anydata.GetType(v_anytype);
 38     DBMS_OUTPUT.PUT_LINE('-------------------------------------');
 39     DBMS_OUTPUT.PUT_LINE('Typecode   : ' || v_typecode);
 40
 41     /* ANYTYPE type-level metadata... */
 42     v_typecode := v_anytype.GetInfo(r_typeinfo.prec,
 43                                     r_typeinfo.scale,
 44                                     r_typeinfo.len,
 45                                     r_typeinfo.csid,
 46                                     r_typeinfo.csfrm,
 47                                     r_typeinfo.schema_name,
 48                                     r_typeinfo.type_name,
 49                                     r_typeinfo.version,
 50                                     r_typeinfo.count);
 51
 52     DBMS_OUTPUT.PUT_LINE('-------------------------------------');
 53     DBMS_OUTPUT.PUT_LINE('Typename   : ' || r_typeinfo.type_name);
 54     DBMS_OUTPUT.PUT_LINE('Attributes : ' || r_typeinfo.count);
 55
 56     /* ANYTYPE attribute-level metadata... */
 57     FOR i IN 1 .. r_typeinfo.count LOOP
 58        v_typecode := v_anytype.GetAttrElemInfo(i,
 59                                                r_attrinfo.prec,
 60                                                r_attrinfo.scale,
 61                                                r_attrinfo.len,
 62                                                r_attrinfo.csid,
 63                                                r_attrinfo.csfrm,
 64                                                r_attrinfo.attr_elt_type,
 65                                                r_attrinfo.aname);
 66        DBMS_OUTPUT.PUT_LINE('-------------------------------------');
 67        DBMS_OUTPUT.PUT_LINE('Attribute  : ' || r_attrinfo.aname);
 68        DBMS_OUTPUT.PUT_LINE('Typecode   : ' || v_typecode);
 69        DBMS_OUTPUT.PUT_LINE('Length     : ' || NVL(r_attrinfo.len, r_attrinfo.prec));
 70     END LOOP;
 71
 72  END;
 73  /

-------------------------------------
Typecode   : 108
-------------------------------------
Typename   : DEPT_OT
Attributes : 3
-------------------------------------
Attribute  : DEPTNO
Typecode   : 2
Length     : 2
-------------------------------------
Attribute  : DNAME
Typecode   : 9
Length     : 14
-------------------------------------
Attribute  : LOC
Typecode   : 9
Length     : 13

PL/SQL procedure successfully completed.
We can see that ANYTYPE methods provide us with dynamic access to our type information. Some notes on the example are as follows.
  • Lines 3 & 4: we will require variables of ANYDATA and ANYTYPE to access the relevant member functions that provide the type metadata;
  • Lines 7-18: the GetInfo member function of ANYTYPE has numerous OUT parameters to describe the type. We define a record type and variable to store this information;
  • Lines 20-29: the GetAttrElemInfo member function of ANYTYPE also has numerous OUT parameters to describe each attribute in the type. We define a record type and variable to store this data;
  • Line 34: we assign an instance of ANYDATA for the example;
  • Lines 42-50: using ANYTYPE's GetInfo member function, we retrieve some metadata on our ANYDATA instance. This metadata includes the number of attributes in our underlying user-defined type, as we can see in the screen output;
  • Lines 57-70: using the number of attributes in our type, we define a loop to retrieve attribute-level metadata for our ANYDATA instance. The GetAttrElemInfo member function of ANYTYPE provides information such as attribute name, its datatype and length/scale/precision, as output by the example.
As noted earlier, the ANYTYPE definition includes methods to build transient, unnamed types "on the fly". This helps with techniques such as Method 4 Dynamic SQL but is beyond the scope of this article.

a sample application for anydata

We have seen the mechanics of ANYDATA and ANYTYPE so far in this article. We will now develop a simple "generic" application to demonstrate a practical use for ANYDATA. In the following example, we will create a small procedure that can be used to execute a SQL statement or PL/SQL block with bind variables. We will use ANYDATA for parameter passing and dynamic binding. Because we will be using DBMS_SQL to bind the parameters, we are restricted on the types we can use, so for simplicity, we will limit the examples to VARCHAR2, NUMBER and DATE types (note that DBMS_SQL also supports CHARs, LOBs, timestamps/timezones, ROWID/UROWID and intervals).
We will begin by creating the types needed to pass an unspecified number of parameters to our generic procedure. This requires an object type to define a single bind input and a collection of this type for multiple binds, as follows.
SQL> CREATE TYPE binds_ot AS OBJECT
  2  ( name  VARCHAR2(30)
  3  , value ANYDATA
  4  );
  5  /


Type created.


SQL> CREATE TYPE binds_ntt
  2     AS TABLE OF binds_ot;
  3  /


Type created.
Now we can create our generic procedure, as follows.
SQL> CREATE PROCEDURE execute_with_binds( p_sql IN VARCHAR2,
  2                                       p_bvs IN binds_ntt ) IS
  3
  4     c BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
  5     v ANYTYPE;
  6     r BINARY_INTEGER;
  7     i PLS_INTEGER;
  8
  9  BEGIN
 10
 11     /* Parse... */
 12     DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
 13
 14     /* Bind... */
 15     i := p_bvs.FIRST;
 16     WHILE i IS NOT NULL LOOP
 17
 18        CASE p_bvs(i).value.getType(v)
 19
 20           WHEN DBMS_TYPES.TYPECODE_NUMBER
 21           THEN
 22              DBMS_SQL.BIND_VARIABLE( c,
 23                                      p_bvs(i).name,
 24                                      p_bvs(i).value.accessNumber() );
 25
 26           WHEN DBMS_TYPES.TYPECODE_VARCHAR2
 27           THEN
 28              DBMS_SQL.BIND_VARIABLE( c,
 29                                      p_bvs(i).name,
 30                                      p_bvs(i).value.accessVarchar2() );
 31
 32           WHEN DBMS_TYPES.TYPECODE_DATE
 33           THEN
 34              DBMS_SQL.BIND_VARIABLE( c,
 35                                      p_bvs(i).name,
 36                                      p_bvs(i).value.accessDate() );
 37
 38        END CASE;
 39
 40        i := p_bvs.NEXT(i);
 41
 42     END LOOP;
 43
 44     /* Execute... */
 45     r := DBMS_SQL.EXECUTE(c);
 46
 47     DBMS_SQL.CLOSE_CURSOR(c);
 48
 49  EXCEPTION
 50     WHEN CASE_NOT_FOUND THEN
 51        DBMS_SQL.CLOSE_CURSOR(c);
 52        RAISE_APPLICATION_ERROR(-20000,'Oops, tried to bind invalid type');
 53
 54  END execute_with_binds;
 55  /


Procedure created.
This procedure is a simple extension of what we have seen in this article. Once we have parsed the incoming SQL string using DBMS_SQL (a familiar technique to most readers), we then interrogate each instance of ANYDATA in turn to determine how to bind it to the SQL. We then use DBMS_SQL.BIND_VARIABLE overloads, which accept a name and value for each bind. Once all bind variables are applied, we can execute the SQL or PL/SQL statement. Note that this example only deals with IN parameters and execute-only operations (i.e. no fetching).
We will test our procedure in two ways. First, we will execute an insert statement with five values (i.e. five binds). Second, we will execute a procedure that will accept three bind variables but simply print their values to the screen. We will begin by creating a table for the first example, as follows.
SQL> CREATE TABLE t
  2  ( c1 INTEGER
  3  , c2 VARCHAR2(1)
  4  , c3 NUMBER
  5  , c4 DATE
  6  , c5 VARCHAR2(20)
  7  );


Table created.
We can now execute an anonymous block to setup our insert statement and bind variables, before calling our EXECUTE_WITH_BINDS procedure.
SQL> DECLARE
  2
  3     v_sql   VARCHAR2(1024);
  4     v_binds binds_ntt := binds_ntt();
  5
  6  BEGIN
  7
  8     /* SQL string... */
  9     v_sql := 'INSERT INTO t VALUES (:c1, :c2, :c3, :c4, :c5)';
 10
 11     /* Bind variables... */
 12     v_binds.EXTEND(5);
 13     v_binds(1) := binds_ot('c1', ANYDATA.convertNumber(1));
 14     v_binds(2) := binds_ot('c2', ANYDATA.convertVarchar2('A'));
 15     v_binds(3) := binds_ot('c3', ANYDATA.convertNumber(100.50));
 16     v_binds(4) := binds_ot('c4', ANYDATA.convertDate(DATE '2000-01-01'));
 17     v_binds(5) := binds_ot('c5', ANYDATA.convertVarchar2(RPAD('B',20,'B')));
 18
 19     /* Execute... */
 20     execute_with_binds( v_sql, v_binds );
 21
 22  END;
 23  /


PL/SQL procedure successfully completed.
To ensure that our SQL statement was successful, we can query our sample table, as follows.
SQL> SELECT * FROM t;


        C1 C2         C3 C4        C5
---------- -- ---------- --------- --------------------
         1 A       100.5 01-JAN-00 BBBBBBBBBBBBBBBBBBBB

1 row selected.
For our second example, we will create a dummy procedure with three parameters (one of each type). This will simply use DBMS_OUTPUT to print their values to the screen.
SQL> CREATE PROCEDURE dummy_procedure( p1 IN VARCHAR2,
  2                                    p2 IN NUMBER,
  3                                    p3 IN DATE ) AS
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE('P1 : ' || p1);
  6     DBMS_OUTPUT.PUT_LINE('P2 : ' || p2);
  7     DBMS_OUTPUT.PUT_LINE('P3 : ' || p3);
  8  END dummy_procedure;
  9  /


Procedure created.
We will now test that the EXECUTE_WITH_BINDS procedure will execute our DUMMY_PROCEDURE, as follows.
SQL> DECLARE
  2
  3     v_plsql VARCHAR2(1024);
  4     v_binds binds_ntt := binds_ntt();
  5
  6  BEGIN
  7
  8     /* PL/SQL string... */
  9     v_plsql := 'BEGIN
 10                    dummy_procedure(:first_parameter,
 11                                    :second_parameter,
 12                                    :third_parameter);
 13                 END;';
 14
 15     /* Binds... */
 16     v_binds.EXTEND(3);
 17     v_binds(1) := binds_ot('first_parameter',ANYDATA.ConvertVarchar2('a string'));
 18     v_binds(2) := binds_ot('second_parameter',ANYDATA.ConvertNumber(100000));
 19     v_binds(3) := binds_ot('third_parameter',ANYDATA.ConvertDate(SYSDATE));
 20
 21     /* Execute... */
 22     execute_with_binds(v_plsql, v_binds);
 23
 24  END;
 25  /

P1 : a string
P2 : 100000
P3 : 11-OCT-02

PL/SQL procedure successfully completed.

further reading

For detailed coverage of ANYDATA and ANYTYPE, read Steven Feuerstein's Oracle PL/SQL Programming. For an overview of the types' methods, see the online Supplied PL/SQL Packages and Types Reference entries for ANYDATA and ANYTYPE.
Credit goes to the below website(s):

 http://www.oracle-developer.net/display.php?id=218

Posted by at
Labels: ,

1 comment:

Subscribe to: Post Comments (Atom)