Integrating the Google Chart API in APEX

A few days ago, Google released the Google Chart API. By calling a special crafted URL Google returns a image containing a chart derived from the parameters included in the URL. For instance


will give the image shown left. At the right the Flash equivalent generated by APEX is shown.

To include this dynamic Google chart based on a query on a page, define a PL/SQL dynamic content region with the following PL/SQL source:

l_url VARCHAR2(2000) := 'http://chart.apis.google.com/chart?cht=p3&chs=200x100&chco=ff0000,00ff00,0000ff,000000';
l_chd VARCHAR2(2000);
l_chl VARCHAR2(2000);
FOR rec IN
(SELECT d.deptno LABEL,
COUNT(e.empno) VALUE
FROM dept d,
emp e
WHERE d.deptno = e.deptno(+)
GROUP BY d.deptno)
l_chd := l_chd || ',' || rec.VALUE;
l_chl := l_chl || '|' || rec.LABEL;
l_url := l_url || 'chd=t:' || SUBSTR(l_chd, 2) || '&chl=' || SUBSTR(l_chl, 2);
htp.p('<img src="' || l_url || '"/>');

If you do not want to use the built-in Flash chart functionality, the Google Chart API is a simple alternative.


Mail from Application Express with Access Control Enabled

One of the post installation task after installing Oracle 11 is the creation of Access Control List (ACL). Oracle provides a few scripts in order to allow flows_030000 to connect any host. What if you do not allow the database to connect any host but only one host. For instance, you want to send mail from Application Express (APEX) ?

First, set up the email settings in the administrative interface (Home>Manage Service>Instance Settings). Secondly, run the following statements as

-- Create an access control list
dbms_network_acl_admin.create_acl(acl => 'apexmail.xml',
description => 'Mail from Apex', principal => 'FLOWS_030000',
is_grant => TRUE, privilege => 'connect', start_date => NULL,
end_date => NULL);

-- Assign the list to your SMTP host, i.e. smtp.yyy.xx
dbms_network_acl_admin.assign_acl(acl => 'apexmail.xml',
host => 'smtp.yyy.xx', lower_port => 25, upper_port => 25);

-- Uncomment to remove access control list
-- dbms_network_acl_admin.drop_acl(acl => 'apexmail.xml');

-- Please, do commit work.

You can find more information about the usage of dbms_network_acl_admin in the Database Security Guide. For all the remaining network services you can apply the same technique.


Where is the Scott schema in APEX?

I decided to install Oracle 11g. And with Oracle 11g Application Express (APEX)is installed by default. Before importing an application based on the emp table, I decided to create a workspace based on the existing schema Scott. However the administrative interface did not allow me to select Scott. By not using the LOV but typing the schema name, APEX gave the following error: Schema is reserved or restricted. What is going on?

APEX uses the following query to populate the list of values:

SELECT name n,
name r
FROM sys.user$ u
WHERE type# = 1
AND name <> 'FLOWS_030000'
FROM wwv_flow_restricted_schemas
FROM wwv_flow_rschema_exceptions
WHERE schema_id =
FROM wwv_flow_restricted_schemas
WHERE SCHEMA = u.name)

Beside some hard coded schema names, there are two flows_030000 tables used in the query, wwv_flow_restricted_schemas and wwv_flows_rschema_exception.

SQL>SELECT * FROM flows_030000.wwv_flow_restricted_schemas t
-- ------ ---------- ---------------- --------------- ---------------
38 SCOTT SYS 3-8-2007 2:12:53

The wwv_flows_rschema_exception contains no rows.

Both tables are used by the package htmldb_site_admin_privs, which serves as an interface packages. Now remove the restriction on Scott by executing the following statements (as Sys).

SQL>exec flows_030000.htmldb_site_admin_privs.unrestrict_schema(p_schema => 'SCOTT');
And of course, this topic is covered by the Application Express User's Guide.


Google Maps in APEX on XE with spatial data

A picture says more than thousand words is well known saying, well let's have a look.

