Solved

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

Posted on 2014-09-29
6
265 Views
Last Modified: 2014-09-29
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;
0
Comment
Question by:Machinegunner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40350322
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
 

Author Comment

by:Machinegunner
ID: 40350498
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40350510
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Machinegunner
ID: 40350545
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40350554
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
 

Author Closing Comment

by:Machinegunner
ID: 40350562
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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PL SQL Search Across Columns 4 62
Extract Currency data from a string and put them in a new field 3 48
make null the repeated levels 2 36
Creation date for a PDB 5 63
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question