Oracle operates on COM objects using PL and SQL
- 2020-05-30 21:14:10
- OfStack
The ORACLE tutorial you are looking at is :Oracle operates on COM objects using PL/SQL. PL/SQL is an extension of standard SQL by Oracle company, which is a special language for programming in Oracle database. It belongs to the 3rd generation procedural programming language. Starting with Oracle8, external C language procedures are provided to be called directly from PL/SQL, allowing developers to use PL/SQL for programming modules written in C. Starting with Oracle8i, the Java program was introduced.
In this article, I mainly introduce the basic principles and use conditions of external routines, introduce how to manipulate COM objects in Windows by referring to external routines, and do an example of manipulating Excel objects.
The operating environment of this paper is all based on Oracle9i and Windows2000. The installation directory of ORACLE (ORACLE_HOME) is D:\oracle\ora92, SID is ORADB, and the host is CHANET.
The need for
Extended PL/SQL language has been integrated with standard SQL, very suitable for design in efficiency and security Oracle database program, but for the application of some function, other programming language is better than PL/SQL, such as: use the operating system resources, C language used in computing and object reference system and the device is superior to the PL/SQL, and application in network is superior to the PL Java language/SQL.
If the PL/SQL language is not suitable for use in the application, it will be compiled in another language and then called by PL/SQL as an external routine.
In the version before Oracle8, PL/SQL and other languages only 1 communication is realized by DBMS_PIPE and DBMS_ALERT packages. Before use, it is necessary to establish an OCI interface or a monitor program prepared by the precompiler, which is quite complicated. The appearance of external routine only requires the establishment of a function mapping to the corresponding function of external routine in PL/SQL, just like the ordinary PL/SQL function USES 1, which simplifies the use process.
The basic principle of
When refer to external C language routines, Oracle listener will start extproc process, the process will be dynamically load the Shared library (called dynamic link library, under Windows is DLL files), process have played a role in a buffer, when external function called PL/SQL process, process the commands sent to the Shared library, then put the results back to PL/SQL process.
The process exists with the usage session of the Shared library (session) after it is called, and the extproc process is automatically shut down if the call is completed or the database user session is closed.
Figure 1-1 below is a description of calling external routines.
Using a configuration
Before invoking an external routine, the following Settings are required:
· configure listeners.
· configure Net component services.
Configure the listener, open the D:\oracle\ network\admin\ listener.ora file and modify the file parameters.
Two of these parameters are important for using external routines.
· (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = extproc1))
Set up external routines based on the IPC protocol
· (SID_DESC =(SID_NAME = PLSExtProc) (ORACLE_HOME = D:\oracle\ora92) (PROGRAM = extproc))
Record the relevant properties of the database, SID_NAME is PLSExtproc by default.
To configure the Net component service, open the D:\oracle\ ora \admin\ tnsnames.ora file and save the following contents in the file.
Description of important parameters:
· (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
· (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO)
Both Settings must correspond to the 1 in listener.ora.
Restart the listener and test if the service is available.
Stop listener: lsnrctl stop
Start listener: lsnrctl start
Test whether the service is available:
Parameter files used:
The TNSNAMES adapter was used to resolve the alias
Test whether the extproc process is normal:
4. Use COM object description
The COM object design provides three basic operations for developers: get a property value, set a property value (except for read-only properties), and call a method. The Oracle database provides an interface to manipulate COM objects on the Windows platform, which belongs to the C language external routine pattern.
The working principle is as follows:
Before using this feature, understand the following:
1) create COM object operation 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
In this article, I mainly introduce the basic principles and use conditions of external routines, introduce how to manipulate COM objects in Windows by referring to external routines, and do an example of manipulating Excel objects.
The operating environment of this paper is all based on Oracle9i and Windows2000. The installation directory of ORACLE (ORACLE_HOME) is D:\oracle\ora92, SID is ORADB, and the host is CHANET.
The need for
Extended PL/SQL language has been integrated with standard SQL, very suitable for design in efficiency and security Oracle database program, but for the application of some function, other programming language is better than PL/SQL, such as: use the operating system resources, C language used in computing and object reference system and the device is superior to the PL/SQL, and application in network is superior to the PL Java language/SQL.
If the PL/SQL language is not suitable for use in the application, it will be compiled in another language and then called by PL/SQL as an external routine.
In the version before Oracle8, PL/SQL and other languages only 1 communication is realized by DBMS_PIPE and DBMS_ALERT packages. Before use, it is necessary to establish an OCI interface or a monitor program prepared by the precompiler, which is quite complicated. The appearance of external routine only requires the establishment of a function mapping to the corresponding function of external routine in PL/SQL, just like the ordinary PL/SQL function USES 1, which simplifies the use process.
The basic principle of
When refer to external C language routines, Oracle listener will start extproc process, the process will be dynamically load the Shared library (called dynamic link library, under Windows is DLL files), process have played a role in a buffer, when external function called PL/SQL process, process the commands sent to the Shared library, then put the results back to PL/SQL process.
The process exists with the usage session of the Shared library (session) after it is called, and the extproc process is automatically shut down if the call is completed or the database user session is closed.
Figure 1-1 below is a description of calling external routines.
Using a configuration
Before invoking an external routine, the following Settings are required:
· configure listeners.
· configure Net component services.
Configure the listener, open the D:\oracle\ network\admin\ listener.ora file and modify the file parameters.
Two of these parameters are important for using external routines.
· (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = extproc1))
Set up external routines based on the IPC protocol
· (SID_DESC =(SID_NAME = PLSExtProc) (ORACLE_HOME = D:\oracle\ora92) (PROGRAM = extproc))
Record the relevant properties of the database, SID_NAME is PLSExtproc by default.
To configure the Net component service, open the D:\oracle\ ora \admin\ tnsnames.ora file and save the following contents in the file.
Description of important parameters:
· (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
· (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO)
Both Settings must correspond to the 1 in listener.ora.
Restart the listener and test if the service is available.
Stop listener: lsnrctl stop
Start listener: lsnrctl start
Test whether the service is available:
Parameter files used:
The TNSNAMES adapter was used to resolve the alias
Test whether the extproc process is normal:
4. Use COM object description
The COM object design provides three basic operations for developers: get a property value, set a property value (except for read-only properties), and call a method. The Oracle database provides an interface to manipulate COM objects on the Windows platform, which belongs to the C language external routine pattern.
The working principle is as follows:
Before using this feature, understand the following:
1) create COM object operation 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