Cursor out of scope

FUNCTION  match_cit_feature
     (seq_id_in IN gcg_bioseq.seq_id%TYPE,
      pub_id_in IN gcg_publication.pub_id%TYPE)
  RETURN gcg_feature.feat_id%TYPE
  is
cursor c_pub is  select feat_id ,pub_id  from gcg_feature_pub
  where feat_id in (select feat_id from gcg_feature where seq_id = seq_id_in  );
  local_feat_id number(15);
  local_pub_id number(15);
  Begin
For i in  c_pub loop
if c_pub.pub_id = pub_id_in then
return c_pub.feat_id;
else
return null;
end if;
end loop;
  end match_cit_feature;
For the above set of code i get the error
Errors for PACKAGE BODY GCGFEAT_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
14/1     PL/SQL: Statement ignored
14/10    PLS-00225: subprogram or cursor 'C_PUB' reference is out of scope
What does out of scope error mean
nandini22Asked:
Who is Participating?
 
Helena MarkováConnect With a Mentor programmer-analystCommented:
I think that this will work:

For rec in  c_pub loop
if rec.pub_id = pub_id_in then
 return rec.feat_id;
else
 return null;
end if;
end loop;
0
 
Arthur_WoodCommented:
the real cause of the error 14/10  is the first error 14/1  which suggests that there is a problem in your FUNCTION declaration.

I would suggest that you should check the PL/SQL syntax for declaring a FUNCTION as a 'Stored Procedure'. It has been about 7 yeears since I used ORACLE, so I can't give you the corect syntax, but my guess is that there is an error in the FUNCTION declaration.  

The 14/10 error is then due to the fact that you cannot declare a cursor outside of the definition of either a valid FUNCTION or a valid STORED PROCEDURE.

AW
0
 
nandini22Author Commented:
Thanks Henka it works ,but I have another problem that when there are no rows selected by the cursor the exception to return a null is not being raised.
0
 
Elena-SConnect With a Mentor Commented:
It is not considered an exception to return null from a function.  If you would like to treat it as exception, you need to raise exception explicitly.  For example,

if some_value is null then
   raise_application_error(-20000, 'Your error message goes here');
end if;

You can find detailed description of this procedure in Oracle help available on-line.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.