How can I return a set of rows through the Oracle8i stored procedure?

    Requires Free Membership to View

    Login

You can use the following features of ORACLE PL/SQL to return a set of rows through a stored procedure in Oracle:
1) VARRAY
2) TABLE
3) REF CURSOR

Using VARRAY

You can use VARRAY to create an ordered set of data elements. Use the VARRAY as a return parameter in a FUNCTION. The restriction on VARRAY is that it has an upperbound. For example please see the example below

Let us first create a table named contact table. Then we create two types, one is an object and the other is a VARRAY of that object. 

create table contact_table
(first_name   varchar2(50),
last_name     varchar2(50),
phone_number  varchar2(50))
/


create or replace type mycontact is object 
      (first_name varchar2(50), last_name varchar2(50),  phone_number(50))
/

create or replace type mycontact_array is varray(100) of mycontact;
/

Now the function below wil return a set of rows. 

CREATE OR REPLACE FUNCTION getmycontact_with_varray
    RETURN mycontact_array
AS
    l_data mycontact_array := mycontact_array();
    CURSOR c_contact IS SELECT first_name, last_name, phone_number   FROM contact_table;
BEGIN
      FOR contact_rec IN c_contact LOOP
       l_data.extend;
       l_data(l_data.count).first_name := contacT_rec.first_name;
       l_data(l_data.count).last_name := contacT_rec.last_name;
       l_data(l_data.count).phone_number := contacT_rec.phone_number;
       END LOOP;
       RETURN l_data;
END;

Using TABLE

The table data type is used and it is unordered but it does not have an upper bound unlike VARRAY.

So, using the CONTACT_TABLE and mycontact object above, we create the following function that returns a set of rows as a table data type.

First we create the following type: 

create or replace type mycontact_table is table of mycontact;
/


CREATE OR REPLACE FUNCTION getmycontact_with_table
    RETURN mycontact_table
AS
    l_data mycontact_table := mycontact_table();
     CURSOR c_contact 
     IS SELECT first_name, last_name, phone_number 
        FROM contact_table;
BEGIN
     FOR contact_rec IN c_contact LOOP
       l_data.extend;
       l_data(l_data.count).first_name := contacT_rec.first_name;
       l_data(l_data.count).last_name := contacT_rec.last_name;
       l_data(l_data.count).phone_number := contacT_rec.phone_number;
       END LOOP;
       RETURN l_data;
END;
/


Using REF CURSOR

First you have to weakly define a cursor in a package to have it available later. 

create or replace package my_contact
as
 type my_ref_cursor IS REF CURSOR;
END;
/


CREATE OR REPLACE FUNCTION getmycontact_with_ref RETURN my_contact.my_ref_cursor
 AS
     l_data my_contact.my_ref_cursor;
 
     cur_str VARCHAR2(2000):= 
      'SELECT first_name, last_name, phone_number 
         FROM contact_table';
 BEGIN
      open l_data for cur_str;
 
      RETURN l_data;
 END;
 /

This was first published in February 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top