Home arrow Oracle arrow Page 2 - Database Interaction with PL/SQL, Working with TABLE in Sub-programs, Parameter Modes

Returning PL/SQL TABLE from a FUNCTION to a PROCEDURE - Oracle

This is part 15 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we looked at several examples that covered the use of sub-programs. In this article we will work with PL/SQL TABLE types in between sub-programs. We will also discuss IN, OUT and IN OUT types of parameters in this article.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, Working with TABLE in Sub-programs, Parameter Modes
  2. Returning PL/SQL TABLE from a FUNCTION to a PROCEDURE
  3. What are IN types of parameters?
  4. What are OUT types of parameters?
  5. What is an IN OUT type of parameter?
By: Jagadish Chatarji
Rating: starstarstarstarstar / 23
August 30, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In the previous section of this article, I used a PL/SQL TABLE as a parameter for the PROCEDURE.  Now, I shall revise the above stated program in such a way that I will use both FUNCTION and PROCEDURE together, communicating with PL/SQL TABLEs.  Let us consider the following example:

declare

    type t_emptbl is table of emp%rowtype;
    procedure dispEmp(p_emptbl t_emptbl) is

    begin

        for i in p_emptbl.first .. p_emptbl.last

           loop

               dbms_output.put_line(p_emptbl(i).ename ||
               ' earns ' || p_emptbl(i).sal);

           end loop;

    end;

    function getEmployeeList return t_emptbl is

        v_emptbl          t_emptbl;                    

        begin

            select * bulk collect into v_emptbl from emp;

            return v_emptbl;

        end;

     BEGIN

        dispEmp(getEmployeeList);

     END;

 

Let us examine the two sub-programs separately.

procedure dispEmp(p_emptbl t_emptbl) is

    begin

       for i in p_emptbl.first .. p_emptbl.last

       loop

                                    dbms_output.put_line(p_emptbl(i).ename || ' earns ' || p_emptbl(i).sal);

       end loop;

    end;

It just receives PL/SQL TABLE as parameter and displays all the information available in it.

function getEmployeeList return t_emptbl is

         v_emptbl          t_emptbl;                     

    begin

         select * bulk collect into v_emptbl from emp;
         return v_emptbl;

     end;

The above function ‘getEmployeeList’ executes the SELECT statement and the output is gathered into ‘v_emptbl’, which is returned back (or pushed out).

dispEmp(getEmployeeList);

The above is the most important statement in the program.  It looks very simple, but a lot of logic is embedded in it.  First of all, it executes the FUNCTION ‘getEmployeeList’.  That FUNCTION returns a PL/SQL TABLE, which will be sent as a parameter to the procedure ‘dispEmp’ automatically.  We need not actually store the PL/SQL TABLE returned by the FUNCTION.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: