Jason Bennett's Developer Corner

I came up with a simple formula a couple of days ago that will automatically determine which result set  a row belongs in if the results of the query need to be grouped in specific numeric sets of rows (such as sets of five rows, or sets of 10 rows, etc ..) for retrieval and display in an object such as a table widget on a web page.  These would be the sets of rows that are displayed by the ‘Next’, ‘Last’, and ‘Previous’ buttons (or arrows) on the widget.  The formula can  be applied directly in the query, or separately as a function (depends on how you are handling your query results before passing them to the widget).  Here is the formula:

Rowset# = (trunc((x-1)/y)+1) where x is equal to the selected row number and y is the number of rows per set.

If we apply this formula in a query where want the resulting rows to be grouped into sets of five (5), it would look something like this (sample query uses the standard Oracle demo EMPLOYEES table) :

As you can see, the formula is simple and (best of all)  it works.   As always, feel to shoot me an email if you have questions.

I have added another open source code/utility offering to the site. It’s called the OWA Session Cache Utility.

The OWA Session Cache utility is a custom add-on for MOD_PLSQL and DBMS_EPG DADs that provides the ability to maintain a stateful session in a Web PL/SQL Toolkit application. The utility has features similar to those found in Java Servlet based applications such as session cookie (holds session id), ability to set session timeout period, expired session management, the ability to store and retrieve session data using AJAX enabled JavaScript, and a garbage collector to clean up the session cache.

To find out more and/or download the code, click on the OWA Session Cache link in the menu at the top of the page, or on the same link under the “Pages” menu on the right hand side of the page.

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:

The APEX_ADD_ON_UTILS PL/SQL package is defined as follows:

The modifed APEX_030200.wwv_flow_epg_include_mod_local function looks like:

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.

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

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:

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:

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:

** 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.

(Originally posted on the “old” Jason Bennett’s Developer Corner, Saturday, June 23, 2007)

I was recently tasked with the “seamless” integration of a large Web PL/SQL based application and newer J2EE application. The older application uses a mod_plsql DAD to authenticate users (using the Basic Authentication method), and the newer application built with Oracle ADF and JSF. Each user has a separate database account, and application security is database role based. The problem: How do I pass the users credentials (basically their database login information) to the J2EE application in order to create a user specific database connection? The user accesses the new application from a hyperlink on the existing application’s main menu. It sounds easy enough. Simply write a servlet filter to capture the “AUTHORIZATION” HTTP header and decode the credentials, right? Wrong. Since the J2EE application in seemingly different domain or session space, the “AUTHORIZATION” header is not passed. By the way, SSO and other “new” technologies were not currently available. So, how do we get around the cross domain/session space issue? It turns out to be fairly simple. Both applications are served through the same Apache web-server (OracleAS 10g R1), allowing us to use a very simple method that leverages mod_rewrite (pre-installed and configured with OracleAS 10g).

The Method …

The method uses the following three steps:

1. The URL from the older (calling) application to the newer (target) application has to look as if it is using the mod_plsql DAD. This gives the false impression that the target of the URL is in the same domain/session space as the current application. The URL would look something like: http://servername:7778/<;dad name>/application.do. This basically fools the browser into passing the domain/session space HTTP headers (specifically the “AUTHORIZATION” header) along with the request.

2. In the httpd.conf file (or a separate include .conf file) associated with your Apache instance, place a mod_rewrite directive like this one:

RewriteRule ^(//application.do)$ //application.do [PT]

3. In the Servlet Filter (or whatever portion of your application that can capture incoming HTTP request headers), get the value of the “AUTHORIZATION” HTTP request header. Here is an example of how this code looks using Java: String credentials = req.getHeader(“AUTHORIZATION”); (req is an instance of HttpServletRequest).

Decrypting the Credentials

After you get the credentials using the three step method above, you need to decrypt them. The value of the “AUTHORIZATION” HTTP header will be a string encoded using the base64 encoding method. It will look something like this:

Basic QWxhZGRpbjpvcGVuIHNlc2FtZQ== ( )

Decrypting the string is pretty simple using a base64 decoder such as Sun’s sun.misc.BASE64Decoder class. Before decrypting the string, we need parse out the credential portion of the string (i.e. we need to remove the “Basic” portion of the string). When decrypted, the above string will look like this:

Aladdin:open sesame (:
)

Once we get the credentials decrypted, simply parse the string to obtain the username and password. The following is a simple Java class that will aid in decrypting and returning the username and password: