Please update and finalize this old, open question. Please:
1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.
EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.
Thanks,
Moondancer - EE Moderator
P.S. Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
Main Topics
Browse All Topics
by: ser6398Posted on 2001-05-14 at 11:35:55ID: 6078834
go to: technet.oracle.com
If you don't have an account yet, get one (it is free to join).
Search for: External Procedures
What follows are some cuts from technet.oracle.com about external procedures:
External Procedures Overview
External procedures are functions written in a third-generation language (3GL), such as C, and callable from within PL/SQL or SQL as if they were a PL/SQL procedure or function. External procedures let you take advantage of the strengths and capabilities of a 3GL programming language in a PL/SQL environment.
The main advantages of external procedures consist of the following:
Performance, because some tasks are performed more efficiently in a 3GL 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 such as SQL*Forms
You can use external procedures to perform specific processes, such as the following:
Solving scientific and engineering problems
Analyzing data
Controlling real-time devices and processes
Creating and using an external procedure would involve the following sequential tasks:
Installing and Configuring
Writing an External Procedure
Building a DLL
Registering an External Procedure
Executing an External Procedure
Enabling Multi-Threaded Callout Support
--------------------------
Attention:
You can combine the instructions described in the fourth and fifth tasks into one SQL script that automates the process of registering and executing your external procedure. For an example of a SQL script that combines these steps, see ORACLE_BASE\ORACLE_HOME\RD
--------------------------
Installing and Configuring
This section describes the installation and configuration of Oracle8i Enterprise Edition and Net8.
Installing Oracle8i Enterprise Edition
Follow the procedures in Oracle8i Enterprise Edition Installation for Windows NT to install these products on your Windows NT server:
Oracle8i Enterprise Edition. Contains PL/SQL, from which external procedures are called, and the PL/SQL external procedure program (EXTPROC), which executes external procedures.
Net8 Client, Net8 Server, and Oracle Protocol support
--------------------------
Note:
You must also have a C compiler and linker installed on your system to build DLLs.
--------------------------
Configuring Net8
If you install Net8 Server from your Oracle8i Enterprise Edition CD-ROM, your server network files are automatically configured to use external procedures.
When PL/SQL calls an external procedure, the Net8 listener launches a session-specific process called EXTPROC. Through Net8, PL/SQL passes the following information to EXTPROC:
DLL name
External procedure name
Parameters (if necessary)
EXTPROC then loads the DLL and invokes the external procedure.
If you copy your Oracle7 server network files into your Oracle8i network files directory, you must manually configure the following files for the external procedure behavior described previously to occur:
ORACLE_BASE\ORACLE_HOME\NE
ORACLE_BASE\ORACLE_HOME\NE
See Chapter 6 of the Oracle Net8 Administrator's Guide for instructions.
--------------------------
Note:
The SQLNET.ORA file requires no changes. By default, the values for the parameters NAMES.DEFAULT_DOMAIN and NAME.DEFAULT_ZONE are set to WORLD. These values match with the .WORLD extension on the end of EXTPROC_CONNECTION_DATA in the TNSNAMES.ORA file.
--------------------------
Writing an External Procedure
Using a 3GL programming language, such as C, you can write functions to be built into DLLs and invoked by EXTPROC. The following is a simple Microsoft Visual C++ example of an external procedure:
--------------------------
Note:
Since external procedures are built into DLLs, they must be explicitly exported. In this example, the dllexport storage class modifier exports the function find_max from a dynamic link library.
--------------------------
#include <windows.h>
#define NullValue -1
/*
This function simply returns the larger of x and y.
*/
long __declspec(dllexport) find_max(long x,
short x_indicator,
long y,
short y_indicator,
short *ret_indicator)
{
/* It can be tricky to debug DLL's that are being called by a process
that is spawned only when needed, as in this case.
Therefore try using the DebugBreak(); command.
This will start your debugger. Uncomment the line with DebugBreak();
in it and you can step right into your code.
*/
/* DebugBreak(); */
/* first check to see if you have any nulls */
/* Just return a null if either x or y is null */
if ( x_indicator==NullValue || y_indicator==NullValue) {
*ret_indicator = NullValue;
return(0);
} else {
*ret_indicator = 0; /* Signify that return value is not null */
if (x >= y) return x;
else return y;
}
}
Building a DLL
After writing your external procedure(s) in a 3GL programming language, use the appropriate compiler and linker to build a DLL, making sure to export the external procedures, as noted above. See your compiler and linker documentation for instructions on building a DLL and exporting its functions.
After building the DLL, you can move it to any directory on your system. For the example above, you can build the external procedure find_max into a DLL called EXTERN.DLL. To build the above example, go to ORACLE_BASE\ORACLE_HOME\RD
Registering an External Procedure
Once you have built a DLL containing your external procedure(s), you must register your external procedure(s) with the Oracle database:
Create a PL/SQL library to map to the DLL.
Start Server Manager:
C:\> SVRMGRL
Connect to the database with the appropriate user name and password.
Create the PL/SQL library using the CREATE LIBRARY command:
SVRMGR> CREATE LIBRARY externProcedures AS 'C:\ORACLE\ORA81\RDBMS\EXT
Where... Represents the...
externProcedures
Alias library (essentially a schema object in the database)
C:\ORACLE\ORA81\RDBMS\EXTP
Path to the Windows NT operating system DLL EXTERN.DLL. This example uses C:\ORACLE as your Oracle base and \ORA81 as your Oracle home.
--------------------------
Note:
The DBA must grant EXECUTE privileges on the PL/SQL library to users who want to call the library's external procedures from PL/SQL or SQL.
--------------------------
Create a PL/SQL program unit specification.
Do this by writing a PL/SQL subprogram that uses the EXTERNAL clause instead of declarations and a BEGIN...END block. The EXTERNAL clause is the interface between PL/SQL and the external procedure. The EXTERNAL clause identifies the following information about the external procedure:
Name
DLL alias
Programming language in which it was written
Calling standard (defaults to C if omitted)
Example 13-1
Executing an External Procedure
To execute an external procedure, you must call the PL/SQL program unit (that is, the alias for the external function) that registered the external procedure. These calls can appear in any of the following:
Anonymous blocks
Stand-alone and packaged subprograms
Methods of an object type
Database triggers
SQL statements (calls to packaged functions only)
In "Registering an External Procedure", the PL/SQL function PLS_MAX registered the external procedure find_max. Follow the procedures below to execute find_max:
Call the PL/SQL function PLS_MAX from a PL/SQL procedure named UseIt:
CREATE OR REPLACE PROCEDURE UseIt AS
a integer;
b integer;
c integer;
BEGIN
a := 1;
b := 2;
c := PLS_MAX(a,b);
dbms_output.put_line('The maximum of '||a||' and '||b||' is '||c);
END;
Run the procedure:
SVRMGR> EXECUTE UseIt;
Enabling Multi-Threaded Callout Support
You can increase the speed of external procedure invocations when making calls to external programs from PL/SQL-stored procedures or PL/SQL methods of object types in the database by taking advantage of multiple, lightweight threads instead of a single, dedicated process-per-user session.
--------------------------
WARNING:
Do not enable multi-threaded callout support if your C code is not thread-enabled. For example, do not use global variables in DLLs. Ensure also that there is synchronized access to variables in DLLs. See your Microsoft programming documentation for information on thread-enabling your code.
--------------------------
To enable multi-threaded callouts:
Open the Windows NT Control Panel.
Double-click Services.
The Services window appears.
Select OracleHOME_NAMEExtprocAgen
Click Start.
Exit from the Windows NT Control Panel.
This enables you to use multiple, lightweight threads when making calls to EXTPROC from PL/SQL programs.
Additional Documentation
See the following documents for more information on external procedures:
Oracle8i Administrator's Guide
PL/SQL User's Guide and Reference
The following files in ORACLE_BASE\ORACLE_HOME\RD
EXTERN.C (code example shown in "Writing an External Procedure")
MAKE.BAT (batch file that builds the dynamic link library)
EXTERN.SQL (automates the instructions described in "Registering an External Procedure" and "Executing an External Procedure")
README.DOC (explains how to run the sample and provides debugging advice)