PL/SQL - Purpose of Ref cursor and OUT parametr in PLSQL Function?

GeekInterview.com > Interview Questions > Oracle > PL/SQL

Go To First | Previous Question | Next Question

PL/SQL | Question 222 of 241

Purpose of Ref cursor and OUT parametr in PLSQL Function?

What is the purpose of Ref Cursor and OUT Parameter in PLSQL Function? Give Examples for each?

Total Answers and Comments: 3

Last Update: October 14, 2009 Asked by: ramyaselvi16

Sponsored Links

Best Rated Answer

—

Submitted by: pratap557

By Ref cursor we can send record set to the calling program

Like .NET VB page can get recordset as datatable from ref cursor returned by a procedure.
OUT parameter sends only one value from procedure

Above answer was rated as good by the following members:
Blufontaine

June 28, 2008 14:15:50

KD09714

Member Since: June 2008 Contribution: 11

RE: Purpose of Ref cursor and OUT parametr in PLSQL Function?

A function can return one value by return statement itself.
Both are used to return more than one value in the function. As long as we are executing function same as procedure (i.e. not from any select statement or DML) out parameter is sufficient to return more than one value from function.

Example :

Create or replace function func1
(id1 IN <datatype1>
id2 OUT <datatype1>
id3 OUT <datatype1>
id4 OUT <datatype1>
id5 OUT <datatype1>
id6 OUT <datatype1>
.....
)
return <datatype>
AS

BEGIN
--body of procedure

END func1;

Calling function from any block

BEGIN
.....
var1 func1(var2 var3 var4 var5 var6 var7 ......);
....
END;

----------------

But when we need to call the function from any select statement or DML we cant write OUT or IN OUT parameter in function thus we can take the help of ref cursor to return more than one values without using out parameters.
Or without using OUT parameter we can return more than one value from function normally (function not called from select DML).

Also definition of ref cursor can be decided at run time.

Example of ref cursor:

DECLARE
A NUMBER : 10;
B NUMBER ;
C NUMBER ;
D NUMBER ;
FUNCTION TEST_FUN (A IN NUMBER B OUT NUMBER C IN OUT NUMBER) RETURN NUMBER IS
D NUMBER;
BEGIN
B : A;
C : B + A;
D : A + B + C;
RETURN (D);
END ;
BEGIN
D : TEST_FUN(A B C);
DBMS_OUTPUT.PUT_LINE(' A ' || A);
DBMS_OUTPUT.PUT_LINE(' B ' || B);
DBMS_OUTPUT.PUT_LINE(' C ' || C);
DBMS_OUTPUT.PUT_LINE(' D ' || D);
END;

Using REF CURSOR we can return objects as return type. Here is example

DECLARE
TYPE EMP_REFCUR IS REF CURSOR;
EMP_CURVAR EMP_REFCUR;
TYPE EMP_REC IS RECORD
(
V_EMPNO EMP.EMPNO TYPE
V_ENAME EMP.ENAME TYPE
V_JOB EMP.JOB TYPE
V_SAL EMP.SAL TYPE
);
EMP_RECVAR EMP_REC;
FUNCTION TEST_FUN (V_EMPNO IN EMP.EMPNO TYPE) RETURN EMP_REFCUR IS
BEGIN
OPEN EMP_CURVAR FOR SELECT EMPNO ENAME JOB SAL FROM EMP WHERE EMPNO V_EMPNO;
RETURN(EMP_CURVAR);
END ;
BEGIN
EMP_CURVAR : TEST_FUN(7369);
FETCH EMP_CURVAR INTO EMP_RECVAR;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS ' || EMP_RECVAR.V_ENAME);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE DESIGNATION IS ' || EMP_RECVAR.V_JOB);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE SALARY IS ' || EMP_RECVAR.V_SAL);
END;

Is this answer useful? Yes | No

1 0

Overall Rating: +1

August 29, 2008 05:48:18

rprakashj

Member Since: August 2008 Contribution: 1

RE: Purpose of Ref cursor and OUT parametr in PLSQL Function?

Ref cursor is used when we need to assign different select statment for a single cursor in executable section.
where as out parameter is used in procedures and functions to bring out the output from a PL/SQL block.

Is this answer useful? Yes | No

October 13, 2009 04:23:52

pratap557

Member Since: October 2007 Contribution: 2

RE: Purpose of Ref cursor and OUT parametr in PLSQL Function?

By Ref cursor we can send record set to the calling program

Like .NET VB page can get recordset as datatable from ref cursor returned by a procedure.
OUT parameter sends only one value from procedure

Is this answer useful? Yes | No

1 0

Overall Rating: +1

Go To Top

User Name		Remember Me?
Password

Question Bank