Oracle PL/SQL Built-ins Pocket Reference

Content preview·Buy reprint rights for this chapter

The Oracle PL/SQL Built-ins Pocket Reference is a quick reference guide to the many built-in packages and functions provided by Oracle Corporation. It contains a concise description of the syntax for the following:

Built-in packages
Built-in functions
RESTRICT REFERENCES pragmas for the built-in packages
Nonprogram elements (e.g., constants, exceptions, etc.) defined in the built-in packages

Although we don’t include every single package and function in this pocket reference, we’ve included all the built-ins that most PL/SQL developers will ever need to use.

Where a package, program, or function is supported only for a particular version of Oracle (e.g., Oracle8), we indicate this in the text.

The purpose of this pocket reference is to help PL/SQL users find the syntax of specific built-in headers. It is not a self-contained user guide; basic knowledge of PL/SQL and its built-ins is required. For more information, see the following books:

Oracle PL/SQL Programming, by Steven Feuerstein and Bill Pribyl (O’Reilly & Associates, Second Edition, 1997).

Oracle Built-in Packages, by Steven Feuerstein, Charles Dye, and John Beresniewicz (O'Reilly & Associates, 1998).

UPPERCASE: Indicates PL/SQL keywords.
lowercase: Indicates user-defined items such as parameters.
italic: Indicates parameters within text.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The Oracle PL/SQL Built-ins Pocket Reference is a quick reference guide to the many built-in packages and functions provided by Oracle Corporation. It contains a concise description of the syntax for the following:

Built-in packages
Built-in functions
RESTRICT REFERENCES pragmas for the built-in packages
Nonprogram elements (e.g., constants, exceptions, etc.) defined in the built-in packages

Although we don’t include every single package and function in this pocket reference, we’ve included all the built-ins that most PL/SQL developers will ever need to use.

Where a package, program, or function is supported only for a particular version of Oracle (e.g., Oracle8), we indicate this in the text.

The purpose of this pocket reference is to help PL/SQL users find the syntax of specific built-in headers. It is not a self-contained user guide; basic knowledge of PL/SQL and its built-ins is required. For more information, see the following books:

Oracle PL/SQL Programming, by Steven Feuerstein and Bill Pribyl (O’Reilly & Associates, Second Edition, 1997).

Oracle Built-in Packages, by Steven Feuerstein, Charles Dye, and John Beresniewicz (O'Reilly & Associates, 1998).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

UPPERCASE: Indicates PL/SQL keywords.
lowercase: Indicates user-defined items such as parameters.
italic: Indicates parameters within text.
[ ]: In syntax descriptions, items in square brackets are optional.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

PL/SQL packages allow you to collect related program elements and control access to those elements. Oracle provides a rich set of built-in packages that extend the functionality of PL/SQL in many important ways. Many of the built-ins allow you access to features that would otherwise be unavailable to you. You will find these packages helpful in developing applications, managing server-side resources, and performing many other operations.

The built-in packages are listed in this section in alphabetical order. For each package, we’ve shown the header (calling sequence) for each of the programs defined in the package, and provided a brief description of the program operation.

There are two default packages that deserve special mention. The STANDARD package contains many of the basic elements of the PL/SQL language (datatypes, functions, even basic operations like + and -). We describe the STANDARD functions later, in the section.

The DBMS_STANDARD package, described in this section, contains kernel extensions to the STANDARD package.

DBMS_ALERT provides mechanisms for synchronous, transaction-based notification to multiple sessions that specific database events have occurred.

PROCEDURE DBMS_ALERT.REGISTER
    (name IN VARCHAR2);

Registers the calling session to receive notification of alert name.

PROCEDURE DBMS_ALERT.REMOVE
    (name IN VARCHAR2);

Unregisters the calling session from receiving notification of alert name.

PROCEDURE DBMS_ALERT.REMOVEALL;

Unregisters the calling session from notification of all alerts.

PROCEDURE DBMS_ALERT.SET_DEFAULTS
    (sensitivity IN NUMBER);

Defines configurable settings for the calling session. (sensitivity defines the loop interval sleep time in seconds.)

PROCEDURE DBMS_ALERT.SIGNAL
    (name IN VARCHAR2
    ,message IN VARCHAR2);

Signals the occurrence of alert name and attaches message. (Sessions registered for alert

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Built-in functions, implemented by Oracle in the STANDARD built-in package, are predefined functions that give you convenient ways to manipulate your data. There are six basic types of built-in functions, each described here in its own section:

Character functions analyze and modify the contents of CHAR and VARCHAR2 string variables.
Numeric functions are a full range of operations that manipulate numbers, including trigonometric, logarithmic, and exponential functions.
Date functions are utilities that allow programmers to perform high-level actions on date variables, including date arithmetic.
Conversion functions convert from one datatype to another, often formatting the output data at the same time.
LOB functions allow operations on LOB (large object) data.
Miscellaneous functions perform operations that don’t fall into any of the other categories.

STANDARD contains definitions and functions for the PL/SQL language. These definitions include all of the PL/SQL datatypes, the named exceptions, and the functions and operators (which are defined as functions). Note that almost all STANDARD functions have corresponding SQL native functions.

Character functions parse names, concatenate strings, and perform other character operations.

FUNCTION ASCII
    (ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN BINARY_INTEGER;

Returns the numeric ASCII code for ch.

FUNCTION CHR (n BINARY_INTEGER) RETURN VARCHAR2;

Returns the character associated with the numeric collating sequence n, according to the database’s character set.

FUNCTION CONCAT 
    (left IN VARCHAR2 CHARACTER SET ANY_CS
    ,right IN VARCHAR2 CHARACTER SET left%CHARSET)
RETURN VARCHAR2;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A pragma is a directive to the PL/SQL compiler. Pragmas pass information to the compiler; they are processed at compile time but do not execute. If you include a call to a built-in package in a SQL statement, you must include a RESTRICT REFERENCES pragma in your code. This pragma tells the compiler the purity level (freedom from side effects) of a packaged program. The purity levels available are:

WNDS—Writes no database state
RNDS—Reads no database state
WNPS—Writes no package state
RNPS—Reads no package state

DBMS_LOB Programs	WNDS	RNDS	WNPS	RNPS
COMPARE

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In addition to the definitions for the programs (shown earlier in the section), a package specification may contain nonprogram elements defined for that package. These elements may include constants, exceptions, record types, and index-by tables.

This section shows the nonprogram elements defined for each of the built-in packages.

maxwait CONSTANT INTEGER := 86400000;

set_session_longops_nohint 
  CONSTANT BINARY_INTEGER := -1;

before            CONSTANT BINARY_INTEGER
browse            CONSTANT BINARY_INTEGER
expired           CONSTANT BINARY_INTEGER
first_message     CONSTANT BINARY_INTEGER
forever           CONSTANT BINARY_INTEGER
immediate         CONSTANT BINARY_INTEGER
locked            CONSTANT BINARY_INTEGER
never             CONSTANT BINARY_INTEGER
next_message      CONSTANT BINARY_INTEGER
next_transaction  CONSTANT BINARY_INTEGER
no_delay          CONSTANT BINARY_INTEGER
no_wait           CONSTANT BINARY_INTEGER
on_commit         CONSTANT BINARY_INTEGER
processed         CONSTANT BINARY_INTEGER
ready             CONSTANT BINARY_INTEGER
remove            CONSTANT BINARY_INTEGER
top               CONSTANT BINARY_INTEGER
waiting           CONSTANT BINARY_INTEGER


TYPE aq$_recipient_list_t IS TABLE OF sys.aq$_agent
  INDEX BY BINARY_INTEGER;

TYPE message_properties_t IS RECORD
   (priority        BINARY_INTEGER DEFAULT 1
   ,delay           BINARY_INTEGER 
                         DEFAULT DBMS_AQ.no_delay
   ,expiration      BINARY_INTEGER 
                         DEFAULT DBMS_AQ.never
   ,correlation     VARCHAR2(128) DEFAULT NULL
   ,attempts        BINARY_INTEGER
   ,recipient_list  DBMS_AQ.aq$_recipient_list_t
   ,exception_queue VARCHAR2(51) DEFAULT NULL
   ,enqueue_time    DATE
   ,state           BINARY_INTEGER);

TYPE enqueue_options_t IS RECORD
   (visibility         BINARY_INTEGER 
                           DEFAULT DBMS_AQ.on_commit
   ,relative_msgid     RAW(16) DEFAULT NULL
   ,sequence_deviation BINARY_INTEGER DEFAULT NULL);

TYPE dequeue_options_t IS RECORD
   (consumer_name  VARCHAR2(30) DEFAULT NULL
   ,dequeue_mode   BINARY_INTEGER 
                        DEFAULT DBMS_AQ.remove
   ,navigation     BINARY_INTEGER 
                        DEFAULT DBMS_AQ.next_message
   ,visibility     BINARY_INTEGER
                        DEFAULT DBMS_AQ.on_commit
   ,wait           BINARY_INTEGER 
                        DEFAULT DBMS_AQ.forever
   ,msgid          RAW(16) DEFAULT NULL
   ,correlation    VARCHAR2(128) DEFAULT NULL);

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Table of Contents