The register_student and Update student procedures are grouped together in a package

Donald K. Burleson

Oracle and Expert Systems Technology

Oracle - Pinning Packages in the Shared Pool Library Cache

Based on my personal DBA experience, I highly recommend that you store all SQL in packages in you have enough shared_pool memory. Storing SQL in packages has several benefits.  It ensures that all SQL is uniform and reusable, and it makes the SQL "pin-able".

To prevent paging, packages can be marked as nonswappable. Marking a package as nonswappable tells a database that after the package is initially loaded into the shared pool, the package must always remain in memory. This procedure is sometimes called pinning or memory fencing.

Oracle provides a procedure called dbms_shared_pool.keep to pin a package, and packages can be unpinned with dbms_shared_pool.unkeep.

Only packages can be pinned. Stored procedures cannot be pinned unless they are placed into a package.

The choice of whether to pin a package in memory is a function of the size of the object and the frequency of its use. Very large packages that are called frequently might benefit from pinning, but any difference might go unnoticed because the frequent calls to the procedure have kept it loaded into memory anyway. Therefore, because the object never pages out in the first place, pinning has no effect. Also, the way procedures are grouped into packages can have some influence. Some Oracle DBAs identify high-impact procedures and group them into a single package, and then pin this package in the shared pool library cache.

In an ideal world, the shared_pool parameter of the init.ora should be large enough to accept every package, stored procedure, and trigger that can be used by the applications. However, reality dictates that the shared pool cannot grow indefinitely, and wise choices must be made in terms of which packages are pinned.

Unix users might want to add code to their database startup script ensure that the packages are re-pinned after each database startup, guaranteeing that all packages are re-pinned with each bounce of the box. A script might look like this:

ORACLE_SID=mydata
export ORACLE_SID
su oracle -c "/usr/oracle/bin/svrmgrl /<<!
connect internal;

EXECUTE dbms_shared_pool.keep('DBMS_ALERT');
EXECUTE dbms_shared_pool.keep('DBMS_DDL');
EXECUTE dbms_shared_pool.keep('DBMS_DESCRIBE');
EXECUTE dbms_shared_pool.keep('DBMS_LOCK');

exit;
!"

The database administrator also needs to remember to run pin.sqlwhenever restarting a database. This is done by reissuing the PIN command from inside SQL*DBA immediately after the database has been restarted.

If you have large procedures or large anonymous PL/SQL blocks in your application, you may also want to put these into packages and pin them in the shared pool.  You can determine what large stored objects are in the shared pool by selecting from the v$db_object_cache fixed view. This will also tell you which objects have been marked kept. This can be done with the following query:

select * from v$db_object_cache where sharable_mem > 10000;

If you have plenty of free memory in the shared pool and you wish to mark all packages in the system 'kept', you can execute the following PL/SQL snippet:

declare
   own varchar2(100);
   nam varchar2(100);  

cursor pkgs is  

select
   owner,
   object_name
from
   dba_objects
where
   object_type = 'PACKAGE';

begin open pkgs;
   loop fetch pkgs into own, nam; |
   exit when pkgs%notfound;
   dbms_shared_pool.keep(own || '.' || nam, 'P');
end loop; 

end;

Now, let's look at how we can identify packages that should be pinned.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Packages in PL/SQL

A package is a way of logically storing the subprograms like procedures, functions, exception or cursor into a single common unit.

A package can be defined as an oracle object that is compiled and stored in the database.

Once it is compiled and stored in the database it can be used by all the users of database who have executable permissions on Oracle database.

Components of Package

Package has two basic components:

• Specification: It is the declaration section of a Package
• Body: It is the definition section of a Package.

Benefits of using Package

Following are some of the benefits of packages in PL/SQL:

1. REUSABILITY

   Whenever a package is created, it is compiled and stored in the database. So, you write the code once which can be reused by other applications.

2. OVERLOADING

   Two or more >procedures or functions can be created in a package with the same name.

3. CREATING MODULES

   A large application can be created by simply creating modules(or subprograms) clearly defined and easy to work.

4. IMPROVES PERFORMANCE

   Package code gets loaded inside the SGA(system global area) of Oracle at first call itself due to which other subsequent calls will work very fast.

5. GLOBAL DECLARATION

   If the objects(procedures, functions, variables, constants, exceptions, cursor etc) are declared globally in a package, they can be easily used when required.

How to create a PL/SQL Package?

Following are the steps to declare and use a package in PL/SQL code block:

STEP 1: Package specification or declaration

It mainly comprises of the following:

