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 | 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 |
Example | -- Create the package header
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 ## 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 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
Definer v Invoker Rights
There are two access modes for procedures and functions
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.