Inside APEX it’s possible to specify ‘Page Items to Submit’:

  • Within the action of the type ‘Execute PL/SQL-code’ of a dynamic action.
  • Within a region of the type ‘Interactive report’ or ‘Classic Report’.

The listed page items will be submitted to the server and thus available to use within your ‘PL/SQL-code’ or ‘Source’.
When you’re using a page item the ‘Session State Protection’ of this item must be ‘Unrestricted’.  If you use another setting,  par example ‘Checksum Required – Application Level’, the following Javascript-error will occur:

Error: JSON.parse: unexpected character
Source: http://localhost:8585/i/javascript/apex_4_1.min.js
Line: 16

Uncaught SyntaxError: Unexpected token <      apex_4_1.min.js:16

Patrick Wolf blogged about this, but I’d like to have it available here as well:

If you get the error Workspace “[workspace name]” is inactive. Contact your administrator. when you try to login into your workspace or when you run an application, then you should run the following script to fix the status of your workspaces.

  • Connect as SYS, SYSTEM, APEX_040100 or any user who has the APEX_ADMINISTRATOR_ROLE role
  • Run the following PL/SQL script:
      for l_workspace in ( select short_name
                           from   apex_040100.wwv_flow_companies
                           where  account_status='AVAILABLE' )
      end loop;

Note: This problem only occurs for workspaces which have been created on the command line with the apex_instance_admin.add_workspace procedure. Workspaces which have been created through the UI should be fine.

The problem has been filed as bug# 13769526 and will get fixed in the next version of APEX. Thanks to Dimitri who notified us about the problem.

taken from: GREAT story 🙂


A friend dropped me a line saying her Apex app’s Interactive Report wasn’t working for her users anymore and she asked if I knew what could cause it. She said it worked for her in the Apex development environment, but not outside. I talked her into giving me an account on her Apex workspace and started digging through it.

For me in the developer environment, it wouldn’t do any of the JSON updates, like search for text, filter, change columns, sort, etc. – all the cool things we love about IRs. I’d seen this before, but it’d been so long I’d forgotten. I finally dug into the page template and noticed that there wasn’t much inclusion of external CSS going on and when I added that in, the report worked. So I sent her that as “fixed!” I couldn’t figure out how this application could have worked before, but had stopped!

Unfortunately, she wrote back saying that didn’t do it for someone running outside the development environment! Aaagh!

But I love a good challenge, so I dug back in. I got another session going in another browser so it was outside Apex’s environment. I noticed a difference between the two user sessions, that in the Development Environment, it showed my username on the top of the page, but not when I wasn’t. That was the hint I needed! It reminded me that the Interactive Reports store your queries by the APP_USER name into the database. No user name, no interactivity!

It turned out that this was a new application, she was using a custom authentication method that was new to her and she wasn’t setting the APP_USER value!

I’d used this same custom authentication method and found I needed to call

APEX_CUSTOM_AUTH.SET_USER(p_user => ‘some custom value’);

in the Post section of the authentication method.

Once that was set, everything worked great.

It was only after I told my friend the solution that she said that this was a new application she was building. Oops! If she’d said that up front, I might have figured out what was wrong sooner! :-/


By using Shared Components->Text Messages it’s possible to translate internal messages used by APEX.

See for a more detailed description of translating internal messages. The list that’s given is not complete.

How can you find the internal message you need to translate when it’s not in the list as mentioned above? You can find the internal message by looking in the table ‘WWV_FLOW_MESSAGES$’.


I wanted to translate the message ‘Password Expired’ that is issued by the procedure ‘apex_authentication.login’ (see the process ‘Login’ of the default login-page (101) that’s created when creating a new application) when a password is entered that is expired.

Because the message that I needed was not mentioned in the list I looked in the table ‘WWV_FLOW_MESSAGES$’. I found the message ‘PASSWORD_EXPIRED’ but that it’s not the message I must use to translate ‘Password Expired’. Instead of the message ‘PASSWORD_EXPIRED’ the message ‘F4500_P2613_EXPIRED’ should be used. So the table ‘WWV_FLOW_MESSAGES$’ contains two messages with the text ‘Password Expired’ and at first I was using the wrong one.

