Introduction of Packages

It is essential for a good programming style that logically related blocks, procedures, and functions
are combined into modules, and each module provides an interface which allows users
and designers to utilize the implemented functionality. PL/SQL supports the concept of modularization by which modules and other constructs can be organized into packages. A package consists of a package specification and a package body. The package specification defines the interface that is visible for application programmers, and the package body implements the package specification (similar to header- and source files in the programming language C).
Below a package is given that is used to combine all functions and procedures to manage information about employees.

create package manage_employee as — package specification
function hire_emp (name varchar2, job varchar2, mgr number, hiredate date,
sal number, comm number default 0, deptno number)
return number;
procedure fire_emp (emp_id number);
procedure raise_sal (emp_id number, sal_incr number);
end manage_employee;
create package body manage_employee as
function hire_emp (name varchar2, job varchar2, mgr number, hiredate date,
sal number, comm number default 0, deptno number)
return number is
– Insert a new employee with a new employee Id
new_empno number(10);
begin
select emp_sequence.nextval into new_empno from dual;
insert into emp values(new_empno, name, job, mgr, hiredate,
sal, comm, deptno);
return new_empno;
end hire_emp;
procedure fire_emp(emp_id number) is
– deletes an employee from the table EMP
begin
delete from emp where empno = emp_id;
if SQL%NOTFOUND then — delete statement referred to invalid emp_id
raise_application_error(-20011, ’Employee with Id ’ ||
to_char(emp_id) || ’ does not exist.’);
end if;
end fire_emp;
procedure raise_sal(emp_id number, sal_incr number) is
– modify the salary of a given employee
begin
update emp set sal = sal + sal_incr
where empno = emp_id;
if SQL%NOTFOUND then
raise_application_error(-20012, ’Employee with Id ’ ||
to_char(emp_id) || ’ does not exist’);
end if;
end raise_sal;
end manage_employee;

Remark: In order to compile and execute the above package, it is necessary to create first the
required sequence (help sequence):

create sequence emp sequence start with 8000 increment by 10;

A procedure or function implemented in a package can be called from other procedures and
functions using the statement .[()]. Calling such a procedure from the SQL*Plus shell requires a leading execute. Oracle offers several predefined packages and procedures that can be used by database users and application developers. A set of very useful procedures is implemented in the package DBMS OUTPUT. This package allows users to display information to their SQL*Plus session’s screen as a PL/SQL program is executed. It is also a very useful means to debug PL/SQL programs that have been successfully compiled, but do not behave as expected. Below some of the most important procedures of this package are listed:

Procedure name Remark

DBMS OUTPUT.ENABLE enables output
DBMS OUTPUT.DISABLE disables output
DBMS OUTPUT.PUT() appends (displays) to output buffer
DBMS OUTPUT.PUT LINE() appends to output buffer and appends a new-line marker
DBMS OUTPUT.NEW LINE displays a new-line marker
Before strings can be displayed on the screen, the output has to be enabled either using the
procedure DBMS OUTPUT.ENABLE or using the SQL*Plus command set serveroutput on (before
the procedure that produces the output is called).
Further packages provided by Oracle are UTL FILE for reading and writing files from PL/SQL
programs, DBMS JOB for job scheduling, and DBMS SQL to generate SQL statements dynamically,
that is, during program execution. The package DBMS SQL is typically used to create and
delete tables from within PL/SQL programs. More packages can be found in the directory
$ORACLE HOME/rdbms/admin.