Release 8.0
A54653_01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Server Distributed Database Systems Release 8.0 A54653_01 |
|
This chapter describes the special considerations that are necessary if you are designing an application to run in a distributed database system. Oracle8 Server Concepts describes how Oracle eliminates much of the need to design applications specifically to work in a distributed environment.
The topics covered include:
The Oracle8 Server Application Developer's Guide provides a complete discussion of implementing Oracle8 applications. This chapter provides information specific to development for an Oracle8 distributed database environment.
In a distributed database environment, you should coordinate with the database administrator to determine the best location for the data. Some issues to consider are:
When a global object name is referenced in a SQL statement or remote procedure call, database links establish a connection to a session in the remote database on behalf of the local user. The remote connection and session are only created if the connection has not already been established previously for the local user session.
The connections and sessions established to remote databases persist for the duration of the local user's session, unless the application (or user) explicitly terminates them. Terminating remote connections established using database links is useful for disconnecting high cost connections (such as long distance phone connections) that are no longer required by the application.
The application developer or user can close (terminate) a remote connection and session using the ALTER SESSION command with the CLOSE DATABASE LINK parameter. For example, assume you issue the following query:
SELECT * FROM emp@sales;
COMMIT;
The following statement terminates the session in the remote database pointed to by the SALES database link:
ALTER SESSION CLOSE DATABASE LINK sales;
To close a database link connection in your user session, you must have the ALTER SESSION system privilege.
Note: Before closing a database link, you must first close all cursors that use the link and then end your current transaction if it uses the link.
Oracle does not permit declarative referential integrity constraints to be defined across nodes of a distributed system (that is, a declarative referential integrity constraint on one table cannot specify a foreign key that references a primary or unique key of a remote table). However, parent/child table relationships across nodes can be maintained using triggers. For more information about triggers to enforce referential integrity, see Oracle8 Server Concepts.
Note: If you decide to define referential integrity across the nodes of a distributed database using triggers, be aware that network failures can limit the accessibility of not only the parent table, but also the child table.
For example, assume that the child table is in the SALES database and the parent table is in the HQ database. If the network connection between the two databases fails, some DML statements against the child table (those that insert rows into the child table or update a foreign key value in the child table) cannot proceed because the referential integrity triggers must have access to the parent table in the HQ database.
A distributed query is decomposed by the local Oracle into a corresponding number of remote queries, which are sent to the remote nodes for execution. The remote nodes execute the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.
If a portion of a distributed statement fails, for example, due to an integrity constraint violation, Oracle returns error number ORA-02055. Subsequent statements or procedure calls return error number ORA-02067 until a rollback or rollback to savepoint is issued.
You should design your application to check for any returned error messages that indicate that a portion of the distributed update has failed. If you detect a failure, you should rollback the entire transaction (or rollback to a savepoint) before allowing the application to proceed.
For information about tuning distributed queries, see Oracle8 Server Tuning.
When a procedure is executed locally or at a remote location, four types of exceptions can occur:
When using local procedures, all of these messages can be trapped by writing an exception handler, such as shown in the following example:
BEGIN
...
EXCEPTION
WHEN ZERO_DIVIDE THEN
/* ...handle the exception */
END;
Notice that the WHEN clause requires an exception name. If the exception that is raised does not have a name, such as those generated with RAISE_APPLICATION_ERROR, one can be assigned using PRAGMA_EXCEPTION_INIT, as shown in the following example:
DECLARE
null_salary EXCEPTION;
PRAGMA EXCEPTION_INIT(null_salary, -20101);
BEGIN
...
RAISE_APPLICATION_ERROR(-20101, 'salary is missing');
...
EXCEPTION
WHEN null_salary THEN
...
END;
When calling a remote procedure, exceptions can be handled by an exception handler in the local procedure. The remote procedure must return an error number to the local, calling procedure, which then handles the exception as shown in the previous example. Note thatPL/SQL user-defined exceptions always return ORA-06510 to the local procedure. Therefore, it is not possible to distinguish between two different user-defined exceptions based on the error number. All other remote exceptions can be handled in the same manner as local exceptions.