Another example:

To translate the hint-text ‘Calendar’ of the image of the datepicker you’ll find 4 messages that contains the text ‘Calendar’: ‘CALENDAR_TYPE’, ‘F4000.CALENDAR’, ‘WWV_FLOW_UTILITIES.CAL’ and ‘APEXIR_CALENDAR’. I’ve tried all four and ‘CALENDAR_TYPE’ is the message to use.

Recently (since 4.1 update) I’ve experienced issues when importing an application into an Apex environment.

After a little bit of searching I found the way to do the import on the command-line.

It is documented in the Apex documentation, but as you know I’d like this blog to be an “OPI” (one point of information) 🙂

Please have a look at the script, alter it whenever appropriate.

  -- the name of the workspace in wich to import
  t_workspace    varchar2(30):= 'MERIDA';
  -- an application number of an existing application in the workspace.
  t_existing_app number      := 100;
  -- the "new" application number, an existing number will be dropped first.
  t_new_app      number      := 100;
  -- security group id, you don't have to set this variable
  t_secgrp_id    number;
  -- get the Security Group ID
  select workspace_id
  into   t_secgrp_id
  from   apex_applications
  where  application_id = t_existing_app;

  -- Set the Security Group ID
  wwv_flow_api.set_security_group_id(p_security_group_id => t_secgrp_id);
  -- Set the Application ID to use
  -- This procedure generates the offset value used during application import.
  -- The offset value is used to ensure that the metadata for the Application
  -- Express application definition does not collide with other metadata on
  -- the instance. For a new application installation, it is usually sufficient
  -- to call this procedure to have Application Express generate this offset
  -- value for you.
  -- Set the parsing schema
  apex_application_install.set_schema( t_workspace );
  -- Set the application alias
  apex_application_install.set_application_alias( 'F' || t_new_app );
-- Do the actual import


That’s all folks 🙂

Creating a new page in an application I’m writing for a customer APEX gave me an error against the WWV_FLOW_UNIQUE_MENU_OPT constraint, after I hit the finish button in the last step of the wizard.. Now, that’s strange, why would APEX break on me in this situation?

I logged in as APEX_040100 to see and understand what actually went wrong.

The constraint mentioned is an index on the WWV_FLOW_MENU_OPTIONS table on columns MENU_ID and PAGE_ID. The page_id is the page_id as known in apex (1040) in my case. The menu_id seems to be some kind of identifier, in my case ‘6.69651082468548E15’.

Hmm.. what can we do to resolve this?

I think this index is in error.. shouldn’t the flow_id also be taken in consideration?

Apparently it is: the menu_id seems to be the same for all pages within the same flow.

Even after a restart of the database I’m not able to generate pages with the requested Page_id (1040).

After a bit of searching on the APEX forums at Oracle it ended to be a very logical problem.

As I often do for referential source data, I created a “Form on a table with report”. I tend to put both the report and the form on one page.

This is easy in maintenance. The report I put first and in column 1, the form I put in second and in column 2. This way I have an easy way of manipulating the source data.

However in my normal examples I don’t use breadcrumbs, and in this case I did. The wizard gives me the option, so why shouldn’t I?

The index is preventing me to insert two records in the table for those two breadcrumbs on one and the same table.

So the index IS correct afterall.

I do think that:

  • The wizard should detect that I’m using one page for both report and form and therefore only insert one breadcrumb
  • The wizard should warn me that I’m using one form AND breadcrumbs on bith pages.

Apex team? This probably will get a low priority, but the bug is ugly and easily to prevent.


For a client of me, the Ministry of Defence of Kingdom of The Netherlands I have developed a solution to use CDM RuleFrame with APEX. They are using CDM RuleFrame 6.5, Apex 4.0 and  the framework ‘ApexLib’.

CDM RuleFrame with APEX

