Oracle PL/SQL Packages

Packages are stored libraries in the database, they are owned by the user schema where they're created, like table and views, this ownership makes packages schema-level objects in the database catalog, like standalone functions and procedures. Users who wish to use the package must have execute privilege on the package. You define package only-scope functions and procedures in package bodies, package only scope functions and procedures can access anything in the package specification. Normally you declare identifiers in the following order: datatypes, variables, exceptions, functions and procedures.

A PL/SQL package is away to group a set of program units (procedures/functions) together, a package is divided into two parts

package header - lists the interface to the package, variables/constants that are visible to the outside world
package body - contains the PL/SQL code

Package header	create or replace package <package_name> [authid {definer \| current_user}] IS [pragma serially_reusable;] [variable_name [constant] scalar_datatype [:= value];] [collection_name [constant] collection_datatype [:= constructor];] [object_name [constant] object_datatype [:= constructor];] [type record_structure IS record ( field_name1 datatype);] [cursor cursor_name [(parameter_name1 datatype)] IS select_statement [type ref_cursor is ref cursor [return {catalog_row \| record_structure}];] [user_exception_name exception; [pragma exception_init(user_exception_name, -2001);]] function <function_name>; procedure <procedure_name>; end <package_name>; /
Package body	create or replace package body <package_name> [authid {definer \| current_user}] IS [pragma serially_reusable;] [variable_name [constant] scalar_datatype [:= value];] [collection_name [constant] collection_datatype [:= constructor];] [object_name [constant] object_datatype [:= constructor];] [type record_structure IS record ( field_name1 datatype);] [cursor cursor_name [(parameter_name1 datatype)] IS select_statement [type ref_cursor is ref cursor [return {catalog_row \| record_structure}];] [user_exception_name exception; [pragma exception_init(user_exception_name, -2001);]] function <function_name> <function_body> procedure <procedure_name> <procedure_body> end <package_name>; /
Example	-- Create the package header create or replace package package_test IS function test_function return varchar2; procedure test_procedure (name IN varchar2); END package_test; -- Create the package bodies create or replace package body package_test IS function test_function return varchar2 IS BEGIN return 'Function Test!!'; END; procedure test_procedure (name IN varchar2) IS BEGIN dbms_output.put_line('Procedure Test:' \|\| name ); END; END package_test; /
Calling a procedure from a package	exec package_test.test_procedure('Paul Valle');
Calling a function from a package	select package_test.test_function from dual;
Remove a package	drop package package_test;
Listing Packages	select object_name, object_type, last_ddl_time, timestamp, status from user_objects where object_name IN (...your list....); select object_name, procedure_name from user_procedures; ## The package name will be in the object_name column, the procedure/function name will be in the procedure column.
List source code	select text from user_source where name = '<package_name>';
Recompile a package	alter package <package_name> compile package; alter package <package_name> compile specification; alter package <package_name> compile body;
Useful Views
DBA_PROCEDURES	lists all functions and procedures along with their associated properties
DBA_SOURCE	describes the text source of all stored objects in the database

Package Variables

Packages are non-serially reuseable by default, this means that the second user isn't guaranteed the same package after the first user calls a package, this works well if you are not using shared variables. If you are using shared variables then you should make the package serially reusable, you do this by using pragma serially_reusable, this changes the behavior of the variables. A serially reusable package creates a new (fresh) copy of the package when it is called by another program unit, whereas a default (non-serailly package) reuses variables.

Package variables are very much like instance variables in Java.

non-serially, serailly example

-- Using shared variables
create or replace procedure shared_variables IS
   -- pragma serially_reusable                     -- use when not using shared variables
   protected constant number := 1;
   unprotected number := 1;
end shared_variables;
/

create or replace procedure change_unprotected (value number) is
  pragma autonmous_transaction;
begin
  shared_variables.unprotected := shared_variables.unprotected + value;
  dbms_output.put_line('Unprotected [' || shared_variables.unprotected || ']');
end change_unprotected;
/

execute change_unprotected(2);

Note: the above procedure adds two each time and the value held over each execute, so it displays 3, 5, 7 and so on. If you add the pragma serailly_reusable you will only get 3 each time as the variables are recreated

Package Body Variables

Any variables declared in a package body cannot be accessed outside the package, which is different from package declared variables. Only function and procedures within the package body can access package body variables. To access these variables you create get and set functions or procedure (very much like Java) to access them.

Visibility

Using packages gives you the ability to only expose interfaces and information that you want to make visible. A user can only access the information in the package header specification, the package body is completely hidden from the user. This is the same principle that OOP uses then defining objects as object can have public and private methods and variables. From a security point only the methods within the package can access the data thus increasing security.

Scope

With packages you have three levels of scope

Package declared procedures, variables and constants which are available to everyone, inside and outside the package
Package body declared procedures, variables and constants which are only available to program units in the package
PL/SQL program unit declared variables and constants which are only available within the program unit.

Definer v Invoker Rights

There are two access modes for procedures and functions

definer - access the procedure or function with all the privileges of the schema owner
invoker - access the procedure or function with all the privileges of the user who executed the program.

If the user accesses the procedure or function using invoker rights then the user must have privileges for any of the objects accessed by the program unit, otherwise you will recieve privilege errors.

Generally synonyms are used to hide the schema information and then a grant to public on the synonym to allow access is commonly used. Becareful as this is a powerful tool and could open up all sorts of security issues.

Dependencies

If another program unit is used within the package, then the package has a dependency on that program unit, if that program unit changes then the package will be come invalid, the package will have to be recompiled.If you are having to recompile lots of packages because of changes then performance will degrade. Note that the package will only become invalid if the change to the dependent program unit affects the package header.

Display invalid objects

select object_name from dba_objects where status = 'INVALID';

Runtime memory usage

When a PL/SQL package is loaded the code for all the contents of the package are loaded into the SGA, so make sure that you group only related program units together, as too many units will cause a delay in running the package as it needs to load all of them first.

Oracle Packages

Oracle has over 150 packages in 10g, to see more details on these packages then please consult the Oracle documentation.