The Oracle tutorial you are looking at is that Oracle uses Pl/sql to manipulate COM objects. The Pl/sql is extended by Oracle company to standard SQL, dedicated to programming in Oracle databases, and is a third-generation process programming language. Starting from Oracle8, it provides the process of calling external C language directly from Pl/sql, allowing developers to use PL/SQL for programming modules in C language. Starting with Oracle8i, Java programs are introduced.
In this article, we introduce the basic principles of external routines and the conditions of use, how to manipulate COM objects in Windows by referencing external routines, and do an example of manipulating Excel objects.
The operating environment of this article is all based on oracle9i and Windows2000. Where ORACLE's installation directory (Oracle_home) is d:\oracle\ora92,sid as ORADB and host name is Chanet.
Necessity
The extended Pl/sql language has been integrated with standard SQL and is well suited to design Oracle database programs for efficiency and security, but other programming languages are better suited to some of the features of the application than Pl/sql, such as: using Operating system resources, C language is superior to pl/sql in computing and referencing system objects and using devices, while the Java language is better than pl/sql in network applications.
If the application is not suitable for the use of Pl/sql language, it is necessary to use other languages for the preparation, and then by the Pl/sql as an external routine to invoke.
Prior to the Oracle8 version, the only communication between Pl/sql and other languages was achieved with the help of Dbms_pipe and Dbms_alert packages, and it was necessary to establish a OCI interface or a preprocessor-compiled monitor, which was more complex to use. The appearance of an external routine, simply by creating a function in Pl/sql that maps to an external routine, simplifies the use of the process just as the normal pl/sql function uses.
Basic principle
When referencing an external C language routine, the Oracle listener initiates the EXTPROC process, which dynamically loads the shared library (called a dynamic-link library, or DLL file, under Windows), and the process plays a buffering role when the Pl/sql procedure calls an external function. The process sends the command to the shared library, and then returns the result to the pl/sql process.
When a process is invoked, it exists with the shared library's use session (sessions), and the Extproc process shuts down automatically if the call completes or the database user session is closed.
Figure 1-1 Below is a description of the calling external routine.
Using configuration
Before calling an external routine, you must set the following:
• Configure the listener.
• Configure NET Component Services.
Configure the Listener, open the D:\oracle\ora92\network\admin\listener.ora file, and modify the file parameters.
There are two parts of these parameters that are important for using external routines.
· (address_list = (address = (PROTOCOL = IPC) (KEY = Extproc1))
Setting up an external routine based on the IPC protocol
· (Sid_desc = (Sid_name = plsextproc) (Oracle_home = D:\oracle\ora92) (program = Extproc))
Records the related properties 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 in the file.
Important Parameter Description:
· (address = (PROTOCOL = IPC) (KEY = EXTPROC1))
· (Connect_data = (SID = plsextproc) (presentation = RO)
The two settings must be consistent with the Listener.ora.
Reboot the listener and test that the service is available.
Stop listener: Lsnrctl stop
Start Listener: Lsnrctl start
Test whether the service is available:
Parameter file used:
Used the TNSNAMES adapter to resolve the alias
To test whether the extproc process is normal:
4, using COM object description
COM object Design provides three basic operations for developers to use: Get property values, set property values (except for read-only properties), and Invoke methods. The Oracle database provides an interface for manipulating COM objects under Windows platform, which belongs to the C language external routine mode.
The working principle of the following diagram:
Before using this feature, you need to understand the following:
1 Create a COM object action function.
2) Configure the listener.
In the Listener.ora file, add the following and restart the listener.
3 Pl/sql data type and corresponding COM object type
Table 1-1 Comparison of data types
4) function description.
Table 1-2 Function Description
