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

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

Faced with the need to organize the work of libraries used previously by 32 bits, Oracle found that in the new version of Oracle, this aspect was greatly changed in contrast to previous versions.


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 from previous versions of Oracle:
• extproc32.exe - now not used;
• Snap Client tool now needs to be installed in a separate ORACLE_HOME;
• A new extproc configuration file has appeared -% ORACLE_HOME% \ hs \ admin \ extproc.ora;
• LIST log files are located at% ORACLE_HOME% \ diag \ tnslsnr \ [hostname].

Let's get down to setting up Oracle

The first thing you need to do is perform a selective installation of the 32-bit Oracle Instant Client. Simply select to install: Oracle Database Utilities and Oracle Net Listener. As the installation path, I installed: [disk]: \ oracle \ product \ 11.2.0 \ client_32. At the end of the installation, Net Configuration Assistant will be automatically launched - which is recommended to use in order to avoid possible errors later, such as using the netca utility that will be launched to configure the listener (LISTNER) from ORACLE_HOME by default, and it will be 64-bit.

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

• In the configuration window, select the item: Listener configuration and further
• Select Add and on.
• Set the name LISTENER_32 and on.
• Remove the TCP protocol from the Selected Protocols column and add the IPC protocol and further
• Enter in the IPC Key field: IPC_EXT32 and further
• Refuse to configure another listener and further, further, ready

After completing the Net Configuration Assistant, you can check the generated configuration file of the listener% ORACLE_HOME% \ client_32 \ NETWORK \ ADMIN \ listener.ora, it should look like the example, except for the path 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 

To complete the configuration of the listener, you must configure the services by executing, for example, services.msc. The newly created listener does not start automatically by default, which is logical to fix.
This includes adding 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 configuration file extproc.ora. This file must be configured in both 32-bit and 64-bit Oracle instances. The configuration file is equipped with a completely exhaustive 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: [MY_PATH \ my.dll] option.

Oracle Library Configuration Considerations

For 64-bit libraries, everything remains without any changes, but to tell Oracle which libraries you need 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 the example, we will modify the library object creation script:

 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 library configuration features 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 absent in the environment variables, and the Path environment variable contains the path to the 64-bit database instance, and then to the 32-bit client instance.