Skip to Main Content

Oracle Forms

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Unhandled Exception ORA-100501

Tony007Oct 14 2021 — edited Oct 15 2021

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;

Comments

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.

Tony007

i dont have on error and i dont use Java Beans (or PJC)
onerror.jpgerror3.jpg

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

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

@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

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.

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.

Can the execute_trigger calls cause the ora-100501?
They don't raise the form trigger failure when they fail, right?

SFrix

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.

1 - 9

Post Details

Added on Oct 14 2021
9 comments
781 views