PACKAGES
A package is a container for
related objects. It has specification and body. Each of them is stored
separately in data dictionary.
PACKAGE SYNTAX
Create or replace package <package_name>
is
--
package specification includes subprograms signatures, cursors and global
or
public
variables.
End <package_name>;
Create or replace package body <package_name>
is
-- package
body includes body for all the subprograms declared in the spec, private
Variables and cursors.
Begin
-- initialization
section
Exception
--
Exception handling seciton
End <package_name>;
IMPORTANT POINGS ABOUT PACKAGES
1
The first time a packaged subprogram is called or any reference to
a packaged variable or type is made, the package is instantiated.
2
Each session will have its own copy of packaged variables, ensuring
that two sessions executing subprograms in the same package use different
memory locations.
3
In many cases initialization needs to be run the first time the
package is instantiated within a session. This can be done by adding
initialization section to the package body after all the objects.
4
Packages are stored in the data dictionary and can not be local.
5
Packaged subprograms has an advantage over stand alone subprogram.
6
When ever any reference to package, the whole package p-code was
stored in shared pool of SGA.
7
Package may have local subprograms.
8
You can include authid clause inside the package spec not in the
body.
9
The execution section of a package is know as initialization
section.
10 You can have an exception section at
the bottom of a package body.
11 Packages subprograms are not
invalidated.
COMPILING PACKAGES
1
SQL> Alter package PKG compile;
2
SQL> Alter package PKG compile specification;
3
SQL> Alter package PKG compile body;
PACKAGE DEPENDENCIES
1
The package body depends on the some objects and the package
header.
2
The package header does not depend on the package body, which is an
advantage of packages.
3
We can change the package body with out changing the header.
PACKAGE RUNTIME STATE
Package runtime state is differ for the following packages.
1
Serially reusable packages
2
Non serially reusable packages
SERIALLY REUSABLE PACKAGES
To force the oracle to use serially reusable version then
include PRAGMA
SERIALLY_REUSABLE in
both package spec and body, Examine the following package.
CREATE OR REPLACE PACKAGE PKG IS
pragma
serially_reusable;
procedure
emp_proc;
END PKG;
CREATE OR REPLACE PACKAGE BODY PKG
IS
pragma
serially_reusable;
cursor
c is select ename from emp;
PROCEDURE EMP_PROC IS
v_ename
emp.ename%type;
v_flag
boolean := true;
v_numrows
number := 0;
BEGIN
if
not c%isopen then
open c;
end
if;
while
v_flag loop
fetch c into v_ename;
v_numrows := v_numrows + 1;
if v_numrows = 5 then
v_flag := false;
end if;
dbms_output.put_line('Ename = '
|| v_ename);
end loop;
END EMP_PROC;
END PKG;
SQL> exec pkg.emp_proc
Ename = SMITH
Ename = ALLEN
Ename = WARD
Ename = JONES
Ename = MARTIN
SQL> exec pkg.emp_proc
Ename = SMITH
Ename = ALLEN
Ename = WARD
Ename = JONES
Ename = MARTIN
1
The above package displays the same output for each execution even
though the cursor is not closed.
2
Because the serially reusable version resets the state of the
cursor each time it was called.
NON SERIALL Y REUSABLE PACKAGES
This is the default version used by the oracle, examine the
following package.
CREATE OR REPLACE PACKAGE PKG IS
procedure
emp_proc;
END PKG;
CREATE OR REPLACE PACKAGE BODY PKG
IS
cursor
c is select ename from emp;
PROCEDURE EMP_PROC IS
v_ename
emp.ename%type;
v_flag
boolean := true;
v_numrows
number := 0;
BEGIN
if
not c%isopen then
open c;
end if;
while v_flag loop
fetch c into v_ename;
v_numrows := v_numrows + 1;
if v_numrows = 5 then
v_flag := false;
end if;
dbms_output.put_line('Ename = '
|| v_ename);
end loop;
END EMP_PROC;
END PKG;
SQL> exec pkg.emp_proc
Ename = SMITH
Ename = ALLEN
Ename = WARD
Ename = JONES
Ename = MARTIN
SQL> exec pkg.emp_proc
Ename = BLAKE
Ename = CLARK
Ename = SCOTT
Ename = KING
Ename = TURNER
1
The above package displays the different output for each execution
even though the cursor is not closed.
2
Because the non-serially reusable version remains the state of the
cursor over database calls.
DEPENDENCIES OF PACKAGE RUNTIME STATE
Dependencies can exists between package state and anonymous
blocks.
Examine the following program
Create this package in first session
CREATE OR REPLACE PACKAGE PKG IS
v
number := 5;
procedure
p;
END PKG;
CREATE OR REPLACE PACKAGE BODY PKG
IS
PROCEDURE P IS
BEGIN
dbms_output.put_line('v = ' || v);
v := 10;
dbms_output.put_line('v = ' || v);
END P;
END PKG;
Connect to second session, run the
following code.
BEGIN
pkg.p;
END;
The above code wil work.
Go back to first session and recreate
the package using create.
Then connect to second session and run
the following code again.
BEGIN
pkg.p;
END;
This above code will not work because of the following.
1
The anonymous block depends on pkg. This is compile time
dependency.
2
There is also a runtime dependency on the packaged variables, since
each session has its own copy of packaged variables.
3
Thus when pkg is recompiled the runtime dependency is followed,
which invalidates the block and raises the oracle error.
4
Runtime dependencies exist only on package state. This includes
variables and cursors declared in a package.
5
If the package had no global variables, the second execution of the
anonymous block would have succeeded.
PURITY LEVELS
In general, calls to subprograms are procedural, they
cannot be called from SQL
statements. However, if a stand-alone or packaged function meets certain restrictions,
it can be called during execution of a SQL statement.
User-defined functions are called the same way as built-in
functions but it must meet different restrictions. These restrictions are
defined in terms of purity levels.
There are four types of purity levels.
WNDS -- Writes No Database State
RNDS -- Reads No Database State
WNPS -- Writes No Package State
WNPS -- Writes No Package State
RNPS -- Reads No Package State
In addition to the preceding restrictions, a user-defined
function must also meet the following requirements to be called from a SQL statement.
Ø The function has to be stored in the database, either stand-alone
or as part of a
package.
Ø The function can take only in parametes.
Ø The formal parameters must use only database types, not PL/SQL types such as
boolean or record.
Ø The return type of the function must also be a database type.
Ø The function must not end the current transaction with commit or
rollback, or
rollback to a savepoint prior to the function execution.
Ø It also must not issue any alter session or alter system commands.
RESTRICT_REFERENCES
For packaged functions, however, the RESTRICT_REFERENCES pragma is required to specify the
purity level of a given function.
Syntax:
PRAGMA RESTRICT_REFERENCES(subprogram_name or package_name,
WNDS [,WNPS]
[,RNDS] [,RNPS]);
Ex:
CREATE OR REPLACE PACKAGE PKG IS
function
fun1 return varchar;
pragma
restrict_references(fun1,wnds);
function
fun2 return varchar;
pragma
restrict_references(fun2,wnds);
END PKG;
CREATE OR REPLACE PACKAGE BODY PKG
IS
FUNCTION
FUN1 return varchar
IS
BEGIN
update dept set deptno = 11;
return 'hello';
END FUN1;
FUNCTION FUN2 return varchar IS
BEGIN
update dept set dname ='aa';
return 'hello';
END FUN2;
END PKG;
The above package body will not created, it will give the
following erros.
PLS-00452:
Subprogram 'FUN1' violates its associated pragma
PLS-00452:
Subprogram 'FUN2' violates its associated pragma
CREATE OR REPLACE PACKAGE BODY PKG
IS
FUNCTION
FUN1 return varchar
IS
BEGIN
return 'hello';
END FUN1;
FUNCTION FUN2 return varchar IS
BEGIN
return 'hello';
END FUN2;
END PKG;
Now the package body will be created.
DEFAULT
If there is no RESTRICT_REFERENCES pragma associated with a given packaged function, it will not have
any purity level asserted. However, you can change the default purity level for
a package. The DEFAULT keyword is used instead of the
subprogram name in the pragma.
Ex:
CREATE OR REPLACE PACKAGE PKG IS
pragma
restrict_references(default,wnds);
function
fun1 return varchar;
function
fun2 return varchar;
END PKG;
CREATE OR REPLACE PACKAGE BODY PKG
IS
FUNCTION
FUN1 return varchar
IS
BEGIN
update dept set deptno = 11;
return 'hello';
END FUN1;
FUNCTION FUN2 return varchar IS
BEGIN
update dept set dname ='aa';
return 'hello';
END FUN2;
END PKG;
The above package body will not created, it will give the
following erros because the pragma will apply to all the functions.
PLS-00452:
Subprogram 'FUN1' violates its associated pragma
PLS-00452:
Subprogram 'FUN2' violates its associated pragma
CREATE OR REPLACE PACKAGE BODY PKG
IS
FUNCTION
FUN1 return varchar
IS
BEGIN
return 'hello';
END FUN1;
FUNCTION FUN2 return varchar IS
BEGIN
return 'hello';
END FUN2;
END PKG;
Now the package body will be created.
TRUST
If the TRUST
keyword is present, the restrictions listed in the pragma are not enforced.
Rather, they are trusted to be true.
Ex:
CREATE OR REPLACE PACKAGE PKG IS
function
fun1 return varchar;
pragma
restrict_references(fun1,wnds,trust);
function
fun2 return varchar;
pragma
restrict_references(fun2,wnds,trust);
END PKG;
CREATE OR REPLACE PACKAGE BODY PKG
IS
FUNCTION
FUN1 return varchar
IS
BEGIN
update dept set deptno = 11;
return 'hello';
END FUN1;
FUNCTION FUN2 return varchar IS
BEGIN
update dept set dname ='aa';
return 'hello';
END FUN2;
END PKG;
The above package will be created successfully.
IMPORTANT POINTS ABOUT RESTRICT_REFERENCES
Ø This pragma can appear anywhere in the package specification, after
the function
declaration.
Ø It can apply to only one function definition.
Ø For overload functions, the pragma applies to the nearest
definition prior to the
Pragma.
Ø This pragma is required only for packages functions not for
stand-alone functions.
Ø The Pragma can be declared only inside the package specification.
Ø The pragma is checked at compile time, not runtime.
Ø It is possible to specify without any
purity levels when trust or combination of
default and
trust keywords are present.
PINNING IN THE SHARED POOL
The shared pool is the portion of the SGS that
contains, among other things, the p-code of compiled subprograms as they are
run. The first time a stored a store subprogram is called, the p-code is loaded
from disk into the shared pool. Once the object is no longer referenced, it is
free to be aged out. Objects are aged out of the shared pool using an LRU(Least Recently Used) algorithm.
The DBMS_SHARED_POOL package allows you to pin objects in the shared pool. When an
object is pinned, it will never be aged out until you request it, no matter how
full the pool gets or how often the object is accessed. This can improve
performance, as it takes time to reload a package from disk.
DBMS_SHARED_POOL has four procedures
1
KEEP
2
UNKEEP
3
SIZES
4
ABORTED_REQUEST_THRESHOLD
KEEP
The DBMS_SHARED_POOL.KEEP procedure is used to pin objects in the pool.
Syntax:
PROCEDURE KEEP(object_name varchar2,flag char
default ‘P’);
Here the flag represents different types of flag values for
different types of objects.
P -- Package, function or procedure
Q -- Sequence
R -- Trigger
C -- SQL Cursor
T -- Object type
JS -- Java source
JC -- Java class
JR -- Java resource
JD -- Java shared data
UNKEEP
UNKEEP is the only way to remove a kept object from the shared pool,
without restarting the database. Kept objects are never aged out automatically.
Syntax:
PROCEDURE UNKEEP(object_name varchar2, flag
char default ‘P’);
SIZES
SIZES will echo the contents of the shared pool to the screen.
Syntax:
PROCEDURE SIZES(minsize number);
Objects with greater than the minsize will be
returned. SIZES uses DBMS_OUTPUT to return the data.
ABORTED_REQUEST_THRESHOLD
When the database determines that there is not enough
memory in the shared pool to satisfy a given request, it will begin aging
objects out until there is enough memory. It enough objects are aged out, this
can have a performance impact on other database sessions. The ABORTED_REQUEST_THRESHOLD can be used to remedy this.
Syntax:
PROCEDURE ABORTED_REQUEST_THRESHOLD(threshold_size number);
Once this procedure is called, oracle will not start aging
objects from the pool unless at least threshold_size bytes is needed.
DATA MODEL FOR SUBPROGRAMS AND PACKAGES
1
USER_OBJECTS
2
USER_SOURCE
3
USER_ERRORS
4
DBA_OBJECTS
5
DBA_SOURCE
6
DBA_ERRORS
7
ALL_OBJECTS
8
ALL_SOURCE
9
ALL_ERRORS
No comments:
Post a Comment