Oracle PL/SQL Built-ins Pocket Reference
Publisher: O'Reilly Media
Released: October 1998
Pages: 78

This pocket reference provides quick-reference information that will help you use Oracle Corporation's extensive set of built-in functions and packages, including those new to Oracle8.

Oracle's PL/SQL language is a programming language providing procedural extensions to the SQL relational database language and to an ever-growing number of Oracle development tools. Among the most useful constructs in the PL/SQL language are the built-in functions and packages.

Built-in functions are constructs that operate on certain types of data (e.g., numeric, character) to return a result. By using functions, you can minimize the coding you need to do in your programs. Functions are described in detail in Steven Feuerstein's Oracle PL/SQL Programming; this comprehensive guide to building applications with PL/SQL has become the bible for PL/SQL developers who have raved about its completeness, readability, and practicality.

Built-in functions fall into several major categories:

  • Character functions: Operate on character data. Examples include CONCAT (concatenates two strings into one), LENGTH (returns the length of a string), and REPLACE (replaces a character sequence in a string with a different set of characters).
  • Date functions: Operate on dates and supplement the DATE datatype. Examples include SYSDATE (returns the current date and time in the Oracle Server) and LAST_DAY (returns the last day in the month of the specified date).
  • Numeric functions: Operate on numeric data. Examples include CEIL (returns the smallest integer greater than or equal to the specified number) and POWER (returns a number raised to a particular power).
  • LOB functions: Operate on large object data. Examples include EMPTY_BLOB (returns an empty locator of the binary large object type) and EMPTY_CLOB (returns an empty locator of the character large object type).
  • Conversion functions: Perform explicit conversions of different types of data. Examples include TO_CHAR (converts a number or date to a string) and TO_NUMBER (converts a string to a number).
  • Miscellaneous functions. Examples include GREATEST (returns the greatest of the specified list of values) and UID (returns the user ID of the current Oracle session).

Built-in packages (collections of PL/SQL objects, such as functions, procedures, and data structures) greatly expand the scope of the PL/SQL language. These packages are described in detail in Feuerstein's and Beresniewicz's book, Oracle Built-in Packages. Built-in packages are built by Oracle Corporation and stored directly in the Oracle database. The functionality of the built-ins is available from any programming environment that can call PL/SQL stored procedures, including Visual Basic, Oracle Developer/2000, Oracle Application Server (for Web-based development), and, of course, the Oracle database itself.

Built-in packages extend the capabilities and power of PL/SQL in many significant ways. For example:

  • DBMS_SQL executes dynamically constructed SQL statements and PL/SQL blocks of code.
  • DBMS_PIPE communicates between different Oracle sessions through a pipe in the RDBMS shared memory.
  • DBMS_JOB submits and manages regularly scheduled jobs for execution inside the database.
  • DBMS_LOB accesses and manipulates Oracle8's large objects (LOBs) from within PL/SQL programs.

The book shows how to call all of the commonly used built-in functions and packages. For packages, it also shows the RESTRICT REFERENCES pragmas (needed if you call packages from a SQL statement), as well as the exceptions, constants, and data structures defined in the packages.

Table of Contents
Product Details
About the Author
Recommended for You
Customer Reviews

REVIEW SNAPSHOT®

by PowerReviews
O'Reilly MediaOracle PL/SQL Built-ins Pocket Reference
 
3.0

(based on 1 review)

Ratings Distribution

  • 5 Stars

     

    (0)

  • 4 Stars

     

    (0)

  • 3 Stars

     

    (1)

  • 2 Stars

     

    (0)

  • 1 Stars

     

    (0)

Reviewed by 1 customer

Displaying review 1

Back to top

 
3.0

Oracle PL/SQL Built-ins Pocket Reference Review

By John Ayles

from Undisclosed

Comments about O'Reilly Media Oracle PL/SQL Built-ins Pocket Reference:

I have only had quick read through, and on the whole it seems like a handy little reference guide. One omission I hope will be remedied in the New! Improved! 9i/2nd edition would be the inclusion of format mask information for the TO_CHAR/TO_DATE/TO_NUMBER functions. Specifying a format mask can be passed in to these functions isn't of much use unless format mask options are listed.

Displaying review 1

Back to top

 
Buy 2 Get 1 Free Free Shipping Guarantee
Buying Options
Immediate Access - Go Digital what's this?