0039-0041

Previous Table of Contents Next

Page 39

• You might want to execute different sets of SQL statements based on the results of a query, for example. The query, the subsequent SQL statements, and the conditional logic to execute them all can be incorporated into one PL/SQL block and submitted to the server in one network trip.
  Not only can PL/SQL be processed by Oracle8 Server, but it can be processed by SQL*Forms and Oracle Forms. PL/SQL is used extensively by these tools for client-based procedures and event trigger routines. In a client/server environment, PL/SQL is extremely flexible, because the language used by the client is interchangeable with that used by the server. Some extensions in the client language syntax allow for control of interface components , reference to form objects, and navigation. There are differences between submitting an anonymous PL/SQL block and calling a stored procedure.
  
• Stored procedures: Although Oracle7 supported client/server-based PL/SQL, Oracle8 provides the capability to store and manipulate objects in PL/SQL blocks in the form of stored procedures, functions, and database packages. With the ability to store more complex objects, we are not restricted to manipulating strings, dates, and numbers . Using stored procedures significantly increases the efficiency of a client/server system for several reasons:
  Calling a stored procedure from a client application generates minimal network traffic. Instead of the application submitting an entire PL/SQL program block from the client, all that is required is a single call to the procedure or function with an optional parameter list.
  Stored procedures provide a convenient and effective security mechanism. One of the characteristics of stored PL/SQL is that it always executes with the privileged domain of the procedure owner. This feature enables non-privileged users to have controlled access (through the procedure code) of privileged objects. This capability usually reduces the amount of grant administration the DBA must perform.
  Both the compiled and textual form of stored procedures are maintained in the database. Because the compiled form of the procedure is available and readily executable, the need to parse and compile the PL/SQL at runtime is alleviated.
  
• Database triggers: Database triggers resemble stored procedures, because they are database-resident PL/SQL blocks. The difference between the two is that triggers are fired automatically by the RDBMS kernel in response to a commit time event (such as an insert, update, or delete operation). You can use triggers to enforce complex integrity checking, perform complex auditing and security functions, and implement application alerts and monitors . Similar to stored procedures, database triggers greatly reduce the amount of code and processing necessary in the client portion of an application.

Page 40

• Oracle8's implementation of database triggers is slightly different from that of other vendors . Although most databases support statement-level triggers, Oracle8 also includes the functionality to fire triggers at the row level. Consider an UPDATE statement that affects values in a set of 100 rows. The kernel fires a statement-level trigger once ”for the UPDATE statement (before and/or after the statement executes). Row-level triggers, on the other hand, are fired by the kernel for each row the statement affects ”in this case, 100 times. Oracle8 enables you to use statement-level and row-level triggers with one another.
  
• Declarative integrity: When you define a table in Oracle8, you might include integrity constraints as part of your table definition. Constraints are enforced by the server whenever records are inserted, updated, or deleted. In addition to using referential integrity constraints that enforce primary and foreign key relationships, you can define your own constraints to control the value domains of individual columns within a table.
  Server-enforced integrity reduces some of the code required for validation by the client and also increases the robustness of the business model defined within the database. With constraints, you often can improve performance and provide the flexibility to support multiple front-end interfaces.
  
• User-defined functions: You'll also find PL/SQL blocks in user-defined functions. User-defined functions are similar to stored procedures and also reduce the amount of application code in the client portion of an application. Not only can you call these functions from PL/SQL, but you can use them to extend the set of standard Oracle SQL functions. You can place user -defined functions in SQL statements just as you would any other Oracle SQL function.
  Designing your Oracle application to use these server-based features not only improves the performance of a client/server system but also makes the task of developing and deploying an application easier.

Networking Products

If you're developing an Oracle-based client/server system, you'll probably use Oracle's database networking software to implement connectivity between the nodes in the network. Oracle offers a variety of products and tools to simplify the task of connecting client applications to database servers in a network:

• SQL*Net: SQL*Net is database messaging software that provides optimal, reliable database messaging over every popular network protocol. SQL*Net provides server-location transparency to any node within an application network and uses components that reside on both the client and server sides of an application.
  In addition to providing connectivity between workstations and servers in a client/server environment, servers use SQL*Net to communicate with other servers for

Page 41

• distributed transactions, remote procedure calls, and table replication. Servers reference other servers by using database links that define the names of remote databases, the network nodes where the databases are serviced, and the network protocol used to access the remote nodes. Database links simplify distributed processing by providing transparent access to remote objects, such as tables and procedures, and enabling an application to reference those objects just as if they were resident in the application's local database.
  
• Oracle Names: You can make available database link and network node information to all the nodes in a network by using the Oracle Names common global dictionary. This feature is particularly useful for large application networks that encompass multiple locations to simplify the administration of database link and network information.
• Multiprotocol interchange: Whereas Version 1 of SQL*Net supports connectivity between nodes in a single network protocol, Version 2 of SQL*Net enables database communication between nodes in different network communities running different network protocols. The multiprotocol interchange (MPI) provides a communications bridge over heterogeneous protocols by translating SQL*Net messages from one protocol to another. A client workstation in a Token-Ring LAN, for example, can transparently access a server in a DECnet or TCP/IP network and insulate the application from the complexities of the underlying network infrastructure. In addition to providing multiprotocol communications, the MPI also provides cost-based message-routing functions and uses alternate routes in the event that least-cost paths of a network are unavailable.
• Oracle Network Manager: The complex task of configuring and managing a distributed database network topology is made easier with the Network Manager, a GUI-based administrative interface for SQL*Net. The Network Manager is used not only to manage the Oracle Names dictionary but also to generate configuration files for client and server-side SQL*Net components and to define connection routes for MPI nodes.

Client/Server Development Tools

In addition to its server and networking products, Oracle includes a variety of client-side GUI offerings that complete its integrated client/server architecture. These product suites include full-featured computer-assisted software engineering (CASE) tools, object-oriented development environments, and runtime components capable of operating with the Oracle8 Server and other SQL databases. Here is a list of products that aid in both client/server development or even with the newer intranet development efforts:

• Designer/2000: For developing sophisticated Oracle client/server applications, the Designer/2000 CASE environment provides a comprehensive repository and powerful tool set that enables you to systematically analyze, model, design, and generate both

Previous Table of Contents Next