If CDM RuleFrame is used and one or more business rules are violated on the APEX’s Error Page an error message will be shown that is very cryptic to the end user. Example:

‘ORA-20505: Error in DML: p_rowid=346, p_alt_rowid=TANKBON_ID, p_rowid2=, p_alt_rowid2=. ORA-20998: Transaction failed ORA-06512: at "DGP.QMS$ERRORS", line 184 ORA-06512: at "DGP.QMS_TRANSACTION_MGT", line 840 ORA-06512: at "DGP.CG$AUS_DGP_TANKBONNEN", line 64 ORA-04088: error during execution of trigger 'DGP.CG$AUS_DGP_TANKBONNEN'
 Unable to process row of table DGP_TANKBONNEN.’

The error message issued by CDM RuleFrame is ‘ORA-20998 Transaction failed’ (added with the (back)trace). The ‘real’ error messages are ‘stored’ in the database – in the same session that is used for the DML – and must be retrieved separately.

To retrieve the error messages from the database APEX will issue a new database connection and so it is not guaranteed that the same database connection is used and the correct error messages are retrieved. To avoid this problem, the ‘real’ error messages are added to ‘ORA-20998’ (without ‘Transaction failed’). To recognize the ‘real’ error messages easily they are surrounded by a start- (~QMS~) and end tag (|QMS|). Example:

‘ORA-20505: Error in DML: p_rowid=344, p_alt_rowid=TANKBON_ID, p_rowid2=, p_alt_rowid2=. ORA-20998: ~QMS~DGP-90301 Aantal liters moet groter of gelijk zijn aan 0. (344)<br >DGP-90302 : Kilometerstand moet groter of gelijk zijn aan 0. (344)|QMS| ORA-06512: at "DGP.QMS$ERRORS", line 172 ORA-06512: at "DGP.QMS_TRANSACTION_MGT", line 840 ORA-06512: at "DGP.CG$AUS_DGP_TANKBONNEN", line 64 ORA-04088: error during execution of trigger 'DGP.CG$AUS_DGP_TANKBONNEN'
 Unable to process row of table DGP_TANKBONNEN.’.

To achieve this some adjustments to CDM RuleFrame are necessary.

An additional advantage of this solution is that in par example SQL Plus the ‘real’ error messages are shown directly too. You do not have to retrieve the ‘real’ error messages anymore. However, the format of the error messages differs from the format that is used for APEX. So it is necessary that APEX ‘tells’ CDM RuleFrame that APEX is used.

Despite of the fact that the error message that is returned by CDM RuleFrame contains the ‘real’ error messages it is still not very readable for the end user. In addition, the error message is still shown on the ‘Error Page’ instead of on the page itself (like the other error messages issued by APEX). In order to solve this some adjustments to APEX are necessary.

Adjustments for CDM RuleFrame (database)

Create a package with the name ‘QMS_APEX’. This package contains several settings (par example the start- and end tag surrounding the error messages) for using CDM RuleFrame with APEX.

Source package specification:

  FUNCTION get_message_tag_start RETURN VARCHAR2;
  FUNCTION get_message_tag_end   RETURN VARCHAR2;
  FUNCTION get_apex_used         RETURN VARCHAR2;
  PROCEDURE set_apex_on;
  PROCEDURE set_apex_off;

Source package body:

  gc_apex_used         varchar2(1)  := 'N';
     gc_message_tag_start varchar2(10) := '~QMS~';
     gc_message_tag_end   varchar2(10) := '|QMS|';
    FUNCTION get_apex_used
         return gc_apex_used;
     END get_apex_used;
    PROCEDURE set_apex_on
         gc_apex_used := 'J';
    PROCEDURE set_apex_off
         gc_apex_used := 'N';
    FUNCTION get_message_tag_start
          return gc_message_tag_start;
     END get_message_tag_start;
    FUNCTION get_message_tag_end
          return gc_message_tag_end;
     END get_message_tag_end;

For this package – equals the other packages of CDM RuleFrame – a synonym with the same name (public or private) should be created and execute-privileges should be granted to the correct user(s) or role(s).

