• Introduction
• Connect to Oracle
• Setup the Oracle OCI drivers
• Setup the OCI Connection
  
• Troubleshooting
• More Information

Introduction

This article explains how to connect to Oracle using OCI. This method will work with all operating system. We strongly recommend using Oracle Easy Connect (EZCONNECT) instead because it is much easier to setup.

It covers the following topics:

1. How to connect to Oracle (with code sample).
2. How to setup the required Oracle OCI drivers.
3. How to setup the OCI connections.
   

Connect to Oracle [top]

We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples. The EZCONNECT code sample will work with all operating systems.

Follow these steps to connect:

1. Create a new channel to try out the code:
   • Create a channel like this:
     • Name: DB Oracle Connection (or similar)
     • Source: From Translator
     • Destination: To Channel
   • Alternatively use an existing channel:

     Your channel will need a From/To Translator or a Filter component.

2. Connect to a database using Easy Connect:
   • Paste this code into the Translator:

     If your script uses connection object methods conn:query{} or conn:execute{}to connect to a remote Oracle database, the location of the remote host is specified as part of the name parameter in the db.connect{ } command. If you use conn:merge{} to connect the remote host is specified as part of the name parameter in the conn:merge{} command itself.

     function main()
           local conn = db.connect{   
           api=db.ORACLE_OCI, 
           name='Oracle OCI name',
           user='your_login', 
           password='secret',
           use_unicode = true,
           live = true
        }
     
        conn:execute{sql='SELECT * FROM <your table>', live=true}
     end

   • The name is an Oracle OCI name.
     Note: Oracle documentation calls this the tns_alias (or net_service_name).
3. Adapt the code to your requirements.

Setup the Oracle OCI drivers [top]

This procedure installs the the Oracle OCI drivers globally, which means that the drivers will be available for all users on the machine.

We recommend installing the Oracle OCI drivers (and other database drivers) in the correct/default global driver directories for your operating system. However it is possible to install drivers locally (for a single user) if you wish to restrict access.

The name of the OCI driver library depends on the operating system:

1. 1. Follow the same procedure as used when setting up OCI for Oracle Easy Connect:
      • Setup the Oracle OCI drivers
   2. Create the network admin directory:

      This is the default Oracle client configuration directory specified by Oracle for applications linked using Instant Client. This directory will be created inside the directory where you installed the Instant Client drivers. This directory is where we will create the OCI configuration files tnsnames.ora and sqlnet.ora.

      1. Windows:
         • Use File Explorer to create \network\admin in the instantclient_X_Y folder, like this:
           
      2. Linux:
         • Terminal Commands: Create /network/admin in the instantclient_X_Y folder:

           cd /usr/lib/instantclient*
           sudo mkdir -p network/admin

         • Terminal Command: Change to the directory to ensure that it was created:

           cd /usr/lib/instantclient*/network/admin

           
           Note: This screenshot is from Ubuntu, and will differ from other Linux distributions.

      3. Mac:
         • Terminal Commands: Create /network/admin in the instantclient_X_Y folder:

           cd /usr/local/instantclient*
           sudo mkdir -p network/admin
           

         • Terminal Command: Change to the directory to ensure that it was created:

           cd /usr/local/instantclient*/network/admin

           

         • Alternatively you can create the directories with finder, use Go>Go To Folder to show /usr/local and then add the directories:
           
      4. Alternatively you can store the OCI configuration files (tnsnames.ora and sqlnet.ora) in another directory, and set the TNS_ADMIN environment variable to point to that directory.

Setup the OCI connection [top]

We use the same examples here as do in the Create an EZCONNECT String section of the Connect to Oracle with Easy Connect page

1. Get the following information:
   • username: Login user name for the Oracle server

     If the username and password are left blank then integrated security is used — this requires kerberos. You should speak to your DBA (database administrator) and/or your system/network administrator about if you have questions (about setting up permissions for) integrated users.

   • password: Password for the user
   • host: This name of the Oracle database server

     This must be a valid DNS name or IP address for the Oracle database server.

   • (Optional) port:

     If the port is not included then it defaults to 1521. In general if you are not given a port number then the default port (1521) should work.

   • service name:

     The service name (or database service name) is the “logical database name” that Oracle uses to access a database – it is equivalent to the database name in other database systems.

   • (Optional) server:

     Never set this unless asked to by your DBA (database administrator).

   • (Optional) instance_name:

     Never set this unless asked to by your DBA (database administrator).