Choose your favorite country from the list, and the Google Map will pan to your country. As a bonus the selected country is colored red, the country shape is based on spatial data. You can zoom and move the map, or change the map type.

What do you need to build this application:

  • An Oracle XE database or better. The good news is that XE already contains the MDSYS schema full of spatial utilities. The bad news is that XE has no spatial data included. But ...

  • NAVTEQ provides sample data for Geocoder. Download, import and use it.

  • Apex, included with XE.

  • A Google Maps API key.

And it is all free, as in beer.

Just a remark about using the term spatial. As you can read here XE does not have the Spatial option included nor does it include Oracle Locator with or without Oracle Mapviewer, but XE contains the MDSYS schema and that is all we need.

Include Google Maps

Sign up for a Google Map API key here. For XE use the following URL to sign up. Take a look at the sample page shown after generation of your key.

Now create in APEX a page (in a new or existing application) and navigate to the page attributes.

In the Display Attributes section set the cursor focus to Do not focus cursor. In the HTML Header section insert the following code, replace ... with your Google maps API key.

<script src=http://maps.google.com/maps?file=api&amp;v=2&key=...&sensor=false

In the HTML Body Header (section Header and Footer) insert the following code:

<script type="text/javascript">
function load() {
if (GBrowserIsCompatible()) {
var map = new GMap2(document.getElementById("map"));
map.setCenter(new GLatLng(37.4419, -122.1419), 13);

In the Page HTML Body attribute (section HTML Body attribute) insert the following code:

onload="load()" onunload="Gunload()"

Add a HTML Text region to the page with the following region source:

<div id="map" style="width: 500px; height: 300px"></div>

For Internet Explorer a small change of the page template is needed otherwise Internet Explorer stops responding. The first line of header (section Definition) should look like this:

<html lang="&BROWSER_LANGUAGE." xmlns:htmldb="http://htmldb.oracle.com" xmlns:v="urn:schemas-microsoft-com:vml">

Run the page and if everything is alright you will see a map of Palo Alto. This is the 'hello world' example of Google Maps. Note that it is required to issue the SetCenter statement otherwise only a grey pane is shown on the place of the map. You can also add some bells and whistles: map controls and polylgons (points connected by lines with a fill color as well).

Prepare APEX

Let us finish the web front end first before retrieving the geodata from the database. Modify the page created in the previous section.

Change the pagetype in PL/SQL (anonymous block). Insert the following in Region Source (section Source):


The package p_gis_data is discussed later.

Add two items to the page: P1_COUNTRY (select list with submit) with the following dynamic list of values query:

SELECT initcap(country) display_value,
country return_value
FROM m_world

And add a placeholder for the map P1_DIV (Display as text, does save state) with the following value for Source value of expression (section Source):

<div id="map" style="width: 500px; height: 300px"></div>

In order to initialize the Google Map add a before region process. This process sets the default value of the select list when there is no item selected.

:P1_COUNTRY := 'Netherlands';

Load sample data

Download the sample data and unzip. Create a separate user and tablespace for the sample data:


Only execute steps 1 and 2 from the README. You do not need to install Mapviewer. After some time all the data is loaded.

Retrieve spatial data

It's time to retrieve spatial data from the database. Take a look at the M_WORLD table, there is a column named GEOMETRY with datatype SDO_GEOMETRY which stores all the spatial data. The GEOMETRY column stores the position (property GEOMETRY.SDO_POINT), more or less the center, of a country and one of more polygons. A polygon consists of points connected by (straight) lines. Remember the Google polygons?

Our task is to retrieve all polygons from the GEOMETRY column. Property GEOMETRY.SDO_ELEM_INFO tells us how many polygons the property GEOMETRY.SDO_ORDINATES stores and of which kind they are. The function SDO_UTIL.GETNUMELEM retrieves the number of elements for a given geometry, in our case the number of polygons. The SDO_UTIL.EXTRACT function extracts the nth element from a geometry returning a geometry object as well. And the function SDO_UTIL.GET_VERTICES retrieves all points of geometry. Combining these two functions will result in a number of geometry objects, each object corresponds with one polygon.

We can retrieve this information in one SQL statement:

SELECT country.geometry.sdo_point.x x,
country.geometry.sdo_point.y y,
FROM TABLE(sdo_util.getvertices(sdo_util.EXTRACT(country.geometry, country.element)))
ORDER BY id) area
(SELECT iv.*,
LEVEL element
FROM m_world
WHERE country = cp_name) iv
CONNECT BY LEVEL <= sdo_util.getnumelem(geometry)) country

