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

Accessing the Microsoft Windows SDK from a PL/SQL Interface

Many functions from the Microsoft Windows Software Developer's Kit (SDK) are accessible from a PL/SQL interface. Creating a PL/SQL interface to a Microsoft Windows SDK function is similar to creating a PL/SQL interface to any foreign function.

The parameters and return types of the Microsoft Windows SDK function must be data types that have a PL/SQL equivalent; they cannot be complex data types that cannot be simplified. For more information on simplifying complex data types, refer to Invoking a Foreign Function from a PL/SQL Interface.

A foreign function with complex parameters and return data types that cannot be simplified can only be invoked from a user exit interface. An example of a complex parameter data type that cannot be simplified is a window handle, a parameter data type recognized by many Microsoft Windows SDK functions. A window handle is a unique internal character constant that is used to refer to interface objects.

In instances where the Microsoft Windows SDK function requires a window handle, you cannot invoke the function from a PL/SQL interface. Instead, you should use a user exit interface and the Window_Handle property to provide the necessary parameters. Window_Handle is a property of Oracle Forms items and windows.

In cases where the Microsoft Windows SDK function call is typical of a C language function call without complex data types, you can invoke the function from a PL/SQL interface. Treat the Microsoft Windows SDK function like any other foreign function: during initialization, load the library and register the function name, return type, and parameters. After the creation of a PL/SQL interface to the Microsoft Windows SDK function, invoke the Windows SDK function from Oracle Forms using PL/SQL.

Foreign function: Examples

This section includes several examples that demonstrate how to use foreign functions.

  1. Creating a PL/SQL interface on Microsoft Windows
  2. Using ORA_FFI to open the File Open dialog on Windows
  3. Using ORA_FFI to call Unix(SUN) executables with a STDIN/STDOUT type interface

Using ORA_FFI to open the File Open dialog on Windows Example

PACKAGE OraDlg IS
FUNCTION OraMultiFileDlg
 (Title IN VARCHAR2,
 Filter IN VARCHAR2,
 Dir IN VARCHAR2,
 FileString IN OUT VARCHAR2)
RETURN PLS_INTEGER;
FUNCTION OraSingleFileDlg
 (Title IN VARCHAR2,
 Filter IN VARCHAR2,
 Dir IN VARCHAR2,
 FileString IN OUT VARCHAR2)
RETURN PLS_INTEGER;
END OraDlg;
PACKAGE BODY OraDlg IS
 lh_ORADLG ora_ffi.libHandleType; 
 fh_OraMultiFileDlg ora_ffi.funcHandleType;
 fh_OraSingleFileDlg ora_ffi.funcHandleType;
FUNCTION i_OraMultiFileDlg
 (funcHandle IN ora_ffi.funcHandleType,
 Title IN OUT VARCHAR2,
 Filter IN OUT VARCHAR2,
 Dir IN OUT VARCHAR2,
 FileString IN OUT VARCHAR2)
RETURN PLS_INTEGER;
PRAGMA INTERFACE(C,i_OraMultiFileDlg,11265);
FUNCTION OraMultiFileDlg
 (Title IN VARCHAR2,
 Filter IN VARCHAR2,
 Dir IN VARCHAR2,
 FileString IN OUT VARCHAR2)
RETURN PLS_INTEGER IS
Title_l VARCHAR2(128) := RPAD(SUBSTR(NVL(Title,'Open'),1,128),128,CHR(0));
Filter_l VARCHAR2(128) := RPAD(SUBSTR(NVL
  (Filter,'All Files (*.*)|*.*|'),1,128),128,CHR(0));
Dir_l VARCHAR2(256) := RPAD(SUBSTR(NVL(Dir,' '),1,256),256,CHR(0));
FileString_l VARCHAR2(2000) := RPAD(SUBSTR(NVL(FileString,' '),1,2000),2000,CHR(0));
rc PLS_INTEGER;