2. Create the TNS Alias OCI configuration entry:
   • The content of tnsnames.ora file we will use takes this general format:

     There are many other options for a tnsnames.ora file, but this is about the simplest format required for connecting to a single database.

     <tns_alias>=
        (DESCRIPTION=
           (ADDRESS=(PROTOCOL = TCP)(HOST = hostname)(PORT = portnum))
           (CONNECT_DATA=
              (SERVICE_NAME= <service_name>)))

   • For example:
     • username: FredSmith
     • password: secret-password
     • host: Oracle-Server
     • port: 3351
     • service name: Test-DB
     • tns_alias: Test (or any other name you choose)
       Note: sometimes also called net_service_name
     • EZCONNECT: FredSmith/secret-password@Oracle-Server:3351/Test-DB
     • tnsnames.ora:

       Notice that the username and password are not used in tnsnames.ora — instead they are supplied when you connect to the database (as parameters to db.connect).

       Test=
          (DESCRIPTION=
             (ADDRESS=(PROTOCOL = TCP)(HOST = Oracle-Server)(PORT = 3351))
             (CONNECT_DATA=
                (SERVICE_NAME = Test-DB)))

   • Another example:

     This example is based on the Oracle DB Developer VM that can be downloaded from Oracle.

     • username: hr
     • password: oracle
     • host: oracle-linux
     • port: not specified so use the default of 1521
     • service name: orcl
     • tns_alias: orcl-db (or any other name you choose)
       Note: Sometimes also called net_service_name
     • EZCONNECT: hr/oracle@oracle-linux/orcl
     • tnsnames.ora:

       Notice that the username and password are not used in tnsnames.ora — instead they are supplied when you connect to the database (as parameters to db.connect).

       orcl-db=
          (DESCRIPTION=
             (ADDRESS=(PROTOCOL=TCP)(HOST=oracle-linux)(PORT=1521))
             (CONNECT_DATA=
                (SERVICE_NAME=orcl)))

   • Append your TNS Alias entry to your tnsnames.ora file in the network admin directory:
     Note: If the tnsnames.ora file does not already exist you will need to create it.

     The network admin directory will be in a different place depending on your operating system — see the section for your OS in Setup the Oracle OCI drivers above. The probable/default locations below — where <_X_Y> is the version number, for example instantclient_12_2.

     • Windows: c:\Oracle\instanclient_X_Y\network\admin
     • Linux: /usr/lib/instanclient_X_Y/network/admin
     • Mac: /usr/local/instanclient_X_Y/network/admin
3. Create the sqlnet.ora OCI configuration file:

   This tells the Oracle client to time out if a connection to the Oracle server cannot be obtained within the specified number of seconds (the example uses 5 seconds). The default is to never time out, which can cause problems if the server is down or cannot be reached.

   1. Create your sqlnet.ora file in the network admin directory, containing this command:

      TCP.CONNECT_TIMEOUT=5

4. Using a tns_alias string in Iguana Translator:
   1. Use the db.connect{} API function
   2. name = ‘tns_alias’
   3. user = ‘username’
   4. password = ‘password’
5. For example tns_alias “orcl-db”:

   This is the second example from above and is was based on *the Oracle DB Developer VM.

   • Use the db.connect{} API function
   • name = ‘orcl-db’
   • user = ‘hr’
   • password = ‘oracle’
     

Troubleshooting [top]

• Problems with connection settings:

  Speak to your DBA (database administrator) for general connection issues like: User name and password, Database Server network name etc.

• You get the error “ORA-12154 could not resolve the connect identifier specified”:

  This is a common error when trying to setup an OCI connection, it basically means that the TNS entry for the name of the database cannot be found. There are generally two reasons: One the the tnsnames.ora file cannot be found, the second is that there is no corresponding tns-name entry within the tnsnames.ora file to match the database name you are using. Speak to you DBA (database administrator) if you need help resolving this error.

• Iguana may not always support the very latest version of the OCI drivers:

  Please contact us at support@interfaceware.com if you need to know the latest supported version of the OCI drivers.

• If Iguana cannot find the library automatically:

  This is probably an issue with the symbolic link being in the incorrect directory. Speak to your system administrator and ensure that symbolic link is on the dynamic library search path. Alternatively you can use the Browse button to find the driver manually.

• Not enough permissions to load the Oracle Library:
  

  Speak to your system administrator to ensure you have read/execute permissions for the OCI directories. This error also often occurs if the version of the OCI driver you are using not yet supported by Iguana — often  installing an earlier version of the OCI driver will resolve this error.

If you need more help please contact us at support@interfaceware.com.

More Information [top]

• Using Database Connection Objects
• Connect to Oracle with Easy Connect
• Connect to Oracle from Windows with ODBC
• Connect to Oracle from Linux of Mac with ODBC