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 first published in February 2004

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close