BEGIN
rc := i_OraMultiFileDlg(fh_OraMultiFileDlg, 
  Title_l,
  Filter_l,
  Dir_l,
  FileString_l);
FileString := FileString_l; 
RETURN (rc);
END ;
FUNCTION i_OraSingleFileDlg
 (funcHandle IN ora_ffi.funcHandleType,
 Title IN OUT VARCHAR2,
 Filter IN OUT VARCHAR2,
 Dir IN OUT VARCHAR2,
 FileString IN OUT VARCHAR2)
RETURN PLS_INTEGER;
PRAGMA INTERFACE(C,i_OraSingleFileDlg,11265);
FUNCTION OraSingleFileDlg
 (Title IN VARCHAR2,
 Filter IN VARCHAR2,
 Dir IN VARCHAR2,
 FileString IN OUT VARCHAR2)
RETURN PLS_INTEGER IS
Title_l VARCHAR2(128) := RPAD(SUBSTR(NVL(Title,'Open'),1,128),128,CHR(0));
Filter_l VARCHAR2(128) := RPAD(SUBSTR(NVL
  (Filter,'All Files (*.*)|*.*|'),1,128),128,CHR(0));
Dir_l VARCHAR2(256) := RPAD(SUBSTR(NVL(Dir,' '),1,256),256,CHR(0));
FileString_l VARCHAR2(2000) := RPAD(SUBSTR(NVL(FileString,' '),1,2000),2000,CHR(0));
rc PLS_INTEGER;

BEGIN
rc := i_OraSingleFileDlg(fh_OraSingleFileDlg, 
  Title_l,
  Filter_l,
  Dir_l,
  FileString_l);
FileString := FileString_l; 
RETURN (rc);
END ;
BEGIN
BEGIN  
lh_ORADLG := ora_ffi.find_library('ORADLG.DLL'); 
EXCEPTION WHEN ora_ffi.FFI_ERROR THEN  
lh_ORADLG := ora_ffi.load_library(NULL,'ORADLG.DLL'); 
END ; 
fh_OraMultiFileDlg := ora_ffi.register_function 
   (lh_ORADLG,'OraMultiFileDlg',ora_ffi.PASCAL_STD);
ora_ffi.register_parameter(fh_OraMultiFileDlg,ORA_FFI.C_CHAR_PTR); 
ora_ffi.register_parameter(fh_OraMultiFileDlg,ORA_FFI.C_CHAR_PTR); 
ora_ffi.register_parameter(fh_OraMultiFileDlg,ORA_FFI.C_CHAR_PTR); 
ora_ffi.register_parameter(fh_OraMultiFileDlg,ORA_FFI.C_CHAR_PTR); 
ora_ffi.register_return(fh_OraMultiFileDlg,ORA_FFI.C_LONG); 
fh_OraSingleFileDlg := ora_ffi.register_function 
   (lh_ORADLG,'OraSingleFileDlg',ora_ffi.PASCAL_STD);
ora_ffi.register_parameter(fh_OraSingleFileDlg,ORA_FFI.C_CHAR_PTR); 
ora_ffi.register_parameter(fh_OraSingleFileDlg,ORA_FFI.C_CHAR_PTR); 
ora_ffi.register_parameter(fh_OraSingleFileDlg,ORA_FFI.C_CHAR_PTR); 
ora_ffi.register_parameter(fh_OraSingleFileDlg,ORA_FFI.C_CHAR_PTR); 
ora_ffi.register_return(fh_OraSingleFileDlg,ORA_FFI.C_LONG); 
END OraDlg;


Using ORA_FFI to call Unix(SUN) executables with a STDIN/STDOUT type interface: Example

/* Copyright (c) 1997, 2005 by Oracle */

