Home » Applications » Oracle Fusion Apps & E-Business Suite » PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 (Oracle 6i forms on Oracle 10g database)
icon8.gif  PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570040] Mon, 05 November 2012 01:30 Go to next message
benphilsyahoocom
Messages: 13
Registered: May 2011
Location: NIGERIA
Junior Member
Dear Forum members,
Please i need someone to assist me in solving the error posted above.Whenever i open a form that was running quite well initially it brings out the error PRE -FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502.I Opened the form to check the pre-form trigger and added the following Exception (
Exception
when others then
MESSAGE('SQLERRM: ' || SQLERRM);
to trap the area where the error is coming from and it raised the following line(a package in the database) shown in the form attached below.i have check those lines and amended it but the error keeps on coming.Please i need someone to help in assisting me solved this problem.(attached also in this form is the package where the exception is caught if that can be of any help. i have also checked and increase the size of the column lenght buts its still not working.

Package is here


[EDITED by LF: removed image from the message body; it is too wide and forces me to scroll the screen left/right. Applied [spoiler] tags to cut a long message shorter]

[Updated on: Wed, 07 November 2012 05:05] by Moderator

Report message to a moderator

Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570046 is a reply to message #570040] Mon, 05 November 2012 02:19 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's one idea (possibly not the best one).

Create a table:
create table test (pos number);

Create a stored procedure which will insert a record into that table. Use pragma autonomous transaction so that you could COMMIT that insert, without affecting the caller (i.e. a package you posted above), something like this:
create or replace procedure p_pai_insert (par_pos in number) is
  pragma autonomous_transaction;
begin
  insert into test (pos) values (par_pos);
  commit;
end;

Finally, edit package code and, in front of every section you find appropriate, insert call to the procedure you just created:
p_pai_insert(1);
<some code here>
p_pai_insert(2);
<more code here>
p_pai_insert(3);
...

Execute the form; when the error appears, go to SQL*Plus and select from the TEST table - the highest number will point to the last code executed correctly. Remove unnecessary P_PAI_INSERT calls and, possibly, add some more; truncate the TEST table; repeat form execution until you narrow the error cause and, hopefully, fix it.

Sorry, I can't think of any smarter option right now.
Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570058 is a reply to message #570046] Mon, 05 November 2012 04:33 Go to previous messageGo to next message
benphilsyahoocom
Messages: 13
Registered: May 2011
Location: NIGERIA
Junior Member
Thanks i will try it out now. I will let you know if there is any issue
Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570066 is a reply to message #570058] Mon, 05 November 2012 06:25 Go to previous messageGo to next message
benphilsyahoocom
Messages: 13
Registered: May 2011
Location: NIGERIA
Junior Member
Hi Please i am having a little challenge in inserting call to the procedure i just created can u please put me through on this.
Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570077 is a reply to message #570066] Mon, 05 November 2012 07:49 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's an example:
FUNCTION fn_gv_posn (pi_gv_name IN VARCHAR2)
   RETURN NUMBER
IS
   v_name   VARCHAR2 (100) := UPPER (pi_gv_name);
   v_posn   tbl_gv_count%TYPE;
BEGIN
   --
   p_pai_insert(1);         --> here
   v_posn := 0;
   --
   FOR i IN 1 .. tbl_gv_count
   LOOP
      IF tbl_gv (i).gv_name = v_name
      THEN
         v_posn := i;
         EXIT;
      END IF;
   END LOOP;
   p_pai_insert(2);         --> and here
   --
   RETURN v_posn;
--
END fn_gv_posn;

So, if it fails and you find only "1" in the table, it means that it never reached insert point "2" so the problem must be within the FOR loop.
Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570080 is a reply to message #570077] Mon, 05 November 2012 08:31 Go to previous messageGo to next message
benphilsyahoocom
Messages: 13
Registered: May 2011
Location: NIGERIA
Junior Member
Thank you i have been able to track the first error its in this line
FOR i IN 1 .. tbl_gv_count
but i can't really see the problem here
pls is there anything i can do to rephrase this line.
Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570098 is a reply to message #570080] Mon, 05 November 2012 11:44 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How is TBL_GV declared?
V_NAME is VARCHAR2(100). How large is PI_GV_NAME?
What is TBL_GV_COUNT%TYPE? Is it large enough to hold FOR loop's I?
Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570165 is a reply to message #570098] Tue, 06 November 2012 03:11 Go to previous messageGo to next message
benphilsyahoocom
Messages: 13
Registered: May 2011
Location: NIGERIA
Junior Member
This is how they are all declared
1)TBL_GV
TYPE rec_gv IS RECORD (gv_name VARCHAR2(100),
gv_value VARCHAR2(500),
gv_type VARCHAR2(1), ----- THOUGH I HAVE TRIED TO INCREASE THE SIZE OF EACH DATATYPE HERE BUT ITS STILL DISPLAYING THE ERROR
gv_upd_yn VARCHAR2(1));
--
TYPE tab_gv IS TABLE OF rec_gv INDEX BY BINARY_INTEGER;
--
tbl_gv tab_gv;
tbl_gv_count PLS_INTEGER;

2) V_NAME VARCHAR2(100) := upper(PI_GV_NAME)

3) TBL_GV_COUNT%TYPE IS PLS_INTEGER WHICH I BELEIVE IS LARGE ENOUGH TO HOLD THE FOR LOOP'S I

This is how i declared everything.

Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570179 is a reply to message #570165] Tue, 06 November 2012 05:57 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think that - maybe - TBL_GV_COUNT is a culprit. You use it, but you never set its value.

Here's an example which illustrates what I mean:
SQL> declare
  2    tbl_gv_count number;
  3  begin
  4    for i in 1 .. tbl_gv_count loop
  5      null;
  6    end loop;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 4


SQL>

Does it make any sense to you?
Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570249 is a reply to message #570040] Wed, 07 November 2012 04:27 Go to previous messageGo to next message
benphilsyahoocom
Messages: 13
Registered: May 2011
Location: NIGERIA
Junior Member
Thank you Little foot, your reply is really helping me out,
i have set a value for the tbl_gv_count PLS_INTEGER := 2147483640 and found out that the error is pointing to the next line below but dont know what i can do to correct the line below :
IF tbl_gv(i).gv_name = v_name THEN -------line error. please can you help me check out this line and maybe you can find out what the problem is there

Thanks....

TYPE rec_gv IS RECORD (gv_name VARCHAR2(100),
gv_value VARCHAR2(500),
gv_type VARCHAR2(1),
gv_upd_yn VARCHAR2(1));
--
TYPE tab_gv IS TABLE OF rec_gv INDEX BY BINARY_INTEGER;
--
tbl_gv tab_gv;
tbl_gv_count PLS_INTEGER := 2147483640 --------(1)corrected
--
FUNCTION fn_gv_posn (pi_gv_name IN VARCHAR2) RETURN NUMBER IS
v_name VARCHAR2(100) := UPPER(pi_gv_name);
v_posn tbl_gv_count%TYPE;
BEGIN
--
v_posn := 0;
--
FOR i in 1..tbl_gv_count LOOP
IF tbl_gv(i).gv_name = v_name THEN ------ (2)New error Line
v_posn := i;
EXIT;
END IF;
END LOOP;
--
RETURN v_posn;
--
END fn_gv_posn;
Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570253 is a reply to message #570249] Wed, 07 November 2012 04:41 Go to previous messageGo to next message
benphilsyahoocom
Messages: 13
Registered: May 2011
Location: NIGERIA
Junior Member
The error from the form now is

Error:
ORA-01403: no data found
Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570260 is a reply to message #570253] Wed, 07 November 2012 05:25 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that you are referencing a non-existent collection element. I don't think that you should declare TBL_GV_COUNT as you did (2147483640), but count number of collection elements using the COUNT method:
tbl_gv_count := tbl_gv.count;
...
for i in 1 .. tbl_gv_count loop
...
Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570360 is a reply to message #570260] Thu, 08 November 2012 04:42 Go to previous messageGo to next message
benphilsyahoocom
Messages: 13
Registered: May 2011
Location: NIGERIA
Junior Member
This is what i tried to do but it compiles with error


TYPE rec_gv IS RECORD (gv_name VARCHAR2(100),
gv_value VARCHAR2(500),
gv_type VARCHAR2(1),
gv_upd_yn VARCHAR2(1));
--
TYPE tab_gv IS TABLE OF rec_gv INDEX BY BINARY_INTEGER;
--
tbl_gv tab_gv;
tbl_gv_count := tbl_gv.count;
--
FUNCTION fn_gv_posn (pi_gv_name IN VARCHAR2) RETURN NUMBER IS
v_name VARCHAR2(100) := UPPER(pi_gv_name);
v_posn tbl_gv_count%TYPE;
BEGIN
--
v_posn := 0;
--
FOR i in 1..tbl_gv.count LOOP
IF tbl_gv(i).gv_name = v_name THEN
v_posn := i;
EXIT;
END IF;
END LOOP;
--
RETURN v_posn;
--
END fn_gv_posn;
Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570362 is a reply to message #570360] Thu, 08 November 2012 04:58 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
And the error is ...?
Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570363 is a reply to message #570362] Thu, 08 November 2012 05:06 Go to previous messageGo to next message
benphilsyahoocom
Messages: 13
Registered: May 2011
Location: NIGERIA
Junior Member
The package was created with compilation error.(though the error was not stated)
and the form raises (pre-form trigger raises unhandled exception ora-04063).
Re: PRE-FORM TRIGGER RAISED UNHANDLED EXCEPTION ORA-06502 [message #570365 is a reply to message #570363] Thu, 08 November 2012 05:16 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First you need to fix the package compilation error. How did you create it (which tool?). Was it SQL*Plus? If so, after you run the CREATE PACKAGE script, type SHOW ERR to see what went wrong. Here's how:
SQL> create package pkg_delme is
  2  begin
  3    blabla;
  4  end;
  5  /

Warning: Package created with compilation errors.

SQL> show err package pkg_delme
Errors for PACKAGE PKG_DELME:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1      PLS-00103: Encountered the symbol "BEGIN" when expecting one of
         the following:
         end function package pragma private procedure subtype type
         use <an identifier> <a double-quoted delimited-identifier>
         form current cursor

4/4      PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         end not pragma final instantiable order overriding static
         member constructor map

SQL>
Previous Topic: SQL Query to retriew open GRNs for Purchasning in R12
Next Topic: Generating XML output for a Sales Quote in Oracle 11.5.10
Goto Forum:
  


Current Time: Thu Mar 28 05:08:34 CDT 2024