Re: PL/SQL: weird 'fetch out of sequence' error

This probably won't fix anything, but you could simplify your code to this:

..
BEGIN
   SELECT instance_id INTO instanceid FROM instance

     WHERE class_id = classid
     AND LOWER(instance_name) =  LOWER(instancename);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('** no data found in getInstanceId');     RETURN -1;
  WHEN OTHERS THEN
    RETURN -2; -- or -1 if you prefer END; It would eliminate the assignment statement and any chance that the SELECT could somehow corrupt the return value that you'd expect on failure. In your code, you also failed to account for any other exception possibilities that might've occurred, which would not be trapped or reported and would result in a return value of NULL.

In article <b07o4d$lqedb$1_at_ID-51546.news.dfncis.de>, "Manfred Pruntsch" <manfred.pruntsch_at_ifcos.com> wrote:
>Hello Scott,
>
>"Scott Mattes" <Scott_at_TheMattesFamily.ws> schrieb im Newsbeitrag
>news:PfDV9.85040$VA5.13256456_at_news1.news.adelphia.net...
>> Since a search on tahiti.oracle.com shows no reference to getInstanceId,
>we
>> probably need to see what that function is doing to be able to tell.
>
>Here is the function getInstanceId:
>
>create or replace function getInstanceId(classid IN integer, instancename IN
>varchar2)
>return integer is
>instanceid integer;
>begin
> instanceid := -1;
>
> SELECT instance_id into instanceid FROM instance
> WHERE class_id = classid
> AND LOWER(instance_name) = LOWER(instancename);
> return instanceid;
>
>EXCEPTION
> when NO_DATA_FOUND then DBMS_OUTPUT.PUT_LINE('** no data found in
>getInstanceId'); return instanceid;
>end;
Received on Wed Jan 22 2003 - 15:01:05 CET