Purpose of Packages

Contact Us or call 1-877-932-8228
Purpose of Packages

Purpose of Packages

There are a number of reasons why a package might be used rather than using separate individual subprograms.

Packages organize groups of related subprograms into logical groupings. As you design an application, packages provide a collective entity that provides a clear interface to the underlying PL/SQL code.

Packages provide a separate namespace for subprograms with the same identifier. For instance, you might have two "FORMAT_NAME" functions. One could be associated with a PERSON package that returns the last name concatenated to the first name, and a separate FORMAT_NAME function in a COMPANY package that capitalizes the name of the company and removes any trailing designation such as Inc., LLC, etc.

When Oracle loads an object from a package into memory, other contiguous objects in that package might also be loaded into memory. Performance might be better, and some objects might be shared among subprograms. These advantages are generally secondary to the intent to define well organized logical groupings of PL/SQL functionality.

In versions 7.4 and earlier Oracle attempted to load the entire package in memory. Now Oracle loads package objects on an on-demand basis, in 4k chunks or smaller.

Wrapping is the process of hiding PL/SQL code in order to protect source code from being viewed by unauthorized resources. Since a discussion of this utility is outside the scope of this course, see the Oracle documentation for details of this process. If you are using this feature to wrap packages, the package body (PL/SQL implementation) can be obscured while leaving the package specification visible to the outside world.

Code Sample:

Packages/Demos/test_package.sql
create or replace package test_package is
  
  function f1 return varchar2;
  function f2(p_x in varchar2) return varchar2;
  -- no function f3 in the package!
  function f4 return varchar2;

end test_package;

Code Explanation

Package declarations contain types, exceptions as well as procedure and function declarations.

Code Sample:

Packages/Demos/test_package_body.sql
create or replace package body test_package is

  function f1 return varchar2 as
  begin
    return 'In f1';
  end f1;

  function f2(p_x in varchar2) return varchar2 as
  begin
    return 'in f2';
  end f2;
  
  function f3 return varchar2 as
  begin
    return 'in f3';
  end f3;
  
  function f4 return varchar2 as
  begin
    return f3;
  end f4;
  
end test_package;

Code Explanation

Package bodies contain subprogram implementations. The subprogram declaration signature must match in the package specification and body. This is to say the name, parameters and types must match.

Code Sample:

Packages/Demos/call_test_package_f1_in_sql.sql
select test_package.f1() from dual;

Code Explanation

This demonstrates a call of function F1 from SQL. The package name is used to qualify the call to the function named f1. This allows for functions with the same name to exist in different packages.

Code Sample:

Packages/Demos/call_test_package_f2_in_sql.sql
select test_package.f2('x') from dual;

Code Explanation

Call uses positional parameters. The order that the values are passed in is used to associate each value with a parameter.

Code Sample:

Packages/Demos/call_test_package_f2_in_sql_error.sql
select test_package.f2(p_x =>'x') from dual;

Code Explanation

Named parameters explicitly reference each parameter by name to associate it with a value. They cannot be used in SQL prior to Oracle 11g.

Code Sample:

Packages/Demos/call_test_package_f2_in_plsql.sql
begin
  dbms_output.put_line(test_package.f2(p_x =>'x'));
end;

Code Explanation

Named parameters can be used in the context of PL/SQL.

Next