What is Oracle Package?
A package is a blueprint object that merges logically related Oracle types, variables, and subprograms. Packages usually consist of two parts, a specification (spec) and a body; sometimes the body is optional. The particularization is the interface to the package which consists of the cursors, variables, type, exception and subprograms that can be referenced from outside the package. The body of the package has the queries for the cursors and the code for the subprograms.
Why we use Packages in Oracle?
We are using Package in Oracle due to the following reasons:
- Cohesion: All the procedures and task relating to a particular sub-system are stored at one place. This is good to implement but complex to manage. For example: In source control.
- Constants, sub-types and other useful things. Whatever we define in a package can be used by all the other programs too for instance user-defined exceptions.
- We can perform Overloading in Oracle packages i.e. the feature to define a procedure or function with the same name but different signatures.
- We can add security to Oracle Package by defining private procedures in the package body which can only be used by the package because they are not visible in the specification.
- Code Reusability is another advantage of Oracle Package
- We have to grant EXECUTE only on package rather than all procedures.
Advantages of Oracle Package:
Packages let us contract logically related types, items, and subprograms in a named Oracle module. Each package is easy to grasp and the interfaces between packages are simple, easy and well defined. This aids application development.
2. Easier Application Design
When designing an application, all we need initially is the interface information in the package specs. We can create a package coding without its body. The stored programs will refer the packages and compiled too. We need not define the package bodies fully until we are ready to complete the application.
3. Information Hiding
With packages, we can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains five subprograms, three might be public and only one private. The package invisible the implementation of the private subprogram code so that only the package is affected if the implementation changes. This simplifies provision and enrichment. Also, by making invisibility of creating details from users, we protect the integrity of the package.
4. Added Functionality
Packaged public variables and cursors persist for the duration of a session. They can be visible and used by all subprograms that can execute in that environment. They let we maintain data across transactions without storing it in the database.
5. Better Performance
When we call a packaged subprogram for the first time, the complete package is loaded into memory. Later calls to related routines in the package require no disk I/O.
Packages stop cascading dependencies and avoid unnecessary recompiling. For example, if we change the body of a packaged bundle, Oracle compile only changed part and does not recompile other routines that call the function; these subprograms only depend on the parameters and return value that are defined in the spec, so they are only recompiled if the code changes.
Creating Package in Oracle:
Use the CREATE PACKAGE statement to create the specification for a stored package, which is an enriched collection of related functions, procedures and other program objects stored together in the database. The package particularization defines these objects. Subsequently, these objects are defined in the specified package body.
Before a creating a package, the user must run a SQL script on its SYS, which is commonly known as DBMSSTDX.SQL. The exact address and the name of this script depend on were operating system.
To create a package in our own schema, we must have the CREATE PROCEDURE system privilege. To create a package in another or different user's schema, we must have the CREATE ANY PROCEDURE system privilege.
To embed a statement like CREATE PACKAGE in an Oracle Database precompiled program, we must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.
Figure 1: Syntax to create package
Specify OR REPLACE to re-create the package specification if it is already there. Use this clause to alter or modify or change the specification of an existing package without re-creating, regranting and dropping object privileges previously granted on the package. If we change a package specification, then Oracle recompiles it and check for any mismatch.
Users who had beforehand been granted access on a redefined package can still access the package without being regranted the privileges.
If there is any function-based ranking which depends upon the package, then the database marks that ranking DISABLED.
Specify the schema to contain the package. If we omit schema, then the database creates the package in were own schema.
Defines the name of the package to be created.
If creating the package results in compilation errors, then the database returns an error. We can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS.
The invoker_rights_clause lets we specify whether the functions and procedures in the package execute with the privileges and in the schema of the user who owns the package or with the privileges and in the schema of CURRENT_USER. This particularization applies to the respective package body as well.
This clause also finds out how Oracle Database undertakes external names in queries, DML operations, and dynamic SQL statements in the package.
Specify CURRENT_USER to indicate that the package executes with the privileges of CURRENT_USER. This clause creates an invokes the rights package that who can access it.
This clause also specifies that external statements in queries, DML and DDL operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements undertaking in the scheme in which the package resides.
Specify DEFINER to indicate that the package executes with the privileges of the owner of the schema in which the package resides and that external names resolve in the schema where the package resides.
This is the default and creates a specifier-rights package.
Defines the package specifications, which have cursor declarations, type definitions, constant declarations, exception declarations, variable declarations, Oracle subprogram specifications, and call specifications, which are declarations of a C or Java routine expressed in PL/SQL.
Example to create a package:
Listing 1: Creating a package employee
CREATE OR REPLACE PACKAGE employee AS FUNCTION job (name VARCHAR2, emp_id VARCHAR2, manager_id NUMBER, salary NUMBER, commission_pct NUMBER, department_id NUMBER) RETURN NUMBER; FUNCTION maintain_dept(department_id NUMBER, location_id NUMBER) RETURN NUMBER; PROCEDURE delete_emp(employee_id NUMBER); PROCEDURE delete_dept(department_id NUMBER); PROCEDURE increment_sal(employee_id NUMBER, salary_incr NUMBER); PROCEDURE increment_comm(employee_id NUMBER, comm_incr NUMBER); no_comm EXCEPTION; no_sal EXCEPTION; END employee; /
The specification for the employee package declares the following public program objects:
- The functions job and maintain_dept
- The procedures delete_emp, delete_dept, increment_sal, and increment_comm
- The exceptions no_comm and no_sal
All of these objects are available to users who have access to the package. Once we had created a package, we can develop any number of applications that call any of these public procedures or functions or raise any of the public exceptions of the package.
In this section we studied how we can create a package in Oracle for the sake of simplicity and reusability. How we can allow different grants to these packages.