/*
NAME  
ora_pipe_io_spec.sql - Specification for access to Unix Pipe mechanism  
DESCRIPTION  
Demonstration of how to use the ORA_FFI Package to provide access to the  
Unix Pipe C functions.  
PUBLIC FUNCTION(S)  
popen - Open the Pipe command  
get_line - Get a line of Text from a Pipe  
put_line - Put a line of Text into a Pipe  
pclose - Close the Pipe  
is_open - Determine whether the Pipe descriptor is open.  

NOTES  

In Order to use these routines you could write the following PL/SQL Code:  

-- Example of Calls to ora_pipe_io functions  
DECLARE  
stream ora_pipe_io.PIPE;  
buffer VARCHAR2(240);  
stream := ora_pipe_io.popen('ls -l', ora_pipe_io.READ_MODE);  
loop  
exit when not ora_pipe_io.get_line(stream, buffer, 240);  
:directory.file := buffer;  
down;  
end loop;  
ora_pipe_io.pclose(stream);  
END;  

MODIFIED (MM/DD/YY)  
smclark 08/05/94 - Creation  


*/

PACKAGE ora_pipe_io is
/*  
** Arguments to popen.  
*/  

READ_MODE constant VARCHAR2(1) := 'r';  
WRITE_MODE constant VARCHAR2(1) := 'w';  

/* ------------- TYPE PIPE ----------- */  
/*  
** Public Type PIPE - Handle to a Un*x pipe  
**  
** Do not modify the private members of this type  

TYPE PIPE is RECORD  (file_handle ORA_FFI.POINTERTYPE,  
is_open boolean,  
read_write_mode VARCHAR2(1));  
/* ------------ FUNCTION POPEN ----------- */  
/*  
** Function POPEN -- Open a Un*x pipe command  
**  
** Given a Unix command to execute and a Pipe read/write mode in which  
** to execute the instruction this Function will execute the Command  
** and return a handle, of type PIPE, to the resulting Input/Output  
** stream.  

** The command to be executed is limited to 1024 characters.  
*/  
FUNCTION popen(command in VARCHAR2,  
ctype in VARCHAR2)  
RETURN PIPE;  

/* ------------ PROCEDURE PCLOSE ----------- */  
** Procedure PCLOSE -- Close a pipe  
**  
** Close a previously opened pipe.  
**  
** Raises a VALUE_ERROR exception if incorrect arguments are passed.  
*/  
PROCEDURE pclose(stream in out PIPE);  
/* ------------ FUNCTION GET_LINE ----------- */  
/*  
** Function GET_LINE  
**  
** Get a line of text from a previously opened pipe.  
**  
** Raises a VALUE_ERROR exception if incorrect arguments are passed.  
** For example  
** if you pass a pipe which has never been opened (using popen)  
*/  

FUNCTION get_line(stream in out PIPE,  
s in out VARCHAR2,  
n in PLS_INTEGER)  
RETURN BOOLEAN;  


/* ------------ PROCEDURE PUT_LINE ----------- */  
/*  
** Procedure PUT_LINE -- Put a line of text into a a write mode pipe.  
**  
** Put a line of text into a previously opened pipe.  
**  
** Raises a VALUE_ERROR exception if incorrect arguments are passed.  
** For example  
** if you pass a pipe which has never been opened (using popen)  
**  
** The Internal buffer for the string to write is limited to 2048 bytes  
*/  
PROCEDURE put_line(stream in out PIPE,  
s in VARCHAR2);  
/* ------------ FUNCTION IS_OPEN ----------- */  
/*  
** Function IS_OPEN -- Determines whether a pipe is open.  
** Returns TRUE if the pipe is open, FALSE if the pipe is closed.  


RETURN BOOLEAN;  
/* ora_pipe_io_body.sql - Body of Package for access to Unix Pipe mechanism
DESCRIPTION  
Demonstration of how to use the ORA_FFI Package to provide access to the  
Unix Pipe C functions.  

PUBLIC FUNCTION(S)  
popen - Open the Pipe command  
get_line - Get a line of Text from a Pipe  
put_line - Put a line of Text into a Pipe  
pclose - Close the Pipe  
is_open - Determine whether the Pipe descriptor is open.  
PRIVATE FUNCTION(S)  
icd_popen, icd_fgets, icd_fputs, icd_pclose  


NOTES  
MODIFIED (MM/DD/YY)  
smclark 11/02/94 - Modified for production release changes to ORA_FFI.  
smclark 08/05/94 - Creation

*/

