Examine the structure of the PRODUCTS table.
Name Null? Type
———————– ————— —————–
PRODUCT_ID NOT NULL NUMBER(6)
PRODUCT_NAME VARCHAR2(50)
CATEGORY_ID NUMBER(2)
SUPPLIER_ID NUMBER(6)
LIST_PRICE NUMBER(8,2)
View the Exhibit and examine the PL/SQL block.
On execution, the PL/SQL block generates the following error:
ORA-01001: invalid cursor
What could be the reason?
A.
Both the cursor variable arguments should have been passed in IN mode.
B.
The contents of one cursor variable cannot be assigned to another cursor variable using the :=
operator.
C.
The CLOSE statement closes both the cursor variables, therefore the last FETCH statement
cannot execute.
D.
The name of the cursor variables defined and the name of the cursor variables passed as
arguments must be the same.
C.
declare
type pdtTypCur is ref cursor;
p1 pdtTypCur;
p2 pdtTypCur;
Procedure get_data (pdt_parm1 IN OUT pdtTypCur,
pdt_parm2 IN OUT pdtTypCur)
is
pdt_rec employees%rowtype;
begin
open pdt_parm1 for select * from employees;
pdt_parm2 := pdt_parm1;
fetch pdt_parm1 into pdt_rec;
DBMS_OUTPUT.PUT_LINE( ‘1: ‘ || pdt_rec.first_name );
fetch pdt_parm1 into pdt_rec;
DBMS_OUTPUT.PUT_LINE( ‘2: ‘ || pdt_rec.first_name );
fetch pdt_parm2 into pdt_rec;
DBMS_OUTPUT.PUT_LINE( ‘3_2: ‘ || pdt_rec.first_name );
close pdt_parm1;
fetch pdt_parm2 into pdt_rec;
DBMS_OUTPUT.PUT_LINE( ‘4_2: ‘ || pdt_rec.first_name );
end;
BEGIN
get_data(p1, p2);
–null;
END;
C
C.
pdt_parm2 := pdt_parm1;
…
close pdt_parm1;
fetch pdt_parm2 into pdt_rec; !!!
What Are Cursor Variables (REF CURSORs)?
Cursor variables are like pointers to result sets. You use them when you want to perform a query in one subprogram.
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqloperations.htm#i7106