ISBN: 1565924568 9781565924567

Category: Computers / General

Page count: 72

<p>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.</p> <p>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.</p> <p>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 <i>Oracle PL/SQL Programming</i>; 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.</p> <p>Built-in functions fall into several major categories:</p> <ul> <li>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).</li> <li>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).</li> <li>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).</li> <li>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).</li> <li>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).</li> <li>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).</li> </ul> <p>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, <i>Oracle Built-in Packages</i>. 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.</p> <p>Built-in packages extend the capabilities and power of PL/SQL in many significant ways. For example:</p> <ul> <li>DBMS_SQL executes dynamically constructed SQL statements and PL/SQL blocks of code.</li> <li>DBMS_PIPE communicates between different Oracle sessions through a pipe in the RDBMS shared memory.</li> <li>DBMS_JOB submits and manages regularly scheduled jobs for execution inside the database.</li> <li>DBMS_LOB accesses and manipulates Oracle8's large objects (LOBs) from within PL/SQL programs.</li> </ul> <p>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.</p>