Technical Training
Oracle 9i TrainingOracle 9i Packages
This weeks tutorial explains about Oracle Packages, Developing Packages, Package Specification, Package Body; Overloading, Oracle Supplied Packages and Dbms_output.
Oracle Packages
A package is a group of procedures, functions, variables and SQL statements created as a single unit. It is used to store together related objects. A package has two parts, Package Specification or spec or package header and Package Body.
Package Specification acts as an interface to the package. Declaration of types, variables, constants, exceptions, cursors and subprograms is done in Package specifications. Package specification does not contain any code.
Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification or spec.
Advantages:
- It allows you to group together related items, types and subprograms as a PL/SQL module.
- When a procedure in a package is called entire package is loaded, though it happens to be expensive first time the response is faster for subsequent calls.
- Package allows us to create types, variable and subprograms that are private or public
Package Syntax:
The package specification

The package body

If the specification of the package declares only types, constants, variables, exceptions, and call specs the package body is not required there. This type of packages only contains global variables that will be used by subprograms or cursors.
Example for a bodiless package

For accessing the types, subprograms etc from a package we write
Package_name.type_name
Package_name.subprogram_name


Executing Procedure and function from above package

The following package spec and package body declare and define 2 procedures. First the procedure is used to insert new records into the table DEPT, where the second procedure deletes a record from it.




Oracle 9i Training
- Oracle 9i Utilities
- Oracle 9i Packages
- Oracle 9i Database Triggers
- Oracle 9i Procedures and Functions
- Oracle 9i PL/SQL Collections
- Oracle 9i Exception Handling
- Download example SQL Scripts used in Oracle 9i Tutorials
- Oracle 9i Cursors
- Oracle 9i PL/SQL Control Structures
- Building PL/SQL Blocks in Oracle 9i
- Oracle 9i Tables and Constraints
- More Oracle 9i Database Objects
- Introduction to Oracle 9i SQL, PLSQL, and SQL *Plus
- Oracle 9i Software Installation, SQL, PLSQL and SQL *Plus References