The inline view iv selects one row from the M_WORLD table and for each element of the corresponding geometry a copy of this row is generated. In the country select each element is stored in a cursor expression. The next thing is to write some PL/SQL code to generate javascript based on this query i.e. p_gis_data.

Further reading

The idea for this blog entry came after reading Creating Thematic Google Mapping Applications (for Business Intelligence) using Oracle Locator/Spatial and Application Express and Auf den Ort kommt es an: Geodaten in Application Express-Anwendungen nutzen (in german) and of course Oracle Spatial User's Guide and Reference.


APEX 3.0.1 on XE

On 29 Februari 2008 Oracle released Application Express 3.1 . With this release you can upgrade the Application Express within Oracle XE to 3.1. For more information on this topic see Oracle Database XE and Application Express 3.1 and APEX 3.1 on XE .

Oracle released Application Express 3.0.1 recently. With this release you can upgrade APEX within Oracle XE from 2.1 to 3.0.1 (see Oracle Database XE and Application Express 3.0.1). But how to upgrade when you already installed APEX 3.0 on XE?

There is a patch available on Metalink to upgrade from APEX 3.0 to 3.0.1. According to the Patch Set Notes Oracle9i release 2 and higher support this patch if you have APEX already installed (the Patch Set Notes also includes a list of fixed bugs). In order to download the patch from Metalink, you will need a support contract (which is not included with XE).

Another option is to remove APEX 3.0 completely. After the removal, XE has APEX 2.1. (still) installed and the next step is of course to install APEX 3.0.1. The big advantage of this option is you following the main route of upgrading XE/APEX as proposed by Oracle. However there are some things to keep in mind.

  • As always start with making a backup of your database.

  • Export all applications you would like to see again in APEX 3.0.1. If you remove APEX 3.0 completely, you also remove all applications stored in the FLOWS_030000(APEX 3.0) schema! Note that applications stored in the FLOWS_020100 schema will be migrated to FLOWS_030000 (APEX 3.0.1).

  • Make a backup of changed or added cascading style sheets (CSS), images and java scripts separately.

  • Run the apex_3.0.1\apxremov.sql script (the script is the same for both versions).

  • Run the apex_3.0.1\apxins.sql script. Check the installation log for errors.

  • Copy the apex_3.0.1\images directory to the /i/directory. Also copy the previous backed up CSS, images and java scripts to the /i/ directory.

  • Import all applications exported in the first step. Note that existing applications in APEX 2.1 are migrated to APEX 3.0.1 as part of the upgrade process.

  • Time to make a backup again.

Have a look at the APEX reports in SQL Developer, no errors any more.


Remote debugging Code Tester

Quest Code Tester for Oracle (Code Tester) helps you with defining test cases, generating test harnesses and presenting the test results in a structured way. Code Tester does not provide any features to debug your code. If you run into a red light situation when a test case fails, you have discover where the error is located. This means checking inputs and outcomes in order to exclude incorrect setup and incorrect initialization code. And of course checking your code, recompiling your (test) code and login again.

If this does not result into a green light, it is time to debug your code with a development IDE. As a result you have to transfer your test code into your development IDE. Wouldn't it be nice if you could enter debug mode seamlessly: when executing your test case the execution stops at a breakpoint and you can debug your code. The answer is: yes, you can. With SQL Developer you can remote debug your code within a test run.

The linking pin between Code Tester and SQL Developer is the package sys.dbms_debug_jdwp where jdwp stands for Java Debug Wire Protocol. This protocols needs a debugger process i.e. SQL Developer and a debuggee process i.e. Code Tester. The debugger listens for requests from the debuggee i.e. PL/SQL package procedure calls to connect_tcp and disconnect.

