3 Replies Latest reply on Feb 14, 2006 9:17 AM by Gerd Volberg

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

      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
          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
            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 ...???
                                   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:
              Err_Code NUMBER(5)     := ERROR_CODE;
              MSG     VARCHAR2(150)
                      := SUBSTR('   '||ERROR_TYPE||'-'||TO_CHAR(Err_Code)||': '
              IF Err_Code=<special value here> THEN
                  Message('   <special message, etc. here> ');
                  Raise Form_Trigger_Failure;
              END IF;
              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
                Raise form_trigger_failure;
              End Err_Msg;
              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.
                  Fmt varchar2(30)
                    := replace(replace(replace(replace(
                  Itm  varchar2(30);
                  Msg1 varchar2(100);
                  Msg2 varchar2(30);
                  Itm := Substr(Name_in('SYSTEM.TRIGGER_ITEM'),
                  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.');
                    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;
              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
              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
              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
                  If instr(DBMS_TXT,chr(10))>0 then
                    --remove second msg (dbms_error_text) added on in web forms 10.1
                  End if;
                  MSG := replace(MSG,'ORA-'||subcode,DBMS_TXT);
                End if;
              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>