Opening a Cursor Variable for a Query:

In sequence to relate a cursor variable with a particular SELECT statement, the OPEN syntax is extended to permit the query to be specified. This is completed with the OPEN FOR syntax.

OPEN cursor_variable FOR select_statement; # to obtain the previous variable for a select query#

where select_statement is the desired query and cursor_variable is a previously declared cursor variable.

Note that if the cursor variable is constrained so the select list must match the return type of the cursor. If it is not constrained, the error will show

ORA-6504: PL/SQL: query or return types of result set variables do not match

For instance, given a cursor variable declaration like this,

DECLARE

TYPE T_PRODREF IS REF CURSOR RETURN PRODUCT%ROWTYPE;

V_PRODUCT            T_PRODREF;

V_PRODUCTTAB    PRODUCT%ROWTYPE;

--WE CAN OPEN V_PRODUCT WITH:

BEGIN

OPEN V_PRODUCT FOR SELECT * FROM PRODUCT;

LOOP

FETCH V_PRODUCT INTO V_PRODUCTTAB;

EXIT WHEN V_PRODUCT%NOTFOUND;  

DBMS_OUTPUT.PUT_LINE(V_PRODUCTTAB.PNAME||' '||V_PRODUCTTAB.PCODE||'

'||V_PRODUCTTAB.DEPTNO);

END LOOP;

CLOSE V_PRODUCT;

END;

The output of the program will be:

navtal lock 101 40

storewell 102 40

cinthol old 103 10

1.5 ton a/c 104 20

1.0 ton a/c 105 20

0.75 ton a/c 106 20

puf refrigerator 107 20

hair dye 108 10

shaving cream 109 10

jumpin juke 110 10

 thomas cook 111 30

If, instead, we attempt to open v_productCV the subsequent way,

OPEN v_productCV FOR SELECT deptno,pname,pcode FROM product;# attempt to open v_productCV and select deptno, pname, pcode from product table#

Raises ORA-6504, because the select list of the query does not match the return type of the cursor variable.