Oracle APEX: Easily Integrate Custom Web PL/SQL Procedures and Packages

In this post, we look at a code authorization utility consisting of a schema, a table, and a PL/SQL package that allows developers to easily integrate their own custom web PL/SQL code into an Oracle APEX application.  The utility works in conjunction with APEX’s built-in security function, wwv_flow_epg_include_mod_local, that determines if a given procedure is authorized to be executed by the APEX PL/SQL DAD.  The security function resides in Oracle APEX home schema (in this case, APEX_030200), and must be customized to allow custom (external) procedures.  Most examples of customizing the security function demonstrate the use of an IF statement that grows as new procedures are added to the list of authorized procedures.  Each time a new procedure is added, the function must be edited and then compiled.  The utility presented here replaces that IF statement with a single function and allows the developer to register an entire package or just a single procedure. Compile and forget …

The Utility …

The custom procedure authorization utility consists of the following pieces:

  • A database schema called APEX_ADD_ONS –  schema that holds the components related to the authorization utility (tables, packages, etc …).
  • A table called APEX_ALLOWED_EXECUTE – table that holds the names and types of all authorized procedures and packages.
  • A PL/SQL package called APEX_ADD_ON_UTILS – contains all of the constants, functions, and procedures required to add, removes, and validate a package or procedure.
  • The customized version of APEX_030200.wwv_flow_epg_include_mod_local.

The APEX_ALLOWED_EXECUTE table is defined as follows:

/*
Table used by the APEX_030200.wwv_flow_epg_include_mod_local function to
verify procedures that can be called thru the promary APEX
PL/SQL DAD.

*/

CREATE TABLE APEX_ALLOWED_EXECUTE(
code_name VARCHAR2(200) NOT NULL,
code_type VARCHAR2(10) NOT NULL
)
/

CREATE OR REPLACE TRIGGER UPPER_CASES_CODE
BEFORE INSERT OR UPDATE ON APEX_ALLOWED_EXECUTE
FOR EACH ROW
DECLARE
BEGIN

:NEW.CODE_NAME := UPPER(:NEW.CODE_NAME);
:NEW.CODE_TYPE := UPPER(:NEW.CODE_TYPE);

END;
/

CREATE PUBLIC SYNONYM APEX_ALLOWED_EXECUTE FOR APEX_ALLOWED_EXECUTE;

/* This grant should be to the schema that houses APEX … older versions were called FLOWS_<version>*/
GRANT SELECT ON APEX_ALLOWED_EXECUTE TO APEX_030200;

The APEX_ADD_ON_UTILS PL/SQL package is defined as follows:

/*

APEX_ADD_ONS Utility package.
Package contains utilities that allow for

*/

CREATE OR REPLACE PACKAGE APEX_ADD_ON_UTILS AS

/*————————————————-*/
/* Constants used to Identify Executable Code Type */
/*————————————————-*/

CODE_TYPE_PROCEDURE CONSTANT VARCHAR2(9)  := ‘PROCEDURE';
CODE_TYPE_PACKAGE   CONSTANT VARCHAR2(10) := ‘PACKAGE';

/*———————————————*/
/* Make sure to add name as it will be called. */
/* i.e.  <schema>.<package>.<proc> or          */
/* <schema>.<proc> or just plain old <proc>     */
/*———————————————*/
PROCEDURE addAllowedExecute(p_name VARCHAR2 := NULL,p_code_type VARCHAR2 := CODE_TYPE_PROCEDURE);

PROCEDURE removeAllowedExecutable(p_name VARCHAR2 := NULL,p_code_type VARCHAR2 := CODE_TYPE_PROCEDURE);

/*——————————————————*/
/* This function should be embedded within the          */
/* APEX_030200.wwv_flow_epg_include_mod_local function. */
/*——————————————————*/

FUNCTION  isAllowedExecute(p_name VARCHAR2 := NULL) RETURN BOOLEAN;

END;
/
sho err;

/******************************************************************************************************************************************************/
/************************************************************** PACKAGE BODY **************************************************************************/
/******************************************************************************************************************************************************/

