Oracle Packages:
1.1 A Package is a collection of logically related data objects and the procedures that manipulate them. A package is a schema object which includes definitions of variables, constants, cursors, exceptions, exception handlers, composite data types, procedures, and functions, and is stored in the database as a single object. Variables and cursors can be declared global to all procedures in a package and they maintain their values throughout the session.
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.
1.2 A package has two parts
1.5 Advantages of Packages
Modularity
Easier Application Design
Information Hiding
Added Functionality
Better Performance
1.6 The
Package Specification.
Package syntax
1.8 Package body syntax
Example: A
Generated Package Template
1.1 A Package is a collection of logically related data objects and the procedures that manipulate them. A package is a schema object which includes definitions of variables, constants, cursors, exceptions, exception handlers, composite data types, procedures, and functions, and is stored in the database as a single object. Variables and cursors can be declared global to all procedures in a package and they maintain their values throughout the session.
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.
1.2 A package has two parts
·
Package Specification ( Called
as spec in short) or package header
·
Package Body.
1.3 Package Specification acts as an
interface to the package and contains the following. Package specification does not contain any
code.
·
Declaration of types
·
Variables
·
Constants
·
Exceptions
·
Cursors
·
Subprograms
1.4 Use of Packages:
Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification or spec. PL/SQL stored packages allow you to define a functional structure well beyond what can be accomplished with alternative systems. A package can include definitions of variables, constants, cursors, exceptions, exception handlers, composite data types, procedures, and functions, and is stored in the database as a single object. Variables and cursors can be declared global to all procedures in a package and they maintain their values throughout the session.
In other implementations, variables lose all state between calls. Thus, you can define various packages of self-contained, reusable functionality. For example, you can define an inventory package containing commonly used routines for shipping products or reordering parts, an accounting package containing routines for tracking expenses and budgets, and a human resources package for hiring and promoting employees and tracking their progress.
The specification of a package (i.e., the procedure names and parameter lists) can be separate from its actual implementation. This enables you to specify an entire application on up-front, yet write and test the procedures one part at a time.
Package objects can be made public (accessible to other packages), or private (accessible to only its own package). This allows you to hide implementation details of your own system from the public and provides security.
If changing the implementation of a package does not require changing its specification, then any procedures that call routines in this package do not need to be recompiled. This avoids a chain reaction where the modification of one package sets off hundreds of automatic recompilation throughout the system, and frees database resources for other useful work.
The following are some of the uses of packages
The Packages allows a PL/SQL block to group together related items, types and subprograms as a 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 (Making one time compilation and calling the package when ever necessary through) .
- Package allows us to create types,
variable and subprograms that are private or public as per requirement.
·
Package contains all related
code in a single object.
·
All related code loaded into
memory simultaneously of a package when it is created.
·
As a Package body Single object
compilation is performed.
·
For a given package variables
defined and which persist for term of session.
·
Fewer objects to manage and grant/revoke
privileges.
1.5 Advantages of Packages
Packages offer several advantages:
- Modularity
- Easier Application Design
- Information hiding
- Added functionality
- Better performance.
Modularity
Packages let you encapsulate logically related
types, items, and subprograms in a named PL/SQL module. Each package is easy to
understand, and the interfaces between packages are simple, clear, and well
defined. This aids application development.
Easier Application Design
When designing an application, all you need
initially is the interface information in the package specs. You can code and
compile a spec without its body. Then, stored subprograms that reference the
package can be compiled as well. You need not define the package bodies fully
until you are ready to complete the application.
Information Hiding
With packages, you can specify which types,
items, and subprograms are public (visible and accessible) or private (hidden
and inaccessible). For example, if a package contains four subprograms, three
might be public and one private. The package hides the definition of the
private subprogram so that only the package (not your application) is affected
if the definition changes. This simplifies maintenance and enhancement. Also,
by hiding implementation details from users, you protect the integrity of the
package.
Added Functionality
Packaged public variables and cursors persist
for the duration of a session. So, they can be shared by all subprograms that
execute in the environment. Also, they allow you to maintain data across
transactions without having to store it in the database.
Better Performance
When a package is called with subprogram for the
first time, the whole package is loaded into memory. So, later calls to related
subprograms in the package require no disk I/O. Also, packages stop cascading
dependencies and thereby avoid unnecessary recompiling. For example, if you
change the definition of a packaged function, Oracle need not recompile the
calling subprograms because they do not depend on the package body.
Syntax of the PACKAGE.
CREATE [OR REPLACE] PACKAGE package_name
[AUTHID {CURRENT_USER | DEFINER}] {IS | AS}
[type_definition [type_definition] ...]
[cursor_spec [cursor_spec] ...]
[item_declaration [item_declaration] ...]
[{subprogram_spec | call_spec} [{subprogram_spec | call_spec}]...]
END [package_name];
1.6 The
Package Specification.
The package spec contains public declarations. The scope of
these declarations is local to your database schema and global to the package.
So, the declared items are accessible from your application and from anywhere
in the package.
Diagram-1.1 How schema is Package body and Package
specifications are connected.
Always create
and compile specifications of the Package and
Package body separately.
Package syntax
The general syntax
for the package body is shown below:
CREATE OR REPLACE PACKAGE package_name
IS
PROCEDURE procdure_name1;
PROCEDURE procdure_name2;
PROCEDURE procdure_name3;
END PACK1;
/
1.8 Package body syntax
The general syntax
for the package body is shown below:
PACKAGE BODY package_name
IS
[ declarations of variables and types ]
[ header and SELECT statement of cursors ]
[ header and body of modules ]
[ BEGIN
executable statements ]
[ EXCEPTION
exception handlers ]
END [package_name];
In the body you can
declare other variables, but you do not repeat the declarations in the
specification. The body contains the full implementation of cursors and
modules. In the case of a cursor, the package body contains both the header and
the SQL statement for the cursor. In the case of a
module, the package body contains both the header and body of the module.
The BEGIN keyword
indicates the presence of an execution or initialization section for the
package. This section can also optionally include an exception section.
As with a procedure,
function, and package specification, you can add the name of the package, as a
label, after the END keyword in both the specification and package.
PACKAGE BODY Syntax.
CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}
[type_definition [type_definition] ...]
[cursor_body [cursor_body] ...]
[item_declaration [item_declaration] ...]
[{subprogram_spec | call_spec} [{subprogram_spec | call_spec}]...]
[BEGIN sequence_of_statements]
END [package_name];
Package Body with Procedures
CREATE OR REPLACE PACKAGE <package_name> AS PROCEDURE <procedure_name1> (<parameters>);
PROCEDURE <procedure_name2> (<parameters>);
PROCEDURE <procedure_name3> (<parameters>);
END <package_name>;
Forward Declaration in Packages
In a package you can declare a group of logical subprograms which are logically related in a package. The subprogram specs go in the package spec, and the subprogram bodies go in the package body, where they are invisible to applications. With this packages allow you to hide implementation details.
/* Here is the syntax of the Package and Procedure called Package_Name and Package body and three sample Procedures are declared and it has the three procedures called Package1, Package2, Package3*/
Syntax of PACKAGE
CREATE PACKAGE <Package_Name> AS -- package spec
PROCEDURE Procedure1 (emp_id INTGER, name VARCHAR2 .......);
PROCEDURE Procedure2 (emp_id INTEGER, amount REAL);
PROCEDURE Procedure3 (emp_id INTEGER);
...
END <Package_Name>;
Syntax of PACKAGE BODY
CREATE PACKAGE BODY <Package_Body> AS -- package body
PROCEDURE Procedure1 (Paramter1 INTGER, Paramter2 VARCHAR2......) IS
BEGIN
...
INSERT INTO <Table1> VALUES (v1, v2,.....);
END Procedure1;
PROCEDURE Procedure2 (Paramter1 INTEGER, Paramter2 REAL) IS
salary REAL;
BEGIN
SELECT Parameter1 INTO salary FROM emp
WHERE < Condition>;
...
END Procedure2;
PROCEDURE Procedure3 (Paramter1 INTEGER) IS
BEGIN
DELETE FROM < TABLE1>
WHERE < Condition>;
END Procedure3;
...
END <Package_Body>;
/* This Package
body where Procedure called main_p and ‘validate_input’ and
get_api_input_parameters procedures */
Figure.1 Creation of the Package
1.9 The following list offers a summary of the best practices for
packages covered in this chapter:
·
Use a consistent and effective coding style. Use consistent indentation to reveal
the logical flow of the program and to delineate the different sections of the PL/SQL program structure. Generally, this means that all
executable statements are indented in from the BEGIN keyword, the body of a loop is indented within
the LOOP and END LOOP keywords, and so on. Within a package, all specification
declarations are indented between the IS and END keywords.
Code
all reserved words
in the PL/SQL language in upper-case. Use lower-case
for all application-specific identifiers. Generally, this is accomplished with
hard-coded literals and the use of UPPER and LOWER.
Use
comments to add value to the code. Don't bother
with comments that simply repeat what the code clearly states.
The style elements
found valuable particularly for packages include the following:
Use
banners (specially formatted comment lines) to
mark clearly the different groupings of package elements.
Use
end labels for the package and for all program units defined in the package
body.
Example: A
Generated Package Template
SQL> exec PLVgen.pkg('emp_maint');
1 CREATE OR REPLACE PACKAGE emp_maint
2 /*
3 || Program: emp_maint
4 || Author: Steven Feuerstein
5 || File: emp_maint.SQL
6 || Created: APR 13, 1996 18:56:59
7 */
8 /*HELP
9 Add help text here...
10 HELP*/
11
12 /*EXAMPLES
13 Add help text here...
14 EXAMPLES*/
15
16 IS
17 /* Public Data Structures */
18
19 /* Public Programs */
20
21 PROCEDURE help (context_in IN VARCHAR2 := NULL);
22
23 END emp_maint;
24 /
25
26 CREATE OR REPLACE PACKAGE BODY emp_maint
27 IS
28 /* Private Data Structures */
29
30 /* Private Programs */
31
32 /* Public Programs */
33
34 PROCEDURE help (context_in IN VARCHAR2 := NULL)
35 IS
36 BEGIN
37 PLVhlp.show ('s:emp_maint', context_in);
38 END help;
39 END emp_maint;
40 /
|
Lines
|
Significance
|
|
2-7
|
|
|
8-14
|
|
|
17-19
|
|
|
21
|
Header for a
procedure that delivers online help for this package. Of course, this should
only be included if the online help package is being used.
|
|
23
|
The END statement
with the package name appended.
|
|
28-32
|
Banners to identify
the three kinds of elements that can appear in a package body: private data
structures, program units, and the implementation of the public program
units.
|
|
34-38
|
The implementation
of the help procedure. Notice that the procedure uses an end label with the
program name and is also indented in multiple steps from the overall package.
|
·
Selecting Package Names:
o
Choose appropriate and accurate names
§ There are two aspects to coming up with the right names for your
code elements:
§ The structure of the name should match the role that element plays
in your code.
§ The name should reflect what the element does in your code.
o
Avoiding Redundancy
§ To reference a package element outside of the package you must use dot notation (package.element). As a
result, you will want to avoid redundancy in your package and element names.
For example, suppose I have a package named emp_maint for employee maintenance.
One of the procedures in the package sets the employee salary.
·
Construct the optimal interface to your package.
o
Design your package so that it
is easy -- and a pleasure -- to use. When you build packages for
reuse, other PL/SQL developers become your users of it. Treat them with
respect. Make the parameters in your programs case-insensitive. Don't require
users to know about and pass literal values.
o
Make the programs
case-insensitive
Make
sure users don't trip over senseless obstacles on the path to using your programs.
A common source of frustration is the requirement that arguments to a program
be in one case or another (usually upper or lower).
Consider
the following program:
CREATE OR REPLACE FUNCTION twice
(string_in IN VARCHAR2, action_in IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
IF action_in = 'UL'
THEN
RETURN (UPPER (string_in) || LOWER (string_in));
ELSIF action_in = 'LU'
THEN
RETURN (LOWER (string_in) || UPPER (string_in));
ELSIF action_in = 'N'
THEN
RETURN string_in || string_in;
END IF;
END twice;
/
o If a program is going to be widely reusable, it should be able to
adapt to different circumstances to meet different needs. There are two basic
ways to be flexible when writing programs for others to use:
§ Offer lots of parameters in the parameter lists of the package's
functions and procedures. This is the traditional, well-worn path.
§ Provide toggles or on-off switches, distinct from the main programs
of the package, which modify the behavior of those programs. This approach
takes advantage of the package structure to offer a new way of doing things.
§ One of the most exciting benefits of package toggles is that they
allow a user of the package to modify the behavior of the package without
changing any application code that calls the package element.
§ You didn't have to change your program and you didn't have to modify
the state of your database. From outside the package, you call the
toggle program to reach inside the package and change the way the
package will behave. This ability to leave your own code intact comes in
particularly handy not only for special exceptions but also for testing as
below:
A
common debug and test cycle in PL/SQL shops goes like
this:
1.
You identify incorrect behavior
in your program.
2.
Unable to understand the cause
of the behavior, you place numerous calls to DBMS_OUTPUT.PUT_LINE (or,
with your purchase of this book, PL/Vision's much more friendly p.l
procedure) and other kinds of tracing lines of code so that you can see what is
going on.
3.
You analyze the output, track
down the problem, and fix it.
4.
You finally decide that all the
bugs are gone.
5.
You notify your manager that
the application is ready to go. Excitement mounts. Other organizations are told
to start moving the code from test to production. Suddenly, you break out in a
cold sweat and tell your bewildered manager to "hold off a minute."
6.
You forgot about all that
debugging code you littered into your application. It can't go into production
like that. You have to go back into the program to comment out or outright
remove all that trace code. No problem, you tell yourself. Easy to do...but
there could be a problem. After all, any time you touch the code, you
can break it. After any changes of any kind to your code, you really should
retest.
7.
So you have to go back to your
manager and ask for more time to make sure everything really is all right. Not
a pleasant situation in which to find yourself.
If, on the other
hand, you used packages with toggles to trace your debugging activity, you
would not have to worry about any of that. You could keep your code intact and
simply issue a call to the appropriate package toggle to turn off any
superfluous activity, as in:
SQL> exec pkg.turn_off
o One of the most powerful aspects of the package is the ability to
overload program units. When you overload, you define more than one program
with the same name. These programs will differ in other ways (usually the
number and types of parameters) so that at runtime the PL/SQL
engine can figure out which of the programs to execute. You can take advantage
of the overloading feature of packages to make your package-based features as
accessible as possible.
o Does overloading sound unfamiliar or strange? Well, have you ever
used the TO_CHAR function? If so, then you have
already been enjoying the creature comforts of overloading. TO_CHAR converts
both numbers and dates to strings. Have you ever wondered why you don't have to
call functions with names like TO_CHAR_FROM_DATE or TO_CHAR_FROM_NUMBER?
Probably not. You probably just took TO_CHAR for granted, and that is how it
should be.
o In reality, there are two different TO_CHAR functions (both defined
in the STANDARD package): one to convert dates and another to convert numbers.
The reason that you don't have to care about such details and can simply
execute TO_CHAR is that the PL/SQL runtime engine examines the kind of data you
pass to TO_CHAR and then automatically figures out which of the two functions
(with the same name) to execute. It's like magic, only it's better than magic:
it's intelligent software!
o When you build overloaded modules, you spend more time in design and
implementation than you might with separate, standalone modules. This
additional up-front time will be repaid handsomely down the line in program
productivity and ease of use.
o You will not have to try to remember the different names of the
modules and their specific arguments. Properly constructed, overloaded modules
will have anticipated the different variations, hidden them behind a single
name, and liberated your brain for other, more important matters.
When to overload a
package:
o When you overload, you take the first step towards providing a
declarative interface to PL/SQL-based functionality. With a declarative
approach, a developer does not write a program to obtain the necessary
functionality. Instead, she describes what she wants and lets the underlying
code handle the details (this follows the approach used by the SQL language).
The process of overloading involves abstracting out from separate programs into
a single action.
o You want to display a date? You want to display a number? You want
to display a string and a number? Hold on a minute. The common element
is that you want to display something -- lots of somethings, in fact. So
don't create display_date, display_string, etc. procedures. Instead, offer a
single display procedure, which is in fact many overloaded display procedures.
o With the overloading in place, your user must only remember this:
when I want to display something, I simply ask the display program to take care
of it for me. What do I pass to it? Whatever I want it to display. I will not
(and do not have to) worry about the how of the display mechanism. Those
details are hidden from me.
o Here are some of the circumstances that cause the PL/SQL fairy to
whisper in my ear "Overload, overload...:
§ Apply the same action to different kinds or combinations of data.
§ Allow developers to use a program in the most natural and intuitive
fashion; you use overloading to fit your program to the needs of the user.
§ Make it easy for developers to specify, unambiguously and simply,
the kind of action desired.
|
Package Name
|
Description
|
|
DBMS_OUTPUT
|
Displays output
from PL/SQL programs to the terminal. The "lowest common
denominator" debugger mechanism for PL/SQL code.
|
|
UTL_FILE
|
Allows PL/SQL
programs to read from and write to operating system files.
|
|
DBMS_JOB
|
Used to submit and
manage regularly scheduled jobs for execution inside the database.
|
|
DBMS_LOCK
|
Allows users to
create their own locks using the Oracle Lock Management (OLM) services in the
database.
|
|
DBMS_MAIL
|
Offers an interface
to Oracle Office (previously known as Oracle Mail).
|
|
DBMS_ALERT
|
Provides support
for notification of database events on an asynchronous basis. Registers a
process with an alert and then waits for a signal from that alert.
|
|
DBMS_PIPE
|
Allows
communication between different Oracle sessions through a pipe in the RDBM’s
shared memory. One of the few ways to share memory-resident data between
Oracle sessions.
|
|
DBMS_SESSION
|
Provides a
programmatic interface to several SQL ALTER SESSION commands and other
session-level commands.
|
|
DBMS_SNAPSHOT
|
A programmatic
interface through which you can manage snapshots and purge snapshot logs. You
might use modules in this package to build scripts to automate maintenance of
snapshots.
|
|
DBMS_SQL
|
Full support for
dynamic SQL within PL/SQL. Dynamic SQL means SQL statements that are not
prewritten into your programs. They are, instead, constructed at runtime as
character strings and then passed to the SQL Engine for execution. (PL/SQL
Release 2.1 only)
|
|
DBMS_TRANSACTION
|
A programmatic
interface to a number of the SQL transaction statements, such as the SET
TRANSACTION command.
|
|
DBMS_UTILITY
|
The
"miscellaneous" package. Contains various useful utilities, such as
GET_TIME, which calculates elapsed time to the hundredth of a second, and
FORMAT_CALL_STACK, which returns the current execution stack in the PL/SQL
runtime engine.
|
|
DBMS_ALERT
|
Provides support
for notification of database events on an asynchronous basis. Registers a
process with an alert and then waits for a signal from that alert.
|
Figure2. Some of
the Built-in Packages Stored in the Oracle Database
Figure3. Package creation at SQL PLUS prompt.
2.0 More about
the UTL_FILE
The UTL_FILE
package lets your PL/SQL programs read and write operating system (OS) text
files. It provides a restricted version of standard OS stream file input/output
(I/O).
The file I/O
capabilities are similar to those of the standard operating system stream file
I/O (OPEN, GET, PUT, CLOSE), with some limitations. For example, call the FOPEN
function to return a file handle, which you then use in subsequent calls to
GET_LINE or PUT to perform stream I/O to a file. When you are done performing
I/O on the file, call FCLOSE to complete any output and to free any resources
associated with the file.
The following
are UNIX Specific.
UTL_FILE_DIR=/appl/gl/log
UTL_FILE_DIR=/appl/gl/out
FILE LOCATION FILENAME
/appl/gl/log L10324.log
/appl/gl/out O10324.out
FILE LOCATION FILENAME
/appl/gl/log/backup L10324.log # subdirectory
/APPL/gl/log L10324.log # uppercase
/appl/gl/log backup/L10324.log # dir in name
/usr/tmp T10324.tmp # not in INIT.ORA
Types
TYPE file_type IS RECORD (id BINARY_INTEGER);
The contents of
FILE_TYPE are private to the UTL_FILE package. Users of the package
should not reference or change components of this record.
Exceptions in UTL_FILE
Package Exceptions
Subprograms in the UTL_FILE Package
Subprogram |
Description
|
FOPEN function |
|
IS_OPEN function
|
|
FCLOSE procedure
|
|
FCLOSE_ALL procedure
|
|
GET_LINE procedure
|
|
PUT procedure
|
|
NEW_LINE procedure
|
|
PUT_LINE procedure
|
|
PUTF procedure
|
|
FFLUSH procedure
|
|
FOPEN function
|
|
FOPEN function
|
Opens a file for
input or output with the default line size.
|
2.1
ALTER PACKAGE.
ALTER PACKAGE command
PURPOSE: To recompile a stored package.
SYNTAX: ALTER PACKAGE [schema.]package
COMPILE [PACKAGE | BODY]
Schema : The schema contains the package. If you omit schema, Oracle
assumes the package is in your own schema.
Package : The name of the package to be recompiled.
COMPILE : Recompiles the package specification or body. The COMPILE keyword
is required.
PACKAGE : Recompiles the package body and specification.
BODY : Recompiles only the package body.
The default option is PACKAGE.
PREREQUISITES: The package must be in your own schema or
you must have ALTER ANY PROCEDURE system privilege.
No comments:
Post a Comment