17 Developing Applications for Windows

This chapter points to sources of information on developing applications for Windows and outlines a procedure for building and debugging external procedures.

This chapter contains these topics:

About Finding Information on Application Development for Windows

This section describes where to find information on developing applications specifically for Windows. These products are included on your Oracle Database Server media.


Oracle Objects for OLE (OO4O), Oracle COM Automation on Windows, and Oracle Counters for Windows Performance Monitor are not supported on Oracle Database 12c Release 1 (12.1) for 64-bit and 32-bit Windows. See Oracle Database Upgrade Guide for a list of desupported features.

About Java Enhancements

Oracle Database includes an integrated Java Virtual Machine and JIT Compiler. Oracle Database also provides Oracle Java Database Connectivity (JDBC) Drivers. For more information, refer to Oracle Database Java Developer's Guide and Oracle Database JDBC Developer's Guide.


Oracle Data Provider for .NET (ODP.NET) is an implementation of a Microsoft ADO.NET data provider for Oracle Database. ODP.NET uses Oracle native APIs to offer fast and reliable access to Oracle data and features from any .NET application. ODP.NET also uses and inherits classes and interfaces available in the Microsoft .NET Framework Class Library. For more information, refer to Oracle Data Provider for .NET Developer's Guide for Microsoft Windows and My Oracle Support Note 726240.1.

About Oracle Developer Tools for Visual Studio

The Oracle Developer Tools for Visual Studio (ODT) is a tightly integrated "Add-in" for Microsoft Visual Studio. ODT integrates with Visual Studio to make it easy to browse and edit Oracle schema objects using integrated visual designers and can automatically generate .NET code through a simple drag and drop. Developers can modify table data, execute Oracle SQL statements, edit and debug PL/SQL code, generate and edit SQL scripts, and develop and deploy .NET stored procedures. There are many more features included with these tools. For more information, visit the ODT web home at


About Oracle Providers for ASP.NET

Starting with .NET Framework 2.0, ASP.NET includes service providers that store state in databases. By storing this state in a database, applications can ensure high availability of data, while making the data equally available to all web servers. For more information, refer to Oracle Providers for ASP.NET Developer's Guide for Microsoft Windows.

About Support for Internet Applications

Oracle Database support for internet applications includes Oracle WebCenter Portal, which enables you to publish your data to the web, Oracle Web Tier (Oracle HTTP Server), and PL/SQL Embedded Gateway, which offers PL/SQL procedures stored in Oracle Database that can be started through browsers. For more information, refer to:

About Oracle Services for Microsoft Transaction Server

With Oracle Services for Microsoft Transaction Server, Oracle Database can be a resource manager in Microsoft Distributed Transaction Coordinator (DTC) transactions. Oracle Services for Microsoft Transaction Server act as a proxy for Oracle Database to the DTC. For more information, refer to Oracle Services for Microsoft Transaction Server Developer's Guide for Microsoft Windows.

About Oracle Provider for OLE DB

Refer to Oracle Provider for OLE DB Developer's Guide for Microsoft Windows for information on OLE DB.

About Oracle ODBC Driver

Open Database Connectivity (ODBC) provides a common C programming interface for applications to access data from database management systems. Access to databases is managed by the ODBC Driver Manager. The driver manager provides the linkage between an ODBC application and an ODBC driver for a specific database management system.

Oracle ODBC Driver provides access to Oracle databases for applications written using the ODBC interface.

Oracle Technology Network has both Oracle home based ODBC driver and Instant Client enabled ODBC driver.

Download Instant Client enabled ODBC driver from


About Pro*C/C++ and Pro*COBOL Applications

Refer to the following guides for more information on Pro*C/C++ and Pro*COBOL applications:

About Building External Procedures

This section describes how to create and use external procedures on Windows. The following files are located in ORACLE_HOME\rdbms\extproc:

External Procedures Overview

External procedures are functions written in a third-generation language (C, for example) and callable from within PL/SQL or SQL as if they were a PL/SQL routine or function. External procedures let you take advantage of strengths and capabilities of a third-generation programming language in a PL/SQL environment.


Oracle Database also provides a special purpose interface, the call specification, that lets you call external procedures from other languages, as long as they are callable by C.

The main advantages of external procedures are:

  • Performance, because some tasks are performed more efficiently in a third-generation language than in PL/SQL, which is better suited for SQL transaction processing

  • Code re-usability, because dynamic link libraries (DLLs) can be called directly from PL/SQL programs on the server or in client tools

You can use external procedures to perform specific processes:

  • Solving scientific and engineering problems

  • Analyzing data

  • Controlling real-time devices and processes


Special security precautions are warranted when configuring a listener to handle external procedures. See "Modifying Configuration of External Procedures for Higher Security" and Oracle Database Net Services Administrator's Guide for more information.

To create and use an external procedure, perform the following sequential steps:

Installing and Configuring Oracle Database and Oracle Net Services

This section describes installation and configuration of Oracle Database and Oracle Net.

Installing Oracle Database

