One particularly interesting type of package data is the explicit cursor.I can declare a cursor in a package, in either the body or the specification. The state of this cursor (i.e., whether it is opened or closed and the pointer to the location in the result set) persists for the session, just like any other packaged data. This means that it is possible to open a packaged cursor in one program, fetch from it in a second, and close it in a third. This flexibility can be an advantage and also a potential problem.
If you are declaring an explicit cursor in a package specification, you have two options :-
Declare the entire cursor, including the query, in the specification. This is exactly the same as if you were declaring a cursor in a local PL/SQL block.
Declare only the header of the cursor and do not include the query itself. In this case, the query is defined in the package body only. You have, in effect, hidden the implementation of the cursor.
If you declare only the header, then you must add a RETURN clause to the cursor definition that indicates the data elements returned by a fetch from the cursor. Of course,these data elements are actually determined by the SELECT statement for that cursor,but the SELECT statement appears only in the body, not in the specification.
The RETURN clause may be made up of either of the following datatype structures :-
A record defined from a database table using the %ROWTYPE attribute
A record defined from a programmer-defined record type
If you declare a cursor in a package body, the syntax is the same as if you were declaring it in a local PL/SQL block.
SQL> CREATE OR REPLACE PACKAGE emp_info
2 IS
3 CURSOR emp_dept_cur (p_deptno IN emp.deptno%TYPE)
4 IS
5 SELECT *
6 FROM emp
7 WHERE deptno = p_deptno;
8
9 CURSOR emp_all_cur RETURN emp%ROWTYPE;
10
11 TYPE dept_rt IS RECORD
12 (
13 deptno NUMBER(2),
14 dname VARCHAR2(14),
15 loc VARCHAR2(13)
16 );
17
18 CURSOR dept_cur (p_deptno IN dept.deptno%TYPE) RETURN dept_rt;
19 END emp_info;
20 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY emp_info
2 IS
3 CURSOR emp_all_cur RETURN emp%ROWTYPE
4 IS
5 SELECT *
6 FROM emp;
7
8 CURSOR dept_cur (p_deptno IN dept.deptno%TYPE) RETURN dept_rt
9 IS
10 SELECT *
11 FROM dept
12 WHERE deptno = p_deptno;
13 END emp_info;
14 /
Package body created.
--The select list of the query that is added to the header must match, in number of items and datatype, the RETURN clause in the package specification; in this case, they do. If they do not match or the RETURN clause is not specified in the body, then the package body will fail to compile with one of the following errors :-
PLS-00323: subprogram or cursor '<cursor>' is declared in a package specification and must be defined in the package body
PLS-00400: different number of columns between cursor SELECT statement and return valueSQL> DECLARE
2 emp_rec1 emp_info.emp_all_cur%ROWTYPE;
3 emp_rec2 emp%ROWTYPE;
4 l_deptno NUMBER := 10;
5 dept_vr emp_info.dept_rt;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE('1st Cursor :::');
8 OPEN emp_info.emp_all_cur;
9 LOOP
10 FETCH emp_info.emp_all_cur INTO emp_rec1;
11 EXIT WHEN emp_info.emp_all_cur%NOTFOUND;
12 DBMS_OUTPUT.PUT_LINE(emp_rec1.ename);
13 END LOOP;
14 CLOSE emp_info.emp_all_cur;
15
16 DBMS_OUTPUT.PUT_LINE('2nd Cursor :::');
17 OPEN emp_info.emp_dept_cur(l_deptno);
18 LOOP
19 FETCH emp_info.emp_dept_cur INTO emp_rec2;
20 EXIT WHEN emp_info.emp_dept_cur%NOTFOUND;
21 DBMS_OUTPUT.PUT_LINE(emp_rec2.ename);
22 END LOOP;
23 CLOSE emp_info.emp_dept_cur;
24
25 DBMS_OUTPUT.PUT_LINE('3rd Cursor :::');
26 OPEN emp_info.dept_cur(l_deptno);
27 LOOP
28 FETCH emp_info.dept_cur INTO dept_vr;
29 EXIT WHEN emp_info.dept_cur%NOTFOUND;
30 DBMS_OUTPUT.PUT_LINE(dept_vr.dname||'-'||dept_vr.loc);
31 END LOOP;
32 CLOSE emp_info.dept_cur;
33 END;
34 /
--OUTPUT :::
1st Cursor :::
CLARK
JONES
SCOTT
FORD
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
2nd Cursor :::
CLARK
MILLER
3rd Cursor :::
ACCOUNTING-NEW YORK
--Because my cursor is declared in a package specification,its scope is not bound to any given PL/SQL block.Suppose that I run this code:
SQL> DECLARE
2 l_deptno NUMBER := 10;
3 BEGIN
4 OPEN emp_info.emp_dept_cur(l_deptno);
5 END;
6 /
PL/SQL procedure successfully completed.
--In the same session, I run the below anonymous block.I will then get this error :
SQL> DECLARE
2 emp_rec1 emp_info.emp_all_cur%ROWTYPE;
3 emp_rec2 emp%ROWTYPE;
4 l_deptno NUMBER := 10;
5 dept_vr emp_info.dept_rt;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE('1st Cursor :::');
8 OPEN emp_info.emp_all_cur;
9 LOOP
10 FETCH emp_info.emp_all_cur INTO emp_rec1;
11 EXIT WHEN emp_info.emp_all_cur%NOTFOUND;
12 DBMS_OUTPUT.PUT_LINE(emp_rec1.ename);
13 END LOOP;
14 CLOSE emp_info.emp_all_cur;
15
16 DBMS_OUTPUT.PUT_LINE('2nd Cursor :::');
17 OPEN emp_info.emp_dept_cur(l_deptno);
18 LOOP
19 FETCH emp_info.emp_dept_cur INTO emp_rec2;
20 EXIT WHEN emp_info.emp_dept_cur%NOTFOUND;
21 DBMS_OUTPUT.PUT_LINE(emp_rec2.ename);
22 END LOOP;
23 CLOSE emp_info.emp_dept_cur;
24
25 DBMS_OUTPUT.PUT_LINE('3rd Cursor :::');
26 OPEN emp_info.dept_cur(l_deptno);
27 LOOP
28 FETCH emp_info.dept_cur INTO dept_vr;
29 EXIT WHEN emp_info.dept_cur%NOTFOUND;
30 DBMS_OUTPUT.PUT_LINE(dept_vr.dname||'-'||dept_vr.loc);
31 END LOOP;
32 CLOSE emp_info.dept_cur;
33 END;
34 /
--OUTPUT :::
1st Cursor :::
CLARK
JONES
SCOTT
FORD
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
2nd Cursor :::
DECLARE
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "SYSTEM.EMP_INFO", line 5
ORA-06512: at line 17Given the persistence of packaged cursors, you should always keep the following rules in mind :-
Never assume that a packaged cursor is closed (and ready to be opened).
Never assume that a packaged cursor is opened (and ready to be closed).
Always be sure to explicitly close your packaged cursor when you are done with it.You also will need to include this logic in exception handlers; make sure the cursor is closed through all exit points in the program.
SQL> CREATE OR REPLACE PACKAGE test_pkg
2 IS
3 CURSOR emp_cur IS SELECT * FROM emp;
4
5 PROCEDURE open_cursor;
6
7 PROCEDURE fetch_cursor;
8
9 PROCEDURE close_cursor;
10
11 var1 NUMBER := 1;
12 var2 NUMBER := 2;
13 END test_pkg;
14 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY test_pkg
2 IS
3 var3 NUMBER := 3;
4 var4 NUMBER := 4;
5
6 PROCEDURE open_cursor
7 IS
8 BEGIN
9 OPEN emp_cur;
10 END;
11
12 PROCEDURE fetch_cursor
13 IS
14 emp_rec emp%ROWTYPE;
15 BEGIN
16 LOOP
17 FETCH emp_cur INTO emp_rec;
18 EXIT WHEN emp_cur%NOTFOUND;
19 DBMS_OUTPUT.PUT_LINE(emp_rec.ename);
20 END LOOP;
21 END;
22
23 PROCEDURE close_cursor
24 IS
25 BEGIN
26 CLOSE emp_cur;
27 END;
28 END test_pkg;
29 /
Package body created.
SQL> BEGIN
2 test_pkg.open_cursor;
3 test_pkg.fetch_cursor;
4 test_pkg.close_cursor;
5 END;
6 /
--OUTPUT :::
KING
BLAKE
CLARK
JONES
SCOTT
FORD
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
PL/SQL procedure successfully completed. As you have seen, package data by default persists for your entire session (or until the package is recompiled).
This is an incredibly handy feature, but it has some drawbacks :-
Globally accessible (public and private) data structures persist, and that can cause undesired side effects. In particular, I can inadvertently leave packaged cursors open, causing “already open” errors in other programs.
ORA-06511: PL/SQL: cursor already open My programs can suck up lots of real memory (package data is managed in the user’s memory area or user global area [UGA]) and then not release it if that data is stored in a package-level structure.
To help you manage the use of memory in packages, PL/SQL offers the SERIALLY_REUSABLE pragma.
This pragma, which must appear in both the package specification and the body (if one exists), marks that package as serially reusable.
For such packages,the duration of package state (the values of variables, the open status of a packaged cursor, etc.) can be reduced from a whole session to a single call of a program in the package.
The SERIALLY_REUSABLE pragma indicates that the package state is needed only for the duration of one call to the server (for example, an OCI call to the database or a stored procedure call through a database link).
