How do I create a PL/SQL package body in Oracle? I
understand that if I put my PL/SQL procedures into a package, then I can pin
them into RAM with dbms_shared_pool.keep
, so I need the create package
Answer: First, there are many benefits to
encapsulating PL/SQL functions and stored procedures into packages:
Better PL/SQL performance. Oracle stored procedures load once into
the shared pool and remain there unless they become paged out.
Coupling of data with methods. DBAs can use naming conventions with
Oracle packages to couple tables with the PL/SQL associated with a table,
essentially using Oracle stored procedures as "methods".
Isolation of PL/SQL code. Since all PL/SQL anonymous blocks are moved
out of the external programs and into the Oracle stored procedures, the
application programs become nothing more than calls to stored procedures. As
such, it becomes very simple to swap out one database and swap in another
One of the foremost reasons PL/SQL packages function faster than traditional
code is related to caching in the Oracle SGA. Once loaded into the RAM memory of
the shared pool, PL/SQL packages will execute very quickly.
The package body includes the definition of procedures and functions declared
in the package spec and, like the package spec, can also include variables,
constants, user-defined exceptions, and user-defined datatypes.
Unlike objects created in a PL/SQL package specification, variables and other
constructs defined within a package body are private to the package body. These
constructs can only be referenced by procedures and functions within the package
body. Stored procedures are created inside a package without using the
CREATE PROCEDURE command. Instead, a procedure's definition is defined as part
of the CREATE PACKAGE BODY command.
Here is an example of the Oracle create package body command from the docs:
CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
create_dept(department_id NUMBER, location_id NUMBER)
RETURN NUMBER IS
SELECT . . .
remove_emp (employee_id NUMBER) IS
DELETE . . .
remove_dept(department_id NUMBER) IS
DELETE FROM . .
increase_sal(employee_id NUMBER, salary_incr NUMBER) IS
SELECT . .
increase_comm(employee_id NUMBER, comm_incr NUMBER) IS
SELECT . .
The DBA will be required to alter a package when there are changes to tables,
views, sequences, and so on that the package procedures and functions reference.
This is accomplished through the use of the CREATE OR REPLACE PACKAGE [BODY]
form of the CREATE PACKAGE command.
create or replace package body
plus_tax(v_msrp in number) return number
v_total := v_msrp + (v_msrp * .07);
v_job_desc in character,
v_min_lvl in number,
v_max_lvl in number)
insert into jobs values (v_job_id, v_job_desc,v_min_lvl,v_max_lvl);
WHEN OTHERS THEN
raise_application_error(-20001,'An error -
'||SQLCODE||' -ERROR- '||SQLERRM);
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.