Steve A
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_P ROGRAM_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;
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_
WS_PROGRAM_CD := PROGCDXREF.PROGRAM_CD;
--End of Main Procedure...
--Procedure - Select_Program_CD_Xref...
procedure SELECT_PROGRAM_CD_XREF(V_P
CUR_SELECT_PROGRAM_CD_XREF
IS
begin
Open cur_select_Program_CD_Xref
SELECT
Program_NM,
Program_CD,
deactivate_dt
FROM Program_CD_Xref
WHERE PROGRAM_NM = V_PROGRAM_NM;
End Select_Program_CD_Xref;
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.
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.
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.
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_N M varchar2)
is varchar2
v_program_cd varchar2(3);
begin
SELECT
Program_CD,
FROM Program_CD_Xref
where PROGRAM_NM = V_PROGRAM_NM;
End;
--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_
--End of Main Procedure...
function GET_PROGRAM_CD(V_PROGRAM_N
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!
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