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
Ø The first time a packaged
subprogram is called or any reference to a packaged variable or type is made,
the package is instantiated.
Ø Each session will have its
own copy of packaged variables, ensuring that two sessions executing
subprograms in the same package use different memory locations.
Ø 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.
Ø Packages are stored in the
data dictionary and can not be local.
Ø Packaged subprograms has
an advantage over stand alone subprogram.
Ø When ever any reference to
package, the whole package p-code was stored in shared pool of SGA.
Ø Package may have local
subprograms.
Ø You can include authid
clause inside the package spec not in the body.
Ø The execution section of a
package is know as initialization section.
Ø You can have an exception
section at the bottom of a package body.
Ø Packages subprograms are
not invalidated.
COMPILING PACKAGES
Ø SQL> Alter package PKG
compile;
Ø SQL> Alter package PKG compile
specification;
Ø SQL> Alter package PKG compile
body;
PACKAGE DEPENDENCIES
Ø The package body depends
on the some objects and the package header.
Ø The package header does
not depend on the package body, which is an advantage of packages.
Ø We can change the package
body with out changing the header.
PACKAGE RUNTIME STATE
Package runtime state is
differ for the following packages.
Ø Serially reusable packages
Ø 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
Ø The above package displays
the same output for each execution even though the cursor is not closed.
Ø 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
Ø The above package displays
the different output for each execution even though the cursor is not closed.
Ø 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.
Ø The anonymous block
depends on pkg. This is compile time dependency.
Ø There is also a runtime
dependency on the packaged variables, since each session has its own copy of packaged
variables.
Ø Thus when pkg is
recompiled the runtime dependency is followed, which invalidates the block and
raises the oracle error.
Ø Runtime dependencies exist
only on package state. This includes variables and cursors declared in a
package.
Ø 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
Ø KEEP
Ø UNKEEP
Ø SIZES
Ø 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
Ø USER_OBJECTS
Ø USER_SOURCE
Ø USER_ERRORS
Ø DBA_OBJECTS
Ø DBA_SOURCE
Ø DBA_ERRORS
Ø ALL_OBJECTS
Ø ALL_SOURCE
Ø ALL_ERRORS
No comments:
Post a Comment