Setting up the debuggee
In Code Tester you have to modify the initialization section of your test case. Add the following code
dbms_debug_jdwp.connect_tcp(host => '', port => 4000);
The first parameter is your IP address of the client where the Code Tester IDE runs (as seen from the database server you're connected to). Because I'm running Code Tester and Oracle XE on the same machine, I use the local host address The second parameter is the default port. An alternative for the hard coded IP address is SYS_CONTEXT ('USERENV', 'IP_ADDRESS').

After the test case is executed, switch off remote debugging. Add the following code to the cleanup section:

Setting up the debugger
In SQL Developer login with the same user as Code Tester and right click on your connection, a context menu appears and select the 'Remote Debug' option. A small window with the title 'Debugger - Listen for the JPDA' (Java Platform Debugger Architecture) appears, enter the address or host name where SQL Developer should listen to connect. Use the same IP address as in dbms_debug_jdwp.connect_tcp. Also check if the port is the same.

Before switching to Code Tester again set a breakpoint in your code (and compile your code for debug) otherwise the debugger will not stop at your breakpoint. At last but not at least make sure the user has the debug connect session privilege and the debug any procedure when debugging other users objects.

Debugger meets debuggee
It is time to run your test with Code Tester. SQL Developer will stop on your breakpoint. Note: while debugging your code Code Tester will not respond. After stepping through your code press the resume button in SQL Developer to return to Code Tester. As an example I modified the code of the normal usage test case of the function qctod#betwnstr (see for an explanation and the source code How Quest Code Tester for Oracle can help you get rid of bugs in your PL/SQL procedures). In the picture below SQL Developer hits the breakpoint at line 13. In the data tab you can see all variables and their values.

This example shows how to use remote debugging with Code Tester. Instead of using SQL Developer as debugger you can also use Toad for Oracle or Jdeveloper. And also every front end can be used as debuggee for remote debugging as long the calls to dbms_debug_jdwp can be implemented. Let's start debugging!

I found the following links usefully while writing this blog entry:


Returning into clause and post statement triggers

If you are using RETURNING INTO clauses with DML statements and you are also using (post statement) triggers then you should be aware of the following caveat where the values of the RETURNING INTO do not match the column values.

To show this behavior, set up a small test case first. A table t1 with one column c1 with an after insert statement trigger, modifying the c1 column.


Table created.

4 SET c1 = 'TRIGGER';
5 END;
6 /

Trigger created.

Insert a row in t1 and return the new value of c1 into bind variable c1.

2 /

1 row created.

And inspect the contents of the table and the value of the bind variable.

SQL> SELECT c1 "column",
2 :c1 "bind"
3 FROM t1
4 /

column bind
---------- ----------

1 row selected.

As expected the value of column c1 is TRIGGER. The value is the bind variable is SQLPLUS. This value is set before the after statement trigger starts.

Although this code is not production code, you might notice this behavior in production code as well: I discovered this behavior hidden in a mutating table workaround.


APEX reports and SQL Developer

Both Kris Rice and Dimitri Gieles blogged about new APEX reports in SQL Developer 1.1.3 . Minor detail: those reports are build for Application Express version 3.0.1 , which is not released yet.

I installed the new version of SQL Developer, and tried to run the APEX reports (location in the reports tree: All reports, Data dictionary Reports, Application Express). Surprisingly, I did not get the message Application Express 3.0.1 or higher required. In a few cases I received an error: ORA-00904 invalid identifier.

To find the SQL behind the reports a simple copy and paste of the report did not work i.e select the Application report, right click and select copy. Select the User Defined Reports, right click and paste from, nothing happened. Even export of the report(s) did not work.

After some searching I found the report SQL hidden in a file: oracle.sqldeveloper.report.jar (location sqldeveloper\extentions directory). This .jar file includes an apex30.xml, this file contains all the APEX reports in XML format (you can unzip the XML file with an unzip utility). The next step is to import the apex30.xml and then you will see all the reports in the User Defined Reports branche as well.

Changing the SQL is left as an exercise to the reader... ;-)


