Which APEX am I logging into?

I’ve got two computers at home both running APEX, and when I log into APEX the page looks exactly the same – except for the IP address in the URL, which is just slightly different. If you’re forgetful like me it’s easy to confuse which machine I’m logged in to. Here’s a tip:

Login to apex_admin on each machine in turn and click Manage Service, then Messages. Then select a Custom Message for the Login Message and/or a System Message, and enter the name of the machine, or some other message that will remind you which machine it is. The message will be shown whenever you see the login screen, and when you login to APEX.

If you find the text isn’t visible enough, you can insert HTML into the message, e.g. <B>Development</B>.

Un-riching Rich Text Format

Let’s just call it Legacy because I’m not going to say what the source is. It is a single-user desktop application that after a little investigation (i.e. searching the online forum for the app) was found to be storing its data in tables readable by MS Access. I wanted to get at this data, analyze it, maybe do some smart things to it, and then present it via Apex.

Step 1: Get the data into Oracle.

Simple matter of exporting from MS Access via ODBC. At least, it was simple once I replaced the Oracle ODBC drivers with the latest download from OTN. Before that I was getting a number of annoying TNS errors.

Step 2: Transform the data.

Most of the tables are easy-to-understand normalized relational tables. One of them, however, has a column that came through as a CLOB containing strange values like this:

{\fonttbl{\f0\fnil\fcharset0 Arial;}
{\f1\fnil\fcharset0 Verdana;}}{\colortbl\red0\green0\blue0;\red255\green0\blue0;\red0\green128\blue0;\red0\green0\blue255;\red255\green255\blue0;\red255\green0\blue255;\red128\green0\blue128;\red128\green0\blue0;\red0\green255\blue0;\red0\green255\blue255;\red0\green128\blue128;\red0\green0\blue128;\red255\green255\blue255;\red192\green192\blue192;\red128\green128\blue128;\red255\green255\blue255;}

Now based on my knowledge of the application I knew that this column was used to store small pieces of text (typically 8 to 30 short lines), with some amount of formatting (e.g. fonts, alignment, etc.). Again the online forum came in useful in that a side comment from one of the developers (regarding a small bug undocumented anti-feature) revealed that they stored the formatted text as RTF – Rich Text Format. Should have known from the opening 6 bytes in the data.

Somewhere in these oceans of rtf codes were swimming the plain text I craved. So Googled RTF, skimmed this old RTF specification, and ended up with this admittedly poorly-performing PL/SQL, which for the 651 rows in this table, each with an RTF of average 3KB, works just well enough for my purposes. As it turned out the only RTF codes I was interested in were \fcharset and \*, both of which I used to ignore bits of text I didn’t want in my output. Oh and \par, which denotes the end of a paragraph. I can run this script once a month on the freshly exported data and apply the full weight of Oracle’s analytic capabilities on it.

The code below exemplifies the use of a pipelined function. This is not a good idea, by the way, if you want to use it in regular queries, e.g. a view. In my case, however, I only wanted to call this from within PL/SQL, and then only once a month. Because of the way parameters work with functions like this, I had to call it with dynamic SQL (execute immediate).

That was kind of fun, but I’d rather not have to deal with RTF ever again, thank you.

create or replace package myutil_rtf is
  type t_v4000_table is table of varchar2(4000);
  function extract_text (p_recid in number)
  return t_v4000_table pipelined deterministic;

create or replace package body myutil_rtf is
  function extract_text (p_recid in number)
    return t_v4000_table pipelined deterministic is
    l_ch varchar2(1);
    l_ctrl varchar2(4000);
    l_line varchar2(4000);
    l_rtf clob;
    --don't output any text between
    --\fcharset and closing }
    l_fcharset boolean := false;
    --increments for each enclosed pair of { }
    --within a discard section
    l_discard number;
    select rtf_clob into l_rtf
    from rtf_table where recid = p_recid;
    for i in 1..dbms_lob.getlength(l_rtf) loop
      l_ch := substr(l_rtf,i,1);
      if l_ch = '}' then
        if l_fcharset then
          --closing } found; re-enable output
          l_fcharset := false;
          l_line := null;
        end if;
        if l_discard &gt; 0 then
          l_discard := l_discard - 1;
          if l_discard = 0 then
            l_discard := null;
          end if;
        end if;
      elsif l_ch = '{' then
        if l_discard is not null then
          l_discard := l_discard + 1;
        end if;
      elsif l_ch = '\' then
        --controls start with a backslash
        l_ctrl := '\';
      elsif l_ctrl is not null then
        --controls are always ended by some
        --non-alphanumeric character
        if instr('abcdefghijklmnopqrstuvwxyz'
        || '0123456789',lower(l_ch)) &gt; 0 then
          l_ctrl := l_ctrl || lower(l_ch);
          if l_ctrl = '\par' then
            pipe row (l_line);
            l_line := null;
          elsif substr(l_ctrl,1,9) = '\fcharset' then
            l_fcharset := true;
          elsif l_ctrl || l_ch = '\*' then
            --{\* ... } means you can ignore
            --anything between the { }
            if l_discard is null then
              l_discard := 1;
            end if;
          end if;
          l_ctrl := null;
        end if;
      elsif l_ch not in (chr(10), chr(13), '{')
        and not l_fcharset and l_discard is null then
        l_line := l_line || l_ch;
      end if;
    end loop;
    if l_line is not null and not l_fcharset then
      pipe row (l_line);
    end if;
  end extract_text;