• Package Name.
• Variable/constant/cursor/procedure/function/exception declaration.
• This declaration is global to the package.

Here is the syntax:

CREATE OR REPLACE PACKAGE <package_name> IS/AS
	FUNCTION <function_name> (<list of arguments>) 
	RETURN <datatype>;
	PROCEDURE <procedure_name> (<list of arguments>);
	-- code statements
END <package_name>;

where,

CREATE OR REPLACE PACKAGE are keywords used to create a package

FUNCTION and PROCEDURE are keywords used to declare function and procedure while creating package.

<package_name>, <function_name>, <procedure_name> are user-defined names.

IS/AS are keywords used to declare package.

RETURN is a keyword specifying value returned by the function declared.

STEP 2: Package Body

It mainly comprises of the following:

• It contains the definition of procedure, function or cursor that is declared in the package specification.
• It contains the subprogram bodies containing executable statements for which package has been created

Here is the syntax:

CREATE  OR REPLACE PACKAGE BODY <package_name> IS/AS
FUNCTION <function_name> (<list of arguments>) RETURN <datatype>IS/AS
	-- local variable declaration;
	BEGIN
		-- executable  statements;
	EXCEPTION
		-- error handling statements;
END <function_name>;

PROCEDURE <procedure_name> (<list of arguments>)IS/AS
	-- local variable declaration;
	BEGIN
		-- executable statements;
	EXCEPTION
		-- error handling statements;
	END <procedure_name>;
END <package_name>;

Where,

CREATE OR REPLACE PACKAGE BODY are keywords used to create the package with a body.

FUNCTION and PROCEDURE are keywords used to define function and procedure while creating package.

<package_name>, <function_name>, <procedure_name> are user-defined.

IS/AS are keywords used to define the body of package, function and procedure.

RETURN is a keyword specifying value returned by the function defined.

DECLARE, BEGIN, EXCEPTION, END are the different sections of PL/SQL code block containing variable declaration, executable statements, error handling statements and marking end of PL/SQL block respectively where DECLARE and EXCEPTION part are optional.

Note: Creating a package only defines it, to use it we must refer it using the package object.

Following is the syntax for referring a package object:

Packagename.objectname;

The Object can be a function, procedure, cursor, exception that has been declared in the package specification and defined in the package body and to access their executable statements above syntax is used.

Time for an Example!

We have a STUDENT table as specified below:

Let's write a simple program to demonstrate the use of Package in PL/SQL.

PL/SQL code for package specification:

CREATE OR REPLACE PACKAGE pkg_student IS
	PROCEDURE  updateRecord(sno student.rollno%type);
	FUNCTION deleteRecord(snm student.sname%type)
		RETURN boolean;
END pkg_student;

Package Created

PL/SQL code for package body:

 set serveroutput on;
CREATE OR REPLACE PACKAGE BODY pkg_student IS
	PROCEDURE updateRecord(sno student.rollno%type) IS
		BEGIN
			Update student set age=23 where rollno=sno;
			IF  SQL%FOUND THEN
				dbms_output.put_line('RECORD UPDATED');
			ELSE
				dbms_output.put_line('RECORD NOT FOUND');
			END IF;
		END updateRecord;

	FUNCTION deleteRecord(snm student.sname%type) RETURN boolean IS
		BEGIN
			Delete from student where sname=snm;
			RETURN SQL%FOUND;
		END deleteRecord;
END pkg_student;

Package Body Created

Now let's write the PL/SQL code for calling the Procedure and Function used in Package.

 set serveroutput on;
DECLARE
	sno student.rollno%type;
	s_age student.age%type;
	snm student.sname%type;
BEGIN
	sno := &sno;
	snm := &snm
	pkg_student.updateRecord(sno);
	IF pkg_student.deleteRecord(snm) THEN
		dbms_output.put_line('RECORD DELETED');
	ELSE
		dbms_output.put_line('RECORD NOT FOUND');
	END IF;
END;

Enter value for sno: 12 Enter value for snm: Neha RECORD UPDATED RECORD NOT FOUND PL/SQL procedure successfully completed.

Note: If the package specification or package body has been created with compilation errors then a following warning message is displayed on the screen:

WARNING: Package Body created with compilation errors.

In that case, the errors can be seen by executing following statement:

 SHOW ERRORS;

In the next tutorial we will cover transactions in PL/SQL.

Why do you have functions and procedures together in a package?

What are procedures functions and packages in Oracle?

How can we create procedure inside package in Oracle?

Which of the following is true about Plsql package body?