Unhandled Exception ORA-100501

Tony007 · Oct 14, 2021 4:24PM

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;

Michael Ferrante-Oracle · Oct 14, 2021 7:16PM

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 · Oct 14, 2021 11:24PM

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

Michael Ferrante-Oracle · Oct 15, 2021 12:16PM

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 · Oct 15, 2021 1:16PM

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 · Oct 15, 2021 1:23PM

@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 · Oct 15, 2021 4:51PM

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;

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 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;

: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. · Oct 21, 2021 5:17PM

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. · Oct 21, 2021 5:45PM

Can the execute_trigger calls cause the ora-100501?

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

SFrix · Oct 22, 2021 1:27PM

https://community.oracle.com/tech/developers/discussion/comment/16812829#Comment_16812829

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. 😉

Forum Stats

Quick Links

Categories

Unhandled Exception ORA-100501

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Unhandled Exception ORA-100501

Answers