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

Simplifying Complex Parameter Data Types

The ORA_FFI package only supports simple PL/SQL parameter and return data types that correspond to the C programming language. When complex parameter and return data types are used in foreign function prototypes, you must simplify or substitute the parameter and return type. If simplification is not possible, use a user exit interface instead.

For example, there is no PL/SQL equivalent to a C language structure data type. You can simplify the structure data type by creating a foreign function that builds the structure. You cannot create a PL/SQL interface to a foreign function without simplifying the complex parameter type.

MainStruct is an example of a complex parameter type:

Foreign_Func(MainStruct a, int b, char c)
{
/* process MainStruct */
...
}

To simplify the the MainStruct parameter, consider the following approach:

New_Foreign_Func(int x, float y, char* z, int b, char c)
{
  struct MainStruct
 {
  int x;
  float y;
  char* z;
 }
/* process MainStruct */
...
}

Creating a PL/SQL interface on Microsoft Windows: Example

Although creating a PL/SQL interface is similar on most platforms, this topic describes aspects of Oracle Forms that is specific to its use in Microsoft Windows.

This example invokes Windows Profile String functions from a PL/SQL interface. The PL/SQL package OraWinProfile contains the PL/SQL interface to the Windows Profile String functions.

This is the PL/SQL package specification:

PACKAGE OraWinProfile IS
 /*
  Function WritePrivateString calls the Windows
  WritePrivateProfileString function
 */
 FUNCTION WritePrivateString(pSection IN VARCHAR2,
   pEntry IN VARCHAR2,
   pString IN VARCHAR2,
   pFilename IN VARCHAR2)
  RETURN BOOLEAN;

 /*
  Function GetPrivateString calls the Windows
  GetPrivateProfileString function
 */
 FUNCTION GetPrivateString(pSection IN VARCHAR2,
   pEntry IN VARCHAR2,
   pDefault IN VARCHAR2,
   pReturnBuffer IN OUT VARCHAR2,
   pReturnBufferN IN BINARY_INTEGER,
   pFilename IN VARCHAR2)
  RETURN BINARY_INTEGER;
END OraWinProfile;

This is the PL/SQL package body:

PACKAGE BODY OraWindProfile IS
 lh_window ora_ffi.libHandleType;
 lh_forms ora_ffi.libHandleType;
 fh_wpps  ora_ffi.funcHandleType;
 fh_gpps  ora_ffi.funcHandleType;

 /*
  icd_wpps function acts as the interface to the
  WritePrivateString function in Windows
 */

 FUNCTION icd_wpps(funcHandle IN ora_ffi.funcHandleType,
  pSection IN OUT VARCHAR2,
  pEntry IN OUT VARCHAR2,
  pString IN OUT VARCHAR2,
  pFilename IN OUT VARCHAR2)
  RETURN BINARY_INTEGER;
  PRAGMA interface(c,icd_wpps,11265);

 /*
  icd_gpps function acts as the interface to the
  GetPrivateString function in windows
 */

 FUNCTION icd_gpps(funcHandle IN ora_ffi.funcHandleType,
  pSection IN OUT VARCHAR2,
  pEntry IN OUT VARCHAR2,
  pDefault IN OUT VARCHAR2,
  pReturnBuffer IN OUT VARCHAR2,
  pRetrunBufferN IN BINARY_INTEGER,
  pFilename IN OUT VARCHAR2)
  RETURN BINARY_INTEGER;
  PRAGMA(c,icd_gpps,11265);

 FUNCTION WritePrivateString(pSection IN VARCHAR2,
   pEntry IN VARCHAR2,
   pString IN VARCHAR2,
   pFilename IN VARCHAR2)
  RETURN BOOLEAN IS
  /* WritePrivateString calls the Windows function */
  /* Make copies of in out arguments */
  lSection VARCHAR2(1024) := pSection;
  lEntry VARCHAR(1024) := pEntry;
  lString VARCHAR2(1024) := pString;
  lFilename VARCHAR(1024) := pFilename;

 BEGIN

  /*
  validate arguments--although NULL is a valid argument
  for Windows functions, we are going to prohibit this
  case because of the format of the string that is
  returned
  */

  IF (lSection is NULL) OR (lEntry is NULL) OR
   (lString is NULL) OR (lFilename is NULL) THEN
   RAISE VALUE_ERROR;
  END IF;
  RETURN(icd_wpps(fh_wpps,lSection,lEntry,lString,
lFilename)<>0);
 END WritePrivateString;

 FUNCTION GetPrivateString(pSection IN VARCHAR2,
   pEntry IN VARCHAR2,
   pDefault IN VARCHAR2,
   pReturnBuffer IN OUT VARCHAR2,
   pReturnBufferN IN BINARY_INTEGER,
   pFilename IN VARCHAR2)
  RETURN BINARY_INTEGER IS
  /* GetPrivateString calls the Windows function */
  /* Make copies of in out arguments */
  lSection VARCHAR2(1024) := pSection;
  lEntry VARCHAR(1024) := pEntry;
  lDefault VARCHAR2(1024) := pDefault;
  lFilename VARCHAR(1024) := pFilename;

 BEGIN

  /*
  validate arguments--although NULL is a valid argument for
  Windows functions, we are going to prohibit this case
  because of the format of the string that is returned
  */

  IF (lSection is NULL) OR (lEntry is NULL) OR
   (lDefault is NULL) OR (lFilename is NULL) OR
   (pReturnBufferN <= 0) THEN
   RAISE VALUE_ERROR;
  END IF;
  /* Pad the buffer with spaces */
  pReturnBuffer := rpad(' ', pReturnBufferN);
  RETURN(icd_gpps(fh_gpps,lSection,lEntry,lDefault,
pReturnBuffer,pReturnBufferN,lFilename));
 END GetPrivateString;

