PL/SQL: Create Dynamic PL/SQL Functions with the AnonymousFunction Data Type
Sunday, August 23rd, 2009(Originally posted on the “old” Jason Bennett’s Developer Corner, Sunday, November 9, 2008)
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 )
Here is the code for the curious …
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 := ; ‘||
‘v_result SYS.ANYDATA := NULL ; ‘||
‘ ‘||
‘BEGIN ‘||
‘ ‘||
‘ 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(); ‘;
WHEN DBMS_TYPES.TYPECODE_BFILE THEN
v_convert_text := ‘v_result := AnyData.convertBFile(); ‘;
WHEN DBMS_TYPES.TYPECODE_BFLOAT THEN
v_convert_text := ‘v_result := AnyData.convertBFloat(); ‘;
WHEN DBMS_TYPES.TYPECODE_BLOB THEN
v_convert_text := ‘v_result := AnyData.convertBlob(); ‘;
v_result_sql := v_blob_result_sql;
WHEN DBMS_TYPES.TYPECODE_CHAR THEN
v_convert_text := ‘v_result := AnyData.convertChar(); ‘;
WHEN DBMS_TYPES.TYPECODE_CLOB THEN
v_convert_text := ‘v_result := AnyData.convertClob(); ‘;
v_result_sql := v_clob_result_sql;
WHEN DBMS_TYPES.TYPECODE_DATE THEN
v_convert_text := ‘v_result := AnyData.convertDate(); ‘;
WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
v_convert_text := ‘v_result := AnyData.convertNumber(); ‘;
WHEN DBMS_TYPES.TYPECODE_RAW THEN
v_convert_text := ‘v_result := AnyData.convertRaw(); ‘;
WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
v_convert_text := ‘v_result := AnyData.convertVarchar2(); ‘;
WHEN DBMS_TYPES.TYPECODE_VARCHAR THEN
v_convert_text := ‘v_result := AnyData.convertVarchar(); ‘;
WHEN DBMS_TYPES.TYPECODE_VARRAY THEN
v_convert_text := ‘v_result := AnyData.convertCollection(); ‘;
WHEN DBMS_TYPES.TYPECODE_OBJECT THEN
v_convert_text := ‘v_result := AnyData.convertObject(); ‘;
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,”,SELF.af_function_text);
v_sql := REPLACE(v_sql,”,v_convert_text);
v_sql := REPLACE(v_sql,”,v_key);
v_sql := REPLACE(v_sql,”,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;
