Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: B.2 Restrictions on Calling Stored ProceduresAppendix CNext: C.2 DBMS_ALERT
 

C. Built-In Packages

Contents:
Using the Built-in Packages
DBMS_ALERT
Oracle AQ, the Advanced Queueing Facility
DBMS_DDL
DBMS_ JOB
DBMS_LOB (PL/SQL8 Only)
DBMS_LOCK
DBMS_MAIL
DBMS_OUTPUT
DBMS_PIPE
DBMS_ROWID (PL/SQL8 Only)
DBMS_SESSION
DBMS_SNAPSHOT
DBMS_SQL
DBMS_TRANSACTION
DBMS_UTILITY
UTL_FILE

This appendix provides a quicksummary of the most commonly used RDBMS-based packages built by Oracle Corporation and made available to all developers. Table C.1 shows the list of packages covered here. Unless otherwise noted, the packages are available in PL/SQL Release 2.1 and above.


Table C.1: Built-In Packages Stored in the Oracle Database

Package Name

Description

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_AQ

Offers an interface to Oracle/AQ, the Advanced Queueing Facility of Oracle8 (PL/SQL8 only).

DBMS_AQADM

Used to perform administrative tasks for Oracle/AQ (PL/SQL8 only).

DBMS_DDL

Provides programmatic access to some of the SQL DDL statements.

DBMS_JOB

Submits and manages regularly scheduled jobs for execution inside the database (PL/SQL Release 2.1)

DBMS_LOB

Provides a set of programs to manipulate LOBs (large objects) (PL/SQL8 only).

DBMS_LOCK

Lets you create your own user locks in the database.

DBMS_MAIL

Interfaces to Oracle Office (formerly known as Oracle*Mail).

DBMS_OUTPUT

Displays output from PL/SQL programs to the terminal.

DBMS_PIPE

Communicates between different Oracle sessions through a pipe in the RDBMS shared memory.

DBMS_ROWID

Encapsulates information about the structure of the ROWID datatype and allows for conversion between restricted and extended ROWIDs (PL/SQL8 only).

DBMS_SESSION

Provides a programmatic interface to several SQL ALTER SESSION commands and other session-level commands.

DBMS_SNAPSHOT

Provides 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

Provides full support for dynamic SQL within PL/SQL. Dynamic SQL refers to statements that are not prewritten into your programs. They are, instead, constructed at run time as character strings and then passed to the SQL engine for execution. (PL/SQL Release 2.1)

DBMS_ TRANSACTION

Provides a programmatic interface to a number of the SQL transaction statements, such as SET TRANSACTION.

DBMS_UTILITY

The miscellaneous package. Contains various useful utilities, such as FORMAT_CALL_STACK, which returns the current stack of called modules.

UTL_FILE

Allows PL/SQL programs to read from and write to operating system files. (PL/SQL Release 2.3)

All of the packages in Table C.1 are stored in the database and can be executed both by client- and server-based PL/SQL programs. In addition to these packages, many of the development tools, like Oracle Forms, offer their own specific package extensions, such as packages to manage OLE2 objects and DDE communication.[1]

[1] For more detailed information about these built-in packages, see my book, Oracle Built-in Packages.

C.1 Using the Built-in Packages

In this appendix, I've provided a brief overview of each package, followed by a description and header for each program in the package. These headers are structured as follows:

PROCEDURE pkg.procname (<parameter list>);

FUNCTION pkg.funcname (<parameter list>) RETURN <return datatype>;

where pkg is the name of the package, procname and funcname are the names of the programs, <parameter list> is the list of parameters (if there are no parameters, then you do not provide parentheses either) and <return datatype> is the datatype of the value returned by the function.

Let's look at an example. Suppose that you want to receive a message from a pipe. The header for the built-in function which does this is:

FUNCTION DBMS_PIPE.RECEIVE_MESSAGE=20
   (pipename IN VARCHAR2, timeout INTEGER DEFAULT
    DBMS_PIPE.MAXWAIT)
RETURN INTEGER;

Note that all identifiers are in uppercase except for parameter names. This is consistent with my conventions: all keywords and other identifiers built by Oracle are in uppercase. The parameter names are lowercase because in many program headers, I have provided my own parameter names to make the headers more readable.

When I want to call a packaged program, I must use dot notation. For example to make use of the RECEIVE_MESSAGE built-in, I would write code like this:

DECLARE
    pipe_status INTEGER;
BEGIN
     pipe_status DBMS_PIPE.RECEIVE_MESSAGE (mypipe, 10);
END;


Previous: B.2 Restrictions on Calling Stored ProceduresOracle PL/SQL Programming, 2nd EditionNext: C.2 DBMS_ALERT
B.2 Restrictions on Calling Stored ProceduresBook IndexC.2 DBMS_ALERT

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference