Oracle built-in packages pdf

Some of the packages contain programs that you would not want to make generally or widely available. Write less code. Have you ever noticed the really impressive lengths of the names of Oracle built-in packages and their programs? Your encapsulations can use shorter names and thereby erase the federal deficit.

As you will read in the next section, there are many restrictions on accessing stored code from products like Oracle Forms.

Encapsulation can help you work around these restrictions. Roughly speaking, there are two types of encapsulation to consider when working with the built-in packages:. This is the most common type of encapsulation for built-in packages.

In this case, you provide one or more programs that extend the functionality or usability of the underlying package. When you create a cover for a built-in package, you create a package with a specification that matches that of the built-in package same program names, same parameter lists.

You can even give your package the same name as the built-in package, but you install it in a schema other than SYS.

This book and the accompanying disk contains many packages that encapsulate or cover an underlying built-in package or, in some cases, a subset of the package. Hides details of creating, starting, stopping, and dropping queues and queue tables. The package allows you to write less code and also handles common errors. See the next section for details. You can reference only packaged procedures and functions.

You must supply a value for each argument in a packaged procedure or function. You cannot rely on default values in the headers of those programs. This is true even if those default values do not reference elements in the built-in packages in other words, are literal values.

You must be connected to the Oracle database before you can compile program units that reference built-in packages. The following sections explore these restrictions in more detail and suggest work-arounds for making full use of the built-in packages from within products like Oracle Forms and Oracle Reports.

PARSE procedure. Here is the header for this program:. Oracle Forms simply does not know how to interpret anything but procedures and functions in stored packages. You have several options:.

Find out the literal value behind the named constant and use that, or. I suggest that you do not take this approach. You are always better off not proliferating the use of literals like this one in your code. The second option encapsulating the constant inside a function is better.

I could, for example, create a tiny package as follows:. This code is almost identical to my first example, but I am calling a function rather than referencing a literal, and that makes all the difference. PARSE, is perhaps the optimal solution. Why should you even have to bother passing the database mode? Why not collect them all together in a single encapsulator package?

This point is driven home in the next section. It declares a number of exceptions and, as noted previously, the only way to handle those exceptions and know which exception was raised is to create an explicit exception handler, as in:.

Unfortunately, you cannot write this kind of code from Oracle Forms. What can you do? FOPEN directly, for example, and risk raising an exception you cannot interpret accurately, you might want to consider something like this:. I translate the package-specific exceptions to NNN exceptions.

I can check for that number within my client-side application and take appropriate action. With this wrapper approach, you can build a package that allows you to read and write a particular server-side file from Oracle Forms.

You would still need to build read, write, and close procedures, but the technique should be clear. Throughout this book, you will find documentation indicating whether a particular packaged function can be called from within an SQL statement, or whether a packaged procedure can be called by a function that, in turn, is called from within SQL. This section explains the significance of that capability.

If you are running a version of Oracle Server 7. If you are not running at least Oracle Server 7. You can also call a packaged function from within a SQL statement. Here, for example, is the code you would have to write to place totcomp inside a package and still call it from a query:.

The line in bold is the statement asserting that the empcomp. Here is how you would call this packaged function inside SQL:. The same rules apply for built-in packaged programs callable from SQL. And since Oracle did not pragmatize built-in packages prior to Oracle 7. If you try to call a packaged function in SQL that does not have such a pragma, you will receive this error:. You will also encounter this same error if you try to execute a function in SQL that, in turn, calls a packaged procedure that does not have a pragma.

Consequently, the following function exactly the same as that shown earlier, except for the addition of the procedure call will not be executable within SQL:. If that is the case, you are all set! Suppose that I am working on the large objects stored in files. Here are the meanings for each of those purity levels :. Writes No Database State.

Writes No Package State. In other words, does not change the values of any package data structures. Read No Database State. You can never update the database. Here is an example; in it, I display the length of each photograph stored in the archives for my family:. Table provides a complete list of all packaged programs that can be called directly or indirectly from within a SQL statement, the purity levels for each, and the Oracle versions in which these purity levels become available thus enabling you to call the programs from within SQL.

