Connect to Oracle with OCI

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.

Note: Oracle Easy Connect (EZCONNECT) is much easier to setup — we strongly recommend Easy Connect unless your DBA (database administrator) says otherwise. According to Oracle Easy Connect is not recommended for complex systems.

Quote from the Oracle documentation: “Easy Connect naming is not suitable for large or complex environments with advanced features, such as connection pooling, external procedure calls, or Heterogeneous Services, that require additional connect information. In these cases, another naming method is recommended.”

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:

operating system OCI library Name
Linux libclntsh.so
Mac OS X libclntsh.dylib
Windows oci.dll

Note: Iguana may not always support the very latest version of Oracle drivers, so you can simply download an earlier version if necessary. This is particularly true if you are not able to use the the latest version of Iguana.

If you need to know which versions of Oracle drivers are supported by the version of Iguana you are using please contact us at support@interfaceware.com.

Tip: If you install the Oracle OCI drivers (and other database drivers) in the correct/default global driver directories for your operating system then Iguana can find them “automatically”. This is the preferred method — particularly for production servers.

However you can also install drivers in any location and manually configure Iguana to use them. This may be useful for development and testing.

    1. Follow the same procedure as used when setting up OCI for Oracle Easy Connect:
    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:
          create oracle network folder
      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

          confirm network admin directory
          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

          test admin directory exists

        • Alternatively you can create the directories with finder, use Go>Go To Folder to show /usr/local and then add the directories:
          mac oracle network/admin directory
      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’
      oracle OCI connection

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:
    not enough permissions

    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]

Leave A Comment?