29 June, 2012

Cursor Declaration in Packages


When you use global cursor in package, you should determine where cursor declaration in your code.
Some developers declare cursor in package specification and others in package body.
My post today to illustrate difference between different approach of cursor declaration in package.



#1 Declaration in Package Specification
If you declare cursor in package specification, then it is like session variable that still available through whole alive session.
This approach take a lot of shared memory but if cursor is shared for a lot of  plsql units then it has low cost.
Example
CREATE PACKAGE pack_name AS
   CURSOR c1 is select * from emp;
   ...
END pack_name;

#2 Declaration in Package Body
If you declare cursor in package body then it will be available for each program unit in package. but not available for plsql units abroad of package procedures and functions.
Example
CREATE PACKAGE BODY pack_name AS
   CURSOR c1 is select * from emp;
   ...
END pack_name;

#3 Separate Cursor Specs and Bodies with Package
In this approach you declare cursor specs in package specification without select statement.
That way, you can change the cursor body without having to change the cursor spec. You code the cursor spec in the package spec using this syntax
CURSOR cursor_name [(parameter[, parameter]...)] RETURN return_type;
 
In package body you should repeat complete declaration  again for cursor
Example
CREATE PACKAGE pack_name AS
   CURSOR c1 return emp%ROWTYPE;
   ...
END pack_name;
 
CREATE PACKAGE BODY pack_name AS
   CURSOR c1 return emp%ROWTYPE
   IS select * from emp; 
 ...
END pack_name; 
   
In this approach, If you change query of cursor you only recompile package body only as no need to recompile package specification.

Benefits of Cursor Declaration in Packages
It is like global variable which you can use it in every program units in your database.
Example
DECLARE
    emp_rec emp%ROWTYPE;
BEGIN
   OPEN pack_name.c1;
   LOOP
      FETCH pack_name.c1 INTO emp_rec; 
      EXIT WHEN  pack_name.c1%NOTFOUND;
   END LOOP;
   CLOSE pack_name.c1;
END;

Unit of maintenance as it is one place to change your code.
Reusable code as you use the same code multiple times.

Thanks

1 comment:

  1. It's perfect time to make some plans for the future and it's time to be happy.

    I have read this post and if I could I wish to suggest you some interesting things or tips.
    Maybe you can write next articles referring to this article.
    I wish to read more things about it!

    Here is my website diet Plans that work

    ReplyDelete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...