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.

Is it possible to remove child records without removing master table records...the two having pk,fk relationship?

0 Answers  

How to process query result in pl/sql?

0 Answers  

What is pivot in sql?

0 Answers  

what is the cursor and use of cursor in pl/sql ?

4 Answers  

How to export the table data (this table having 18 million records) to .csv file. Please tell me is there any faster way to export the data.

8 Answers  

What are the datatypes a available in PL/SQL ?

2 Answers  

What is a relationship and what are they?

0 Answers  

What is Pragma EXECPTION_INIT ? Explain the usage ?

2 Answers  

What are all the common sql functions?

0 Answers  

Is null operator in sql?

0 Answers  

Which function is used to return remainder in a division operator in sql?

0 Answers  

type type_name is table of varchar2(30) index by binary_integer for the above type you have to create a identifier... like identifier_name type_name; for the above type you can use the below methods..like first , last , prior, next , delege..etc...like this.. if you create a cursor...like cursor cursor_name is select * from scott.emp; is there any methods like above to use prior, fist , last , next , trim ,etc...

1 Answers   Satyam,