Re: REF Cursor Question

amerar_at_iwc.net wrote:
> Hey All,
>
> I got this piece of code I am trying to write and the cursor is not
> working properly. Please take a look at this. It basically complains
> about v_osid not being valid......
>
>
> CREATE OR REPLACE PROCEDURE valid_spf(p_spf VARCHAR2,p_meps_id
> VARCHAR2, p_processing_spf OUT VARCHAR2) IS
> rec_array SYS_REFCURSOR;
> cursor ora_sid IS select oracle_sid from meps_system_info where meps_id
> = p_meps_id;
> v_osid meps_system_info.oracle_sid%TYPE;
> sql_statement varchar2(400);
>
> begin
> open ora_sid;
> fetch ora_sid INTO v_osid;
> close ora_sid;
> open rec_array FOR select processing_spf from processing_spf_at_v_osid
> where spf = p_spf;
> pass_ref_cur(rec_array,p_processing_spf);
> close rec_array;
> end;
> /

Truly no help should be given as you didn't include the actual error message but this has enough problems none is required.

First off there is no value in the cursor except to slow things down. Rewrite as:

BEGIN
   SELECT oracle_sid
   INTO v_osid
   FROM meps_system_info
   WHERE meps_id = p_meps_id;

   ...
END;
/

Then you can not use the v_osid variable as though it is a string literal. What is it you are actually trying to do?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)