facebook google plus twitter
Webucator's Free Oracle PL/SQL Tutorial

Lesson: Packages

Welcome to our free Oracle PL/SQL tutorial. This tutorial is based on Webucator's Oracle PL/SQL Training course.

As an application grows in size and complexity it becomes increasingly important to structure units of application code. Well organized code is easier to debug and maintain. Procedures and functions are the basic building blocks of PL/SQL code that are stored in the database. In this lesson, you will learn ways to organize these building blocks into groups of related functionality called packages.

Lesson Goals

  • Learn about packages
  • Learn how to encapsulate subprograms within packages
  • Learn how to create packages
  • Learn about maintaining packages

Structure of Packages

A package is an object that groups logically related PL/SQL subprograms.

A package specification (or spec) is the interface to these subprograms.

A package body provides the implementation for subprograms in the specification as well as additional subprograms that are used internally within the package itself.

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.

Subprograms Omitted from Specifications

A subprogram can be declared in the package body but not in the package specification. It can only be called within the package itself (by itself or another subprogram). If you need to call your subprogram outside of the package itself, you must declare it in the package specification.

Code Sample:

Packages/Demos/call_test_package_f3_in_sql_error.sql
select test_package.f3() from dual;

Code Explanation

The function f3 is defined in the package body, but is not visible from outside the package because it is not defined in the package specification. Attempting to run this query results in an error.

Code Sample:

Packages/Demos/call_test_package_f4_in_sql.sql
select test_package.f4() from dual;

Code Explanation

The function f3 is visible to function f4, which is exposed in the package specification. This query will run successfully, and return a result from f3 through f4.

Using PL/SQL Packages

Duration: 10 to 15 minutes.
  1. Recall the stored procedure, function, and anonymous block from the previous lesson.
       phone_number_function1.sql
       employee_report_procedure.sql
       anonymous_block.sql
    
    The content of these files is listed under exercise code below. Review these files.
  2. Create a package and package body called reports.
  3. Add the procedure and function to the package and package body.
  4. Drop the procedure and the function that are outside the package (if needed).
       DROP FUNCTION format_phone2;
       DROP PROCEDURE employee_report;
    
  5. Modify the anonymous block to call the procedure within the package.

Code Sample:

Packages/Exercises/phone_number_function1.sql
create or replace 
function format_phone
(p_phone_number in employees.phone_number%type
)
return varchar2
is 
  v_phone employees.phone_number%type;
begin

  IF length(p_phone_number)     = 12
  and instr(p_phone_number,'.') = 4
  and instr(p_phone_number,'.', 5) = 8
  THEN
    v_phone := '(' || p_phone_number;
    v_phone := replace(v_phone, '.' ,') ');
    v_phone := substr(v_phone, 1,9) || '-' || substr(v_phone, 12);
    return v_phone;
  ELSE
    return p_phone_number;
  END IF;

END;
/

Code Explanation

Code Sample:

Packages/Exercises/employee_report_procedure.sql
create or replace procedure employee_report
(c in out sys_refcursor
)
is
begin  
  open c for 
  select 
    last_name, 
    first_name, 
    format_phone(phone_number) as phone 
  from employees 
  order by last_name, first_name;
  
end;
/

Code Explanation

Code Sample:

Packages/Exercises/anonymous_block.sql
DECLARE
  c1 sys_refcursor;
  fname employees.first_name%type;
  lname employees.last_name%type;
  phone employees.phone_number%type;
  tab CHAR(1) := CHR(9);
BEGIN
 employee_report(c1);
 loop
    fetch c1 into fname, lname, phone;
    exit when c1%notfound;
    dbms_output.put_line(fname || ' ' || lname || tab || phone);
  end loop;
  close c1;
END;
/

Code Explanation

Solution:

Packages/Solutions/package_specification.sql
create or replace package reports is

  procedure employee_report
  (c in out sys_refcursor);

  function format_phone
  (p_phone_number in employees.phone_number%type)
  return varchar2;

end reports;
/

Code Explanation

Solution:

Packages/Solutions/package_body.sql
create or replace package body reports is

procedure employee_report
(c in out sys_refcursor)
is
begin
  
  open c for 
  select 
    last_name, 
    first_name, 
    reports.format_phone(phone_number) phone 
  from employees 
  order by last_name, first_name;
  
end employee_report;


function format_phone
(p_phone_number in employees.phone_number%type)
return varchar2
is 
  v_phone employees.phone_number%type;
begin

  IF length(p_phone_number)     = 12
  and instr(p_phone_number,'.') = 4
  and instr(p_phone_number,'.', 5) = 8
  THEN
    v_phone := '(' || p_phone_number;
    v_phone := replace(v_phone, '.', ') ');
    v_phone := substr(v_phone, 1, 9) || '-' || substr(v_phone, 12);
    return v_phone;
  ELSE
    return p_phone_number;
  END IF;

END format_phone;

end reports;
/

Code Explanation

Solution:

Packages/Solutions/anonymous_block.sql
DECLARE
  c1 sys_refcursor;
  fname employees.first_name%type;
  lname employees.last_name%type;
  phone employees.phone_number%type;
  tab CHAR(1) := CHR(9);
BEGIN
 reports.employee_report(c1);
 loop
    fetch c1 into fname, lname, phone;
    exit when c1%notfound;
    dbms_output.put_line(fname || ' ' || lname || tab || phone);
  end loop;
  close c1;
END;
/

Code Explanation