Follow the steps in Oracle Database Installation Guide for Microsoft Windows to install these products on your Windows server:

  • Oracle Database Enterprise Edition, Oracle Database Standard Edition, Oracle Database Standard Edition One, Oracle Database Standard Edition 2, or Oracle Database Personal Edition. Each type contains PL/SQL, from which external procedures are called, and the PL/SQL external procedure program (EXTPROC), which runs external procedures.

  • Oracle Net Services

  • Oracle Protocol Support

Configuring Oracle Net Services

During database server installation, Oracle Net Configuration Assistant configures listener.ora and tnsnames.ora files for external procedure calls.

When an application calls an external procedure, Oracle Net Listener starts an external procedure agent called EXTPROC. By default, the extproc process communicates directly through the server process. Using a network connection established by the listener, the application passes the following information to EXTPROC:

  • DLL name

  • External procedure name

  • Parameters (if necessary)

EXTPROC then loads the DLL, runs the external procedure, and passes back any values returned by the external procedure.

If you overwrite default listener.ora and tnsnames.ora files, then you must manually configure the following files for the external procedure behavior described previously to occur:

Writing an External Procedure

Using a third-generation programming language, you can write functions to be built into DLLs and started by EXTPROC. The following is a simple Microsoft Visual C++ example of an external procedure called FIND_MAX:


Because external procedures are built into DLLs, they must be explicitly exported. In this example, the DLLEXPORT storage class modifier exports the function FIND_MAX from a dynamic link library.
#include <windows.h>
#define NullValue -1
  This function tests if x is at least as big as y.
