What is ref cursor in pl/sql?

Questions by sandip_kate

Showing Answers 1 - 17 of 17 Answers

mahi

  • Mar 16th, 2006
 

Cursor variables are like pointers to result sets. You use them when you want to perform a query in one subprogram, and process the results in a different subprogram (possibly one written in a different language). A cursor variable has datatype REF CURSOR, and you might see them referred to informally as REF CURSORs.

  Was this answer useful?  Yes

Cnu

  • Mar 18th, 2006
 

A REF CURSOR is majorly used when we want to execute a dynamic select statement causes to retrieve more than one record. We can pass Ref cursor result as a out parameter which can be used in other subprograms.

Static cursor (general) is associated with only one SELECT statement.It can't be with many.That too we can't use at dynamic time.

Ref Cursors also known as Dynamic cursors cane be associated with as many SELECT statements you want at different times.You can associate with different SELECT stataments at dynamic time.

pramod kumar

  • Apr 6th, 2006
 

Ref cursor are the pointor to the server side cursor variables, they can be attached to diff statments and can store diff value during runtime.i mean they act basically dynamiclly , we call them as true PLSQL variable.

  Was this answer useful?  Yes

Divesh

  • Apr 23rd, 2006
 

ref cursor are just like pointer which can point to different select statements ie same cursor can be associated with different select statements

g_sidhu

  • Feb 1st, 2008
 

Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself. Thus, declaring a cursor variable creates a pointer, not an item. In PL/SQL, a pointer has the datatype REF X, where REF is short for REFERENCE and X stands for a class of objects. A cursor variable has datatype REF CURSOR. Like a cursor, a cursor variable points to the current row in the result set of a multirow query. However, cursors differ from cursor variables the way constants differ from variables. A cursor is static, but a cursor variable is dynamic because it is not tied to a specific query.

  Was this answer useful?  Yes

Sadashiv

  • Aug 8th, 2011
 

REF Cursor OR Dynamic Cursors:

Query associated with cursor is not known at the time of compilation. The select statement associated with cursor is changing at the time of running the program

Syntax:

Code
  1.    Type<REF_Cursor_Type> IS REF CURSOR[RETURN<Data_Type>];

  2. EX:

  3.    TYPE ref_cur_type1 IS REF CURSOR,RETURN emp %ROWTYPE;

  4.           --->Cursors which returns STRONG Cursor.

  5.  

  6.   TYPE ref_cur_type2 IS REF CURSOR;

  7.         --->Cursor which returns Weak Cursors

  Was this answer useful?  Yes

Sahaja Mallarapu

  • Aug 26th, 2011
 

A Ref Cursor is a variable that points to cursor.A ref cursor allows you to store the result of a "bulk collect" from a table (or any PL/SQL cursor return set) into a PL/SQL table and then reference the array with the ref cursor as a pointer.

There are two types of Ref cursors:
1.Weak ref cursor: cursor which does not have a return type in its definition.

Code
  1. DECLARE

  2. TYPE tcursor IS REF CURSOR;

  3. tcur tcursor;

  4. e1 emp%ROWTYPE;

  5. d1 dept%ROWTYPE;

  6. tname VARCHAR2(20);

  7. BEGIN

  8. tname := &tablename;

  9. IF tname = 'emp' THEN

  10. OPEN tcur FOR SELECT * FORM emp;

  11. DBMS_OUTPUT.PUT_LINE ('Emp table opened.');

  12. CLOSE tcur;

  13. DBMS_OUTPUT.PUT_LINE ('Emp table closed.');

  14. ELSE IF tname = 'dept' THEN

  15. OPEN tcur FOR SELECT * FROM dept;

  16. DBMS_OUTPUT.PUT_LINE ('Dept table opened.');

  17. CLOSE tcur;

  18. DBMS_OUTPUT.PUT_LINE ('Emp table closed.');

  19. ELSE

  20. RAISE_APPLICATION_ERROR (-20004, 'Table name is wrong');

  21. END IF;

  22. END;


Eg:

2.Strong ref cursor: cursor which has a return type in its definition.

Eg:

Code
  1. DECLARE

  2. TYPE ecursor IS REF CURSOR RETURN emp%ROWTYPE;

  3. ecur ecursor;

  4. e_rec emp%ROWTYPE;

  5. dn NUMBER;

  6. BEGIN

  7. dn := &deptno;

  8. OPEN ecur FOR SELECT * FROM emp WHERE deptno = dn;

  9. FOR e_rec IN ecur

  10. LOOP

  11. DBMS_OUTPUT.PUT_LINE ('Employee No : ' || e_rec.empno);

  12. DBMS_OUTPUT.PUT_LINE ('Employee Salary: ' || e_rec.salary);

  13. END LOOP;

  14. END;

  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