asked on

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

Arthur_Wood

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

ASKER CERTIFIED SOLUTION

Helena Marková

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nandini22

ASKER

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.

SOLUTION

Elena-S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial