Solved

How to return records from Oracle table type using Ref Cursor

Posted on 2011-09-15
6
647 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

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.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now