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

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

  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

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

  Was this answer useful?  Yes

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.

  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.

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

  Was this answer useful?  Yes

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

  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.

Thanks

  Was this answer useful?  Yes

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

  • 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

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
  1. span style="font-style: italic;">--

    2. 

  2. --

  Was this answer useful?  Yes

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:

  Was this answer useful?  Yes

kishore

  • Jul 11th, 2013
 

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

  Was this answer useful?  Yes

hitesh

  • Jul 29th, 2013
 

before , after, intead of

  Was this answer useful?  Yes

anjan

  • 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

neelu

  • Dec 1st, 2014
 

Statement level trigger, Row level trigger

  Was this answer useful?  Yes

anitha

  • Feb 26th, 2015
 

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

  Was this answer useful?  Yes

shyam

  • Mar 2nd, 2015
 

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

Code
  1. create package pack

    2. 

  2. is

    3. 

  3. type mul_val is record

    4. 

  4. (emp_id employees.employee_id%type,

    5. 

  5. emp_name employees.last_name%type);

    6. 

  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