1
Introducing Oracle COM Automation Feature

This chapter describes the Oracle COM Automation feature Software Development Kit (SDK) and provides an overview of the product. Read this chapter before installing or using Oracle COM Automation feature.

Specific topics discussed are:

• Introducing Oracle COM Automation Feature
• Benefits of Oracle COM Automation Feature
• Oracle COM Automation Feature Architecture

Introducing Oracle COM Automation Feature

Component software has recently been promoted as the next evolution in software development. The growth of object-oriented programming and distributed objects are proof of this industry-wide trend. The scripting of components enables you to reuse code that is pre-built and pre-tested. The reuse of code fulfills one of the key objectives of software development: shorter development cycles and reduced time to market. COM is the ubiquitous technology to promote componentization and reuse of software for Windows-based systems.

Oracle COM Automation feature has been created to enable you to use COM-based components to customize and enhance the functionality of the Oracle8i database database on Windows NT.

What Is Oracle COM Automation Feature?

Oracle COM Automation feature enables PL/SQL developers to programmatically manipulate COM objects through the OLE Automation interface (IDispatch).

The feature provides a PL/SQL package and exposes a set of application programming interfaces (APIs) to instantiate COM objects, get and set their properties, and invoke their methods. PL/SQL developers can call these APIs from PL/SQL subprograms, stored procedures, stored functions, or triggers to manipulate COM objects. There are no restrictions as to where the COM objects can reside. They can be either local to the database server or be accessed remotely through the Distributed Component Object Model (DCOM).

OLE Automation is the most common and basic mechanism for third-generation language (3GL) and fourth-generation language (4GL) programs to manipulate COM objects. Most COM objects support OLE Automation and the major 4GL programming environments, such as Powersoft PowerBuilder and Microsoft Visual Basic. OLE Automation is the mechanism for scripting COM objects.

Benefits of Oracle COM Automation Feature

Oracle COM Automation feature is a powerful and enabling infrastructure technology for Oracle developers on Windows NT. The feature provides four compelling benefits for developers who are creating and deploying Oracle solutions on Windows NT:

• Ease of Development

Oracle COM Automation feature exposes a simple set of APIs to manipulate COM objects. If you are familiar with COM and Microsoft Visual Basic, you will have no problems incorporating these APIs into your PL/SQL subprograms.
 
• Reusability

Oracle COM Automation feature enables you to leverage pre-built COM components that have been developed in-house or by third-party independent software vendors (ISVs). In addition, there are already thousands of pre-existing COM components from which you can choose. The COM component market is expanding rapidly and already offers solutions to many of the most common problems that enterprises need to solve.
 
• Flexibility and Extensibility

You can use Oracle COM Automation feature to customize and enhance the functionality of the Oracle database. Through the use of COM components, the Oracle database can be customized to exchange data between productivity applications, such as Microsoft Word, Microsoft Excel, and Microsoft PowerPoint, generate reports using Crystal Reports, and send and receive e-mail to Lotus Notes, Microsoft Exchange, or Oracle InterOffice mail servers. The possibilities for customization and extensibility of the Oracle database are limitless.
 
• Enhanced Integration

Oracle COM Automation feature enables you to deploy Oracle in a Microsoft-centric environment and be assured that Oracle can integrate fully with and capitalize on the services that are exposed by Windows NT, Microsoft BackOffice applications, and Microsoft Office applications.

Oracle COM Automation Feature Architecture

Oracle COM Automation feature provides a PL/SQL package of APIs for manipulating COM objects. These APIs are implemented as external procedures in a dynamic linked library (DLL).

Oracle8 release 8.0.3 was the first release of the database to support external procedures. External procedures enable developers to call 3GL functions from server-based object type methods and stored procedures. External procedures are invoked exactly like standard PL/SQL stored procedures. However, unlike standard PL/SQL procedures where the body of the procedure is written in PL/SQL and stored in the database, external procedures are C functions that reside within a DLL. Therefore, you can invoke Oracle COM Automation feature APIs in the same manner as if you are calling a standard PL/SQL stored procedure or function.

This figure illustrates the interaction between an Oracle database, Oracle COM Automation feature, and external procedures:

Invoking OLE Automation External Procedure APIs

The Oracle database invokes any of the OLE Automation external procedure APIs as follows:

1. The PL/SQL interpreter looks up the pathname to the Oracle COM Automation feature DLL (COM81.DLL).

 
2. The PL/SQL interpreter sends a message using Net8 to the listener to start EXTPROC.EXE, if it has not already been started for the current user session.

 
3. The PL/SQL interpreter passes the procedure name, parameters, and the pathname of the DLL to EXTPROC.EXE.

 
4. EXTPROC.EXE loads the DLL and executes the external procedure. Each of the OLE Automation external procedure APIs in turn call Win32 APIs that instantiate a COM object, set or get properties of a COM object, or invoke a method of a COM object.

 
5. EXTPROC.EXE acts as an intermediary and handles any interaction between Oracle COM Automation feature and Oracle Server.

This figure shows an Oracle8i database invoking OLE Automation external procedure APIs.

Architectural Impact on Availability and Performance Issues

The dependence on external procedures by Oracle COM Automation feature has implications for the availability of the Oracle database and the performance of Oracle COM Automation feature.

Oracle Database Availability

You do not jeopardize the availability of the Oracle database by using Oracle COM Automation feature and custom or third-party COM objects in a production environment. Oracle COM Automation feature operates outside of the Oracle kernel's address space. This safeguards the Oracle kernel from COM objects that crash unexpectedly.

Oracle COM Automation Feature Performance

Currently, there are two architectural models in which an external procedure can be invoked. The model used directly affects the performance of Oracle COM Automation feature. In this release, you can choose Architectural Model 1 or Architectural Model 2.

Architectural Model	Description
1	A separate process, EXTPROC, is responsible for loading external DLLs and handling the interaction between the Oracle kernel and Oracle COM Automation feature. A copy of EXTPROC is spawned by the Oracle kernel when a user makes the first OLE Automation external procedure API call. EXTPROC remains in memory until the user's session terminates. Therefore, there will be a separate EXTPROC process for each user session that makes an external procedure call. The first external procedure call incurs substantial overhead because Oracle needs to spawn a new EXTPROC process. Process creation on Windows NT is much slower than thread creation. However, subsequent external procedure calls in the same session will execute rapidly. If you use this model, you should consolidate as many external procedure calls as possible in one session to achieve optimum performance.
2	EXTPROC operates as a multi-threaded NT service. Instead of spawning an EXTPROC process for each user session, Oracle spawns a thread in the EXTPROC service that can handle all of the external procedures' calls for a user's session. This architectural model greatly reduces the initial overhead of the first external procedure call.