Multi value selectors

APEX had three multi value selectors: check box, multi select and list manager. APEX 3.0 introduces a new multi value selector: the shuttle. An shuttle consists of two text areas with buttons between the the area's to move items back and forth. At the right side, the selection, there are buttons to change the order of the items in the right text area. All selectors are based on a list of values. Setting or getting values of these selectors can be done with colon separated strings i.e. 'Violet:Indigo:Blue'.

The shuttle and list manager also preserve the select order as you can see in the picture. For each selector the same values in the same order are selected: first Violet and second Indigo. As you can see in the text boxes the shuttle and list manager preserve selection order in contrary with check box and multi select. The picture shown here is a modified version of on-line demo Item Types.


APEX 3.0 on XE part 2

On 29 Februari 2008 Oracle released Application Express 3.1 . With this release you can upgrade the Application Express within Oracle XE to 3.1. For more information on this topic see Oracle Database XE and Application Express 3.1 and APEX 3.1 on XE .

If you followed the guidelines of APEX 3.0 on XE, you have a working APEX 3.0 interface. However the administrative interface of APEX is not installed on XE by default. Using this interface you can manage all aspects of the service including creating, removing, and managing workspaces. Barney Mattox discovered a workaround to install this interface as well.

In order to install the administrative interface perform the following steps:

  1. Start SQL*Plus. Make sure your working folder is the APEX installation folder (where apexins.sql is located).

  2. Connect as user SYS.


  4. The following command imports the administrative interface: @script\f4050.sql

  5. The following command imports the request for workspace interface: @script\f4700.sql

  6. Change the password for the ADMIN user in order to login in the administrative interface: @apxxepwd.sql

  7. Use the following URL to access the administrative interface http://server:port/apex/f?p=4550:10 i.e. .

Additonal notes:

  • Issue a spool install.log after logging in to SQL* Plus. If there occurs an error SQL*Plus will exit. You can use the logfile to find out what went wrong.

  • I experienced problems with step 3: Illegal security group id value for this schema. A workaround is to login as FLOWS_030000 and run the scripts. First you need to unlock user FLOWS_030000:


Enumeration types in PL/SQL?

In the book Oracle PL/SQL for DBAs the definition of the type BOOLEAN is shown:

FROM all_source
WHERE owner = 'SYS'
AND type = 'PACKAGE'
AND line <5


/********** Types and subtypes, do not reorder **********/

4 rows selected.

Have you seen that kind of type definition before? I did, it looks like a Delphi enumeration type when you replace the is with = . Let's define our own type.

create package TRAFFIC is 

Warning: Package created with compilation errors.


2,19 PLS-00505: User Defined Types may only be defined
as PLSQL Tables or Records
2,3 PL/SQL: Declaration ignored

2 rows selected

Perhaps this is an enhancement request for Oracle 11g release 2?


APEX 3.0 on XE

On 29 Februari 2008 Oracle released Application Express 3.1 . With this release you can upgrade the Application Express within Oracle XE to 3.1. For more information on this topic see Oracle Database XE and Application Express 3.1 and APEX 3.1 on XE .

Oracle Database Express Edition (XE) contains Application Express (APEX2.1) version 2.1 and yesterday Oracle released version 3.0 . While reading the installation guide regarding the Oracle Database requirement I noticed the following:

Do not install this version of Oracle Application Express on Oracle Database Express Edition (Oracle Database XE).

With the previous release of APEX2.2.1 some people installed this version into their XE database (see this OTN thread: Install APEX 2.2.1 on XE is possible! I think...). So why not try to upgrade my APEX2.1 to APEX3.0 on my XE database? It really sounds like a challenge!

However installing APEX3.0 on XE is an unsupported configuration by Oracle. This does not imply it will not work but if you run into problems there is no technical support available (see this OTN thread Install Apex 3.0 on Oracle XE). Also make a backup before you start the installation. My first attempt to upgrade ended with a database where I could not login into APEX, the images where missing and when trying to find out what went wrong, a BSOD appeared. You are warned...