BEGIN
 /* Declare a library handle to the Windows 386 Kernel */
 lh_windows := ora_ffi.load_library(NULL,'krnl386.exe');

 /*

  Register the components of WritePrivateString
   This is function prototype for WritePrivateProfileString:
   BOOL WritePrivateProfileString(LPCSTR lpszSection,
LPCSTR lpszEntry,
LPCSTR lpszString,
LPCSTR lpszFilename)
 */

 fh_wpps := ora_ffi.register_function(lh_windows,
'WritePrivateProfileString',
ora_ffi.PASCAL_STD);

 ora_ffi.register_return(fh_wpps,ORA_FFI.C_SHORT);
 ora_ffi.register_parameter(fh_wpps,ORA_FFI.C_CHAR_PTR);
 ora_ffi.register_parameter(fh_wpps,ORA_FFI.C_CHAR_PTR);
 ora_ffi.register_parameter(fh_wpps,ORA_FFI.C_CHAR_PTR);
 ora_ffi.register_parameter(fh_wpps,ORA_FFI.C_CHAR_PTR);

 /*
  Register the components of GetPrivateString
  This is function prototype for GetPrivateProfileString:
  int GetPrivateProfileString(LPCSTR lpszSection,
LPCSTR lpszEntry,
LPCSTR lpszDefault,
LPCSTR lpszReturnBuffer,
int cbReturnBuffer,
LPCSTR lpszFilename)
 */
 fh_gpps := ora_ffi.register_function(lh_windows,
'GetPrivateProfileString',
ora_ffi.PASCAL_STD);
 ora_ffi.register_return(fh_gpps,ORA_FFI.C_INT);
 ora_ffi.register_parameter(fh_wpps,ORA_FFI.C_CHAR_PTR);
 ora_ffi.register_parameter(fh_wpps,ORA_FFI.C_CHAR_PTR);
 ora_ffi.register_parameter(fh_wpps,ORA_FFI.C_CHAR_PTR);
 ora_ffi.register_parameter(fh_wpps,ORA_FFI.C_CHAR_PTR);
 ora_ffi.register_parameter(fh_wpps,ORA_FFI.C_INT);
 ora_ffi.register_parameter(fh_wpps,ORA_FFI.C_CHAR_PTR);
END OraWinProfile;

Here is an example of how to invoke the WritePrivateProfileString and the GetPrivateProfileString functions from a PL/SQL interface in Oracle Forms:

DECLARE
 tmpb BOOLEAN;
 tmpn NUMBER;
 buffer VARCHAR(1024) := '';
 buffern BINARY_INTEGER := 1024;
BEGIN
 tmpb := OraWinProfile.WritePrivateString('Section',
  'Entry',
  'string1',
  'PROFILE.INI');
 tmpn := OraWinProfile.GetPrivateString('Section',
  'Entry',
  'Bad Entry',
  buffer,
  buffern,
  'PROFILE.INI');
MESSAGE(buffer);
END;

You invoke the WritePrivateString and GetPrivateString functions from the OraWinProfile package. After executing the example PL/SQL code, the following two lines are appended to the PROFILE.INI file.

[Section]
Entry=String1

The identical results would have been obtained if the Windows Profile String functions, WritePrivateString and GetPrivateString, were called from outside Oracle Forms. From this example, you can see how a variety of foreign functions can be invoked in Oracle Forms from a PL/SQL interface.


Passing parameter values to a foreign function using PL/SQL

Returning a value from a foreign function using PL/SQL example

About the user exit interface