CREATE OR REPLACE PACKAGE BODY APEX_ADD_ON_UTILS AS

PROCEDURE addAllowedExecute(p_name      VARCHAR2 := NULL,
p_code_type VARCHAR2 := CODE_TYPE_PROCEDURE)
IS

v_sql_chk VARCHAR2(200) := ‘SELECT count(”x”) FROM apex_allowed_execute WHERE upper(code_name) = :code_name AND code_type= :code_type';
v_chk_val NUMBER(5)     := 0;

v_sql_ins VARCHAR2(200) := ‘INSERT INTO apex_allowed_execute(code_name,code_type) VALUES (:code_name,:code_type)';

BEGIN

EXECUTE IMMEDIATE v_sql_chk INTO v_chk_val USING UPPER(p_name),UPPER(p_code_type);

IF v_chk_val = 0 THEN

EXECUTE IMMEDIATE v_sql_ins USING UPPER(p_name),UPPER(p_code_type);
COMMIT;

END IF;

END;

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

PROCEDURE removeAllowedExecutable(p_name      VARCHAR2 := NULL,
p_code_type VARCHAR2 := CODE_TYPE_PROCEDURE)
IS

v_sql VARCHAR2(200) := ‘DELETE FROM apex_allowed_execute WHERE code_name = :code_name AND code_type := :code_type';

BEGIN

BEGIN
EXECUTE IMMEDIATE v_sql USING UPPER(p_name),UPPER(p_code_type);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;

END;

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

FUNCTION  isAllowedExecute(p_name VARCHAR2 := NULL) RETURN BOOLEAN
IS

CURSOR crsrCheckProc(cv_code_name VARCHAR2,
cv_code_type VARCHAR2) IS
SELECT
‘x’
FROM
apex_allowed_execute
WHERE
code_name = cv_code_name
AND    code_type = cv_code_type;

v_package_name VARCHAR2(200) := NULL;

BEGIN

— Check for matching procedure name

FOR data_rec IN crsrCheckProc(UPPER(p_name),CODE_TYPE_PROCEDURE) LOOP

RETURN TRUE;

END LOOP;

— Try to isolate package name from <schema>.<package>.<code> or <package>.<code> or <package>
IF INSTR(p_name,’.’,1,2) > 0 THEN

v_package_name:= SUBSTR(p_name,1,INSTR(p_name,’.’,1,2)-1);

ELSIF INSTR(p_name,’.’,1,1) > 0 THEN

v_package_name := SUBSTR(p_name,1,INSTR(p_name,’.’,1,1)-1);

ELSE

v_package_name := p_name;

END IF;

FOR data_rec IN crsrCheckProc(UPPER(v_package_name),CODE_TYPE_PACKAGE) LOOP

RETURN TRUE;

END LOOP;

RETURN FALSE;

END;

/*=============*/
/* END PACKAGE */
/*=============*/

END;
/
sho err;

CREATE PUBLIC SYNONYM APEX_ADD_ON_UTILS FOR APEX_ADD_ON_UTILS;
GRANT EXECUTE ON APEX_ADD_ON_UTILS TO APEX_030200;

The modifed APEX_030200.wwv_flow_epg_include_mod_local function looks like:

/*

Created a schema called APEX_ADD_ONS and a packaged function called APEX_ADD_ON_UTILS.isAllowedExecute
that compares the passed procedure name with a table stored list of allowed packages and procedures.

Run this script as SYS or other user that can alter the session and set it to the owning
schema for Oracle APEX.

*/

alter session set current_schema=APEX_030200;

CREATE OR REPLACE function wwv_flow_epg_include_mod_local(procedure_name in varchar2)return boolean
IS
BEGIN

IF APEX_ADD_ONS.APEX_ADD_ON_UTILS.isAllowedExecute(upper(procedure_name)) THEN

RETURN TRUE;

END IF;

RETURN FALSE;

END;
/

How it works …