The installation consists of two parts: running update scripts and copying the images file into the virtual path /i/. The first script to run is apexins.sql. The good news about this script is this remark: 02/15/2007 - Remove XE check. There is no check on the database edition anymore. And the bad news is apexins.sql calls coreins.sql which contains the following code:

Rem Set XE variable to '1' for XE installation, '0' for non-XE installation
define XE = '0'

Set the variable XE to 1 and save this script and run apexins.sql according to the installation guide.After completion of this script make sure there are no invalid objects. I have still one invalid package body WWV_FLOW_XE_CONFIG. There are no other objects calling this package, so errors can occur if this package is called with dynamic PL/SQL.

While the script is running you can copy the images into the virtual directory (see Where are the /images of Application Express in OracleXE). The total installation time was 1 hour.

Login and choose About Application Express, you should see something like this picture.


PL/SQL Test-A-Thon

At the end of the first day of OPP2007, Steven Feuerstein organized a Test-A-Thon to promote the use of Quest Code Tester for Oracle (QCTO).

A Test-A-Thon is a contest that tests your ability to construct unit tests for PL/SQL code with a tool like QCTO.

You have a fixed amount of time to solve a number of exercises. Each exercise consists of a PL/SQL program and a number of tests with the expected outcome.

The winner is the person who successfully builds the most test cases within a certain amount of time and is able to show the test results with the test tool.

I joined in the contest and obtained a shared second place together with two other participants.The first price went to Filipe Silva from Portugal who solved four exercises and a bonus exercise within an hour. Congratulations!


APEX System Preferences

In a previous post I struggled with the differences between system preferences and database parameters. In this post I focus on system preferences again.

Oracle provides the package wwv_flow_platform to set and get the system preferences. With this query you get all the system preferences as well:

SELECT name, value, pref_desc FROM flows_020100.wwv_flow_platform_prefs

NAME                              VALUE         PREF_DESC
-------------------------------- ------------ --------------------------------------
SMTP_HOST_ADDRESS smtp.isp.com The address of the SMTP server
used to relay mail.
SMTP_HOST_PORT 25 The port that the SMTP server
listens for requests.
MAX_SCRIPT_SIZE 500000 The maximum allowable size for
a script text.
SQL_SCRIPT_MAX_OUTPUT_SIZE 200000 The maximum allowable size for
an individual script result.
WORKSPACE_MAX_OUTPUT_SIZE 2000000 The maximum space allocated for
script results.
ENABLE_TRANSACTIONAL_SQL Y The flag which determines if
Transactional SQL Commands are
enabled on this instance.
PLSQL_EDITING Y The flag used in SQL Workshop
Object Browser to enable user
to edit and compile PL/SQL.
Adminstration which determines
if demonstration applications
and database objects are created in
new workspaces.
DISABLE_ADMIN_LOGIN N The flag which determines if
Adminstration services are disabled.
DISABLE_WORKSPACE_LOGIN N The flag which determines if
workspace login is disabled.
ALLOW_DB_MONITOR Y The flag which determines if database
monitoring is enabled.
SERVICE_REQUEST_FLOW MANUAL Determines default provisioning


Unbreakable Materialized Views

Oracle8i: Invalidating a Materialized View

Dependencies related to materialized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view depends on the detail tables referenced in its definition. Any DDL operation, such as a DROP or ALTER, on any dependency in the materialized view will cause it to become invalid.

Oracle9i: Invalidating Materialized Views

Dependencies related to materialized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view depends on the detail tables referenced in its definition. Any DML operation, such as a INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.

Oracle10g: Invalidating Materialized Views

Dependencies related to materialized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view depends on the detail tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.


In 9i and 10g, a materialized view becomes automatically invalid when performing DML or DDL. Don't worry:

A materialized view is automatically revalidated when it is referenced. In many cases, the materialized view will be successfully and transparently revalidated.

