Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to return records from Oracle table type using Ref Cursor

Posted on 2011-09-15
6
Medium Priority
?
671 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

636 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