Jason Bennett's Developer Corner

 






Click to see the XML version of this web page.




View David Jason Bennett's profile on LinkedIn

 

 

A Little About Jason Bennett ...

I've had an interest in publishing technical articles and HELPFUL code for a few years.  I am (by trade and hobby) a developer who specializes in Oracle technologies and web based architectures.  I have been an employee of both TUSC and Oracle Corporation.  My intent here is to share my ideas and coding experiences with the developer community as a whole.  As with all developers some of my ideas are great and some of them are ....  well you know.  Anyway, I hope you find something here that will aid in your endeavor, or spark a new idea. 

I am more than happy to assist with technical issues and will even write a little code if need be. If you find something on the site that is really useful and you'd like to make a contribution (absolutely up to you and absolutely not required), just click the "Make a Donation" button on the left!

Good luck and good coding !




  Sunday, November 09, 2008


PL/SQL: Create Dynamic PL/SQL Functions with the AnonymousFunction Data Type

   Spending some time with loosely typed languages like JavaScript and LISP has made me realize how powerful anonymous functions can be.  In my last big project using ADF Faces, I found it necessary to rewrite some the ADF Faces JavaScript functions on the fly (at runtime) in order to force a specific and non-native behavior. Since JavaScript is loosely typed (meaning, in a nutshell, I don't need to explicitly specify a type for my functions or variables) and supports anonymous functions (functions defined and executed at runtime), altering the functions provided by the Oracle developers was a snap.  I was able to wrap my code around their code and create a new function on the fly.  Cool stuff.  LISP provides a similar ability with the lambda function.  I thought it would be cool to have the same ability when writing PL/SQL code.  However, PL/SQL is strongly typed, and doesn't provide an easy mechanism for defining generic anonymous functions out of the box.  However, since Oracle 9i, there is a way to implement generics through the ANYDATA, ANYDATASET, ANYTYPE objects.  Using these new tools, I was able to create a user defined type called AnonymousFunction that allows a developer to create dynamic functions (or anonymous functions) at runtime.  The user defined type makes use of both SYS.ANYDATA and the DBMS_TYPES package to allow the developer to create and execute functions of almost every type with relative ease.  Here is a sample of how a user would define a numeric function on the fly:

  DECLARE
            v_numberFunction AnonymousFunction := AnonymousFunction();
            v_function_text VARCHAR2(200) := 'FUNCTION testNumber RETURN NUMBER IS BEGIN RETURN 12345; END;';
            v_result NUMBER(10) := NULL;
  BEGIN

           v_numberFunction.defineFunctionNumber(p_function_name=>'testNumber', 
                                                 p_function_text=>v_function_text);

           v_result := v_numberFunction.executeFunctionNumber;

           DBMS_OUTPUT.PUT_LINE('Number result='||v_result);

  END;

  The (trivial) code example above would generate the result 12345 if executed in SQL*Plus or TOAD with SERVER OUT set to ON.  Rather than bore you with more details (you can get them from the code or ask me), here is the code for the user defined type:

(Download this code and a more comprehensive example here: AnonymousFunction.zip )

/*====================================================

  Global Temporary table used to store and retrieve
  the results of the anonymous function execution.

=====================================================*/

CREATE GLOBAL TEMPORARY TABLE anon_func_results(
id         NUMBER(12) PRIMARY KEY,
result     SYS.ANYDATA,
clob_result CLOB,
blob_result BLOB);

/*=====================================================================
   Sequence used to create a unique key for the result storage rows.
======================================================================*/
CREATE SEQUENCE anon_func_seq;


/*====================================================================

  The user defined type Anonymous Function.  This type allows a user
  to execute dynamic functions of multiple types: Char, Varchar2, Number,
  Float, Double, Raw, BFile, RAW, CLOB, BLOB, Collection, Object.

  There are several member procedures and functions defined as conveniences
  for executing functions of type: Varchar2, Number, Char, Float, Double,
  Raw, BFILE, Clob, and Blob.  Collections and Objects requires a few more
  steps since the are not stand Oracle data types.

  The UDT takes advantage of Oracle's ANYDATA type as a generic type target.

=====================================================================*/
CREATE OR REPLACE TYPE AnonymousFunction AS OBJECT(

   af_function_text VARCHAR2(32000),
   af_function_type NUMBER(12),
   af_function_name VARCHAR2(70),

   -- Constructor
   CONSTRUCTOR FUNCTION AnonymousFunction RETURN SELF AS RESULT,

   -- Define the function to be executed.
   -- Include the function name, function body, and function type.
   -- Use the DBMS_TYPES package to specify the function's data type.
   MEMBER PROCEDURE defineFunction(p_function_name VARCHAR2,
                                   p_function_text VARCHAR2,
                                   p_function_type NUMBER),

   MEMBER FUNCTION executeFunction RETURN SYS.ANYDATA,
  
   MEMBER PROCEDURE defineFunctionChar(p_function_name VARCHAR2,
                                           p_function_text VARCHAR2),
   MEMBER FUNCTION  executeFunctionChar RETURN CHAR,

   MEMBER PROCEDURE defineFunctionVarchar2(p_function_name VARCHAR2,
                                               p_function_text VARCHAR2),

   MEMBER FUNCTION  executeFunctionVarchar2 RETURN VARCHAR2,

   MEMBER PROCEDURE defineFunctionNumber(p_function_name VARCHAR2,
                                         p_function_text VARCHAR2),

   MEMBER FUNCTION  executeFunctionNumber RETURN NUMBER,

   MEMBER PROCEDURE defineFunctionCLOB(p_function_name VARCHAR2,
                                           p_function_text VARCHAR2),

   MEMBER FUNCTION  executeFunctionCLOB RETURN CLOB,

   MEMBER PROCEDURE defineFunctionBLOB(p_function_name VARCHAR2,
                                       p_function_text VARCHAR2),

   MEMBER FUNCTION executeFunctionBLOB RETURN BLOB,

   MEMBER PROCEDURE defineFunctionDate(p_function_name VARCHAR2,
                                       p_function_text VARCHAR2),

   MEMBER FUNCTION executeFunctionDate RETURN DATE,

   MEMBER PROCEDURE defineFunctionBDouble(p_function_name VARCHAR2,
                                          p_function_text VARCHAR2),

   MEMBER FUNCTION executeFunctionBDouble RETURN BINARY_DOUBLE,

   MEMBER PROCEDURE defineFunctionBFile(p_function_name VARCHAR2,
                                        p_function_text VARCHAR2),

   MEMBER FUNCTION executeFunctionBFile RETURN BFILE,

   MEMBER PROCEDURE defineFunctionBFloat(p_function_name VARCHAR2,
                                         p_function_text VARCHAR2),

   MEMBER FUNCTION executeFunctionBFloat RETURN BINARY_FLOAT,

   MEMBER PROCEDURE defineFunctionRaw(p_function_name VARCHAR2,
                                      p_function_text VARCHAR2),

   MEMBER FUNCTION executeFunctionRaw RETURN RAW


)
/
sho err;

CREATE OR REPLACE TYPE BODY AnonymousFunction AS

CONSTRUCTOR FUNCTION AnonymousFunction RETURN SELF AS RESULT
IS
BEGIN

    SELF.af_function_text := NULL;
    SELF.af_function_type := NULL;
    SELF.af_function_name := NULL;     

    RETURN;

END;

/*============================================================================================================*/


MEMBER PROCEDURE defineFunction(p_function_name VARCHAR2,
                                p_function_text VARCHAR2,
                                p_function_type NUMBER)
IS
BEGIN

   SELF.af_function_name := p_function_name;
   SELF.af_function_text := p_function_text;
   SELF.af_function_type := p_function_type;


END;


/*==========================================================================================================*/

-- This is the key function ... it executes the dynamic functions and provides the correct type encoding.
MEMBER FUNCTION executeFunction RETURN SYS.ANYDATA
IS

  v_sql VARCHAR2(32000) := 'DECLARE '||
                               'result_key anon_func_results.id%TYPE := <KEY>; '||
                               'v_result SYS.ANYDATA := NULL ; '||
                               '<FUNC> '||
                           'BEGIN '||
                                '<CONVERT> '||
                                ' CASE v_result.getTypeName '||
                                ' WHEN ''SYS.CLOB'' THEN '||
                                  ' INSERT INTO anon_func_results(id,clob_result) VALUES (result_key,v_result.accessClob()); '||
                                ' WHEN ''SYS.BLOB'' THEN '||
                                   'INSERT INTO anon_func_results(id,blob_result) VALUES (result_key,v_result.accessBlob()); '||
                                ' ELSE '||
                                   'INSERT INTO anon_func_results(id,result) VALUES (result_key,v_result); '||
                                ' END CASE; '||
                           'END;';
                               
  v_convert_text VARCHAR2(100) := NULL;

  v_key          NUMBER(12) := 0;

  v_result_sql VARCHAR2(100) := 'SELECT result FROM anon_func_results WHERE id=:id';
 
  v_clob_result_sql    VARCHAR2(100) := 'SELECT AnyData.convertClob(clob_result) FROM anon_func_results WHERE id=:id';

  v_blob_result_sql    VARCHAR2(100) := 'SELECT AnyData.convertBlob(blob_result) FROM anon_func_results WHERE id=:id';

  v_return_val SYS.ANYDATA := NULL;

BEGIN

   CASE SELF.af_function_type

     WHEN DBMS_TYPES.TYPECODE_BDOUBLE THEN
       v_convert_text := 'v_result := AnyData.convertBDouble(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_BFILE THEN
       v_convert_text := 'v_result := AnyData.convertBFile(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_BFLOAT THEN
       v_convert_text := 'v_result := AnyData.convertBFloat(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_BLOB THEN
       v_convert_text := 'v_result := AnyData.convertBlob(<NAME>); ';
       v_result_sql   := v_blob_result_sql;
     WHEN DBMS_TYPES.TYPECODE_CHAR THEN
       v_convert_text := 'v_result := AnyData.convertChar(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_CLOB THEN
       v_convert_text := 'v_result := AnyData.convertClob(<NAME>); ';
       v_result_sql   := v_clob_result_sql;
     WHEN DBMS_TYPES.TYPECODE_DATE THEN
       v_convert_text := 'v_result := AnyData.convertDate(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
       v_convert_text := 'v_result := AnyData.convertNumber(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_RAW THEN
       v_convert_text := 'v_result := AnyData.convertRaw(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
       v_convert_text := 'v_result := AnyData.convertVarchar2(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_VARCHAR THEN
       v_convert_text := 'v_result := AnyData.convertVarchar(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_VARRAY THEN
       v_convert_text := 'v_result := AnyData.convertCollection(<NAME>); ';
     WHEN DBMS_TYPES.TYPECODE_OBJECT THEN
       v_convert_text := 'v_result := AnyData.convertObject(<NAME>); ';
   END CASE;

   IF v_convert_text IS NOT NULL THEN

     SELECT anon_func_seq.nextval INTO v_key FROM DUAL;

     v_sql := REPLACE(v_sql,'<FUNC>',SELF.af_function_text);
     v_sql := REPLACE(v_sql,'<CONVERT>',v_convert_text);
     v_sql := REPLACE(v_sql,'<KEY>',v_key);
     v_sql := REPLACE(v_sql,'<NAME>',SELF.af_function_name);

     EXECUTE IMMEDIATE v_sql;

     EXECUTE IMMEDIATE v_result_sql INTO v_return_val USING v_key;

   END IF;

   RETURN v_return_val;

END;

/*====================================================================================================*/

MEMBER PROCEDURE defineFunctionChar(p_function_name VARCHAR2,
                                        p_function_text VARCHAR2)
IS
BEGIN

   SELF.defineFunction(p_function_name,
                       p_function_text,
                       DBMS_TYPES.TYPECODE_CHAR);


END;

/*====================================================================================================*/

MEMBER FUNCTION  executeFunctionChar RETURN CHAR
IS
   v_result SYS.ANYDATA := NULL;
BEGIN

  IF DBMS_TYPES.TYPECODE_CHAR = SELF.af_function_type THEN

     v_result := SELF.executeFunction;

     RETURN v_result.accessChar();

  ELSE

     RETURN NULL;

  END IF;


END;

/*=====================================================================================================*/

MEMBER PROCEDURE defineFunctionVarchar2(p_function_name VARCHAR2,
                                            p_function_text VARCHAR2)
IS
BEGIN

   SELF.defineFunction(p_function_name,
                       p_function_text,
                       DBMS_TYPES.TYPECODE_VARCHAR2);


END;

/*====================================================================================================*/

MEMBER FUNCTION  executeFunctionVarchar2 RETURN VARCHAR2
IS

   v_result SYS.ANYDATA := NULL;

BEGIN

  IF DBMS_TYPES.TYPECODE_VARCHAR2 = SELF.af_function_type THEN

     v_result := SELF.executeFunction;

     RETURN v_result.accessVarchar2();

  ELSE

     RETURN NULL;

  END IF;


END;

/*============================================================================================*/

MEMBER PROCEDURE defineFunctionNumber(p_function_name VARCHAR2,
                                      p_function_text VARCHAR2)
IS
BEGIN

   SELF.defineFunction(p_function_name,
                       p_function_text,
                       DBMS_TYPES.TYPECODE_NUMBER);


END;

/*===========================================================================================*/

MEMBER FUNCTION  executeFunctionNumber RETURN NUMBER
IS

   v_result SYS.ANYDATA := NULL;

BEGIN

  IF DBMS_TYPES.TYPECODE_NUMBER = SELF.af_function_type THEN

     v_result := SELF.executeFunction;

     COMMIT;

     RETURN v_result.accessNumber();

  ELSE

     RETURN NULL;

  END IF;


END;

/*===========================================================================================*/

MEMBER PROCEDURE defineFunctionClob(p_function_name VARCHAR2,
                                    p_function_text VARCHAR2)
IS
BEGIN

   SELF.defineFunction(p_function_name,
                       p_function_text,
                       DBMS_TYPES.TYPECODE_CLOB);


END;

/*==========================================================================================*/

MEMBER FUNCTION  executeFunctionClob RETURN CLOB
IS

  v_result SYS.ANYDATA := NULL;
  v_clob   CLOB        := NULL;

BEGIN

  IF DBMS_TYPES.TYPECODE_CLOB = SELF.af_function_type THEN

     v_result := SELF.executeFunction;

     v_clob   := v_result.accessClob();

     RETURN v_clob;

  ELSE

     RETURN NULL;

  END IF;


END;

/*========================================================================================*/

MEMBER PROCEDURE defineFunctionBlob(p_function_name VARCHAR2,
                                    p_function_text VARCHAR2)
IS
BEGIN

   SELF.defineFunction(p_function_name,
                       p_function_text,
                       DBMS_TYPES.TYPECODE_BLOB);


END;

/*=======================================================================================*/

MEMBER FUNCTION  executeFunctionBlob RETURN BLOB
IS

  v_result SYS.ANYDATA := NULL;
  v_blob   BLOB := NULL;

BEGIN

  IF DBMS_TYPES.TYPECODE_BLOB = SELF.af_function_type THEN

     v_result := SELF.executeFunction;

     v_blob   := v_result.accessBlob();

     RETURN v_blob;

  ELSE

     RETURN NULL;

  END IF;


END;

/*=======================================================================================*/

MEMBER PROCEDURE defineFunctionDate(p_function_name VARCHAR2,
                                        p_function_text VARCHAR2)
IS
BEGIN

   SELF.defineFunction(p_function_name,
                       p_function_text,
                       DBMS_TYPES.TYPECODE_DATE);


END;

/*======================================================================================*/

MEMBER FUNCTION  executeFunctionDate RETURN DATE
IS

  v_result SYS.ANYDATA := NULL;

BEGIN

  IF DBMS_TYPES.TYPECODE_DATE = SELF.af_function_type THEN

     v_result := SELF.executeFunction;

     RETURN v_result.accessDate();

  ELSE

     RETURN NULL;

  END IF;


END;

/*=====================================================================================*/

MEMBER PROCEDURE defineFunctionBDouble(p_function_name VARCHAR2,
                                           p_function_text VARCHAR2)
IS
BEGIN

   SELF.defineFunction(p_function_name,
                       p_function_text,
                       DBMS_TYPES.TYPECODE_BDOUBLE);


END;

/*=====================================================================================*/

MEMBER FUNCTION  executeFunctionBDouble RETURN BINARY_DOUBLE
IS

  v_result SYS.ANYDATA := NULL;

BEGIN

  IF DBMS_TYPES.TYPECODE_BDOUBLE = SELF.af_function_type THEN

     v_result := SELF.executeFunction;

     COMMIT;

     RETURN v_result.accessBDouble();

  ELSE

     RETURN NULL;

  END IF;


END;

/*====================================================================================*/

MEMBER PROCEDURE defineFunctionBFloat(p_function_name VARCHAR2,
                                          p_function_text VARCHAR2)
IS
BEGIN

   SELF.defineFunction(p_function_name,
                       p_function_text,
                       DBMS_TYPES.TYPECODE_BFLOAT);


END;

/*===================================================================================*/

MEMBER FUNCTION  executeFunctionBFloat RETURN BINARY_FLOAT
IS

  v_result SYS.ANYDATA := NULL;

BEGIN

  IF DBMS_TYPES.TYPECODE_BFLOAT = SELF.af_function_type THEN

     v_result := SELF.executeFunction;

     COMMIT;

     RETURN v_result.accessBFloat();

  ELSE

     RETURN NULL;

  END IF;

END;

/*===================================================================================*/

MEMBER PROCEDURE defineFunctionBFile(p_function_name VARCHAR2,
                                         p_function_text VARCHAR2)
IS
BEGIN

   SELF.defineFunction(p_function_name,
                       p_function_text,
                       DBMS_TYPES.TYPECODE_BFILE);


END;

/*===================================================================================*/

MEMBER FUNCTION  executeFunctionBFile RETURN BFILE
IS

  v_result SYS.ANYDATA := NULL;

BEGIN

  IF DBMS_TYPES.TYPECODE_BFILE = SELF.af_function_type THEN

     v_result := SELF.executeFunction;

     RETURN v_result.accessBFile();

  ELSE

     RETURN NULL;

  END IF;


END;

/*===================================================================================*/

MEMBER PROCEDURE defineFunctionRaw(p_function_name VARCHAR2,
                                       p_function_text VARCHAR2)
IS
BEGIN

   SELF.defineFunction(p_function_name,
                       p_function_text,
                       DBMS_TYPES.TYPECODE_RAW);


END;

/*===================================================================================*/

MEMBER FUNCTION  executeFunctionRaw RETURN RAW
IS

  v_result SYS.ANYDATA := NULL;

BEGIN

  IF DBMS_TYPES.TYPECODE_RAW = SELF.af_function_type THEN

     v_result := SELF.executeFunction;

     RETURN v_result.accessRaw();

  ELSE

     RETURN NULL;

  END IF;


END;

/* END TYPE BODY */
END;

As with all of my entries and code, feel free to use this or modify it as you see fit.  If you have questions or comments, please feel free to contact me via email.                           


6:53:59 PM    

Click here to visit the Radio UserLand website. © Copyright 2009Jason Bennett.
Last update: 3/11/2009; 4:52:53 PM.

November 2008
Sun Mon Tue Wed Thu Fri Sat
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30            
Sep   Jan