Separation of Specification and Body The specification of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package. Oracle stores the specification and body of a package separately in the database. Other schema objects that call or reference public program objects depend only on the package specification, not on the package body.

This distinction allows you to change the definition of a program object in the package body without causing Oracle to invalidate other schema objects that call or reference the program object. Oracle invalidates dependent schema objects only if you change the declaration of the program object in the package specification.

The package contains one stored function and two stored procedures. The function accepts all arguments for the fields in the employee table except for the employee number. A value for this field is supplied by a sequence. The function returns the sequence number generated by the call to this function. If no employee is found, then an exception is raised. The procedure accepts two arguments. For example:. This section lists each of the Oracle supplied server packages and indicates where they are described in more detail.

These packages run as the invoking user, rather than the package owner. Unless otherwise noted, the packages are callable through public synonyms of the same name. Caution: The procedures and functions provided in these packages and their external interfaces are reserved by Oracle and are subject to change in future releases. You must not modify Oracle supplied packages. Doing so could cause internal errors and security violations in the database. Requires the Distributed Option. Requires the Replication Option.

Oracle does not recommend that the subprograms be directly called. For this reason, these three supplied packages are not documented in this book. Subprograms in Supplemental Packages The packages listed in the remainder of this chapter are primarily documented in other Oracle books.

This section lists the subprograms provided with each of these packages. Please refer to the above table for the cross-reference to the full documentation. All Rights Reserved. Library Product Contents Index. Oracle8 i Supplied Packages Reference Release 8. Calendar see Note 2 below. Provides calendar maintenance functions. Oracle8i Time Series User's Guide. Provides support for the asynchronous notification of database events. Chapter 2. Lets you register an application name with the database for auditing or performance tracking purposes.

Chapter 3. Lets you add a message of a predefined object type onto a queue or to dequeue a message. Chapter 4. Lets you perform administrative functions on a queue or queue table for messages of a predefined object type.

Chapter 5. Chapter 6. Chapter 7. Provides the user interface to a replicated transactional deferred remote procedure call facility. Chapter 8. Permits querying the deferred remote procedure calls RPC queue data that is not exposed through views. Chapter 9. Provides the system administrator interface to a replicated transactional deferred remote procedure call facility.

Chapter Describes the arguments of a stored procedure with full name translation and security checking. Maintains the Trusted Database List, which is used to determine if a privileged database link from a particular server can be accepted. Lets you create and modify objects in the Heterogeneous Services dictionary. Lets you schedule administrative procedures that you want performed at periodic intervals; it is also the interface for the job queue.

Lets you request, convert and release locks through Oracle Lock Management services. Provides functions to initialize and run the log reader. Queries the dictionary tables of the current database, and creates a text based file containing their contents. Provides public APIs for offline instantiation of master groups.

Provides public APIs for offline instantiation of snapshots. Provides procedures for summaries, dimensions, and query rewrites. Accumulates information in a buffer so that it can be retrieved out later. Provides intra-partition parallelism for creating partition-wise local indexes.

Provides a DBMS pipe service which enables messages to be sent between sessions. Provides a built-in random number generator. Provides APIs used to detect and resolve data inconsistencies between two replicated sites.

Lets you create groups of snapshots that can be refreshed together to a transactionally consistent point in time. Provides data corruption repair procedures. Provides routines to administer and update the replication catalog and environment. Lets you create users with the privileges needed by the symmetric replication facility. Instantiates deployment templates. Controls the maintenance and definition of refresh group templates. Provides routines to generate shadow tables, triggers, and packages for table replication.

Maintains plans, consumer groups, and plan directives; it also provides semantics so that you may group together changes to the plan schema. Maintains privileges associated with resource consumer groups. Provides row level security administrative interface. Lets you keep objects in shared memory, so that they will not be aged out with the normal LRU mechanism. Lets you refresh snapshots that are not part of the same refresh group and purge logs.

Provides segment space information not available through standard SQL. Publisher resources Download Example Code. Table of contents Product information. Overview 1. Application Development Packages 2. Create a queue table that will support message grouping Step 2. Enqueue messages within the same transaction boundary Dequeuing messages when part of a group Working with Multiple Consumers Using the subscriber list Overriding with a recipient list 6.

Server Management Packages Distributed Database Packages ISBN: