Trigger Cuasing error ORA-06502 and ORA-01403 [message #170223] |
Tue, 02 May 2006 14:30 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
annagel
Messages: 220 Registered: April 2006
|
Senior Member |
|
|
Just to preface this I am somewhat new to making forms and am kind of learning things as I go. I have at this point been working with triggers and layouts. My layout is working well enough triggers though are causing me some problems. I am getting the following error message just about every time I write a trigger so I am getting the feeling I am doing something fundamentally wrong. Like there is something I am just not getting because I am getting this error on the simplest of actions.
The error:
FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-06502.
--------------------------------------------------------------
ORA-01403: no data found
The trigger is this:
cs3_prog.set_date_mode;
this calls the following procedure in the package cs3_prog:
procedure set_date_mode IS
BEGIN
:cs3_data.street := 'something';
end;
this in the past did a lot more but I kept throwing stuff out to see what could have been causing the problem. This does actually work if I ok through the first error message and then try entering data again into the field the trigger is tied to. On this second firing of the trigger is does successfully alter the value.
Any help pointing out what I could be doing wrong here would be appreciated. Just the fact that this is happening every time I create a trigger and it seems to happen regardless of what I have the trigger doing leads me to believe I have something fundamentally wrong and I would love to have it set straight.
thanks
Andrew
|
|
|
Re: Trigger Cuasing error ORA-06502 and ORA-01403 [message #170349 is a reply to message #170223] |
Wed, 03 May 2006 07:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/67467.jpg) |
djmartin
Messages: 10181 Registered: March 2005 Location: Surges Bay TAS Australia
|
Senior Member Account Moderator |
|
|
Please put this code into your Forms packageprocedure set_date_mode IS
BEGIN
message('set_date_mode: before'); pause;
:cs3_data.street := 'something';
message('set_date_mode: after'); pause;
end;
Now run your code and tell us if you see the second 'message' before or after the '06502' message.
Do you have 'Fire in Query Mode' turned on for your When-Validate-Item trigger?
David
PS Please use the 'code' tags in future posts.
[Updated on: Wed, 03 May 2006 07:39] Report message to a moderator
|
|
|
Re: Trigger Cuasing error ORA-06502 and ORA-01403 [message #170355 is a reply to message #170349] |
Wed, 03 May 2006 07:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
annagel
Messages: 220 Registered: April 2006
|
Senior Member |
|
|
Fire in query mode is turned on for that trigger. When I run the form, I get the exception before wither of the messages is displayed. I added a second when-validate-item trigger to one of my fields that calls the same code. After getting the error message when the first trigger is fired, I do not get it when the 2nd is fired.
To check to see if it was just the When-New-Form-Instance trigger I took the call out that trigger and left it just in my when-validate-item trigger. When I do this, I get the error the first the trigger is called, but on subsequent calls I get no error.
|
|
|
|
Re: Trigger Cuasing error ORA-06502 and ORA-01403 [message #170368 is a reply to message #170362] |
Wed, 03 May 2006 08:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
annagel
Messages: 220 Registered: April 2006
|
Senior Member |
|
|
Sorry if I was not clear there. I moved the call out of the original location and attached it to a different trigger, one that could be fired multiple times rather than simply once like the when-new-form-instance call. On the fist firing of this trigger I get an error, on the second I do not. Then going back I reinserted the call into the original when-new-form-instance and kept it in my when-validate-item trigger. I get an error from the when-new-form-instance trigger and no errors from the when-validate-item trigger.
Based on all this the only conclusion I can draw is that I am getting the error the first time I make a call, any call into the package that contains the actual code. So the error is coming from the package I would assume and no the trigger as it appears no matter what trigger I put the call into.
To do some more checking I created a 2nd procedure in my package that does essentially the same thing as the the first except that it sets a different field in the form:
procedure set_date_mode2 is
begin
message('set_date_mode: before'); pause;
:cs3_data.last_name := 'something';
message('set_date_mode: after'); pause;
end;
The calls for the two procedure where put into when-validate-item triggers so I could fire one or the other first. Whichever procedure was triggered first gave the exception, whichever was fired 2nd gives no exception.
I am getting called out of here in a minute for a meeting so I am going to go dark for about an hour, and I will be back after that. Just didn't want anyone to think I was ignoring your help or anything because I appreciate it very much.
And just before I go some additional fun for the mix. I tried blocking the calls from one of trigger and catching the exception with something like this:
begin
/*set the initial system state*/
message('about to enter package');pause;
cs3_prog.set_date_mode;
message('out of package');pause;
exception
when VALUE_ERROR then
cs3_prog.set_date_mode;
end;
this will give me the message before I am about to make the call. It then also gives me the two messages from the call made after the exception which is kind of what I would expect. What I did not expect is that when I go to close the form latter I get the messages from inside the original call, though never the message indicating that I am 'out of package'.
Sorry there is a lot here I am just trying to include the results of everything I have done thus far.
|
|
|
The Problem [message #170393 is a reply to message #170223] |
Wed, 03 May 2006 09:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
annagel
Messages: 220 Registered: April 2006
|
Senior Member |
|
|
I have found my problem and thought I would post it because given everything I could find on this error, I never thought this would be the cause of the problem. It turns out the problem was incorrect constant definitions. Some constants where defined as CHAR's rather than VARCHAR2's and this, it would appear, though more testing will be required to be 100% certain, was causing my problem.
Thanks for your help David and sorry I sent you on a while goose chase given that with the info you, there was no way you could have figure out what was actually causing my problem!
Andrew
|
|
|
Re: The Problem [message #170433 is a reply to message #170393] |
Wed, 03 May 2006 15:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/67467.jpg) |
djmartin
Messages: 10181 Registered: March 2005 Location: Surges Bay TAS Australia
|
Senior Member Account Moderator |
|
|
Yes I would have! My next post would have been for you to post your code and the one after that would be a request for your item definitions. I just don't ask for all the information in my first reply as peope become confused if I ask too many questions at one time. They also have a tendancy to only answer some of my questions. Therefore, I only ask one or two in each post.
David
[Updated on: Wed, 03 May 2006 23:57] Report message to a moderator
|
|
|
Re: The Problem [message #170452 is a reply to message #170433] |
Wed, 03 May 2006 22:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
annagel
Messages: 220 Registered: April 2006
|
Senior Member |
|
|
Don't take that as an offence, all I meant to say what you are not psychic.
And just because I am curious do you have any idea why that kind of error was raised as a result of a bad constant data definition? I could understand it if maybe I tried to access one of the constants that was ill defined because then there is kind of a non-existant value error there. For this though the error would have been thrown I woudl assume as a part of the package initalization...but why that one?
[Updated on: Wed, 03 May 2006 22:32] Report message to a moderator
|
|
|
|
|
|
|