gomathi

• 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

aseemnaithani

 Profile Answers by aseemnaithani Questions by aseemnaithani

• Mar 19th, 2006

 

we return one more than one value by using ref cursors.

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....

 

harikishan

• 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

pandish

 Profile Answers by pandish Questions by pandish

• Apr 30th, 2006

 

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.

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.

rupak

• 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

Rupak

Rajeshwaran

• 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...

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.

Thanks

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 7th, 2008

 

Here is the example for using out parameter in functions

CREATE OR REPLACE FUNCTION TEST_FUNC(A IN NUMBER, B IN OUT NUMBER,C OUT NUMBER) RETURN NUMBER IS

D NUMBER(3);

BEGIN

B := B + A;

C := B;

D := A + B + C;

RETURN D;

END;

DECLARE

A1 NUMBER(3) := 10;

B1 NUMBER(3) := 20;

C1 NUMBER(3) := 30;

D1 NUMBER(3) := 40;

BEGIN

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

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

END;

srm_ranjan

 Profile Answers by srm_ranjan 

• 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.

Art11

 Profile Answers by Art11 

• 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.

Code
SET serveroutput ON


DECLARE


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


  v_RetVal  t_EmpNoArr:= t_EmpNoArr();


--


FUNCTION retArray RETURN t_EmpNoArr


IS


  v_empnoArr t_EmpNoArr:= t_EmpNoArr();


BEGIN


  FOR i IN ( SELECT empno FROM emp1 )


  LOOP


    v_empnoArr.extend() ;


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


  END LOOP;


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


    DBMS_OUTPUT.PUT_LINE(v_empnoArr(j));


  END LOOP;


 RETURN v_empnoArr;


END retArray;


--


BEGIN


  v_RetVal:= retArray();


END;


/


 

RJ

• 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:

kishore

• Jul 11th, 2013

 

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

hitesh

• Jul 29th, 2013

 

before , after, intead of

anjan

• Jul 31st, 2013

 

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

neelu

• Dec 1st, 2014

 

Statement level trigger, Row level trigger

anitha

• Feb 26th, 2015

 

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

shyam

• Mar 2nd, 2015

 

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

Code
create package pack


is


type mul_val is record


(emp_id employees.employee_id%type,


emp_name employees.last_name%type);


end pack;

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

ace1234567

 Profile Answers by ace1234567 

• Mar 4th, 2015

 

Please find the template. Using out type parameter we can return more than one value from a function.

Code
CREATE OR REPLACE FUNCTION FUN_TEST(var1 VARCHAR2,var2 VARCHAR2,result OUT VARCHAR2) RETURN NUMBER


IS


.


.


BEGIN


.


.


END;