Oracle 10g Tutorials - Identifying PL/SQL Objects

In this tutorial you will learn about indentifying PL/SQL Objects like Packages, Procedures and Functions using SQL Plus based examples and illustrations.

Document Summary

This document is a simple guide to understanding PL/SQL objects such as stored procedures, functions, triggers etc. This guide provides insight into creating the above-mentioned objects using SQL Plus.

Identifying PL/SQL Objects

PL/SQL objects are named blocks of PL/SQL with declarative, executable and exception handling sections. The PL/SQL objects are stored in the data dictionary with a name and can be reused. PL/SQL objects include Packages, Stored Procedures, Functions and triggers.

Packages:

A Package is a PL/SQL construct that allows related objects to be stored together. Apart from grouping related objects, packages also provide many more advantages such as improved performance and ability to reference the PL/SQL objects contained within a package from other PL/SQL blocks.

A Package consists of two portions:

a) Package Specification

Package Specification consists of information about the contents of the package. Essentially, package specification contains ‘forward declarations’ of the procedures and functions in that package but does not contain the codes for any of these subprograms.

The syntax for creating package specification is as below:

CREATE [OR REPLACE] PACKAGE package_name {IS/AS}
type_definition/
procedure_specification/
function_specification/
variable_declaration/
exception_declaration/
cursor_declaration/
pragma_declaration
END [package_name];

b) Package Body

Package Body contains the actual code for the subprograms in the package. Package body is a separate data dictionary object from the package header. The package body cannot be compiled without the package specification being compiled successfully. The package body should compulsorily contain the definition for all sub program definitions contained in the package specification.

The syntax for creating package specification is as below:

CREATE [OR REPLACE] PACKAGE BODY package_name {IS/AS}
procedure_definitions
function_definitions
END [package_name];

Below is an example of creating a package:

Figure -1 Package Creation

Next Page: Oracle 10g Tutorials - Identifying PL/SQL Objects - Page 2