Home » Developer & Programmer » Precompilers, OCI & OCCI » OCI - ORA-1403 & ORA-1405 Errors (Oracle 10g)
OCI - ORA-1403 & ORA-1405 Errors [message #346009] Fri, 05 September 2008 09:58 Go to next message
Messages: 1
Registered: September 2008
Junior Member
We have a very simple OCI call to a db proc that uses a ref cursor to select from a table.

We intermittently get ORA-1403 NO DATA FOUND and ORA-01405 FETCH COLUMN VALUE IS NULL errors. However, the data does exist and the columns are set as not null. If we run the proc manually then it works fine.

We have only recently switched to using RAC (2 instances).

Example code:

procedure tile_features (in_mm_tile_key in mm_tile_geometries.mm_tile_key%type, out_features_cursor out sys_refcursor, out_status_code out number, out_status_text out varchar2)
open out_features_cursor for
select m.mm_tile_key,
from mm_tile_geometries m
where m.mm_tile_key = in_mm_tile_key;

out_status_code := 0;
when others
out_status_code := 1;
out_status_text := 'Error in mm_query.tile_features: ' || sqlcode || ' - ' || sqlerrm;

Any ideas??
Re: OCI - ORA-1403 & ORA-1405 Errors [message #347154 is a reply to message #346009] Wed, 10 September 2008 21:47 Go to previous message
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
I've run into (somewhat) similar situations. Not with RAC (no idea if there are any bugs there) but with plain old instances. And, of course, never with any stored procedures I wrote (insert appropriate smiley here).

Where I would look is at the exception block in your stored procedure. This contains the "where others bug", in which you trap all errors and fail to reraise any of them. Which means that any error found in your procedure will not be seen by any calling procedure.

I know how this is supposed to work: the calling routine is supposed to look at out_status_code and take appropriate action. My point is that I have had to troubleshoot cases where the calling routine was not doing this (actually, there were three levels of calls; the top level was checking the "return code", but the routine it called was not checking the "return code" from the routine which it called).

Suppose your routine raises an error, something which is believable. And whatever calls it fails to check (because it "just cannot fail"). So, it proceeds with bad input, and raises the 1403 or 1405.

I don't know how much of this applies to your situation (and you certainly have all the hooks in place to avoid this) but your description makes these errors "impossible", and this is one way for the impossible to happen.
Previous Topic: Compile pro*c file with "-DDAEMON -DBACKGROUND" option on Solaris
Next Topic: Problems running query in PRO*C that works in SQLPLUS
Goto Forum:

Current Time: Fri Jan 27 20:52:59 CST 2023