Forms Error Information

A HunBug Document
Document No.: 11370
Forms Version: 6i +
Last Updated: 01 Jul 2007
Author: HunBug

Most applications often have unexplained errors. These can often be because the user reports an error but does not provide enough information to research the possible cause. A screen capture just is not enough sometimes! After much wasted time investigating, these errors may have to be written off as network or Oracle anomalies. The potential of a more series issue can not be written off.

Here we look at logging the system information to a table, when triggered to do so by the user. Our example uses a common menu, ideally located under the help heading.

Table and Sequence

CREATE TABLE form_error_information
 (FORMERRORID      NUMBER(10,0) NOT NULL
 ,USERNAME         VARCHAR2(30) NOT NULL
 ,ENTRYDATE        DATE         NOT NULL
 ,DBERRORTEXT      VARCHAR2(250)
 ,ERRORCODE        NUMBER(10,0)
 ,ERRORTEXT        VARCHAR2(250)
 ,ERRORTYPE        VARCHAR2(3)
 ,FORMPARAMETER    VARCHAR2(250)
 ,FORMSETUPTYPE    VARCHAR2(30)
 ,SETROLES         VARCHAR2(250)
 ,BLOCKSTATUS      VARCHAR2(10)
 ,CURRENTFORM      VARCHAR2(50)
 ,CURSORITEM       VARCHAR2(75)
 ,CURSORRECORD     NUMBER(5,0)
 ,CURSORVALUE      VARCHAR2(250)
 ,FORMSTATUS       VARCHAR2(10)
 ,MESSAGELEVEL     NUMBER(3,0)
 ,SYSMODE          VARCHAR2(20)
 ,RECORDSTATUS     VARCHAR2(10)
 ,CALLINGFORM      VARCHAR2(50)
 ,DEFAULTWHERE     VARCHAR2(250)
 ,TOPRECORD        NUMBER(5,0)
 ,PRIMARYKEY       VARCHAR2(100)
 ,LASTQUERY        VARCHAR2(2000)
 ,BLKLASTQUERY     VARCHAR2(2000)
 ) 
/

CREATE SEQUENCE dsq_formErrorId START WITH 1000000 INCREMENT BY 1 NOCACHE NOCYCLE;

On-Error Trigger

To gather the error information, the following needs to be added to the on-error trigger. Four parameters need to be created in each form to store the error information. Package level variables or global variables could be used instead.

DECLARE

  v_dbErrorText   VARCHAR2(250) := SUBSTR(DBMS_ERROR_TEXT,1,250);
  v_errorCode     NUMBER        := ERROR_CODE;
  v_errorText     VARCHAR2(250) := SUBSTR(ERROR_TEXT,1,250);
  v_errorType     VARCHAR2(3)   := SUBSTR(ERROR_TYPE,1,3);

  ...

BEGIN

  --//---------------------------------------------------------------

  COPY(v_dbErrorText,'PARAMETER.dbErrorText');
  COPY(v_errorCode,  'PARAMETER.errorCode');
  COPY(v_errorText,  'PARAMETER.errorText');
  COPY(v_errorType,  'PARAMETER.errorType');

  --//---------------------------------------------------------------

  ...

END;
 
It is assumed you already have an on-error trigger. If not you would need to add code to handle your errors, otherwise they will be ignored.

Menu Trigger

The following code resides in the menu item trigger. It sets values as constants that can not be referenced directly at the insert. It then loops through the cursor block to build the primary key value. It is assuming that the primary key items have the primary key property set to true. It then builds a list of the session roles. All of the data is then inserted into the FORM_ERROR_INFORMATION table, with the id being displayed to the user for reference.

DECLARE

  c_dbErrorText     CONSTANT VARCHAR2(250) := NAME_IN('PARAMETER.dbErrorText');
  c_errorCode       CONSTANT NUMBER        := NAME_IN('PARAMETER.errorCode');
  c_errorText       CONSTANT VARCHAR2(250) := NAME_IN('PARAMETER.errorText');
  c_errorType       CONSTANT VARCHAR2(3)   := NAME_IN('PARAMETER.errorType');
 
  c_callingForm     CONSTANT VARCHAR2(30)  := GET_APPLICATION_PROPERTY(CALLING_FORM);
  c_defaultWhere    CONSTANT VARCHAR2(250) := SUBSTR(GET_BLOCK_PROPERTY(:SYSTEM.CURSOR_BLOCK,DEFAULT_WHERE),1,250);
  c_lastQuery       CONSTANT VARCHAR2(250) := SUBSTR(GET_BLOCK_PROPERTY(:SYSTEM.CURSOR_BLOCK,LAST_QUERY),1,250);
  c_topRecord       CONSTANT NUMBER        := TO_NUMBER(GET_BLOCK_PROPERTY(:SYSTEM.CURSOR_BLOCK,TOP_RECORD));

  l_formErrorId     NUMBER(10,0);
  l_primaryKey      VARCHAR2(100);
  l_sessionRoles    VARCHAR2(300);
  l_item            VARCHAR2(61);
 