long __declspec(dllexport) find_max(long   x, 
                                short   x_indicator, 
long    y, 
short y_indicator, 
                                short *ret_indicator)
   /* It can be tricky to debug DLL's that are being called by a process
      that is spawned only when needed, as in this case.  
      Therefore try using the DebugBreak(); command.  
      This will start your debugger.  Uncomment the line with DebugBreak(); 
      in it and you can step right into your code.
   /* DebugBreak();  */

   /* First check to see if you have any nulls. */
   /* Just return a null if either x or y is null. */

   if ( x_indicator==NullValue || y_indicator==NullValue) {
      *ret_indicator = NullValue;   
   } else { 
      *ret_indicator = 0;       /* Signify that return value is not null. */
      if (x >= y) return x;
      else return y;

Building a DLL

After writing your external procedures in a third-generation programming language, use the appropriate compiler and linker to build a DLL, making sure to export the external procedures as noted previously. See your compiler and linker documentation for instructions on building a DLL and exporting its functions.

You can build the external procedure FIND_MAX, created in "Writing an External Procedure", into a DLL called extern.dll by going to ORACLE_HOME\rdbms\extproc and typing make. After building the DLL, you can move it to any directory on your system.

The default behavior of EXTPROC is to load DLLs only from ORACLE_HOME\bin or ORACLE_HOME\lib. To load DLLs from other directories, you must set environment variable EXTPROC_DLLS to a colon (:) separated list (semicolon-separated on Windows systems) of DLL names qualified with their complete paths. The preferred way to set this environment variable is through the ENVS parameter in listener.ora.

See Also:

Oracle Database Development Guide for more information on EXTPROC

Registering an External Procedure

Once you have built a DLL containing your external procedures, you must register your external procedures with Oracle Database. Starting with Oracle Database 12c Release 1 (12.1), you can configure the EXTPROC process to be authenticated through a CREDENTIAL for better security.

Oracle Database 12c Release 1 (12.1) supports two new extensions to the CREATE LIBRARY command. This includes a CREDENTIAL clause and a DIRECTORY object option. The CREDENTIAL clause defines the user the EXTPROC runs as while the DIRECTORY object option specifies the directory where the DLL can be located.

To create a PL/SQL library to map to the DLL:

  1. Set environment variable EXTPROC_DLLS in the ENVS parameter in listener.ora. For example:

     (SID_LIST =
      (SID_DESC =
  2. Start SQL*Plus:

    C:\> sqlplus
  3. Connect to the database with appropriate username and password.

  4. Create the PL/SQL library using the CREATE LIBRARY command:

    CREATE LIBRARY externProcedures as 'extern.dll' in DLL_LOC credential the_credential;

    where the_credential is the name chosen during the DBMS_CREDENTIAL.CREATE_CREDENTIAL invocation

    SQL> CREATE LIBRARY externProcedures AS 'C:\app\oracle\product\12.1.0\dbhome_1\rdbms\ extproc\extern.dll';

    where externProcedures is an alias library (essentially a schema object in the database), and


    is the path to the Windows operating system dllextern.dll. This example uses C:\app\oracle\product\12.1.0 as your Oracle base and dbhome_1 as your Oracle home.


    The DBA must grant the EXECUTE privilege on the PL/SQL library to users who want to call the library's external procedure from PL/SQL or SQL. Separate EXECUTE privilege on credential and directory object extensions are required for them to function properly.
  5. Create a PL/SQL program unit specification.

    Do this by writing a PL/SQL subprogram that uses the EXTERNAL clause instead of declarations and a BEGIN...END block. The EXTERNAL clause is the interface between PL/SQL and the external procedure. The EXTERNAL clause identifies the following information about the external procedure:

    • Name

    • DLL alias

    • Programming language in which it was written

    • Calling standard (defaults to C if omitted)

    In the following example, externProcedures is a DLL alias. You need the EXECUTE privilege for this library. The external procedure to call is find_max. If enclosed in double quotation marks, it becomes case-sensitive. The LANGUAGE term specifies the language in which the external procedure was written.

      LIBRARY externProcedures
      NAME "find_max"
        x long,                  -- stores value of x
        x_INDICATOR short,       -- used to determine if x is a NULL value
        y long,                  -- stores value of y
        y_INDICATOR short,        -- used to determine if y is a NULL value
      RETURN INDICATOR short );  -- need to pass pointer to return value's
                                 -- indicator variable to determine if NULL
    -- This means that my function will be defined as:
        -- long max(long x, short x_indicator,
        -- long y, short y_indicator, short * ret_indicator)

Restricting Library-Related Privileges to Trusted Users Only

The CREATE LIBRARY, CREATE ANY LIBRARY, ALTER ANY LIBRARY, and EXECUTE ANY LIBRARY privileges, and grants of EXECUTE ON library_name convey a great deal of power to users. If you plan to create PL/SQL interfaces to libraries, only grant the EXECUTE privilege to the PL/SQL interface. Do not grant EXECUTE on the underlying library. You must have the EXECUTE privilege on a library to create the PL/SQL interface to it. However, users have this privilege implicitly on libraries that they create in their own schemas. Explicit grants of EXECUTE ON library_name are rarely required. Only make an explicit grant of these privileges to trusted users, and never to the PUBLIC role.

Executing an External Procedure

To run an external procedure, you must call the PL/SQL program unit (that is, the alias for the external function) that registered the external procedure. These calls can appear in any of the following:

  • Anonymous blocks

  • Standalone and packaged subprograms

  • Methods of an object type

  • Database triggers

  • SQL statements (calls to packaged functions only)

In "Registering an External Procedure", PL/SQL function PLS_MAX registered external procedure find_max. Follow these steps to run find_max:

  1. Call PL/SQL function PLS_MAX from a PL/SQL routine named UseIt:

                    a integer;
                    b integer;
                    c integer;
                    a := 1;
                    b := 2;
                    c := PLS_MAX(a,b);
                    dbms_output.put_line('The maximum of '||a||' and '||b||' is  '||c);
  2. Run the routine:


Overview of Multithreaded Agent Architecture

An agent process is started for each session to access a system at the same time leading to several thousand agent processes concurrently. The agent processes operate regardless of whether each individual agent process is actually active at the moment. Agent processes and open connections can consume a disproportionate amount of system resources. This problem is addressed by using multithreaded agent architecture.

The multithreaded agent architecture uses a pool of shared agent threads. The tasks requested by the user sessions are put in a queue and are picked up by the first available multithreaded agent thread. Because only a small percentage of user connections are active at a given moment, using a multithreaded architecture allows for more efficient use of system resources.

About Debugging External Procedures

Usually, when an external procedure fails, its C prototype is faulty. That is, the prototype does not match the one generated internally by PL/SQL. This can happen if you specify an incompatible C data type. For example, to pass an OUT parameter of type REAL, you must specify float *. Specifying float, double *, or any other C data type will result in a mismatch.

In such cases, you might get a lost RPC connection to external procedure agent error, which means that agent extproc terminated abnormally because the external procedure caused a core dump. To avoid errors when declaring C prototype parameters, refer to Oracle Database Data Cartridge Developer's Guide.


To help you debug external procedures, PL/SQL provides utility package DEBUG_EXTPROC. To install the package, run script dbgextp.sql, which you can find in the PL/SQL demo directory.

To use the package, follow instructions in dbgextp.sql. Your Oracle Database account must have EXECUTE privileges on the package and CREATE LIBRARY privileges.

To debug external procedures:

  1. From Windows Task Manager, in the Processes dialog, select ExtProc.exe.

  2. Right click, and select Debug.

  3. Click OK in the message window.

    If you have built your DLL in a debug fashion with Microsoft Visual C++, then Visual C++ is activated.

  4. In the Visual C++ window, select Edit > Breakpoints.

    Use the breakpoint identified in dbgextp.sql in the PL/SQL demo directory.

    See Also:

About Accessing Text Files with UTL_FILE

Package UTL_FILE allows your PL/SQL programs to read and write operating system text files. It provides a restricted version of standard operating system stream file I/O, including open, put, get, and close operations. When you want to read or write a text file, you call the function fopen, which returns a file handle for use in subsequent procedure calls. For example, the procedure put_line writes a text string and line terminator to an open file, and the procedure get_line reads a line of text from an open file into an output buffer.

FSEEK, a UTL_FILE subprogram, adjusts the file pointer forward or backward within the file by the number of bytes specified. In order for UTL_FILE.FSEEK to work correctly, the lines in the file must have platform-specific line terminator characters. On Windows platforms the correct line terminator characters are <CR><LF>.

See Also:

Oracle Database Development Guide