APEXtras

a development team dedicated to Oracle APEX

Page visit logging

with 2 comments

APEX has the ability to log and analyse user activity in some detail. However, the unit of the built-in APEX logs is the APEX page, and the data collected about what a user does on a particular page is limited. In many of our APEX applications, a single APEX page may correspond to multiple web pages. For example we have implemented a dynamic questionnaire system in which each screen of the questionnaire is rendered on the same APEX page. A PL/SQL routine is called with a parameter representing the page of the questionnaire that should be displayed which outputs the complete questionnaire screen by htp.p calls. We still need to collect information about “page” visits although only one APEX page is involved. In addition, there is often a requirement to collect more detailed information about how people are using the system – search terms entered, number of checkboxes ticked, etc.

The solution is to build your own page logging system that inserts a row into a log table for every page visited. You can then record any sort of detail you like. We call the logging code as an Application Process at Process Point “On Load: Before Header (page template header)”. Because APEX doesn’t provide a built-in variable that records the current page alias (and we use page aliases throughout our system), we also need a Before Header Application Computation that fills the Application Item APP_PAGE_ALIAS (see http://oraclequirks.blogspot.com/2008/02/build-and-use-apex-page-alias.html).

The actual insert into the log table is a simple SQL statement that could be executed in the APEX process, but we prefer to keep code that writes to tables in PL/SQL packages, separate from APEX: it’s more maintainable there, error handling can be more rigorously enforced, and we end up with a single PL/SQL procedure to perform each action, rather than several APEX PL/SQL regions doing more-or-less the same thing.

Written by Roger

25 February, 2009 at 3:52 pm

Posted in Uncategorized

Tagged with , , ,

Underscore acts as wildcard in PL/SQL INSTR function for CLOB

leave a comment »

Oracle 10.2.0.4.0, Windows 32-bit, NLS_CHARACTERSET AL32UTF8.
We have a PL/SQL utility function that retrieves the nth item from a delimited string. The string parameter is a CLOB so that it can be used with input of any length. Normally the delimiters are commas, and the function has worked fine for years.
An APEX user’s sort order preference for a static report is stored in column WWV_FLOW_PREFERENCES$.ATTRIBUTE_VALUE as a string that looks something like fsp_sort_1_desc. We used our function to retrieve the various parts of this string, with ‘_’ as the delimiter, but it always returned a null. To our surprise it turned out that the underscore acted as a wildcard and matched any character in the string. That is expected behaviour in a SQL LIKE clause, but should not happen in PL/SQL. There is in fact a bug report in Oracle Metalink which describes this behaviour. There is no fix for Oracle version 10g – the bug is said to be fixed in Oracle 11.2. Our workaround was to replace all the underscores with another delimiter before calling the function.

Written by Roger

19 February, 2009 at 11:06 am

Posted in Uncategorized

Tagged with , , ,

APEX Deployment Script

with 2 comments

We are used to working in more formal coding environments than our in-house Oracle/Apex development setup, for which we have a dedicated Oracle server and no full-time DBA. When we had to start making periodic application releases to a remote live server, we expected to be able to write a script that would do the release automatically. It proved a little more difficult than we thought it would – we needed a PL/SQL package, a PERL script, and an APEX command line utility. If you’re faced with the same problem, here’s how we did it.

We used Windows servers for this project, so the final product was two Windows batch files – however it wouldn’t be difficult to write PERL or shell scripts using the same methods.

Of course you will need to customise the batch files and possibly the PL/SQL package for your own environment, and you should test the deployment process to destruction before you let it loose on a live server.

Why did we need an Apex deployment script?

We were coming to the end of a large Apex project developed and to be run on Microsoft Windows Servers. The data was in a single dedicated Oracle workspace but there were three separate Apex applications and 17 PL/SQL packages amounting to nearly 30,000 lines of code.

There were three dedicated Windows Oracle servers – a development server at our office and staging and live servers at a remote co-location facility. We were making formal code and Apex applications releases from the development server to the staging server for user acceptance testing, then releasing from the staging to the live server when the system was approved. Each version of the project had to be retained so that releases could be rolled back if there was any problem.

The manual process
We started off doing both deployments (development->stage and stage->live) manually.
For development->stage:

  1. Export each of the 3 Apex applications from the development server to a text file with a name that represents the new version number, then import it into the staging server.
  2. Copy each PL/SQL package from the development to the staging server, taking care not to overwrite sections of code that are site-specific; that includes SMTP server names and addresses, sender email addresses, and database links. We did this using cut and paste (carefully) between PL/SQL developer windows.
  3. Save the current state of the PL/SQL packages under the new version name – we used Subversion for this.
  4. Copy the style sheet and images from a directory on the development server to one on the staging server. It was easy to forget this step.

This process was complicated, time-consuming, and inherently risky – after several Apex versions had been generated and saved on disk it was easy to pick the wrong one for import. It was also essential to know how to edit PL/SQL code so that the site-specific code sections were retained – which meant that only a programmer could make a deployment.

The automated solution
Our goal was a single script (ie a Windows batch file) to run on the originating server that would export a project version in the form of SQL scripts to the target server, and a single script to run on the target server to import the new version.

A sample export batch file can be found here. It relies on a PL/SQL package, a PERL script, and the APEX utility APEXExport.class, which is in the standard APEX 3.1 distribution under apex\utilities\oracle\apex.

To run this, you’d save it as a Windows batch file (.bat or .cmd extension) on the source Oracle server then invoke it from the Windows command line with the version number as the only parameter. The source and destination file locations, and the numbers of the Apex applications to be deployed are hard-coded in the batch file. The names of the packages, functions, and procedures to be deployed are package-level constants in the PL/SQL package. The name of the Oracle directory for output PL/SQL code is also a package-level constant. The directory name and its physical path on disk needs to be set up (once) from Oracle – see below.

Here’s a corresponding sample import batch file. You’d save it as a Windows batch file on the target Oracle server then run it with the same version number as was given to the export batch file.

And here’s some more detail about how it works:

1. Exporting PL/SQL packages, functions and procedures (your code)

Where to write the export file containing your code
Oracle can write to external files by means of the UTL_FILE package. In order to use this, we needed to define an external directory on the Oracle server:
CREATE OR REPLACE DIRECTORY CODE_SAVE AS ‘C:\MYDIRECTORY\ETC’

Where to get your code from
The standard Oracle view USER_SOURCE contains all the package, function, and procedure code visible to the current user. To turn the code into executable SQL scripts we just needed to surround it with “CREATE OR REPLACE” and “/”.

2. Site-specific code

We used a simple mechanism for identifying conditional code sections. Each such section was given a start and finish label on a separate Oracle commented line. We arbitrarily chose ‘!~’ and ‘~!’ as begin and end delimiters for the labels because those strings didn’t occur anywhere else in our code.

The labels consisted of two parts separated by an underscore(_): an identifier that specifies the server for which the code section should be live, and the word BEGIN or END. In the exported code the relevant section for the target server is uncommented and all other sections remain commented out.

Thus a package specification could contain the following on a development server:
--!~DEVELOPMENT_BEGIN~!
smtp_host CONSTANT VARCHAR2(256) := 'smtp.developmentdomain.com';
--!~DEVELOPMENT_END~!
--!~LIVE_BEGIN~!
/*
smtp_host CONSTANT VARCHAR2(256) := 'smtp.livedomain.com';
*/
--!~LIVE_END~!
Which would be rendered on a live server as:
--!~DEVELOPMENT_BEGIN~!
/*
smtp_host CONSTANT VARCHAR2(256) := 'smtp.developmentdomain.com';
*/
--!~DEVELOPMENT_END~!
--!~LIVE_BEGIN~!
smtp_host CONSTANT VARCHAR2(256) := 'smtp.livedomain.com';
--!~LIVE_END~!

3. Exporting Apex Applications

Apex comes with a command-line export tool written in Java. This produces a SQL script that can be directly invoked from SQL*Plus to reimport the application. It was not easy to make the export tool run, but after some trial and error, we found that (at least in our setup) the CLASSPATH environment variable needed to point to both the Oracle Java library and the location of the Apex export script.

To import an exported application into a different server from the one that exported it, the security group id needs to be changed to that of the target server. That involves rewriting the security group id in the following line of code:
wwv_flow_api.set_security_group_id(p_security_group_id=>SomeBigNumber);

We did this by processing the output Apex script with a simple PERL script. Instead of using a literal security group ID to replace SomeBigNumber, we rewrote this line as
wwv_flow_api.set_security_group_id(p_security_group_id=> apex_util.find_security_group_id(‘MY_WORKSPACE’));

That allowed the import to work on any server with the correct workspace name.
Of course you can use any other text substitution tool to do the replacement instead of a PERL script, as long as it can be invoked from the command line.

4. Versions

This should work on any recent version of Oracle and Apex, but it has been tested only on:
Apex 3.1.2
Oracle 10.2.0.4

Additional Software

Java is likely to be already installed on your Windows server, but PERL may not be.

PERL
We used ActiveState ActivePerl 5.10.0.1004.

32-bit Windws download:
http://www.activestate.com/store/download_file.aspx?binGUID=c8a0cfb5-29dc-484c-9b12-227ed449dacb

64-bit Windws download:
http://www.activestate.com/store/download_file.aspx?binGUID=01ab9ee2-63be-4f69-8b27-fafda0563882

JAVA
Java for Windows (currently Version 6 Update 11) download is available from:
http://javadl.sun.com/webapps/download/AutoDL?BundleId=26223

Written by Roger

11 February, 2009 at 5:18 pm

Posted in Uncategorized

Tagged with , , ,

Welcome to the APEXtras blog…

leave a comment »

The first post in a new blog is frequently an optimistic statement of intent, and this one is no different.

At APEXtras we’ve built up a fair bit of knowledge about how to build engaging / intuitive / secure / fast / complex web interfaces to databases using Oracle’s Application Express and we want to give some of that knowledge back to the APEX community.

If you find any of this stuff useful, or discover a better way of doing things then please add a comment or write about it on your own blog.

Written by John

10 February, 2009 at 2:45 pm

Posted in Uncategorized