PACKAGE BODY ora_pipe_io is  
lh_libc ora_ffi.libHandleType;  
fh_popen ora_ffi.funcHandleType;  
fh_pclose ora_ffi.funcHandleType;  
fh_fputs ora_ffi.funcHandleType;  

/* ------------ FUNCTION ICD_POPEN ----------- */  
** Function ICD_POPEN -- Interface routine to C function popen  
**  
** This function acts as the interface to the popen function in  
** libc.  
*/  

FUNCTION icd_popen(funcHandle in ora_ffi.funcHandleType,  
command in out VARCHAR2,  
ctype in out VARCHAR2)  
return ORA_FFI.POINTERTYPE;  

/* ------------ PROCEDURE ICD_PCLOSE ----------- */  
/*  
** Function ICD_PCLOSE -- Interface routine to C function pclose  
**  
** This function acts as the interface to the pclose function in  
** libc.  
*/  

PROCEDURE icd_pclose(funcHandle in ora_ffi.funcHandleType,  
stream in out ORA_FFI.POINTERTYPE);  
pragma interface(c, icd_pclose, 11265);  

/* ------------ FUNCTION ICD_FGETS ----------- */  
/*  

** Function ICD_FGETS -- Interface routine to C function fgets  
**  
** This function acts as the interface to the fgets function in  
** libc.  
*/  

s in out VARCHAR2, n in PLS_INTEGER,  
stream in out ORA_FFI.POINTERTYPE)  
RETURN ORA_FFI.POINTERTYPE;  
pragma interface(c, icd_fgets, 11265);  
/* ------------ FUNCTION ICD_FPUTS ----------- */  
/*  
** Function ICD_FPUTS -- Interface routine to C function fputs  
**  
** This function acts as the interface to the fputs function in  
** libc.  
*/  

PROCEDURE icd_fputs(funcHandle in ora_ffi.funcHandleType,  
s in out VARCHAR2,  
stream in out ORA_FFI.POINTERTYPE);  
pragma interface(c, icd_fputs, 11265);
 
/* ------------ FUNCTION POPEN ----------- */  

** Function POPEN -- Open a Un*x pipe command  
*/  

ctype in VARCHAR2)  
RETURN PIPE is  

/*  

** Take a copy of the arguments because we need to pass them  
** IN OUT to icd_popen, but we really don't want people to have  
** to call our routines in the same way.  
*/  

cmd varchar2(1024) := command;  
cmode varchar2(1) := ctype;  

BEGIN  
if (cmode not in (READ_MODE, WRITE_MODE))  
or (cmode is NULL)  
or (cmd is NULL)  
then  
end if;  
stream.file_handle := icd_popen(fh_popen, cmd, cmode);  
stream.read_write_mode := ctype;  
return(stream);  
END popen;
 

/* ------------ PROCEDURE PCLOSE ----------- */  
/*  
** Procedure PCLOSE -- Close a pipe  
*/  
PROCEDURE pclose(stream in out PIPE) is  
BEGIN  
icd_pclose(fh_pclose, stream.file_handle);  
stream.is_open := FALSE;  
END pclose;  

/* ------------ FUNCTION GET_LINE ----------- */  
/*  
** Function GET_LINE -- Get a line of text into a buffer  
** from the read mode pipe.  

FUNCTION get_line(stream in out PIPE,  
s in out VARCHAR2, n in PLS_INTEGER)  
RETURN BOOLEAN is  
buffer ORA_FFI.POINTERTYPE;  
BEGIN  
if (n <= 0)  
or (stream.is_open = FALSE)  
or (stream.is_open is NULL)  
or (stream.read_write_mode <> READ_MODE)  
then  
raise VALUE_ERROR;  
end if;  
/*  

** Initialise the Buffer area to reserve the correct amount of space.  
*/  
s := rpad(' ', n);  
buffer := icd_fgets(fh_fgets, s, n, stream.file_handle);  