We’ve briefly taken a look at all of the pieces of the utility, but how does it work?  The following scenario demonstrates how to add a package (or procedure … same process) as  authorized to be executed in an Oracle APEX application.

First, let’s look at what happens when we add a PL/SQL region to a page containing a reference to a PL/SQL packaged procedure that IS NOT registered with the utility as being authorized.

ApexCustomWebPLSQL1

The following code was added to the PL/SQL region displayed above:

IF NOT APEX_ADD_ON_UTILS.isAllowedExecute(‘HRChartsDemo.dataSet’)OR
NOT APEX_ADD_ON_UTILS.isAllowedExecute(‘FusionFlashCharts’) THEN

htp.p(‘<div style=”color:red”><b>Unauthorized Stored Procedure Call</b></div>’);

END IF;

htp.p(‘<div id=”OPC_Div”>’);

FusionFlashCharts.addChart(p_chart_id     =>’OfficesPerCountry’,
p_chart_type   => FusionFlashCharts.Column3D,
p_chart_width  =>’600′,
p_chart_height =>’400′,
p_data_url     =>’HRChartsDemo.dataSet?p_set_code=OPC’);

htp.p(‘</div>’);

In the code sample above, we execute a packaged procedure called FusionFlashCharts.addChart that will generate a Fusion Flash Charts  3D bar chart.  The procedure produces the following HTML:

<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase=http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0" width="800" height="600" id="OfficesPerCountry" >
<param id="movie_OfficesPerCountry" name="movie" value="FusionFlashCharts.swf?p_chart_code=CL3D" />
<param id="FlashVars_OfficesPerCountry" name="FlashVars" value="&dataURL=HRChartsDemo.dataSet%3Fp_set_code=OPC&chartWidth=800&chartHeight=600">
<param id="quality_OfficesPerCountry" name="quality" value="high" />
<embed id="embed_OfficesPerCountry" src="FusionFlashCharts.swf?p_chart_code=CL3D" flashVars="&dataURL=HRChartsDemo.dataSet%3Fp_set_code=OPC&chartWidth=800&chartHeight=600" quality="high" width="800" height="600" name="OfficesPerCountry" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer" />
</object>

In the HTML above, there are references to two Web PL/SQL packaged procedures that must be executed through the APEX DAD:

  • FusionFlashCharts.swf – Streams the requested Flash based chart object
  • HRChartsDemo.dataSet – Generates the XML data set required to populate the chart.

Since we have not yet registered these packaged procedures with APEX using the wwv_flow_epg_include_mod_local function, we see the following output when executing the page:

ApexCustomWebPLSQL2

Notice in the image above that no chart was displayed, and our error message indicating that references to unauthorized procedures have been made is being displayed.  The <object> tag was unable to load the .swf file.  It is also unable to execute the code that generates the data set for the chart.  How can we fix this issue?  We have to either authorize the two offending package procedures, or simply authorize the packages themselves.  Here is the code we would execute to accomplish the task:

BEGIN
APEX_ADD_ON_UTILS.addAllowedExecute(‘FusionFlashCharts’,APEX_ADD_ON_UTILS.CODE_TYPE_PACKAGE);
APEX_ADD_ON_UTILS.addAllowedExecute(‘HRChartsDemo’,APEX_ADD_ON_UTILS.CODE_TYPE_PACKAGE);
END;
/

The code above places references to the two packages into the APEX_ALLOWED_EXECUTE table.  If we refresh the APEX page after executing the code, we will see that the chart is rendered:

ApexCustomWebPLSQL3

** Note ** We still have grant execute on any package or procedure to the user (or schema) assigned to the APEX PL/SQL DAD.  In this case, the user is ANONYMOUS.

Wrapping it up …

Using the method described in this post, we can easily add (and remove) stand alone and packaged procedures the “Oracle APEX execution authorization list” without having to edit and compile the APEX_030200.wwv_flow_epg_include_mod_local function.   If you would like the code for creating this utility, you can get it here: ApexAddOns.zip.  If you have any questions, please feel free to contact me.

Comments are closed.