A package is a schema object that groups logically related PL/SQL types, variables, and subprograms.
Packages usually have two parts:
- A specification (spec)
- A body
- The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package.
- A package always has a specification, which declares the public items that can be referenced from outside the package.
- The body defines the queries for the cursors and the code for the subprograms (Function and Procedure).
- Enable the Oracle server to read multiple objects into memory at once.
- You can map a package subprogram to an external Java or C subprogram by using a call specification
Advantages of Using Packages
- Modularity : Encapsulating related constructs
- Easier maintenance: Keeping logically related functionality together
- Better Performance: Oracle load whole package into memory. if you change the body of a package function, Oracle Database does not recompile other subprograms that invoke the function, because these subprograms depend only on the parameters and return value that are declared in the specification.
- Easier to Grant Roles: You can grant roles on the package, instead of granting roles on each object in the package.
- Easier application design: Coding and compiling the specification and body separately
- Hiding information: Only the declarations in the package specification are visible and accessible to applications
- Private constructs in the package body are hidden and inaccessible
- All coding is hidden in the package body
CREATE OR REPLACE PACKAGE pkg_sal IS v_sal NUMBER; PROCEDURE get_max_sal; END pkg_sal;
Creating package body
create or replace package body pkg_sal is procedure get_max_sal is begin select max(salary) into v_sal from employees; dbms_output.put_line('Maximum Salary '|| v_sal); end get_max_sal; end pkg_sal;
begin pkg_sal.get_max_sal; end;
Creating and Using Body less Packages
CREATE OR REPLACE PACKAGE pkg_area IS c_pie CONSTANT NUMBER := 3.14; c_km CONSTANT NUMBER := 1000; END pkg_area; DECLARE v_area_circle NUMBER; v_radius NUMBER :=5; BEGIN v_area_circle:= pkg_area.c_pie * v_radius; dbms_output.put_line('area of circle '||v_area_circle); END;
Remove the package specification and body
DROP PACKAGE package_name;
Remove the package body only
DROP PACKAGE BODY package_name;
Viewing Packages Using the Data Dictionary
View the package specification.
SELECT text FROM user_source WHERE name = 'COMM_PKG' AND type = 'PACKAGE' ORDER BY LINE;
View the package body.
SELECT text FROM user_source WHERE name = 'COMM_PKG' AND type = 'PACKAGE BODY' ORDER BY LINE;
Reference Link: https://docs.oracle.com/database/121/LNPLS/packages.htm#LNPLS99924