BEGIN

  --//--
  --// Loop through block to build primary key value

  WHILE l_item IS NOT NULL LOOP
      l_item := :SYSTEM.CURSOR_BLOCK||'.'||l_item;
      IF  GET_ITEM_PROPERTY(l_item,ITEM_TYPE) IN ('TEXT ITEM','DISPLAY ITEM')
    AND GET_ITEM_PROPERTY(l_item,PRIMARY_KEY) = 'TRUE'
    THEN
      l_primaryKey := l_primaryKey||': '||NAME_IN(l_item)||' :';
    END IF;
    l_item := GET_ITEM_PROPERTY(l_item,NEXTITEM);
  END LOOP;

  --//--
  --// Build session roles
 
  FOR r IN ( SELECT role FROM session_roles ) LOOP
    l_sessionRoles := l_sessionRoles||': '||r.role||' :';
    IF LENGTH(l_sessionRoles) > 250 THEN
      l_sessionRoles := SUBSTR(l_sessionRoles,1,247)||'...';
      EXIT;
    END IF;
  END LOOP;

  --//--
  --// Select Sequence

  SELECT dsq_formErrorId.NEXTVAL INTO l_formErrorId FROM SYS.DUAL;

  --//--
  --// Insert data

  INSERT INTO form_error_information
  ( formErrorId
   ,username
   ,entryDate
   ,dbErrorText
   ,errorCode
   ,errorText
   ,errorType
   ,setRoles
   ,blockStatus
   ,currentForm
   ,cursorItem
   ,cursorRecord
   ,cursorValue
   ,formStatus
   ,lastQuery
   ,messageLevel
   ,sysMode
   ,recordStatus
   ,callingForm
   ,defaultWhere
   ,blkLastQuery
   ,topRecord
   ,primaryKey
  )
  VALUES
  ( l_formErrorId
   ,USER
   ,SYSDATE
   ,c_dbErrorText
   ,c_errorCode
   ,c_errorText
   ,c_errorType
   ,l_sessionRoles
   ,:SYSTEM.BLOCK_STATUS
   ,:SYSTEM.CURRENT_FORM
   ,:SYSTEM.CURSOR_ITEM
   ,:SYSTEM.CURSOR_RECORD
   ,:SYSTEM.CURSOR_VALUE
   ,:SYSTEM.FORM_STATUS
   ,:SYSTEM.LAST_QUERY
   ,:SYSTEM.MESSAGE_LEVEL
   ,:SYSTEM.MODE
   ,:SYSTEM.RECORD_STATUS
   ,c_callingForm
   ,c_defaultWhere
   ,c_lastQuery
   ,c_topRecord
   ,l_primaryKey
  );

  --//--
  --// Alert id to user
 
  lpk_alert.note('Your Form Error Id is: '||l_formErrorId);

END;
The package lpk_alert is used to show an alert. See the article on Alerts and Translations for details on this package.

Report

This report procedure outputs the data into a html table via the plsql cartridge. The same principles can be easily applied to an Oracle Form or Report if required.

CREATE OR REPLACE PROCEDURE

  --//--------------------
  --//  HunBug: 01/Jul/07
  --//--------------------

dpr_formErrorInformation
( p_formErrorId       IN NUMBER ) IS

  TYPE rc_info IS REF CURSOR;
  rcur_info           rc_info;

  l_info              VARCHAR2(32000);
  l_query             VARCHAR2(32000);

BEGIN

  --//--

  FOR t IN ( SELECT column_name
               FROM user_tab_columns
              WHERE table_name = 'FORM_ERROR_INFORMATION'
              ORDER BY column_id )
  LOOP
    l_query := l_query||'||''<TR><TD BGCOLOR="#FEFFCE">'||t.column_name
                      ||'</TD><TD BGCOLOR="#FFFFF4">''||'||t.column_name
                      ||'||''<BR></TD></TR>''';
  END LOOP;

  --//--

  l_query :=   'SELECT '||SUBSTR(l_query,3)||' x FROM form_error_information'
             ||' WHERE formErrorId = '||p_formErrorId;

  --//--

  htp.p('<HTML><BODY><H1> Form Error:'||p_formErrorId||' </H1>');
  htp.p('<TABLE CELLPADDING=0 BORDER=1 STYLE="{font-size:10.0pt;font-family:Arial;}">');

  OPEN rcur_info FOR l_query;
  LOOP
    FETCH rcur_info INTO l_info;
    EXIT WHEN rcur_info%NOTFOUND;
    htp.p(l_info);
  END LOOP;
  CLOSE rcur_info;

  htp.p('</TABLE></BODY></HTML>');

  --//--

END;
/

Enhancements

These examples can be modified to include application specific elements or even error management columns, so that they can be the base for an error management system.

Further Reading


HunBug Store








Comments are all individually read, none are automatically posted to the site.