HOEKSTRA.CO.UK

Famous Posters

hoekstra.co.uk

Oracle External Procedures

Article Index

Page 4 of 6

 

System configuration

 

The server configuration details that will enable the calling of external procedures from within Oracle are shown below. You might already have something have something similar from a previous installation, in which case you can skip this part.

 

You should ideally have a separate listener for external procedures. Ensure that the KEY and SID values in the tnsnames.ora file correlate to the KEY and SID values in the listener.ora file.

 

The following files reside in the $TNS_ADMIN directory (normally $ORACLE_HOME/network/admin) and should be amended to suit your environment.

 

File $TNS_ADMIN/listener.ora:

 

  1. # SET ORACLE_HOME TO your CURRENT environment
  2. SID_LIST_LISTENER =
  3.   (SID_LIST =
  4.     (SID_DESC =
  5.       (SID_NAME = PLSExtProc)
  6.       (ORACLE_HOME = /opt/oracle/product/10g)
  7.       (PROGRAM = extproc)
  8.       (ENVS="EXTPROC_DLLS=ANY")
  9.     )
  10.   )
  11.  
  12. # SET HOST = 0.0.0.0 TO compensate FOR a bug in 10.1.0.2 RELEASE OF the
  13. # Oracle listener - normally SET it TO DNS host name
  14. LISTENER =
  15.   (DESCRIPTION_LIST =
  16.     (DESCRIPTION =
  17.       (ADDRESS_LIST =
  18.         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
  19.       )
  20.       (ADDRESS_LIST =
  21.         (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  22.       )
  23.     )
  24.   )

 

File $TNS_ADMIN/tnsnames.ora:

 

  1. EXTPROC_CONNECTION_DATA =
  2.   (DESCRIPTION =
  3.     (ADDRESS_LIST =
  4.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
  5.     )
  6.     (CONNECT_DATA =
  7.       (SID = PLSExtProc)
  8.       (PRESENTATION = RO)
  9.     )
  10.   )
  11.  
  12. # Instance descriptions as per this example
  13. SID_NAME =
  14.   (DESCRIPTION =
  15.     (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1)(PORT = 1521))
  16.     (CONNECT_DATA =
  17.       (SERVER = DEDICATED)
  18.       (SERVICE_NAME = sid_name)
  19.     )
  20.   )

 

Configuration for two instances

 

File $TNS_ADMIN/listener.ora:

 

  1. # Listener configuration
  2. # This example supports two Oracle instances on server HAPPY
  3. LISTENER =
  4.   (DESCRIPTION_LIST =
  5.     (DESCRIPTION =
  6.       (ADDRESS_LIST =
  7.         (ADDRESS = (PROTOCOL = TCP)(HOST = happy)(PORT = 1524))
  8.       )
  9.     )
  10.   )
  11.  
  12. CALLOUT_LISTENER =
  13.  (ADDRESS_LIST =
  14.     (ADDRESS =
  15.        (PROTOCOL = IPC)
  16.        (KEY = EXTPROC0)
  17.     )
  18.  )
  19.  
  20. SID_LIST_LISTENER =
  21.   (SID_LIST =
  22.     (SID_DESC =
  23.       (SID_NAME = INSTANCE1)
  24.     )
  25.     (SID_DESC =
  26.       (SID_NAME = INSTANCE2)
  27.     )
  28.   )
  29.  
  30. SID_LIST_CALLOUT_LISTENER =
  31.  (SID_LIST =
  32.     (SID_DESC =
  33.       (SID_NAME = PLSExtProc)
  34.       (ORACLE_HOME = /app/oracle/product/9.2.0)
  35.       (PROGRAM = extproc)
  36.       (ENVS="EXTPROC_DLLS=ANY")
  37.     )
  38.  )

 

 

File $TNS_ADMIN/tnsnames.ora:

 

  1. EXTPROC_CONNECTION_DATA.WORLD=
  2.  (DESCRIPTION=
  3.     (ADDRESS_LIST=
  4.       (ADDRESS= (PROTOCOL=IPC)(KEY=EXTPROC0))
  5.     )
  6.     (CONNECT_DATA=
  7.       (SID=PLSExtProc)
  8.       (PRESENTATION= RO)
  9.     )
  10.  )
  11.  
  12. INSTANCE1.WORLD =
  13.   (DESCRIPTION =
  14.     (ADDRESS_LIST =
  15.       (ADDRESS = (PROTOCOL = TCP)(HOST = happy)(PORT = 1524))
  16.     )
  17.     (CONNECT_DATA =
  18.       (SERVICE_NAME = INSTANCE1)
  19.     )
  20.   )
  21.  
  22. INSTANCE2.WORLD =
  23.   (DESCRIPTION =
  24.     (ADDRESS_LIST =
  25.       (ADDRESS = (PROTOCOL = TCP)(HOST = happy)(PORT = 1524))
  26.     )
  27.     (CONNECT_DATA =
  28.       (SERVICE_NAME = INSTANCE2)
  29.     )
  30.   )

 

File $TNS_ADMIN/sqlnet.ora:

 

You might not have a sqlnet.ora file. If you do, keep it simple and only have the following in it:

 

  1. NAMES.DIRECTORY_PATH= (TNSNAMES,ONAMES,HOSTNAME)

 

or in Oracle release 9 onwards:

  1. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

 

Restart the Oracle listener

 

This will manifest the changes that you made in the listener.ora file:

 

  1. $ su - oracle
  2. $ lsnrctl stop
  3. $ lsnrctl start
  4. $ lsnrctl status 

 

Restart the database (as a last resort)

 

Only do this when none of your tests have succeeded.

 

  1. $ su - oracle
  2. $ sqlplus / as sysdba
  3. SQL> shutdown immediate
  4. SQL> startup

 

Testing

 

You can test the installation of this from within SQLPLUS:

 

  1. $ sqlplus /nolog
  2. SQL> connect / as sysdba
  3. SQL> set autoprint on
  4. SQL> variable i number;
  5. SQL> exec :i:=hostcmd('touch /tmp/test');

 

 - which should return 0 if the command succeeded, and <> 0 on failure.

 

You can test for the file's existance from within SQLPLUS by shelling out (use 'host' instead of '!' when running SQLPLUS from a DOS command line):

 

  1. SQL> ! ls /tmp/test

 

 - which should return:

 

  1. /tmp/test