Introduction

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:

CREATE OR REPLACE PACKAGE QMS_APEX AS
  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;
END QMS_APEX;

Source package body:

CREATE OR REPLACE PACKAGE BODY QMS_APEX AS
  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 VARCHAR2
     IS
     BEGIN
         return gc_apex_used;
     END get_apex_used;
    PROCEDURE set_apex_on
     IS
     BEGIN
         gc_apex_used := 'J';
     END;
    PROCEDURE set_apex_off
     IS
     BEGIN
         gc_apex_used := 'N';
     END;
    FUNCTION get_message_tag_start
     RETURN VARCHAR2
     IS
     BEGIN
          return gc_message_tag_start;
     END get_message_tag_start;
    FUNCTION get_message_tag_end
     RETURN VARCHAR2
     IS
     BEGIN
          return gc_message_tag_end;
     END get_message_tag_end;
END QMS_APEX;

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
 is
     --
     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);
     --
 begin
    --
    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
    then
       --
       if not l_apex_used
       then
           l_error_display := 'Transaction failed' || chr(10); -- not needed for APEX
           l_next_line     := chr(10);
       else
           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'
          then
              l_error_display := substr(   l_error_display
                                        || case
                                              when i > 1 then l_next_line
                                           end
                                        || l_message_rectype_tbl(i).msg_code
                                        || ' '
                                        || l_message_rectype_tbl(i).msg_text
                                        ,1,4000);
          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
       then
           l_error_display := l_error_display || qms_apex.get_message_tag_end;
       end if;
       --
    end if;
    --
    raise qms$exception;
    --
 exception
    when others
    then
       raise_application_error(-20998,l_error_display);
 end RaiseQMSFailure;

The existing source:

procedure RaiseQMSFailure
 is
 begin
    raise qms$exception;
 exception
    when others
    then
           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’ (http://apexlib.oracleapex.info/) 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#
 </div>
 <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 != "")
  {
    vCookieCount++;
    vCookieContent = vErrorStack.substr(0, 4000);
    vErrorStack    = vErrorStack.substr(3999);
  document.cookie="ApexLibErrorStack"+vCookieCount+"="+encodeURIComponent(vCookieContent);
  }
  //----------------------------------------------------------------------------
  // 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.
  //----------------------------------------------------------------------------
  window.history.go(-1);
 } // 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'))
 {
   ApexLib_extractErrorPage();
 }
 else
 {
   document.getElementById("ApexLibErrorMessage").style.display="block";
 }
 </script>

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.