Suppose you have a materialized view on EMP and your application aborts immediate after logon when invalid objects are found. The "early bird" user modifies the EMP table and all others users cannot start the application anymore. I hear the sound of a ringing phone...


Oracle Drive and Oracle Express

I read this post about Oracle Drive. Oracle Drive is a WebDav (Web-based Distributed Authoring and Versioning) client for use in an Oracle Portal environment. I installed the client and used my Oracle Express (XE) database as server. I mapped the N: drive to

Now I can use the following features:

  • I can work off line, I can change files while I am not connected with the database i.e. database is down or when I work at a remote location. Synchronization takes place when you connect to the database.

  • I can schedule a backup of Windows files, this backup copies the files (without compression) to the WebDav folder, the files are stored within the database.

  • I can put a lock on a file within a WebDav folder, other users can not change this file.

Oracle Drive works with XE and has some "nice to have" features at the cost of a little memory usage. Personally I like the built in Windows Explorer as described in this post a lot more.


Mail from Application Express again

I used a beta version of Oracle XE. The DMBS_EPG gateway did not work at all. So I decided to reinstall Oracle XE (production). After reading a post on the Application Express (APEX) forum I recreated my sendmail application from scratch. Everything went fine, except the litte detail of sending email did not work anymore. My first solution was to adjust the smtp hostname in the package body but still no mail.

Have look at the comments in the package specification of htmldb_mail.

-- Parameters p_smtp_hostname and p_smtp_portno remain for backward
-- compatibility. But they are ignored. The SMTP host name and
-- parameter are exclusively derived from system preferences
-- when sending mail.
procedure push_queue( p_smtp_hostname in varchar2 default null,
p_smtp_portno in varchar2 default null );

The SMTP hostname and portnumber are ignored while calling push_queue. The real settings are derived from system preferences.

I have never heard of system preferences before, people always talk about database parameters. And because htmldb_mail depends on utl_smtp, I thought that the startup parameter smtp_out_server did not have the correct value. In fact, the parameter value was null and after setting it to the correct value, the mail was still not sent.

It took a while surfing to find the system preferences. You can check these preferences as follows.
SELECT wwv_flow_platform.get_preference('SMTP_HOST_ADDRESS') FROM dual
SELECT wwv_flow_platform.get_preference('SMTP_HOST_PORT')FROM dual
In APEX you can set these preferences in the main menu of application builder and choose the task email configuration.
The following screen appears:

You can set the smtp hostname and portnumber in the email preferences. Or you can manage the mail queue (the mail queue is like the outbox in your mail client) or you can manage the email log. When an item is sent, the header data is moved from the queue into the log.

Finally, the sendmail application works fine.


Mail from Application Express

On the OTN forum Application Express (APEX) somebody asked: how to send mail from a report. Assume you have a report with all employees, you can select one or more rows , push a button and your mail is sent. It takes just a few steps!

The first step is to create a page with a report region, a submit button, an after submit process and an after processing branch. In the after submit process we call a database package p_send_mail. Make sure the package specification exists before creating the process otherwise you will not be able to save your process.

The package specification is:

PROCEDURE submit(p_cb_arr wwv_flow_global.vc_arr2);
We will come back on this package later.

Building the report

We build a simply report based on the query.
SELECT ename                         ename_ro
, htmldb_item.checkbox(1,ename) ename_cb
FROM emp

The first column ename_ro displays the ename as a normal report field. The second column ename_cb displays a checkbox. The second column uses the function htmldb_item.checkbox. The first argument 1 is the form element name. The second argument is the value returned by the form element when checked. When the page is submitted APEX composes an array of employee names. The name of this array is htmldb_application.g_f01 (g_f01 corresponds with the form element name 1) and it only contains the employee names of the checked rows.

Create a submit button and an 'On submit - after Computations and Validations' process. Set the value of the when button pressed attribute to the name of the submit button. Don't forget to set the messages as well. Set the process source to:
p_send_mail.submit(p_cb_arr =>htmldb_application.g_f01);

The procedure submit has an array of employee names as input. In the next paragraphs we will explain the package body.

Implementing the package body

The code of the procedure submit:

