asked on

retrieving data from a ref cur when calling from a stored procedure

Hello,
Is this the correct way in retrieving information using a ref cur when calling from a stored procedure?
Thanks.

--Main Procedure..
PROGCDXREF ref cursor;
WS_PROGRAM_NM varchar2(20) := 'TNT';
ws_program_cd varchar2(5);

--execute the procedure to retrieve the program code...
SELECT_PROGRAM_CD_XREF(ws_program_nm, PROGCDXREF);

WS_PROGRAM_CD := PROGCDXREF.PROGRAM_CD;

--End of Main Procedure...

--Procedure - Select_Program_CD_Xref...
procedure SELECT_PROGRAM_CD_XREF(V_PROGRAM_NM varchar2,
CUR_SELECT_PROGRAM_CD_XREF OUT REF CURSOR)
IS
begin
Open cur_select_Program_CD_Xref For
SELECT
Program_NM,
Program_CD,
deactivate_dt
FROM Program_CD_Xref
WHERE PROGRAM_NM = V_PROGRAM_NM;

End Select_Program_CD_Xref;

slightwv (䄆 Netminder)

Did you try it?

No. Once the ref cursor is returned, you need to FETCH the values.

You typically do this in a loop.

There is an example here. It makes no difference if the cursor is created in the same code or created from a procedure call. The loop and FETCH is the same.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/expressions006.htm#SQLRF52079

Steve A

ASKER

Thanks for the reply,
Yes, the above code is within another loop that a regular cursor selects the
program_nm into the variable, ws_program_nm.
You would think that the ref cursor - PROGCDXREF will return the values from the
select procedure and then you can select individual columns within that ref cur.
Thanks.

slightwv (䄆 Netminder)

A cursor isn't a true result set. It is only a pointer to the 'query'. Just opening it doesn't really do anything. It isn't until you fetch the data is there any data available.

Steve A

ASKER

Just looking how this code is just supposed to retrieve a program code, it would seem prudent to just write a function to do this, instead of using a ref cursor. Something like this:

--Main Procedure..
WS_PROGRAM_NM varchar2(20) := 'TNT';
ws_program_cd varchar2(5);

--execute the procedure to retrieve the program code...
Select GET_PROGRAM_CD(ws_program_nm) into ws_program_cd from dual;

--End of Main Procedure...

function GET_PROGRAM_CD(V_PROGRAM_NM varchar2)
is varchar2
v_program_cd varchar2(3);
begin

SELECT
Program_CD,
FROM Program_CD_Xref
where PROGRAM_NM = V_PROGRAM_NM;

End;

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

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

Start Free Trial

Steve A

ASKER

Thanks for the information.
I am going to go ahead and just use this function psuedo-code as an example, as this will be used many times
within the main procedure(s) to retrieve a program code, so will be the best approach to this.
Thanks again!