end myutil_rtf;

To extract the text from the table with recid=1:

select column_value line_of_text
      ,rownum line_number
from table(myutil_rtf.extract_text(1));

Do you know the difference between USER_TAB_COLS & USER_TAB_COLUMNS?

I just noticed the difference – I was wondering why I couldn’t see the virtual columns (i.e. columns that support function-based indexes) on a table, and it was because I was querying USER_TAB_COLUMNS. Once I used USER_TAB_COLS instead there they were.

The difference is noted in the documentation (Oracle 10g Release 2 (10.2)) for USER_TAB_COLS and ALL_TAB_COLS but not for USER_TAB_COLUMNS or ALL_TAB_COLUMNS, unfortunately.

Replace the default XDB welcome page

I was annoyed that to get to my main PL/SQL page I have to type in a full URL like this:


(e.g. “mydad” could be “apex” for Oracle Apex)

If I was using Apache HTTP Server I’d do something this article suggests. But I’m using Oracle’s Embedded PL/SQL Gateway.

A. I got rid of the “:7777” by changing the HTTP port to 80, e.g.:

SQL> exec dbms_xdb.sethttpport(80);

Now, I can get to it without the port number:


B. Now I want to remove the need to remember to type “home”. To do this, I just tell the DAD what the default page is:

SQL> exec dbms_epg.set_dad_attribute('MYDAD','default-page','home');

Now, the url is a bit simpler:


The URL is now rewritten automatically to point to “mydad/home”.

C. Finally, I want to remove the need to specify the DAD. To do this is a little more complicated. I’ll create an XDB resource that will override the default XDB navigator that comes up.

    1. Log into Enterprise Manager


    1. Open the “Administration” tab and select “Resources” under “XML Database”


    1. Click “Create” and set the fields as follows:
      Owner = SYS
      Name = index.html
      Location = /
      Type = XML Database Resource File
      Source = Specify the file contents
      Contents =
      <html><head><meta http-equiv="REFRESH" content="0; URL=http://host/mydad"></head><body><a href="http://host/mydad">Home</a></body></html>


  1. Click “Ok”

(you’ll need to change “host” and “mydad” to appropriate values in the sample Contents above)

Now, the url is simply:


This causes it to load the index.html page from the XML database, which redirects to the DAD, the default page for which is “home”.

APEX 3.0 via Embedded PL/SQL Gateway