PROCEDURE submit(p_cb_arr wwv_flow_global.vc_arr2)
IF p_cb_arr.COUNT > 0 THEN
FOR i IN p_cb_arr.FIRST .. p_cb_arr.LAST
send_mail(p_to => con_email_to,
p_body => 'Employee ' || p_cb_arr(i) || ' selected.',
p_subj => 'Mail from Application Express');
-- In the case there are no checkboxes checked inform the user
RAISE e_all_cb_unchecked;
END submit;

In the procedure submit we check first If there are 0 elements in the array this means that none of the check boxes is checked. In this case raise an error to inform the user that there is no mail sent. Otherwise call for every employee the procedure send_mail.

The code for the procedure send_mail is:

PROCEDURE send_mail(p_to IN VARCHAR2, -- receiver
p_body IN VARCHAR2, -- email body (text)
p_subj IN VARCHAR2) -- subject
-- Place your email in the outbox.
htmldb_mail.send(p_to => p_to,
p_from => con_email_from,
p_body => p_body,
p_subj => p_subj);
-- Send you email(s) now
htmldb_mail.push_queue(p_smtp_hostname => con_smtp_hostname,
p_smtp_portno => con_smtp_portno);
END send_mail;

APEX provides the HTMLDB_MAIL package. We use two procedures send and push_queue. The procedure send adds an email message to the queue. And procedure push_queue sends all messages in the queue immediately.

Besides the procedures the package body contains some constants. You will have to set these constants:
  • email_to is the receiver of the emails i.e scott@acme.com.

  • smtp_hostname is the SMTP hostname

  • smtp_portno is the portnumber of the SMTP host, default 25

The complete code of the package body is below and after compiling the package body it time to email!

Update: after reinstalling my Oracle XE database I discovered that Oracle changed the behaviour a little bit. Please read Mail from application express again as well.

-- Change these settings
-- receiver
con_email_to constant VARCHAR2(2000) := 'aaa@bbb.cc';
-- sender
con_email_from constant VARCHAR2(2000) := 'XE';

-- You find the SMTP values in the account settings of your email client.
con_smtp_hostname constant VARCHAR2(2000) := 'smtp.xxx.yy';
con_smtp_portno constant VARCHAR2(4) := '25';

e_all_cb_unchecked EXCEPTION;

-- A simple procedure to create and send an email message.
PROCEDURE send_mail(p_to IN VARCHAR2, -- receiver
p_body IN VARCHAR2, -- email body (text)
p_subj IN VARCHAR2) -- subject
-- Place your email in the outbox.
htmldb_mail.send(p_to => p_to,
p_from => con_email_from,
p_body => p_body,
p_subj => p_subj);
-- Send you email(s) now
htmldb_mail.push_queue(p_smtp_hostname => con_smtp_hostname,
p_smtp_portno => con_smtp_portno);
END send_mail;

-- This procedure is called from the page process.
-- It takes as argument an array with employee names
-- For each checked employee an email is send.
PROCEDURE submit(p_cb_arr wwv_flow_global.vc_arr2)
IF p_cb_arr.COUNT > 0 THEN
FOR i IN p_cb_arr.FIRST .. p_cb_arr.LAST
send_mail(p_to => con_email_to,
p_body => 'Employee ' || p_cb_arr(i) || ' selected.',
p_subj => 'Mail from Application Express');
-- In the case there are no checkboxes checked inform the user
RAISE e_all_cb_unchecked;
END submit;
END p_send_mail;



APEX is the short name for Application Express, the successor of html-db. I've searched blogs with the keyword Apex. Type apex in the Blogger NavBar (the small blue bar at the top of this page) and click [SEARCH ALL BLOGS]. Do not forget to read the rest of this blog!

One of the results is Apex SQL Log 2005.03. Apex SQL Log is a powerful SQL database auditing and recovery tool that analyzes SQL Server's own Transaction Log to display information on data changes, including user (nt user), computer and application where the change originated. On the same page you find the slogan ApexSQL SQL Developer Essentials. "SQL Developer", I've heard that name before...