Re: PL/SQL: Numeric or value errorr. Que?

T-Pro wrote:
>
> Ok, I don't get this one. Maybe you do:
>
> I've got a function that determines if a user may execute a procedure with
> the given parameters. It uses several explicit cursors to determine this.
> Which cursors to use depends on the given parameters. This is done by
> evaluating what parameters are null-values. (IF par1 IS NULL THEN..)
>
> The function compiles fine. Every now and then however I get a 6502:
> Numeric or value error. There is no pattern to be found (unless I have
> totally overlooked smomething). When the function is recompiled, it's fine
> again (note that the function is always valid, so there should be no need
> to recompile).
>
> I'm using Oracle 7.3.2 on Windows NT 4.0.
>
> If anyone has had similar experiences (or better yet: has a solution or
> workaround) I would be very grateful.
>
> With regards,
> Tony van der Prijt

We were having the same problems with some packages. At the end of the day people were receiving 6502's. We applied the following patch, after that everything has been OK:

Fix to bug524408/516664
08/20/97
To apply the fix, simply run "sh README.524408" Fix to bug524408 on HP-UX 10.x 9000-700/800

         Oracle V7.3.2.3

I don't know if you're hitting the same bug.

You're testing for NULL values. I guess you're having a cursor and a record declared as the rowtype of that cursor? There are some problems with this statement:

Open My_cur(a, b);
Fetch My_cur Into My_Rec;
Close My_Cur;
If My_Rec.Id Is Null Then -- There were no occurences matching a and b.

I think this should be a perfectly OK way to do the existence-check, but I think there is a bug somewhere so that sometimes Oracle won't accept this if the fetch returns zero rows. A workaround would be to check %Found to see if you received any rows, and if you didn't, then don't execute My_Rec.Id

Summary: You might be hitting a bug, contact Oracle support. Try the %Found-workaround.

--
Njål Arild Ekern, ADB-seksjonen USIT
Postboks 1059 Blindern, 0316 OSLO
Tlf 22852477, fax 22852730 Received on Wed Jul 01 1998 - 03:10:34 CDT