In addition, the existing procedure ‘QMS$ERRORS.RaiseQMSFailure’ should be changed as follows:

procedure RaiseQMSFailure
     l_apex_used           boolean := qms_apex.get_apex_used = ‘J’
     l_message_rectype_tbl hil_message.message_tabtype;
     l_message_count       number;
     l_raise_error         boolean;
     l_error_display       varchar2(4000);
     l_next_line           varchar2(10);
    cg$errors.get_error_messages(p_message_rectype_tbl => l_message_rectype_tbl
                                ,p_message_count       => l_message_count
                                ,p_raise_error         => l_raise_error
    if l_message_count > 0
       if not l_apex_used
           l_error_display := 'Transaction failed' || chr(10); -- not needed for APEX
           l_next_line     := chr(10);
           l_error_display := qms_apex.get_message_tag_start;
           l_next_line     := '<br >';
       end if;
       for i in 1..l_message_count loop
          if l_message_rectype_tbl(i).severity = 'E'
              l_error_display := substr(   l_error_display
                                        || case
                                              when i > 1 then l_next_line
                                        || l_message_rectype_tbl(i).msg_code
                                        || ' '
                                        || l_message_rectype_tbl(i).msg_text
          end if;
          -- Put the message back on the stack to preserve the error stack
          cg$errors.push(p_errorrec => l_message_rectype_tbl(i));
       end loop;
       if l_apex_used
           l_error_display := l_error_display || qms_apex.get_message_tag_end;
       end if;
    end if;
    raise qms$exception;
    when others
 end RaiseQMSFailure;

The existing source:

procedure RaiseQMSFailure
    raise qms$exception;
    when others
           raise_application_error(-20998,'Transaction Failed');
 end RaiseQMSFailure;

By calling several procedures of CDM RuleFrame from the procedure ‘QMS$ERRORS.RaiseQMSFailure’ for several functions/procedures compiling errors will occur regarding ‘PRAGMA RESTRICT REFERENCES’. Since Oracle 8i the ‘PRAGMA’ are not necessary anymore and so the ‘PRAGMA’ that are causing the compiling errors can easily be deleted.

It concerns the following procedures/functions:

  • QMS$ERRORS.RaiseQMSFailure
  • QMS$ERRORS.OldHeadstartException
  • QMS$ERRORS.internal_error
  • QMS$ERRORS.show_message
  • QMS$ERRORS.show_debug_info
  • QMS$ERRORS.unhandled_exception
  • QMS_PROFILE.get_profile_value
  • HIL_PROFILE.get_profile_value
  • QMS_MESSAGE.get_message
  • QMS_MESSAGE.SendDebugPipe
  • HIL_MESSAGE.get_message
  • HIL_MESSAGE.SendDebugPipe
  • CG$ERRORS.push (p_error_rec)
  • CG$ERRORS.QMSRecord2Stack

Adjustments for the APEX-application

The framework ‘ApexLib’ ( is used. By using ‘ApexLib’ error messages raised within the database are already shown on the page itself instead of on the ‘Error Page’. To achieve this ‘ÁpexLib’ use a ‘trick’: code is added to the ‘Error Page’ to retrieve any messages displayed on the ‘Error Page’ and store them in a cookie. After that the ‘Error Page’ is left to go back the page. On this page the messages stored in the cookie are displayed.

To retrieve the messages from the ‘Error Page’ ‘ApexLib’ has added the code displayed below to the section ‘Error Page Template Control’ of each Page-template used by the application. This code has been changed to extract the ‘real’ error messages from the error message issued by CDM RuleFrame; the modifications are marked with ‘// CDM RuleFrame’.

<div id="ApexLibErrorMessage" style="display:none">#MESSAGE#
 <a href="javascript:ApexLib_extractErrorPage()">#OK#</a>
<script language="javascript">
 function ApexLib_extractErrorPage()
  // CDM Ruleframe
 function extractMessage(pMessage)
    var vTagStart = "~QMS~";
    var vTagEnd = "|QMS|";
    var vMessage = pMessage;
    var vStart = pMessage.indexOf(vTagStart);
    if (vStart != -1)
      var vEnd = pMessage.lastIndexOf(vTagEnd);
      if (vEnd != -1)
        vMessage = vMessage.substring(vStart+vTagStart.length,vEnd);
    return vMessage;
  // CDM Ruleframe
  var vElementList = null;
  var vErrorStack  = null;
  // Find our ErrorPageMessage and ApexLibErrorMessage div elements.
  // Extract and store them in a cookie
  vElementList = window.document.getElementsByTagName("div");
  for (i=0; i < vElementList.length; i++)
    if ((vElementList[i].className == "ErrorPageMessage") ||
        (vElementList[i].className == "ApexLibErrorMessage"))
      vErrorStack=(vErrorStack==null?"":vErrorStack+"<br />")+vElementList[i].innerHTML;
  // store the error message in a cookie
  // CDM Ruleframe
  vErrorStack = extractMessage(vErrorStack);
  //CDM Ruleframe
  var vCookieCount   = 0;
  var vCookieContent = null;
  while (vErrorStack != "")
    vCookieContent = vErrorStack.substr(0, 4000);
    vErrorStack    = vErrorStack.substr(3999);
  // now go back the the previous page, on that page an onload event will check
  // if the cookie exists and paste it into the page.
 } // ApexLib_extractErrorPage
// If debug mode is enabled, don't do a redirect immediatly so that
 // the debug info isn't lost
 if (!html_GetElement('pdebug'))

N.B. If you change the start- and end tag in the package ‘QMS_APEX’ the function ‘extractMessage’ should be changed accordingly.

The ‘trick’ used by ‘ApexLib’, can also be used without using ‘ApexLib’:
– the code for the ‘Error Page’ can be equal to the code used by ‘ApexLib’.
– for an example of the code to retrieve the messages stored in the cookie and display them in the page take a look at the function ‘apexlib.error.handleErrorPage’ (and ‘apexlib.error.init’ for the initialization) in the ‘ApexLib’-library (Javascript-library). The call to these functions are added to page zero.

Finally the process ‘Set Apex on’ should be added to the application:
– process point: ‘On Submit: After Page Submission – Before Computations and Validations’
– type: ‘PL/SQL Anonymous Block’
– process text: ‘qms_apex.set_apex_on’

This process ensures that CDM RuleFrame knows that APEX is being used. When APEX is used CDM RuleFrame will format the error messages for APEX. Normally CDM RuleFrame will format the error messages to display them for example by SQL Plus.

While creating a simple test application, I stumbled upon a feature in apex that I wanted to share. This is not a solution mind you, just a description of what happens to help you think of a solution that will suit your needs.

Consider the following:

Let’s say that you are a regular user, trying to logon to an APEX application (4.0.1). And this application has the standard security features applied that are provided by apex. And because this user has been away for a long time (or any other reason you can think of), his account was locked by the application administrator.

Upon returning and logging on to Apex, the user gets following error message:

However the logon credentials provided were correct.

Question: what happened?

— Requesting the workspace id for the user…

— (if you find too many results just add ‘where user_name = <the apex user> ‘

Select user_name, workspace_id from apex_workspace_apex_users;

— Find out that this account is locked…

select user_name,account_locked from wwv_flow_users;

–attempt to unlock the account…


— Getting the just error code that you must be an administrator to unlock an account…

.ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 356
ORA-06512: at "SYS.HTP", line 1368
ORA-06512: at "SYS.HTP", line 1443
ORA-06512: at "SYS.HTP", line 1735
ORA-06512: at "APEX_040100.WWV_FLOW_ERROR", line 298
ORA-20987: User  requires ADMIN privilege to perform this operation.
ORA-06512: at "APEX_040100.WWV_FLOW_ERROR", line 693
ORA-06512: at "APEX_040100.WWV_FLOW_ERROR", line 1008
ORA-06512: at "APEX_040100.WWV_FLOW_FND_USER_API", line 76
ORA-06512: at "APEX_040100.WWV_FLOW_FND_USER_API", line 1740
ORA-06512: at "APEX_040100.HTMLDB_UTIL", line 1696
ORA-06512: at line 3


If you have a user with admin privileges then it is quite possible to unlock your own account, or any other, using SQL*PLUS

If you are a regular user and your account is locked, then basically any action you try to perform in the database, will be prevented by an error message “invalid logon credentials”.

This is a one-on-one copy of an item blogged by Scott Splendolini


Earlier today, I tweeted the following:

After this morning, I don’t think I will ever use the “v” function again. #orclapex

I wanted to qualify what I meant by that, since sometimes you only see one side of the conversation on Twitter. Also, it’s been a while since my last post, so this give me the opportunity to remedy that as well. The APEX “v” function works, and works quite well. For those who have not used it, the “v” function is an APEX-specific function that when you pass an APEX item to it, it will return the value of that item for a specific user session. What’s cool about it is that it also works from named PL/SQL program units, as long as they were initiated from an APEX session. Thus, you can write a PL/SQL package that takes in few, if any parameters and still can refer to items that are set in the APEX session state via the “v” function:

  l_customer_name VARCHAR2(255) := v('P1_CUSTOMER_NAME');
END; /

The specific issue that I had was that I did used the “v” function in quite a few places across a suite of PL/SQL packages. It cut down on what I needed to pass from package to package, and even allowed me to omit some procedures from the package specification. It worked magnificently. That is, until I tried to call one of the packages from SQL*Plus. Since there is no APEX session context set in SQL*Plus – and even if there was, which you can do, the items that I required to be set would not be – my package failed spectacularly. Thus, I had to go back through several packages and retro-fit them to be APEX-agnostic and remove all traces of the “v” function in favor of parameters. The lesson to learn from this is simple: take some time to consider whether or not you think a block of code will ever be called from outside of APEX. Even if there is a remote chance that it will, it may pay off big time later if you choose to make that code APEX-agnostic and rely on parameters instead.


<EDIT sept 2, 2011>

Today oracle released the definitive version of Oracle XE 11.2. I haven’t had the opportunity to test that version against my blogs, but I’ll do that shortly. stay tuned


In addendum on my installation series (OraXE11 on CentOS5.5) here my experiences on doing the same on CentOS6

Please be informed that Oracle is not supported on CentOS (let alone CentOS 6) !!

Swap area

When doing the installation on a virgin system (CentOS 6), I discovered that XE now requires a 2Gb swap area. Searching around I found this. It coveres an installation of XE on Ubuntu, but I only used the swap suggestion.

cat /proc/meminfo

That should give you info on the current status.

look at SwapTotal and SwapFree

SwapTotal:       1048564 kB
SwapFree:        1048564 kB

To install a 1 GB swapfile named swapfile in /, for example:

dd if=/dev/zero of=/swapfile bs=1024 count=1048576

This may take a while. After it completes, issue:

mkswap /swapfile
swapon /swapfile
cp /etc/fstab /etc/fstab.orig
echo '/swapfile swap swap defaults 0 0' >> /etc/fstab

You can check it too:

swapon -a
swapon -s

You’re ready to do the actual installation.


First you need to make sure libaio and are installed.

yum install libaio bc

And do the installation

mkdir OracleXE
unzip -d OracleXE
cd OracleXE
rpm -ivh oracle-xe-11.2.0-0.5.x86_64.rpm

Oracle now needs to get configured:

/etc/init.d/oracle-xe configure

Specify the HTTP port that will be used for Oracle Application Express [8080]: <- ENTER
Specify a port that will be used for the database listener [1521]: <- ENTER
Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration: TYPE YOUR PASSWORD
Confirm the password: TYPE YOUR PASSWORD
Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]: <- ENTER

Starting Oracle Net Listener...Done
Configuring Database...

This takes a looooong while

Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

You can now continue at step 4 in step 1 of the installation guide

That’s it folks