krishnamohan_geek
Profile Answers by krishnamohan_geek

Mar 25th, 2007

Hi,
Let me answer your second questin first. Why ref cursor instead of %ROWTYPE.
%ROWTYPE is used for fetches record/row of a single table.
OR in case of columns of different tables then you have to create a RECORD and then used <recordname>%ROWTYPE.

But if you have a cursor which gets data dynamically based on some input parameters and you would like to fetch the record/row then you cannot go for rowtype instead you need a REF CURSOR.
REF CURSORS are used when the query in the cursor needs to be changed dynamically.
Say in a cursor query
CURSOR cur_emp IS
SELECT emp_id,emp_name,emp_address
FROM <tablename>;

say if the logged in used is a employee then in the above query <tablename> = EMP and if the logged in user is a admin then <tablename> = ADMIN

this can be acheived only through REF CURSOR.

SO in the proc DECLARE section use

TYPE cur_ref IS REF CURSOR cur_emp%ROWTYPE;

v_refcur cur_ref%TYPE;

BEGIN

text_query := 'SELECT emp_id,emp_name,emp_address'
IF (logged_user = 'EMP') THEN
text_from := 'FROM EMP';
ELSE
text_from := 'FROM ADMIN';
END IF;

OPEN cur_emp FOR (text_query || ' ' || text_from)
LOOP
{
write the code
};
END LOOP;

For futher clarification on the syntax and examples of the code please refer to any PL/SQL text book.

Rgds,
Krishna

Oracle referance cursor code

Hi,
1. Iam looking for sample program for ref cursor. ref cursor can be associated with many select statements and we can use the ref cursor to return the multiple value from the procedure or function. how to use the ref cursor in a program to associate with many select statements.

2. we can't use the %rowtype attribut to return the multiple from the procedure or function instead of ref cursor.
if we can use the %rowtype attribute then what is difference between ref cursor and %row type attribute.