This discussion is archived
3 Replies Latest reply: Feb 14, 2006 1:17 AM by 119596 RSS

FRM-40735:Pre_Insert trigger raised unhandled exception ORA-20011

490315 Newbie
Currently Being Moderated
When I try to insert a new record I get the above message. And when I go to Dispaly Error I then get this other message: FRM-42100: No errors encountered recently. Can someone who has experienced or solved this problem please come to my assistance.
  • 1. Re: FRM-40735:Pre_Insert trigger raised unhandled exception ORA-20011
    396386 Newbie
    Currently Being Moderated
    Do you have insert triggers associated with the table or any other tables popluated during the pre-insert processing. This error looks like one which is designed by your development team.
  • 2. Re: FRM-40735:Pre_Insert trigger raised unhandled exception ORA-20011
    user346369 Expert
    Currently Being Moderated
    Here is part of my standard PLL_On_Error trigger that is called by every form. It displays the full message if you get the FRM-40735 error. To simplify it, I have replaced calls to several PLL alert and message routines with Err_Msg.

    <p>Edit: In addition, I have posted a related PLL_On_Message procedure here: Re: :system.message_level ...???
    PROCEDURE PLL_ON_ERROR(ERR_IN IN NUMBER   DEFAULT NULL,
                           MSG_IN IN VARCHAR2 DEFAULT NULL) IS           --*
    -----------------------------------------------------------------------*
    -- PLL_On_Error overrides default form error processing.  Usually
    -- this is called from a form-level on-error trigger with the command
    --    PLL_ON_ERROR;
    -- However, if the form requires additional error processing, the
    -- following method can be used:
    /*
    DECLARE
      Err_Code NUMBER(5)     := ERROR_CODE;
      MSG     VARCHAR2(150)
              := SUBSTR('   '||ERROR_TYPE||'-'||TO_CHAR(Err_Code)||': '
                                         ||ERROR_TEXT,1,150);
    BEGIN
      IF Err_Code=<special value here> THEN
          Message('   <special message, etc. here> ');
          Raise Form_Trigger_Failure;
      ELSE
        PLL_ON_ERROR(Err_Code,MSG);
      END IF;
    END;
    ----------------------------------------------------------------------*/
      Err_Code NUMBER(5)     := NVL(ERR_IN,ERROR_CODE);
      Err_Text VARCHAR2(100) := ERROR_TEXT;
      MSG      VARCHAR2(255) := NVL(MSG_IN,SUBSTR('   '||ERROR_TYPE||'-'
                               ||TO_CHAR(Err_Code)||': '||Err_Text,1,150));
      DBMS_TXT VARCHAR2(500);
      subcode  varchar2(5);
      --
      Procedure Err_Msg(M1 in varchar2) is begin
        Message(M1);
        Raise form_trigger_failure;
      End Err_Msg;
      --
    BEGIN
      IF Err_Code IN (40401,40405,     --No changes to save/apply
                      40100,40352) THEN --at first/last record
        MESSAGE(MSG,NO_ACKNOWLEDGE); -- Don't raise Form_Trigger_Failure
      Elsif Err_Code between 50002 and 50026
        and Err_Code - 50000 in(2,3,4,12,17,18,19,21,22,23,25,26) then
        -- Format date input error messages
        -- 50002: Month must be between 1 and 12.
        -- 50003: Year must be 00-99 or 1000-4712
        -- 50004: Day must be between 1 and last of month.
        -- 50012: Date must be entered in a format like <fxMMDDRR>
        -- 50017,18,19: Hour,Min,Sec must be between 0 and 23,59,59.
        -- 50025: Date/time must be entered in a format like <xxyytttt>
        -- 50026: same as 50012 and 50025 with <yyyy> year.
        Declare
          Fmt varchar2(30)
            := replace(replace(replace(replace(
                 upper(GET_ITEM_PROPERTY(
                                         Name_in('SYSTEM.TRIGGER_ITEM'),
                                         FORMAT_MASK)),
                'RR','YY'),'FX',''),'HH24','hh'),'MI','mm');
          Itm  varchar2(30);
          Msg1 varchar2(100);
          Msg2 varchar2(30);
        Begin
          Itm := Substr(Name_in('SYSTEM.TRIGGER_ITEM'),
                        instr(Name_in('SYSTEM.TRIGGER_ITEM'),'.')+1);
          Itm := replace(Itm,'_',' ');
          Msg1 := '   0012  Invalid value in '|| Itm ||'.   ';
          If fmt = 'YY' then
            Err_Msg(Msg1||'Enter a two-digit year between 00 and 99.');
          Else
            Msg2 := substr(Err_Text,1,instr(Err_Text,' ')-1);
            If upper(Msg2) in ('MONTH','DAY','YEAR','HOUR','MINUTES') then
              Msg1 := Msg1 ||'Error in ' || Msg2 ||' --  ';
            end if;
            Err_Msg(Msg1 || 'Format is ' || Fmt );
          End if;
        End;
      ELSIF Err_Code BETWEEN 40200 AND 40299 --field errors detected by Form
         OR Err_Code between 40600 and 40699 --record locking errs
         OR Err_Code = 40108 -- No such form
         OR Err_Code > 50000 THEN
        Err_Msg(MSG);
      ELSIF Err_Code between 40100 and 40199 -- Forms navigational errs.
         OR Err_Code IN
         (40501,       --unable to reserve rec for updt/delete (ORA-00054)
          41008,       --undefined fn key(dbms_txt=ora-01003:no stmt parsed)
          47013,47021) --no such parameter errs.
        THEN -- display msg on status line.  Don't check dbms_txt below
        Err_Msg(MSG);
      ELSIF Err_code=40735-- xxx trigger raised unhandled excpt ORA-nnnnn
        and instr(MSG,'ORA-')>0 then
          --extract ORA- msg from FRM-40735 msg
        subcode := substr(MSG,instr(MSG,'ORA-')+4,5);
        If translate(subcode,'+0123456789','+') is null then--if numeric
          DBMS_TXT:=sqlerrm(-1*to_number(subcode));
          If instr(DBMS_TXT,chr(10))>0 then
            --remove second msg (dbms_error_text) added on in web forms 10.1
            DBMS_TXT:=substr(DBMS_TXT,1,instr(DBMS_TXT,chr(10))-1);
          End if;
          MSG := replace(MSG,'ORA-'||subcode,DBMS_TXT);
        End if;
        Err_Msg(ltrim(MSG));
      ELSE -- All other messages:  Display Forms error along with database
           -- error.  Helps determine the "real" problem.
        DBMS_TXT := SUBSTR(DBMS_ERROR_TEXT,1,500);
        if  DBMS_TXT is null
        or substr(DBMS_TXT,5,5) = '00000'
        or Err_code between 41000 and 41999
          and substr(DBMS_TXT,5,5) = '01403' --no data found
                --get 1403 msg with many messages
        or Err_code between 40501 and 40599
          and instr(upper(MSG),'ORACLE ERROR')=0 then
          Err_Msg(MSG); -- just issue FRM error
        else -- Display Msg plus DBMS_Txt
          DBMS_TXT := replace(DBMS_TXT,'ORA-',chr(10)||'ORA-');
          DBMS_TXT := replace(DBMS_TXT,' '||chr(10), chr(10));
          -- show MSG and DBMS_TXT both --
          Err_Msg( ltrim(MSG) || DBMS_TXT );
        End if;
      END IF;
    END PLL_ON_ERROR;
    <font color=red>Edited by: Steve Cosner on Sep 16, 2008 7:35 AM (Changed markup PRE tag to CODE so the PL/SQL code displays correctly single-spaced)</font>
  • 3. Re: FRM-40735:Pre_Insert trigger raised unhandled exception ORA-20011
    119596 Newbie
    Currently Being Moderated
    nice snippet
    Gerd