External Procedures Overview

External procedures are functions written in a third-generation language (C, for example) and callable from within PL/SQL or SQL as if they were a PL/SQL routine or function.

External procedures let you take advantage of strengths and capabilities of a third-generation programming language in a PL/SQL environment.

Note:

Oracle Database also provides a special purpose interface, the call specification, that lets you call external procedures from other languages, as long as they are callable by C.

The main advantages of external procedures are:

• Performance, because some tasks are performed more efficiently in a third-generation language than in PL/SQL, which is better suited for SQL transaction processing

• Code re-usability, because dynamic link libraries (DLLs) can be called directly from PL/SQL programs on the server or in client tools

You can use external procedures to perform specific processes:

• Solving scientific and engineering problems

• Analyzing data

• Controlling real-time devices and processes

Note:

Special security precautions are warranted when configuring a listener to handle external procedures.

To create and use an external procedure, perform the following steps:

1. Writing an External Procedure

2. Building a DLL

3. Registering an External Procedure

4. Restricting Library-Related Privileges to Trusted Users Only

5. Executing an External Procedure

Note:

• You must have a C compiler and linker installed on your system to build DLLs.

• You can combine the instructions described in the fourth and fifth tasks into one SQL script that automates the task of registering and executing your external procedure. See ORACLE_HOME\rdbms\extproc\extern.sql for an example of a SQL script that combines these steps.