Using PL to operate COM objects in Oracle databases

In the previous article, we mainly talked about how to use PL/SQL to operate COM objects in Oracle databases, in the following article, we will introduce the specific content of the description of using COM objects in Oracle databases. The following is a detailed description of the article.

Description of using COM Object

The COM Object design provides three basic operations for developers to use: Get the attribute value, except for setting the attribute value 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. Before using this function, you must understand the following:

1) create a COM Object operation function using PL/SQL in the Oracle database.

 

  
SQL＞ CONNECT SYSTEM/chanet@oradb   
  
SQL＞ CREATE USER chanet identified by chanet;   
  
SQL＞ GRANT CREATE LIBRARY TO chanet;   
  
SQL＞ CONNECT chanet/chanet@oradb;   
  
SQL＞ @D:oracleora92comcomwrap.sql; 
 

2) configure the listener.

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

 

  
STARTUP_WAIT_TIME_LISTENER = 0   
  
CONNECT_TIMEOUT_LISTENER = 10   
  
TRACE_LEVEL_LISTENER = off   
  
PASSWORDS_LINTENER = (oracle) 
 

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

Table 1-1 data type comparison

PL/SQL data type COM API data type

 

  
VARCHAR2 BSTR   
  
BOOLEAN BOOL   
  
BINARY_INTEGER BYTE，INT，LONG   
  
DOUBLE PRECISION DOUBLE，FLOAT，CURRENCY   
  
DATE DATE 
 

4) function description.

Table 1-2 Functions

Name Function Description

CreateObject

DestroyObject

GetLastError

GetProperty

SetProperty

InitArg is the Invoke function initialization parameter.

InitOutArg is the GetArg initialization output parameter.

GetArg get output parameters

SetArg: set parameters for the Invoke Function

Invoke calls a function or process that uses PL/SQL to operate a COM object in the Oracle database

5) Excel operation example

This section describes how to operate on a COM Object. Take an Excel object as an example. You can use PL/SQL statements to output table records in the database to an Excel file. You can use the Object Browser to view the properties and methods provided by the Excel object. For example, in the Excel menu, choose tools> macros> open the Visual Basic Editor. In the editor, choose View> Object Browser ).

Excel objects are generally used to operate on cells. For example, the Code for setting the Font Size of the first cell is Range ("A1"). Font. Size = 20 ). The operation steps of the corresponding COM external routine are as follows:

1. Obtain the program handle;

2. Obtain the workbook handle;

3. Obtain the worksheet handle;

4. Obtain the Range handle;

5. Obtain the Font class handle;

6. Set the Size attribute.

The above content is an introduction to the use of PL/SQL to operate COM objects in Oracle databases. I hope you will find some gains.