Oracle function return multiple values

Oracle function return multiple values DEFAULT

Normally when we came across the difference between function and procedure, we say that function always returns value(e.g single) where as procedure may or may not. But we can use OUT parameter to return multiple value from a procedure. Similarly we can also return multiple value from a function by using TABLE type object.

TABLE type objects are defined from a user defined collection/object type. We can also say that collection type object can be made as TABLE type object in oracle plsql.

Here we are going to returns two column’s value from our function. So it is necessary to define our user type collection object having two data types.

STEP – 1 Create Object Type (e.g COLLECTION)/Defining Object Type:

An object type is a kind of data type. It can be used like our standard data type such as NUMBER,VARCHAR. You can also specify an object type as a column type of the database table.

Here we are considering the EMP table for our below example process.

Create or Replace type emp_type_o as Object


ename varchar2(15), //You may define other name rather than ename/job but the data type

job varchar2(12) should be same as the existing EMP table’s ename/job column.


Once it is executed, “emp_type_o” type is created as a collection type object having two parameter. Then we have to define a table type of the same object type Bcoz function can return multiple value with the help of table type object.

STEP – 2 Create Type as TABLE:

Create type emp_type_t is table of emp_type_o ;

It shows that “emp_type_t” is a table type object of emp_type_o object.

STEP – 3 Create Function to Return the Type as table:

Create or Replace function get_emp_list (id number) return emp_type_t


v_ename emp_type_t; // Define a variable of the same table object type


select emp_type_o(ename,job) bulk collect

into v_ename from emp

where deptno= id;

return v_ename; // Returned the table type object variable.


The above function takes “id” as a parameter. Bcoz it fetches the related employee’s ename and job based on that id(e.g deptno). The variable “v_ename” is a variable of that table type object and it is used in the sql query to keep all the returned o/p value sets. The object “emp_type_o” in the select query helps only to fetch the name and job column values from the emp table into the table type variable [e.g v_ename]. To returned a bulk/mass data from database table we normally use the BULK COLLECT concept. It is a technique that reduces the multiple Context Switches. So less Context Switch occurs in the plsql program, the execution is more faster.

What is Context Switch:

When the PL/SQL runtime engine processes a block of code, it executes the procedural statements within its own engine, but passes the SQL statements on to the SQL engine. The SQL layer executes the SQL statements and then returns information to the PL/SQL engine, if necessary.

This transfer of control between the PL/SQL and SQL engines is called a context switch. Each time a switch occurs, there is additional overhead. There are a number of scenarios in which many switches occur and performance degrades.

STEP – 4 Call Function which Returns Data Set:

select * from table ( get_emp_list (20));

This SQL query is used to fetch the exact data set. But to do this, it must use the oracle inbuilt table function. This function takes our above defined function [e.g get_emp_list] as a parameter. So from this example it’ll show all employee’s ename and job for department no


Ref :


Like this:




Contact US


Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!

  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Us!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Tek-Tips Posting Policies

Contact US

Oracle Function that returns multiple values

Oracle Function that returns multiple values



Hi All,

This might be a silly question but, I am trying to write a function in oracle that returns multiple values.I looked at some of the forums and online docs and found out that functions in oracle returns single value. Is there a way we can write this to return multipl values.If yes,can someone post the syntax of the function.I have some thing like this

Create Function Fun(MinDate In Date,MaxDate,WeekDays Out number,WeekEnds out number) return as number

--My logic goes here to calculate WeekDays and WeekEnds.
--say assigned the values to WeekDays and WeekEnds
WeekEnds:= 3;
WeekDays:= 7;

Return WeekEnds,WeekDays  --Does this Work ?


Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

  1. House for sale lubbock
  2. Popular grey paint colors
  3. 7 watts modded controller instructions
  4. P100d 0 60
  5. Lockheed martin staff engineer salary

PL/SQL function return multiple values

Burleson is the American Team

Note:This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience!Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Copyright © - 

All rights reserved by Burleson


® is the registered trademark of Oracle Corporation.


0606 Functions That Returns Multiple Values

She is a gift from the Gods. She is the beauty of pain. She is an innocent young dove.

Return values multiple function oracle

Stephen advanced menacingly. Mark fainted in surprise. But then his friend chuckled and slapped him on the shoulder. Mark, you're a bloody maniac. Heh, and youll come up with it.


So I already finish like a girl - from the fact that they fuck me. Although I tried to work actively, this, apparently, was not enough for Vadim. Therefore, he grabbed my head and began to push on his penis. Its barrel rested right into my throat. I began to choke and felt nauseous, tears flowed from my eyes.

Now discussing:

Irka called me into the hallway, leaving Kostya in the kitchen and arranged an interrogation, looking at me with surprise, she did not expect to see such. A scene. - Not only to paw Ir, but also give the guy, Kostya is my fucker and I sleep with him in the same bed.

3555 3556 3557 3558 3559