Answer / swapna

We can return one more than one value by using reference
cursors.

Answer / re

hey kumaran ...

its not like out paramaters cannot used.........

but its not preferred ...

its just simple ... just move basics why/for what a function
, procedures is used..

also with conditional if stmts... result is that a single
value is returned
re

Answer / alok sinha

Hello Boyz !!

Get excited to see this example thru

declare
--
varx1 number := 0;
varx2 number := 0;
varx3 number := 3;
--
begin
--
--
dbms_output.put_line('In: Varx #1 : initial value :: ' ||
varx1);
dbms_output.put_line('In: Varx #2 : initial value :: ' ||
varx2);
--
dbms_output.put_line('In: Varx #3 : Initial value :: ' ||
varx3);
dbms_output.put_line('

');
dbms_output.put_line('Hhhmmmnnn
Hhhmmmnnn Hhhmmmnnn ');
dbms_output.put_line('

');
--
--
varx3 := xx_func(varx3, varx1, varx2);
--
--
dbms_output.put_line('------------------------------------
--------------');
dbms_output.put_line('---------- Post Function -----------
------');
dbms_output.put_line('------------------------------------
--------------');
dbms_output.put_line('

');
dbms_output.put_line('------------------------------------
--------------');
dbms_output.put_line('Return Value :: Varx #3 :: returned
from function :: '|| varx3);
dbms_output.put_line('Out: Varx #1 : returned from
function :: '|| varx1);
dbms_output.put_line('Out: Varx #2 : returned from
function :: '|| varx2);
--
end;


create or replace function xx_func(north in number, east in
out number, west in out number ) return number is
south number;
begin
south := north + 4;
east := 4;
west := 5;
--
return south;
end xx_func;

Answer / mglbayar

We can use oracle pipelined function

FUNCTION f_serie_arithmetica (p_nr IN NUMBER)
RETURN ARRAY PIPELINED
IS
v_sum NUMBER;
BEGIN
v_sum := 0;
FOR i IN 1 .. p_nr
LOOP
v_sum := v_sum + i;
PIPE ROW (v_sum);
END LOOP;
RETURN;
END;

Usage:
select * from table(f_serie_arithmetica(23));

Answer / kumar

OUT PARAMETER AND REF CURSOR

Answer / ravivarman_r

Public Type Income
Wages As Currency
Dividends As Currency
Other As Currency
Total As Currency
End TypeYou can now use this structure as the return
type for a function. In a real situation, the function
would look up your database tables to get the values, but
the return values would be assigned like this:

Function GetIncome() As Income
GetIncome.Wages = 950
GetIncome.Dividends = 570
GetIncome.Other = 52
GetIncome.Total = GetIncome.Wages +
GetIncome.Dividends + GetIncome.Other
End FunctionTo use the function, you could type into
the Immediate Window:

GetIncome().Wages

Answer / prajwal maloo

USING OUT AND REF PARAMETERS

Answer / siva

Create or replace procedure proc(n number,s out sys_refcursor)
is
Begin
open s for select ename from emp where deptno=n;
end:

Var c ref_cursor;
exec proc(10,:c);
print : c;
Clark
King
Miller

Answer / vetrikumaran

WE CAN RETURN MORE THAN ONE VALUE FOR A FUNCTION BY USING
THE CONDITIONAL IF STATEMENT..BUT ONLY ONE RETURN STATEMENT
CAN BE EXECUTED AT A TIME..

IN FUNCTION,BY DEFAULT IN PARAMETER MODE ONLY ALLOWED...

OUT PARAMETERS ARE NOT ALLOWED.

Answer / murali

We can return more than one row using out parameter. Try
this with the following.

create or replace function my_func(myno number, myname out
varchar2) return number as
mysal emp.sal%type;
begin
select ename, sal into myname, mysal from emp where
empno=myno;
return sal;
end;

in the above example we are returning first value mysal
using return statement and second value myname using out
parameter.

What is difference between function and trigger?

0 Answers  

What is differance unique key and primary key.

7 Answers   EDS,

What is AUTH_ID and AUTH_USER in pl/sql ?

0 Answers   INDUS,

what is meant by DML?

5 Answers   HCL,

Why join is faster than subquery?

0 Answers  

What are the basic sql commands?

0 Answers  

How to find 3rd highest salary of an employee from the employee table in sql?

0 Answers  

Explain the structure of pl/sql in brief.

0 Answers  

what are integrity rules?

0 Answers  

what are the difference between clustered and a non-clustered index? : Sql dba

0 Answers  

1 SELECT a.field1, b.field2, c.field3, d.field4 2 FROM atable a, atable b, ctable c, dtable d 3 ? 4 ORDER BY 1 What is the minimum number of joins that must be specified on line 3 in the sample code above to properly link the tables? Notice that the table "atable" is aliased twice: once as "a" and once as "b." 1. One join 2. Two joins 3. Three joins 4. Four joins 5. Five joins

6 Answers   Sonata,

what are the methods using performance tunning in sql and pl/sql

2 Answers