Writing Oracle External Procedures with ODAC

External procedure is a procedure stored in a dynamic link library (DLL), or libunit in the case of a Java class method. Different programming languages can be used for external procedures creation - C, C++, Object Pascal, Java. External procedure can be called directly from PL/SQL and SQL. You can use ODAC components for writing external procedures for Oracle database. A small example of external procedure using ODAC components is listed below.

For example, let's create an external procedure that saves LOB to file and stores the file name and the file date in a database. Suppose we have the following table to store file names and dates:

CREATE TABLE scott.odac_file_list
(
  id integer PRIMARY KEY,
  file_name VARCHAR2(100),
  file_date TIMESTAMP
)

Let's create a DLL ExtProc containing our external procedure add_file.

All external procedures and functions in DLL must be listed in the library exports clause. Before calling any OCI functions in DLL InitOCI procedure must be called. When OCI is no longer needed FreeOCI procedure must be called.

In declaration of procedure add_file cdecl directive must be used. It is necessary to call OCIExtProcGetEnv function, that returns environment, service context and error handles. A call to OCIExtProcGetEnv function is required to make OCI callbacks to database.

Then we will create a data module with TOraSession and TOraQuery components. TOraSession component can be linked to external procedure service context by assigning service context pointer to OCISvcCtx property of TOraSession. After such assignment we can execute queries through OraSession.

An external procedure must not raise Delphi exceptions. All these exceptions must be processed inside the procedure and procedure can raise PL/SQL exceptions with OCIExtProcRaiseExcpWithMsg OCI function.

The source code of DLL, that contains add_file procedure is the following:

library ExtProc;

uses
  SysUtils,
  Classes,
  DB, Ora, OraCall, OraError, OraClasses,
  Data in 'Data.pas' {dmData: TDataModule};

{$R *.res}

procedure add_file(Context: pOCIExtProcContext; Id: pOCINumber;
  FileName: PChar; FileDate: pOCIDateTime; FileText: pOCILobLocator); cdecl;
var
  hEnv: pOCIEnv;
  hSvcCtx: pOCISvcCtx;
  hError: pOCIError;
  dmData: TdmData;
  OraLob: TOraLob;
begin
  try
    // get OCI service context
    Check(OCIExtProcGetEnv(Context, hEnv, hSvcCtx, hError));
    dmData := TdmData.Create(nil);
    try
      // set sevice context handle in OraSession
      dmData.OraSession.OCISvcCtx := hSvcCtx;
      with dmData.OraSQL do begin
        ParamByName('ID').DataType := TFieldType(ftNumber);
        ParamByName('ID').AsNumber.OCINumberPtr := Id;
        ParamByName('FILE_NAME').AsString := FileName;
        ParamByName('FILE_DATE').DataType := ftTimeStamp;
        ParamByName('FILE_DATE').AsTimeStamp.OCIDateTime := FileDate;
        Execute;
        OraLob := TOraLob.Create(hSvcCtx);
        try
          OraLob.OCILobLocator := FileText;
          OraLob.ReadLob;
          OraLob.SaveToFile(FileName);
        finally
          OraLob.Free;
        end;
      end;
    finally
      dmData.Free;
    end;
  except
    on e: EOraError do
      OCIExtProcRaiseExcpWithMsg(Context, e.ErrorCode, PChar(e.Message), Length(e.Message));
    on e: Exception do
      OCIExtProcRaiseExcpWithMsg(Context, 20000, PChar(e.Message), Length(e.Message));
  end;
end;

exports
  add_file;

begin
  // Load oci.dll and link OCI functions
  InitOCI;
end.

To use this external procedure compile the DLL and copy it to Oracle server. The DLL must be copied to ORACLE_HOME\bin (Windows) or ORACLE_HOME/lib (UNIX). See Oracle documentation about making the external procedures agent load external procedure libraries from other paths.

External procedures DLL must be defined with CREATE LIBRARY statement. In our ExternalProc Demo the library is created as follows:

CREATE OR REPLACE LIBRARY Scott.ExtProcDemo AS
 'C:\oracle\product\10.2.0\db_1\bin\ExtProc.dll'

Note: the path to the DLL passed to CREATE LIBRARY statement is case sensitive.

Then we define the external procedure:

CREATE PROCEDURE scott.add_file(
   id NUMBER,
   file_name VARCHAR2,
   file_date TIMESTAMP,
   file_text CLOB
  )
  AS LANGUAGE C
  NAME "add_file"
  LIBRARY scott.ExtProcDemo
  WITH CONTEXT
  PARAMETERS (CONTEXT, id OCINUMBER, file_name STRING, file_date OCIDATETIME, file_text OCILOBLOCATOR);

The "LANGUAGE C" option shows that it is an external procedure written in the language compatible with the C language call specification. The "NAME" option is the name of the procedure in the DLL. "WITH CONTEXT" option enables OCI callbacks to the database during an external procedure execution. That means an additional CONTEXT parameter is passed to the procedure. It allows the procedure to use a connection to the database.

Now the add_file procedure can be called from an SQL query.

Note to execute external procedures Oracle Net files listener.ora and tnsnames.ora must be configured for external procedures. See Oracle documentation about the configuration Oracle net for external procedures.

Writing Oracle External Procedures with ODAC

SeeAlso