I managed to get APEX 3.0 working on Oracle using the embedded PL/SQL gateway (i.e. without installing Apache), contrary to the advice given here. I used apex_epg_config instead of apex_epg_config11. For it to work, however, I had to remove some security on XDB so it’s not a recommended option. This involved modifying my xdbconfig.xml to allow anonymous access to XDB using this:

  configxml SYS.XMLType;
  SELECT INSERTCHILDXML(xdburitype('/xdbconfig.xml').getXML(),
  INTO configxml FROM DUAL;

Now for some general tips:

Tip #1. List Your DAD Authorizations

As described so well in the excellent article DBMS_EPG – The Embedded PL/SQL Gateway in Oracle 10g Database Release 2, you can authorize and deauthorize a DAD using the DBMS_EPG package. It should be noted that dropping a DAD will not remove the authorizations as well, so you need to deauthorize it as well. To find out what authorizations have been made on your database, you can query the DBA_EPG_DAD_AUTHORIZATION view, e.g.:

SQL> select * from dba_epg_dad_authorization;

-------- ---------

1 row selected.

There’s also a USER_EPG_DAD_AUTHORIZATION view which shows the DAD_NAME for authorized DADs for the current user.

Tip #2. List Your DADS

You can find out what DADs have been set up on your instance using the DBMS_EPG package. There is a bug, however, that causes dbms_epg.get_all_dad_attributes to return nothing, so this script gets each attribute separately with dbms_epg.get_dad_attribute. The script lists all the DADS, their mappings and their attributes.

set serveroutput on

PROMPT list all dads
  blank       dbms_epg.varchar2_table;
  dad_names   dbms_epg.varchar2_table;
  paths       dbms_epg.varchar2_table;
  procedure show_dad_attribute(dad in varchar2, attr in varchar2) is
    val varchar2(4000);
    val := dbms_epg.get_dad_attribute(dad, attr);
    if val is not null then
      dbms_output.put_line('... ' || attr || '=' || val);
    end if;
  if dad_names.count > 0 then
    for i in dad_names.first..dad_names.last loop
      paths := blank;
      dbms_epg.get_all_dad_mappings(dad_names(i), paths);
      if paths.count > 0 then
        for j in paths.first..paths.last loop
          dbms_output.put_line('... path=' || paths(j));
        end loop;
        dbms_output.put_line('... No mappings found');
      end if;
    end loop;
    dbms_output.put_line('No dads found');
  end if;

When the above script is run, you’ll get something like this:

... path=/apex/*
... authentication-mode=Basic
... database-username=ANONYMOUS
... default-page=apex
... document-path=docs
... document-procedure=wwv_flow_file_mgr.process_download
... document-table-name=wwv_flow_file_objects$
... nls-language=american_america.al32utf8
... request-validation-function=wwv_flow_epg_include_modules.authorize

APEX Tip: Page Auto Refresh

This tip provides your users with the option of choosing a refresh interval for the page. The following steps were tested with APEX version 2.2 but should work on earlier versions of APEX or HTMLDB.

  1. Create an Application Item to store the current refresh interval (e.g. F100_REFRESH_INTERVAL)
  2. Create an Application Computation to initialise it (e.g. F100_REFRESH_INTERVAL_COMP):
    • Computation Item = F100_REFRESH_INTERVAL
    • Computation Point = On New Instance (e.g. On Login)
    • Computation Type = Static Assignment
    • Computation = (default number of seconds, e.g. 60)
  3. Create a static List of Values (e.g. REFRESH_INTERVAL):

Display – Return
1 sec – 1
5 sec – 5
10 sec – 10
30 sec – 30
1 min – 60
5 min – 300
(you can change this list how you like, as long as the return values are positive integers)
(it is probably not a good idea to provide zero seconds as an option, as this will cause the page to continually refresh without giving the user much chance to intervene)

Now, for each page you wish to have auto-refreshed, edit the Page Properties:

  • HTML Header = <meta http-equiv="refresh" content="&F100_REFRESH_INTERVAL.">

Somewhere in your application you will want to provide the user with a way of modifying the refresh rate. I prefer to provide this on the same page that is auto-refreshed.
Create an Item (e.g. P1_REFRESH):

  • Display As = Select List with Submit
  • Region = (any region, I prefer the Breadcrumb region if one exists)
  • Label = Refresh
  • Source Used = Always, replacing any existing value in session state
  • Source Type = Item (application or page item name)
  • Maintain session state = Per session
  • Source value or expression = F100_REFRESH_INTERVAL
  • List of Values definition = REFRESH_INTERVAL

I also like to show the date/time when the page was last refreshed. To do this, I just add a display-only text item to the page with:

  • Source Used = Always, replacing any existing value in session state
  • Source Type = PL/SQL Expression or Function
  • Maintain session state = Per session
  • Source value or expression = TO_CHAR(SYSDATE,'Dy DD Mon HH24:MI:SS')

Now, the page will be auto-refreshed according to the default interval, and the user can change the refresh interval using the select list.

You can add the item and the auto-refresh header to any page you like. If you need a different refresh interval on different pages, you will need to create additional Application Items, along with their own Application Computations to initialise them.

If the user hits the “Stop” button in IE, the page seems to stop auto-refreshing. I don’t know if this feature works the same in other browsers, however.

Beware the wily SHOW_ALERT

What is the return value of SHOW_ALERT if the user doesn’t click one of the available buttons? According to the documentation (Forms 6i):

Displays the given alert, and returns a numeric value when the operator selects one of three alert buttons.
SHOW_ALERT (alert_id Alert);
SHOW_ALERT (alert_name VARCHAR2);
Returns a numeric constant corresponding to the button the operator selected from the alert. Button mappings are specified in the alert design.
If the operator selects… Form Builder returns

So for example, we can use this in code like this:

 btn NUMBER(2);
  -- Yes was chosen
  -- No was chosen
  -- Cancel was chosen

The alert can have two or three buttons, depending on whether you set the Button 3 Label property. For example, you could have an alert named MY_ALERT with the following settings:

Message: Do you want to save the changes you have made?
Button 1 Label: Yes
Button 2 Label: No
Button 3 Label: Cancel

If the user clicks Yes, No or Cancel, the function returns one of the three ALERT_BUTTON constants. Simple enough. But what if the user decides to be different and clicks the “X” (close window button)? Alternatively, what if the user presses the “Esc” key on their keyboard?

Testing with Forms 6 ( reveals that the answer depends on whether you have just two buttons defined, or all three. These results are probably the same on other versions, but you should test this on your version to make sure.

If you have three buttons, both the Close Window button and the “Esc” key cause SHOW_ALERT to return ALERT_BUTTON3. If you only have two buttons, the “Esc” key still returns ALERT_BUTTON3, for some reason, whereas the Close Window button returns ALERT_BUTTON2! The moral of the story is, make sure your code handles all three of the ALERT_BUTTON alternatives correctly! Also, make sure that the last button (2nd or 3rd) really does mean “cancel”. A very bad (hypothetical) example would be:

Message: What would you like to do?
Button 1 Label: Create a new record
Button 2 Label: Modify this record
Button 3 Label: Delete all these records

This post seems quite popular, so I thought I’d add what I think are good examples for handling alerts in Forms:

1. Two-button alert

Message: Do you want to XXX?
Button 1 Label: Yes
Button 2 Label: No
Button 3 Label: [blank]

 btn NUMBER(2);
  -- Yes was chosen
  -- No was chosen, or Close button clicked
  -- ESC key pressed

2. Three-button alert

Message: Do you want to XXX?
Button 1 Label: Yes
Button 2 Label: No
Button 3 Label: Cancel

 btn NUMBER(2);
  -- Yes was chosen
  -- No was chosen
  -- Cancel was chosen, or Close button clicked, or ESC key pressed

Generating test data that matches existing data

I’ve had to create test data a number of times, including data for tables that had mandatory foreign keys to existing tables. It was not feasible to just create new master rows for my test data; I wanted to refer to a random sample of existing data; but the code that generates the test data had to perform reasonably well, even though it had to pick out some random values from a very large table.

Solution? A combination of the new 10g SAMPLE operator, and DBMS_RANDOM. To illustrate:

(create a “very large table”)
SQL> create table t as
2 select rownum n, dbms_random.string(‘a’,30) v
3 from all_objects;

Table created.

SQL> select count(*) from t;


(get a random sample from the table)
SQL> select n, substr(v,1,30) from t sample(0.01)
2 order by dbms_random.value;

N SUBSTR(V,1,30)
———- ——————————
11852 xSsdmFtGqkymbKCFoZwUzNxpJAPwaV
8973 RGyNjqMfVayKdiKFGvLYuAFYUpIbCw
25295 eJJtoieSWtzUTIZXCbOLzmdmWHHPOy
1924 yZucJWgkFviAIeXiSCuNeUuDjClvxt
40646 wMTumPxfBMoAcNtVMptoPchILHTXJa

6 rows selected.

SQL> set serveroutput on

(Get a single value chosen at random)
SQL> declare
2 cursor cur_t is
3 select n from t sample(0.01)
4 order by dbms_random.value;
5 l number;
6 begin
7 open cur_t;
8 fetch cur_t into l;
9 dbms_output.put_line(l);
10 close cur_t;
11* end;
SQL> /

PL/SQL procedure successfully completed.

My test code would open the cursor, fetch as many values as it needed, and then close it. If the cursor ran out of values (e.g. the sample was too small for the desired amount of test data, which varied), my code just re-opened the cursor to fetch another set of random values from the large table.

The reason I sort the sample by dbms_random.value is so that if I only want one value, it is not weighted towards rows found nearer the start of the table.

Note: If I didn’t really care about the sample being picked at random from throughout the table, I could have just selected from the table “where rownum < n".