/*  
** Determine whether a NULL pointer was returned.  
return (ora_ffi.is_null_ptr(buffer) = FALSE);  
END get_line;  
/* ------------ PROCEDURE PUT_LINE ----------- */  
/*  
** Procedure PUT_LINE -- Put a line of text into a a write mode pipe.  
*/  
PROCEDURE put_line(stream in out PIPE,  
s in VARCHAR2) is  
buffer varchar2(2048) := s;  
BEGIN  
if (stream.is_open = FALSE)  
or (stream.is_open is NULL)  
or (stream.read_write_mode <> WRITE_MODE)  
then  
raise VALUE_ERROR;  
end if;  
icd_fputs(fh_fputs, buffer, stream.file_handle);  
buffer := chr(10);  
icd_fputs(fh_fputs, buffer, stream.file_handle);  
END put_line;
 

/* ------------ FUNCTION IS_OPEN ----------- */  
/*  
** Function IS_OPEN -- Determines whether a pipe is open.  
*/  
FUNCTION is_open(stream in PIPE)  
RETURN BOOLEAN is  
BEGIN  
return(stream.is_open);  
END is_open;  

BEGIN
/*  
** Declare a library handle as libc. (Internal so NULL,NULL)  

*/  
lh_libc := ora_ffi.load_library(NULL, NULL);  
if ora_ffi.is_null_ptr(lh_libc) then  
raise VALUE_ERROR;  
end if;  

** Register the popen function, it's return type and arguments.  
*/  

fh_popen := ora_ffi.register_function(lh_libc, 'popen');  
if ora_ffi.is_null_ptr(fh_popen) then  
raise VALUE_ERROR;  
end if;  
ora_ffi.register_return(fh_popen, ORA_FFI.C_DVOID_PTR);  
ora_ffi.register_parameter(fh_popen, ORA_FFI.C_CHAR_PTR);  
ora_ffi.register_parameter(fh_popen, ORA_FFI.C_CHAR_PTR);
 

/*  

** Register the pclose function, it's return type and arguments.  

*/  
fh_pclose := ora_ffi.register_function(lh_libc, 'pclose');  
if ora_ffi.is_null_ptr(fh_pclose) then  
raise VALUE_ERROR;  
end if;  
ora_ffi.register_return(fh_pclose, ORA_FFI.C_VOID);  
ora_ffi.register_parameter(fh_pclose, ORA_FFI.C_DVOID_PTR);  

/*  
** Register the fgets function, it's return type and arguments.  
*/  

fh_fgets := ora_ffi.register_function(lh_libc, 'fgets');  
if ora_ffi.is_null_ptr(fh_fgets) then  
raise VALUE_ERROR;  
end if;  
ora_ffi.register_return(fh_fgets, ORA_FFI.C_DVOID_PTR);  
ora_ffi.register_parameter(fh_fgets, ORA_FFI.C_CHAR_PTR);  
ora_ffi.register_parameter(fh_fgets, ORA_FFI.C_INT);  
ora_ffi.register_parameter(fh_fgets, ORA_FFI.C_DVOID_PTR);  

/*  

** Register the fputs function, it's return type and arguments.  

*/  

fh_fputs := ora_ffi.register_function(lh_libc, 'fputs');  
if ora_ffi.is_null_ptr(fh_fputs) then  
raise VALUE_ERROR;  
end if;  
ora_ffi.register_return(fh_fputs, ORA_FFI.C_VOID);  
ora_ffi.register_parameter(fh_fputs, ORA_FFI.C_CHAR_PTR);  
ora_ffi.register_parameter(fh_fputs, ORA_FFI.C_DVOID_PTR);  
END ora_pipe_io;


About the user exit interface

Invoking a foreign function from a PL/SQL interface

Accessing the Microsoft Windows SDK from a User Exit