How to return more than one value from a function?What are the types of triggers?What are the features of oracle 9i

Questions by aseemnaithani   answers by aseemnaithani

Showing Answers 1 - 20 of 20 Answers


  • Mar 18th, 2006

we can return only one value at a time. if we want more then one value we directly assign that value to the variable(ii) Types of triggers(*) row level trigger(*) statement level trigger(*) Before Trigger(*) After Trigger

  Was this answer useful?  Yes

Antony Gubert

  • Mar 20th, 2006

The following are some of the new features of oracle 9i....

1. Flash Back Query

2. Function based indexes

3. Materialized view enhancement etc....


  Was this answer useful?  Yes


  • Mar 24th, 2006

1)Use Ref cursor to retuen more than one value. 2)Till oracle 8i there are 13 types of Database triggers. For 3 DML operations (ADD,DEL,MOD) before and after triggers Ex Before delete, After delete, Before update after update etc that'll make 6 triggers. For each 6 row level and statemnet level that'll make 12 types of triggers. 1 instead of triggers. to manipulate complex views . from 9i onwards new triggers have been introduced to logon and log off.3) There are 400 new features in oracle 9i. but mostly they are into DW concepts. and for developers point of view. Merge, Flashback query, New features in DB triggers, aditional features in Row id etc. Hope this wil helpHarikishan

  Was this answer useful?  Yes

If you want to return more than 1 value & dont want to use ref curser...try this, 1. Return comma separated varchar & then use dbms_utility.comma_to_table function to parse them.2. Try returning variable of type pl/sql table or varray.I have not tried number 2, but should work.

  Was this answer useful?  Yes

Krishna Mohan

  • May 12th, 2006

1. By default a function would return only one value.

2. For returning multiple values declare a ref cursor in package spec and use the same in the function/procedure in the package body.

3. Declare multiple variables as out parameters in the function/procedure being used . This will also return multple values.


  • May 24th, 2006

HI Shekhar,

                 can you please give me some example that how to return multiple values from a function using a comma separated varchar and parse it. I'm stuck in that situation and found solution using varry type. If getting the comma separated varchar would be better.

Thanks & regards


  Was this answer useful?  Yes


  • Jun 3rd, 2006

More than one values can be returned to the calling environment by a function is achieved by declaring INOUT parameters or OUT parameters in the function .

but it is not good programming practise to force a function to return more than one values...

  Was this answer useful?  Yes

Samir Narielwala

  • Jul 30th, 2007

Hi K.Mohan,

Can you provide a simple code showing how ref cursor can be used to return more than one values from the function? It would be very helpful.


  Was this answer useful?  Yes

Here is the example for using out parameter in functions




B := B + A;

C := B;

D := A + B + C;




A1 NUMBER(3) := 10;

B1 NUMBER(3) := 20;

C1 NUMBER(3) := 30;

D1 NUMBER(3) := 40;


D1 := TEST_FUNC(A1,B1,C1);

DBMS_OUTPUT.PUT_LINE(A1 || ' ' || B1 || ' ' || C1 || ' ' || D1);



  • Nov 11th, 2008

Functions always returns One value. If you want to return more than One value, You can use the Out parameter. Using out parameter you can get more than one value, but you cannot use that function in Select Query.

  Was this answer useful?  Yes


  • Sep 27th, 2011

The function always return a SINGLE value, which includes arrays. Check out the table functions (pipelined), BULK_COLLECT, arrays etc... You can find plenty of examples.

  1. SET serveroutput ON

  2.   TYPE t_EmpNoArr IS VARRAY(20000) OF NUMBER(10) ;

  3.   v_RetVal  t_EmpNoArr:= t_EmpNoArr();

  4. --

  5. FUNCTION retArray RETURN t_EmpNoArr

  6.   v_empnoArr t_EmpNoArr:= t_EmpNoArr();

  7.   FOR i IN ( SELECT empno FROM emp1 )

  8.   LOOP

  9.     v_empnoArr.extend() ;

  10.     v_empnoArr(v_empnoArr.COUNT):= i.empno ;

  11.   END LOOP;

  12.   FOR j IN 1..v_empnoArr.COUNT() LOOP

  13.     DBMS_OUTPUT.PUT_LINE(v_empnoArr(j));

  14.   END LOOP;

  15.  RETURN v_empnoArr;

  16. END retArray;

  17. --

  18.   v_RetVal:= retArray();

  19. /


  Was this answer useful?  Yes


  • Oct 17th, 2011

There are five types of Triggers

1) Data Definition Language triggers: These triggers fire when you make changes to the objects in the database like create, update or delete. They can be implemented to monitor the changes in the schema.
2) Data Manipulation Language Triggers:
3) Compound Triggers:
4) Instead-Of Triggers:
5) System or Database Event Triggers:

  Was this answer useful?  Yes


  • Jul 11th, 2013

By using pipe-lined concept we can return more than one value in function

  Was this answer useful?  Yes


  • Jul 29th, 2013

before , after, intead of

  Was this answer useful?  Yes


  • Jul 31st, 2013

if u create a object , and use this type to return type, then you return multiple value.

  Was this answer useful?  Yes


  • Dec 1st, 2014

Statement level trigger, Row level trigger

  Was this answer useful?  Yes


  • Feb 26th, 2015

we can return multiple values by using return statement and out, inout.

  Was this answer useful?  Yes


  • Mar 2nd, 2015

Yes, we return more than one value in function using package.

  1. create package pack

  2. is

  3. type mul_val is record

  4. (emp_id employees.employee_id%type,

  5. emp_name employees.last_name%type);

  6. end pack;

and the return type of function is that pack type.....

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions