Oracle uses PL/SQL to operate COM objects

The ORACLE tutorial is: Oracle uses PL/SQL to operate COM objects. PL/SQL is a specialized language for programming in Oracle databases. It is a third-generation procedural programming language. Starting from Oracle8, the external C language calling process is provided directly from PL/SQL, allowing developers to use PL/SQL for C programming modules. Java programs have been introduced since Oracle8i.

This article describes the basic principles and conditions of external routines, describes how to reference external routines to operate COM objects in Windows, and provides an example of Excel objects.

All the Running Environments in this article are built on Oracle9i and Windows2000. The ORACLE Installation Directory (ORACLE_HOME) is D: \ oracle \ ora92, SID is ORADB, and host name is CHANET.

Necessity

The extended PL/SQL language has integrated standard SQL, which is very suitable for designing Oracle database programs in terms of efficiency and security. However, for some functions of applications, other programming languages are more suitable than PL/SQL. For example, the C language is better than PL/SQL in computing and referencing system objects and devices, java has better network applications than PL/SQL.

If PL/SQL is not applicable to applications, you must compile it in other languages and use PL/SQL as an external routine.

In versions earlier than Oracle8, the only communication between PL/SQL and other languages is implemented by using the DBMS_PIPE and DBMS_ALERT packages. before using it, you must establish an OCI interface or pre-compiled monitoring program, it is complicated to use. When an external routine appears, you only need to create a function mapped to the external routine in PL/SQL, just like a common PL/SQL function, which simplifies the use process.

Basic Principles

When an external C language routine is referenced, the Oracle listener starts the extproc process, which dynamically loads the Shared Library (called the dynamic link library in Windows, that is, the DLL file ), the process acts as a buffer. When the PL/SQL process calls an external function, the process sends the command to the shared database and then returns the result to the PL/SQL process.

After a process is called, it exists with the session used by the shared database. If the call is completed or the database user session is closed, the extproc process is automatically disabled.

For example, 1-1 is the description of calling an external routine.


Use Configuration

Before calling an external routine, you must make the following settings:

· Configure the listener.

· Configure the Net Component Service.

Configure the listener, open the D: \ oracle \ ora92 \ network \ admin \ listener. ora file, and modify the file parameters.


Two parameters are important for using external routines.

· (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = extproc1 ))

Set IPC-based external routines

· (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D: \ oracle \ ora92) (PROGRAM = extproc ))

Record the related attributes of the database. SID_NAME is PLSExtproc by default.

Configure the Net Component Service, open the D: \ oracle \ ora92 \ network \ admin \ tnsnames. ora file, and save the following content in the file.


Important parameter description:

· (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1 ))

· (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO)

The two settings must be consistent with those in listener. ora.

Restart the listener and test whether the service is available.

Stop listener: lsnrctl stop

Start listener: lsnrctl start

Test whether the service is available:


Used parameter files:


Alias resolved using TNSNAMES Adapter


Test whether the extproc process is normal:


4. Instructions on using COM objects

The COM Object design provides three basic operations for developers to use: Get the attribute value, set the attribute value (except for the read-only attribute), and call the method. Oracle Database provides interfaces for operating COM objects on the Windows platform, which belongs to the C language external routine mode.

The working principle is as follows:


Before using this function, you must understand the following:

1) create a COM Object operation function.


2) configure the listener.

In the listener. ora file, add the following content and restart the listener.


3) PL/SQL data types and corresponding COM Object Types

Table 1-1 data type comparison


4) function description.

Table 1-2 Functions