Solved

How to return records from Oracle table type using Ref Cursor

Posted on 2011-09-15
6
653 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
  • 3
  • 3
6 Comments
 
LVL 76

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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Include a logo in email body using Oracle utl_mail 11 41
Oracle encryption 12 48
join actual table rows based on the column 25 21
plsql job on oracle 18 34
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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 video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

860 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