Q

Creating set of rows through 8i stored procedure

How can I return a set of rows through the Oracle8i stored procedure?
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 last published in February 2004

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close