What are the specific languages for Oracle database programming?

PL/SQL are extensions of SQL by Oracle. They are specialized languages for programming in Oracle databases, it is a process-related programming language of the 3rd generation. From Oracle8, external C language calls are provided directly from PL/SQL.

Allows developers to use PL/SQL for program modules compiled in C language. 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 database installation directory ORACLE_HOME is D: oracleora92, 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 OracleOracle listener starts the extproc process, which dynamically loads the shared library. In Windows, the process is called a dynamic link library, that is, a 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 use of session sessions in the shared library. If the call is completed or the user session in the database is closed, the extproc process is automatically disabled.

Use Configuration

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

· Configure listeners

· Configure the Net Component Service.

Configure the listener, open the D: oracleora92networkadminlistener. ora file, and modify the file parameters.

 
 

  
  
LISTENER =   
  
  
(DESCRIPTION_LIST =   
  
  
(DESCRIPTION =   
  
  
(ADDRESS_LIST =   
  
  
(ADDRESS = (PROTOCOL = TCP)(HOST = CHANET)(PORT = 1521))   
  
  
)   
  
  
(ADDRESS_LIST =  
  
  
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc1))   
  
  
)   
  
  
)   
  
  
)   
  
  
SID_LIST_LISTENER =   
  
  
(SID_LIST =   
  
  
(SID_DESC =   
  
  
(SID_NAME = PLSExtProc)  
  
  
(ORACLE_HOME = D:oracleora92)   
  
  
(PROGRAM = extproc)   
  
  
)   
  
  
(SID_DESC =   
  
  
(GLOBAL_DBNAME = ORADB)   
  
  
(ORACLE_HOME = D:oracleora92)   
  
  
(SID_NAME = ORADB)   
  
  
)   
  
  
)  
 
 

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:oracleora92) (PROGRAM = extproc) ) 
 
 

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

Configure the Net Component Service, open the D: oracleora92networkadmintnsnames. ora file, and save the following content in the file.

 
 

  
  
EXTPROC_CONNECTION_DATA =   
  
  
(DESCRIPTION =   
  
  
(ADDRESS_LIST =   
  
  
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))   
  
  
)   
  
  
(CONNECT_DATA =   
  
  
(SID = PLSExtProc)   
  
  
(PRESENTATION = RO)   
  
  
)   
  
  
)  
 
 

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:

 
 

  
  
C:> tnsping EXTPROC_CONNECTION_DATA
  
  
TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0-Production on month 4-2
  
  
005 16:57:00
  
  
Copyright (c) 1997 Oracle Corporation. All rights reserved.
 
 

Used parameter file: D: oracleora92networkadminsqlnet. ora

Alias resolved using TNSNAMES Adapter

 
 

  
  
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)   
  
  
(KEY = EXTPROC1))) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO)))  
 
 

OK30 ms)

Test whether the extproc process is normal:

 
 

  
  
D: oracleora92bin> extproc
  
  
Oracle Corporation-Thursday 2005 17:37:18. 968
  
  
Heterogeneous Agent Release 9.2.0.1.0-Production
 
 

4. Instructions on using COM objects

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.

 
 

  
  
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 of a COM Object

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 have some gains.

Article by: http://database.csdn.net/c_oracle/tag/2