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;
MachinegunnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
0
MachinegunnerAuthor Commented:
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.
0
slightwv (䄆 Netminder) Commented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

MachinegunnerAuthor Commented:
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;
0
slightwv (䄆 Netminder) Commented:
If the result will ALWAYS be one and only one value, then a function with a single select would probably be the way to go.

The stub example you posted needs a little work though.

If the code can return more than one row and/or more than one value then something that allows a 'collection' to be returned is the way to go.  Returning a cursor is just one way.  Something that returns XML as a CLOB is another.  There are others.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MachinegunnerAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.