Forum Stats

  • 3,768,552 Users
  • 2,252,809 Discussions
  • 7,874,617 Comments

Discussions

Unhandled Exception ORA-100501

Tony007
Tony007 Member Posts: 4,166 Bronze Trophy
edited Oct 15, 2021 12:10PM in Forms

hi after i upgrade from 11g to Forms [64 Bit] Version 12.2.1.4.0 (Production) am having this error when navigation in trace file am having

PRE-TEXT-ITEM Trigger Fired:
Form: LLCOPD04
Block: PERS
Item: SIGNON_TYPE

State Delta:
LLCOPD04, 14, Trigger, Entry, 5798085819025171, PRE-TEXT-ITEM
LLCOPD04, 15, Prog Unit, Entry, 5798085819237650, /LLCOPD04-7/P21_2021_10_14_17_15_19
LLCOPD04, 15, Prog Unit, Exit, 5798085819350575, /LLCOPD04-7/P21_2021_10_14_17_15_19
LLCOPD04, 14, Trigger, Exit, 5798085819594488, PRE-TEXT-ITEM

Executing MESSAGE Built-in:
In Argument 0 - Type: String  Value: test06 Y
In Argument 1 - Type: Number Value: 121

Executing PAUSE Built-in:
# 35 - LLCOPD04:PERS.SIGNON_TYPE.5798086629722378
ALERT OK

Executing FORM_FAILURE Built-in:
Out Argument 0 - Type: Boolean  Value: FALSE
LLCOPD04, 14, Prog Unit, Entry, 5798086630150099, /LLCOPD04-7/LCL_MODE_OBJ▒▒▒
LLCOPD04, 14, Prog Unit, Exit, 5798086644201748, /LLCOPD04-7/LCL_MODE_OBJ▒▒▒

Unhandled Exception ORA-100501
State Delta:
LLCOPD04, 13, Trigger, Exit, 5798086645115573, KEY-NEXT-ITEM
the code in ite is
PRE-TEXT-ITEM Trigger Fired:

if :PERS.SIGNON_TYPE='L' then
	:PERS.L_FORCE_NO_ID_NO:= '(L-Force No/N-ID No)';
end if;

=====

KEY-NEXT-ITEM
IF nvl(:pers.signon_type,' ') not in ('L','N') then
  message ('Value must be [L] or [N]');
  raise form_trigger_failure;
END IF;
:global.signon_type := :pers.signon_type;
next_item;

the code is failing hear if i comment this out it work

IF form_failure and :global.pstat = 'N' then
   LCL_MODE_OBJ;
   :scr_force_no := :global.pers;
   :signon_type := :global.signon_type;
   raise form_trigger_failure;
 ELSIF 
   form_failure then
   LCL_MODE_OBJ;
   :scr_force_no := :global.pers;
   :signon_type := :global.signon_type;
   IF :global.action = 'RETRIEVE' then
    message('Sign-on ID does not exist - use NEW to create');
    --enable_item('File','New');
    set_menu_item_property('File.New', enabled, property_true);

    raise form_trigger_failure;
   ELSE
    :pers.signon_type := :global.signon_type;
    :scr_force_no   := :global.pers;
    IF :pers.signon_type = 'N' then
      :pers.pers_rank_cde := '199';
      go_field('pers_surname');
      set_item_property('pers_rank_cde',UPDATEABLE,property_false);
      set_item_property('pers_rank_cde',ENTERABLE,property_false);
      set_item_property('pers_rank_cde',NAVIGABLE,property_false);
    ELSE
      set_item_property('pers_rank_cde',UPDATEABLE,property_true);
      set_item_property('pers_rank_cde',ENTERABLE,property_true);
      set_item_property('pers_rank_cde',NAVIGABLE,property_true);
      go_field('pers_rank_cde');
    END IF;
     message('pages23');pause;
    show_page(3);
    message('Enter detail');
    --enable_item('File','Save');
    set_menu_item_property('File.Save', enabled, property_true);

    :crt_temp_rec_ind := 'T';
    raise form_trigger_failure;
   END IF;
 ELSE
   LCL_MODE_OBJ;
   IF :global.action = '   NEW' then
    :global.err := 'N';
    message('Sign-on ID already exist - use RETRIEVE option');
    raise form_trigger_failure;
   ELSE
    IF :pers.crt_temp_rec_ind = 'X' then
      raise form_trigger_failure;
    ELSE
      IF :pers.signon_type = 'N' then
       go_field('pers.pers_race_cde');
      ELSE 
       go_field('pers.pers_id_no');
      END IF;
    END IF;
   END IF;
 END IF;
 :system.message_level := '0';

