Call 32-bit external procedures from PL / SQL on Oracle 11g R2 64-bit

I must say right away that now you can use both 32 and 64 bit external procedures at the same time, and having figured out the problem, the configuration is very simple.

Faced with the need to organize the work of the libraries used earlier on the 32th bit, Oracle found that in the new version of Oracle this aspect was greatly changed in contrast to previous versions.


The configuration was performed for the following components:
• Windows 2008 R2 64-bit
• Oracle 11g R2 64-bit
• Oracle Instant Client 11.2.0.2 32-bit

Here is a list of some of the differences between the settings and previous versions of Oracle:
• extproc32.exe - now not used;
• snap-in Instant Client must now be installed in a separate ORACLE_HOME;
• a new extproc configuration file appeared -% ORACLE_HOME% \ hs \ admin \ extproc.ora;
• LISTNER log files are located in -% ORACLE_HOME% \ diag \ tnslsnr \ [hostname].

Let's set up Oracle

The first thing to do is perform a custom installation of 32-bit Oracle Instant Client. It is enough to choose for installation: Oracle Database Utilities and Oracle Net Listener. As the installation path, I installed: [drive]: \ oracle \ product \ 11.2.0 \ client_32. At the end of the installation, the Net Configuration Assistant will automatically start, which is recommended to be used to avoid possible errors later, such as using the netca utility, which will be launched to configure the listener (LISTNER) from ORACLE_HOME by default, and it will turn out to be 64-bit.

I will describe the process of setting up a new listener through Net Configuration Assistant:

• In the configuration window, select: Listener configuration and further
• Select Add and further
• Set the name LISTENER_32 and further
• Remove TCP from the Selected Protocols column and add IPC and further
• Enter IPC Key value in the IPC field: IPC_EXT32 and further
• Refuse configuring another listener and then, ready,

After the Net Configuration Assistant is finished, you can check the generated listener configuration file% ORACLE_HOME% \ client_32 \ NETWORK \ ADMIN \ listener.ora, it should look like the one indicated in the example, with the exception of paths to the Oracle directory:

LISTENER_32 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = IPC_EXT32))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
  )
SID_LIST_LISTENER_32 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = callout32)
      (ORACLE_HOME = [диск]:\oracle\product\11.2.0\client_32)
      (PROGRAM = extproc)
    )
  )
ADR_BASE_LISTENER_32 = [диск]:\oracle

At the end of the configuration of the listener, you need to configure the services by executing, for example, services.msc. A newly created listener does not start automatically by default, which will be logical to fix.
In particular, you need to supplement the tnsnames.ora configuration file, and this can be done in the main instance of the installed database, for example:% ORACLE_HOME% \ dbhome_1 \ NETWORK \ ADMIN \ tnasnames.ora. The configuration file must be supplemented with the following entry:

extproc_connection_data32b =
     (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY = IPC_EXT32))
        (CONNECT_DATA =
        (SID = callout32)
        )
     )

The last thing worth mentioning in the description of the system configuration is the new extproc.ora configuration file. This file must be configured in both 32-bit and 64-bit Oracle instances. The configuration file is equipped with a comprehensive description of the configuration, and I will only give an example of configuration. The simplest is to specify the value of the parameter EXTPROC_DLLS = ANY; or specify the exact path using the ONLY option: [MY_PATH \ my.dll].

Oracle Library Configuration Features

For 64-bit libraries, everything remains without any changes, but to tell Oracle which libraries to run using 32-bit extproc, you need to create a symbolic link and reconfigure the library creation script.

Create a symbolic link:

CREATE DATABASE LINK AGENT_LINK_32B USING 'extproc_connection_data32b';

Using an example, we modify the script for creating the library object:

CREATE OR REPLACE LIBRARY MY_SCHEMA.MY_NAME IS '[диск]:\oracle\product\11.2.0\client_32\BIN\my.dll' AGENT ‘agent_link_32b’;

This is where the features of configuring libraries end.

Setting environment variables

I did not test using environment variables such as TNS_ADMIN, ORACLE_HOME, LD_LIBRARY_PATH and others. In my case, they are not in the environment variables, and the Path environment variable contains in succession the path to the 64-bit database instance, and then to the 32-bit client instance.