Calling External Procedure (DLL) from PL/SQL

If Oracle has been configured for the external procedures , you can call any DLL from your PL/SQL. How to configure for EXTPROC please click on the link below

Configuring SQL*Net for External Procedures

Here is an external procedure that will discover the amount of free space on a given disk drive. This example is just to get you going. This example was designed for Windows NT 4.0, but the idea can be applied to any operating system that meets the requirements for external procedures. In this case, we simply make a call to the appropriate function in the Windows kernel, rather than writing our own DLL.
Windows NT's kernel, kernel32.dll, contains a routine called GetDiskFreeSpaceA, which accepts a drive letter as an input parameter and returns four statistics about the drive. When we register the routine with PL/SQL, we will provide mappings for each of these parameters to a PL/SQL parameter. Then, when we invoke the external procedure from a PL/SQL program, we'll use these statistics to compute the free disk space.
1- First, we need to define a "library" to tell Oracle where the DLL lives:
/*as explained in above article that these dlls should be in ORACLEHOME\bin
so copy kernel32.dll from C:\windows\system32 to Oracle's BIN folder
*/
     CREATE OR REPLACE LIBRARY nt_kernel
     AS
          'D:\oracle\product\10.2.0\db_1\BIN\kernel32.dll';
/       
2- We'll create a package called disk_util that will contain our function, which we will call get_disk_free_space as shown below:

CREATE OR REPLACE PACKAGE disk_util
AS
   FUNCTION get_disk_free_space (
      root_path                  IN       VARCHAR2,
      sectors_per_cluster        OUT      PLS_INTEGER,
      bytes_per_sector           OUT      PLS_INTEGER,
      number_of_free_clusters    OUT      PLS_INTEGER,
      total_number_of_clusters   OUT      PLS_INTEGER
   )
      RETURN PLS_INTEGER;

   PRAGMA RESTRICT_REFERENCES (get_disk_free_space, WNPS, RNPS, WNDS, RNDS);
END disk_util;
/

3- All the magic is in the package body, which uses the EXTERNAL clause rather than a BEGIN..END block. This clause is where we define the interface between PL/SQL and the external routine:

CREATE OR REPLACE PACKAGE BODY disk_util
AS
   FUNCTION get_disk_free_space (
      root_path                  IN       VARCHAR2,
      sectors_per_cluster        OUT      PLS_INTEGER,
      bytes_per_sector           OUT      PLS_INTEGER,
      number_of_free_clusters    OUT      PLS_INTEGER,
      total_number_of_clusters   OUT      PLS_INTEGER
   )
      RETURN PLS_INTEGER
   IS
   EXTERNAL
      LIBRARY nt_kernel                   -- our library (defined previously)
      NAME "GetDiskFreeSpaceA"            -- name of function in kernel32.dll
      LANGUAGE c                          -- external routine is written in C
      CALLING STANDARD pascal             -- uses Pascal parameter convention
      PARAMETERS                    -- map PL/SQL to C parameters by position
                (
         root_path STRING,
         sectors_per_cluster BY REFERENCE LONG,
         bytes_per_sector BY REFERENCE LONG,
         number_of_free_clusters BY REFERENCE LONG,
         total_number_of_clusters BY REFERENCE LONG,
         RETURN LONG
      );                       -- "return code" indicating success or failure
END disk_util;
/

4- Assuming that the DBA has set up the environment (see above article for details) to support external procedures, we can make an easy call to compute the disk space on the C: drive:
 SET SERVEROUTPUT ON SIZE 100000          

DECLARE
   lroot_path                  VARCHAR2 (3) := 'C: ';      -- look at C drive
   lsectors_per_cluster        PLS_INTEGER;
   lbytes_per_sector           PLS_INTEGER;
   lnumber_of_free_clusters    PLS_INTEGER;
   ltotal_number_of_clusters   PLS_INTEGER;
   return_code                 PLS_INTEGER;
   free_meg                    REAL;
BEGIN
   /* Call the external procedure.  We ignore the return code in this simple example.*/
   return_code :=
      disk_util.get_disk_free_space (lroot_path,
                                     lsectors_per_cluster,
                                     lbytes_per_sector,
                                     lnumber_of_free_clusters,
                                     ltotal_number_of_clusters
                                    );
                  /* Using the drive statistics that are returned from the external procedure,
   ||compute the amount of free disk space.  Remember Megabytes = (Bytes / 1024 / 1024)
                */
   DBMS_OUTPUT.put_line ('lsectors_per_cluster = ' || lsectors_per_cluster);
   DBMS_OUTPUT.put_line ('lbytes_per_sector = ' || lbytes_per_sector);
   DBMS_OUTPUT.put_line (   'lnumber_of_free_clusters = '
                         || lnumber_of_free_clusters
                        );
   DBMS_OUTPUT.put_line (   'ltotal_number_of_clusters = '
                         || ltotal_number_of_clusters
                        );
   free_meg :=
        (lsectors_per_cluster * lbytes_per_sector)
      * (lnumber_of_free_clusters / 1024)
      / 1024;
   DBMS_OUTPUT.put_line ('free disk space, megabytes = ' || free_meg);
END;
/
On my machine, this fragment produces the following output:

Of course, you could put this computation in a named function or procedure, and even make it part of the disk_util package.