===

procedure LCL_MODE_OBJ is
begin
--	message('mod3');pause;
 :scr_nohlp_mode := :global.action;
 :scr_nohlp_dte_tme := sysdate;
 :menu.LLCOPD04 := 'LLCOPD04';
 :menu.PERSONNEL_INFORMATION := 'PERSONNEL INFORMATION';
 end;


Answers

  • Michael Ferrante-Oracle
    Michael Ferrante-Oracle Senior Principal Product Manager USMember Posts: 6,890 Employee

    ORA-100501 is generally an error that indicates some external code is not happy. For example a Java Bean, PJC, UserExit, etc.

    If your app uses Java Beans, user-exits, or anything else like that carefully check your code. Consider things like this:

    o If you are using user-exits, that code must be compiled using the ProC compiler version that matches the DB client version used by the Forms version you are using. In the case of Forms 12.2.1.4, that version would be 12.1.0.2

    o If your app uses Java Beans (or PJC), that code should be built against Java 7 or 8. Further, because of changes between Forms versions, it may be necessary to recompile your Bean/PJC code against the current Forms version.

    o If you have one or more ON-ERROR triggers, temporarily remove them so that the native Forms error can be seen. This often will help to pinpoint the issue. You do not need to delete the trigger. Simply rename it to a valid trigger that is not used and one that won't interfere with the running of the form. For example, if you do not use the KEY-OTHERS trigger, simply rename ON-ERROR, KEY-OTHERS. Remember to rename it back later.

    Aaron L.
  • Tony007
    Tony007 Member Posts: 4,166 Bronze Trophy
    edited Oct 14, 2021 11:45PM

    i dont have on error and i dont use Java Beans (or PJC)


  • Michael Ferrante-Oracle
    Michael Ferrante-Oracle Senior Principal Product Manager USMember Posts: 6,890 Employee

    Nothing significantly changed in this regard between 11 and 12 except the RSF (DB client version). So either there are details you are not sharing about what you may have changed or there may be a change that occurred in the client (DB) that is causing the problem.

    By the way, rarely will Forms tracing be helpful for this type of issue. Carefully reviewing the error and all its text and determining the exact point of failure often is more helpful. Please share the complete text of the error when it occurs. Also, immediately after the error occurs, select Help > Display Errors from the menu.

  • SFrix
    SFrix Member Posts: 129 Bronze Badge

    Surely not the solution but I suggest you upgrade your code by using non deprecated built in (go_field should be replaced by go_item) and property names (properties ENTREABLE should be replaced by INSERT_ALLOWED and UPDATEABLE by UPDATE_ALLOWED). At least, they will be excluded of the possible root cause if they could have been considerate as possible cause.

    Look at here, may be something could help you. Without the full context of your code I can't say if it can help: https://community.oracle.com/tech/developers/discussion/3534385/100501-non-oracle-exception-by-raise-form-trigger-failure

  • Michael Ferrante-Oracle
    Michael Ferrante-Oracle Senior Principal Product Manager USMember Posts: 6,890 Employee

    @SFrix Wow - good catch. Not sure how I missed the obsolete calls. This makes me wonder how much more outdated code is in this app.

    @Tony007 You should carefully review this upgrade guide and fix your form(s).

    https://docs.oracle.com/en/middleware/developer-tools/forms/12.2.1.4/upgrade-forms/index.html

  • Tony007
    Tony007 Member Posts: 4,166 Bronze Trophy

    the form was upgraded from 11g to 12 not from 6i the code is working in 11g

    this is the triger code when is failing

    DECLARE ws_stat_idno varchar2(15);

       ws_idno_err varchar2(1);

    BEGIN

     IF :scr_force_no is null then

       CAL_ERRMESS('X130','Sign-on ID',' ',' ');

       raise form_trigger_failure;

     ELSE

       execute_trigger('NUMBER_VAL');

       IF :global.err = 'Y' then

        raise form_trigger_failure;

       ELSE

        BEGIN

         DECLARE err varchar2 (1);

             msg varchar2 (80);

         BEGIN

          IF :pers.signon_type = 'L' then

            LLCS_FORCE_NO_CHKDIGIT(:scr_force_no,

        err,

        msg);

            IF err = 'Y' then

             message(msg);

             raise form_trigger_failure;

            END IF;

          END IF;

          IF :pers.signon_type = 'N' then

            IF NVL(length(:pers.scr_force_no), 0) != 13 then

             message ('The ID Number must be 13 characters long');

             raise form_trigger_failure;

            END IF;

            IF NVL(substr(:pers.scr_force_no,8,6), 0) = '000000' /* CCID 140140 */

            or NVL(substr(:pers.scr_force_no,8,6), 0) = '999999' then

             message ('The ID Number must be a valid civilian ID no');

             raise form_trigger_failure;

            END IF;

            ws_idno_err := 'N';

            LCL_CALC_CONTROL_DIGIT_ID_NO (:pers.scr_force_no,ws_idno_err);

            if ws_idno_err = 'Y' then

              message('ID number invalid (Control digit)');

              raise form_trigger_failure;

            end if;

          END IF;

         END;

        END;

       END IF;

     END IF;

     :global.pers    := :scr_force_no;

     :global.signon_type := :pers.signon_type;

     clear_block(no_validate);

     :system.message_level := '5';

     :global.err := ' ';

     /* to make sure the force no is active */

     :global.pstat := ' ';

     execute_trigger('PERS_STAT');

     /* the end */

     IF :global.pstat    = 'Y' and

       :global.signon_type = 'N' then

       BEGIN 

        select pers_stat, pers_force_no

         into ws_stat_idno, :global.pers

         from ils.pers

        where substr(pers_id_no,1,13) = :global.pers;

        IF ws_stat_idno   = '0001' then

         :global.pstat  := 'Y';

        ELSE

         :global.signon_type := 'L';

         :global.pstat    := 'N';

        END IF;

       EXCEPTION

        when no_data_found then

          :global.pstat := 'Y';

       END;

     END IF;

     execute_query;

     IF form_failure and :global.pstat = 'N' then

       LCL_MODE_OBJ;

       :scr_force_no := :global.pers;

       :signon_type := :global.signon_type;

       raise form_trigger_failure;

     ELSIF 

       form_failure then

       LCL_MODE_OBJ;

       :scr_force_no := :global.pers;

       :signon_type := :global.signon_type;

       IF :global.action = 'RETRIEVE' then

        message('Sign-on ID does not exist - use NEW to create');

        --enable_item('File','New');

        set_menu_item_property('File.New', enabled, property_true);


        raise form_trigger_failure;

       ELSE

        :pers.signon_type := :global.signon_type;

        :scr_force_no   := :global.pers;

        IF :pers.signon_type = 'N' then

          :pers.pers_rank_cde := '199';

          go_field('pers_surname');

          set_item_property('pers_rank_cde',UPDATEABLE,property_false);

          set_item_property('pers_rank_cde',ENTERABLE,property_false);

          set_item_property('pers_rank_cde',NAVIGABLE,property_false);

        ELSE

          set_item_property('pers_rank_cde',UPDATEABLE,property_true);

          set_item_property('pers_rank_cde',ENTERABLE,property_true);

          set_item_property('pers_rank_cde',NAVIGABLE,property_true);

          go_field('pers_rank_cde');

        END IF;

        show_page(3);

        message('Enter detail');

        --enable_item('File','Save');

        set_menu_item_property('File.Save', enabled, property_true);


        :crt_temp_rec_ind := 'T';

        raise form_trigger_failure;

       END IF;

     ELSE

       LCL_MODE_OBJ;

       IF :global.action = '   NEW' then

        :global.err := 'N';

        message('Sign-on ID already exist - use RETRIEVE option');

        raise form_trigger_failure;

       ELSE

        IF :pers.crt_temp_rec_ind = 'X' then

          raise form_trigger_failure;

        ELSE

          IF :pers.signon_type = 'N' then

           go_field('pers.pers_race_cde');

          ELSE 

           go_field('pers.pers_id_no');

          END IF;

        END IF;

       END IF;

     END IF;

     :system.message_level := '0';

     /* Henriette */

     BEGIN

      select cde_short_desc

       into :scr_pers_stat

       from ils.cde_table_entry

       where cde_tab_id = 'LPER' and

          cde_tab_val = :pers_stat;

     EXCEPTION

      when no_data_found then null;

     END;

     :global.msg_pers_force_no := :scr_force_no;

    END;


    this the trace file i get

    PRE-TEXT-ITEM Trigger Fired:

    Form: LLCOPD04

    Block: PERS

    Item: SIGNON_TYPE


    State Delta:

    LLCOPD04, 14, Trigger, Entry, 5798085819025171, PRE-TEXT-ITEM


    LLCOPD04, 15, Prog Unit, Entry, 5798085819237650, /LLCOPD04-7/P21_2021_10_14_17_15_19


    LLCOPD04, 15, Prog Unit, Exit, 5798085819350575, /LLCOPD04-7/P21_2021_10_14_17_15_19


    LLCOPD04, 14, Trigger, Exit, 5798085819594488, PRE-TEXT-ITEM



    Executing MESSAGE Built-in:

    In Argument 0 - Type: String  Value: test06 Y

    In Argument 1 - Type: Number Value: 121


    Executing PAUSE Built-in:


    # 35 - LLCOPD04:PERS.SIGNON_TYPE.5798086629722378

    ALERT OK


    Executing FORM_FAILURE Built-in:

    Out Argument 0 - Type: Boolean  Value: FALSE

    LLCOPD04, 14, Prog Unit, Entry, 5798086630150099, /LLCOPD04-7/LCL_MODE_OBJ▒▒▒


    LLCOPD04, 14, Prog Unit, Exit, 5798086644201748, /LLCOPD04-7/LCL_MODE_OBJ▒▒▒



    Unhandled Exception ORA-100501

    State Delta:

    LLCOPD04, 13, Trigger, Exit, 5798086645115573, KEY-NEXT-ITEM

    Help > Display Errors display nothing

  • Aaron L.
    Aaron L. Member Posts: 27 Red Ribbon
    edited Oct 21, 2021 5:18PM

    Might be a stupid question, but what are the items that don't have a block name in front of them?

    Example: :scr_force_no and :signon_type

    I've never seen that in my 15 years of Forms experience.

  • Aaron L.
    Aaron L. Member Posts: 27 Red Ribbon

    Can the execute_trigger calls cause the ora-100501?

    They don't raise the form trigger failure when they fail, right?

  • SFrix
    SFrix Member Posts: 129 Bronze Badge

    If your item has a unique name in your form module and in the scope of your call, block name is optional.

    Then you can address the item with :block_name.item_name or :item_name.


    My POV, not a good practice. 😉

    Aaron L.