?
Solved

How to return records from Oracle table type using Ref Cursor

Posted on 2011-09-15
6
Medium Priority
?
662 Views
Last Modified: 2012-06-21
Hello, my code and question is below.  

(declared in my package header spec):

type t_inv_inquiry_table IS table of inv_inquiry_type;
TYPE test_tabtype IS REF CURSOR;

--I have the two types (t_inv_inquiry_table) and (inv_inquiry_type)
--set up correctly and in the procedure below, the table type gets filled up with data.
--so how can I assign the table type contents to the out ref cursor?
--and cycle through the ref cursor to see that data?

--this proc is in the package mentioned above.
procedure inv_inquiry3(in_sku in sku_master.sku%type, v_test_tabtype OUT test_tabtype) AS
 
-- Declare a local table structure and initialize it
-- a structure to hold a record and a type
-- that is a table of that record structure

    v_invtype t_inv_inquiry_table:= t_inv_inquiry_table();  
                                                       
    v_cnt     number := 0;
    v_rc1      sys_refcursor;
    v_rc2      sys_refcursor;
    v_rc3      sys_refcursor;
    v_rc4      sys_refcursor;
   
    v_sku   NUMBER(8);
    v_source   CHAR(3);
    v_quantity    NUMBER;
    v_status    VARCHAR2(8);

  begin
    v_rc1 := rmt_layer_inventory(in_sku);
    v_rc2 := rmt_row_pick_inventory(in_sku);
    v_rc3 := rmt_external_inventory(in_sku);
   
    loop
      fetch v_rc1 into v_sku, v_source, v_quantity, v_status;
      exit when v_rc1%NOTFOUND;
      v_invtype.extend;
      v_cnt := v_cnt + 1;
      v_invtype(v_cnt) := inv_inquiry_type(v_sku, v_source, v_quantity, v_status);
    end loop;
    close v_rc1;
   
    loop
      fetch v_rc2 into v_sku, v_source, v_quantity, v_status;
      exit when v_rc2%NOTFOUND;
      v_invtype.extend;
      v_cnt := v_cnt + 1;
      v_invtype(v_cnt) := inv_inquiry_type(v_sku, v_source, v_quantity, v_status);
    end loop;
    close v_rc2;
   
    loop
      fetch v_rc3 into v_sku, v_source, v_quantity, v_status;
      exit when v_rc3%NOTFOUND;
      v_invtype.extend;
      v_cnt := v_cnt + 1;
      v_invtype(v_cnt) := inv_inquiry_type(v_sku, v_source, v_quantity, v_status);
    end loop;
    close v_rc3;
   
  end;
0
Comment
Question by:claghorn
[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: 36545202
>>table type contents to the out ref cursor

I see "v_test_tabtype OUT test_tabtype".  I don't see an out ref cursor.

Are you wanting to know how to assign v_invtype to v_test_tabtype?
0
 

Author Comment

by:claghorn
ID: 36545289
Yes to your question. That is what I want to do. I thought the out ref cursor was in the procedure signature. Is there a better way to allow a client app to call the procedure passing in a sku and receive a ref cursor that point to the data?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1200 total points
ID: 36545372
You can return whatever data type you want.

What I'm not sure how to do is turn a user defined PL/SQL table into a ref cursor.  My guess is if this need exists, there is a flaw in the basic design.

It all depends on what your requirements are, what is calling this procedure, the data types allowed, etc...


Check out the following:
https://forums.oracle.com/forums/thread.jspa?threadID=886365&tstart=0

At the bottom pay attention to:
In summary, the one key thing to remember is..

REF CURSORS ARE NOT DATA CONTAINERS. ONCE OPENED, THEY ARE SIMPLY A POINTER TO A QUERY THAT HAS YET TO FETCH DATA.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:claghorn
ID: 36545584
I have read the forum link. Thanks.
And you answered my question with "What I'm not sure how to do is turn a user defined PL/SQL table into a ref cursor." I guess this was a bad idea:-).
Trouble is I can't put this query together in one sql statement.
I have to arrange the data in a structure of some kind.
My requirements are to return a ref cursor to the calling app which is a .NET SOAP Web service
I read this is the best way to pass data back to a client app like this.
Can you suggest a better way?
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1200 total points
ID: 36545754
>>Can you suggest a better way?

XML?  Since the SOAP call will eventually need to turn whatever you provide into XML, why not give it XML?
0
 

Author Closing Comment

by:claghorn
ID: 36545799
Ok, Thanks for your help. I'll try the XML or maybe create an actual Oracle table and create a standalone Oracle function to populate it and then read from it using something like the example cited in your link.
ie:
SQL> create or replace function get_dept_emps(p_deptno in number) return sys_refcursor is
  2    v_rc sys_refcursor;
  3  begin
  4    open v_rc for 'select empno, ename, mgr, sal from emp where deptno = :deptno' using p_deptno;
  5    return v_rc;
  6  end;
  7  /
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

765 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