• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 676
  • Last Modified:

How to return records from Oracle table type using Ref Cursor

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
claghorn
Asked:
claghorn
  • 3
  • 3
2 Solutions
 
slightwv (䄆 Netminder) Commented:
>>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
 
claghornAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
claghornAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
claghornAuthor Commented:
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

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!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now