A script-enabled browser is required for this page to function properly.

ORA_FFI Example 1A

Suppose you want to create an interface to the C function pow, which is found in the Microsoft Windows runtime llibrary: C:\windows\system\msvcrt.dll. (The pow function raises x to the y power.)


int pow(int x, int y)

First, create a package specification that represents the library and defines the PL/SQL function that you want to invoke:


PACKAGE mathlib IS
 FUNCTION pow(x NUMBER, y NUMBER)
 RETURN NUMBER;
END; 

You would call the PL/SQL function mathlib.pow, defined above, to invoke the foreign function pow, from the dynamic library msvcrt.dll.

Notice that this subprogram does not require a handle to the library or foreign function. For convenience, the various registrations are handled in the package body, defined below.

Note: This example uses a PRAGMA compiler directive to tell the PL/SQL compiler that the function ff_to_power is actually to be compiled as a C function, rather than PL/SQL. ORA_FFI Example 1B shows how to achieve the same result using the ORA_FFI.GENERATE_FOREIGN function to generate a PL/SQL mathlib package. In Example 1B, the PRAGMA directive is handled automatically by the ORA_FFI.GENERATE_FOREIGN function.


PACKAGE BODY mathlib IS
/* Declare the library and function handles. */
mathlib_lhandle ORA_FFI.LIBHANDLETYPE;
to_power_fhandle ORA_FFI.FUNCHHANDLETYPE;


/* Create the PL/SQL function that will actually */
/* invoke the foreign function. */
FUNCTION ff_to_power(fhandle ORA_FFI.FUNCHHANDLETYPE, 
  x NUMBER, y NUMBER)RETURN NUMBER;
  PRAGMA interface(C, ff_to_power, 11265); 

/* Create the PL/SQL function that is defined in */
/* the package spec. This function simply */
/* passes along the arguments it receives to */
/* ff_to_power (defined above), prepending the */
/* foreign function handle to the argument List. */
FUNCTION pow(x NUMBER, y NUMBER) RETURN NUMBER IS
BEGIN
  RETURN(ff_to_power(to_power_fhandle, x, y));
END pow;


/* Define the body of package mathlib */
BEGIN 
 /* Load the library. */
  mathlib_lhandle := ORA_FFI.LOAD_LIBRARY 
  ('C:\WINDOWS\SYSTEM\', 'msvcrt.dll');


/* Register the foreign function. */
  to_power_fhandle := ORA_FFI.REGISTER_FUNCTION
(mathlib_lhandle, 'pow', ORA_FFI.C_STD);


/* Register both parameters of function to_power. */
 ORA_FFI.REGISTER_PARAMETER(to_power_fhandle,ORA_FFI.C_DOUBLE);
 ORA_FFI.REGISTER_PARAMETER(to_power_fhandle,ORA_FFI.C_DOUBLE);


/* Register the return type. */
  ORA_FFI.REGISTER_RETURN (to_power_fhandle, ORA_FFI.C_DOUBLE);

END; /* Package Body Mathlib */

To invoke the C function pow 
 from mscvrt.dll, you simply call 
 the PL/SQL function pow, defined 
 in the mathlib package specification. For example:

PL/SQL>

PROCEDURE raise_to_power (a in number, b in number) IS
BEGIN
  TEXT_IO.PUT_LINE(mathlib.pow(a,b));
END;

PL/SQL> raise_to_power(2,9);

512

See also

About the ORA_FFI built-in package

ORA_FFI built-in package

ORA_FFI built-in package examples