Connect to Oracle from Windows with ODBC

Introduction

This article explains how to connect to Oracle with ODBC when using Windows 10.

It covers the following topics:

  1. How to connect to Oracle (with code samples).
  2. How to setup ODBC:
    • Configure ODBC using the ODBC Data Source Manager:

      We recommend using system DSNs (available to all users).

Connect to Oracle [top]

We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples.

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 ODBC:
    • 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_ODBC, 
      name='your_odbc_server_name',
      user='your_login',
      password='secret',
      use_unicode = true,
      live = true
   }

   conn:execute{sql='SELECT * FROM <your table>', live=true}
end
    • The name is the name of an ODBC source that you create using the ODBC Administrator.
  3. Adapt the code to your requirements.

Setup ODBC [top]

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.

To set up a new ODBC data source for Oracle Server:

  1. Setup the Oracle instant client OCI drivers:

    The Oracle ODBC package depends on the OCI drivers — so you need to set them up first.

  2. Install the latest Oracle ODBC drivers:
    1. Download the latest ODBC zip package from the Oracle instant client download site:
      • Choose the latest Development and Runtime – optional packages > ODBC Package
    2. Unzip the ODBC zip package into the instantclient_X_Y folder where you installed the OCI drivers:
      1. Select the file in Explorer and choose Extract All:
        extract all files
      2. Browse and choose the folder containing the instantclient_X_Y folder where you installed the OCI drivers and click Extract:
        extract oracle ODBC
      3. You should see the ODBC driver files in the instantclient_X_Y folder, something like this:
        view oracle odbc drivers
    3. Open a command prompt as administrator:
      1. Search for “cmd”.
      2. Right click Command Prompt and Choose Run as administrator:
        run cmd as administrator
    4. Terminal Command: Install ODBC: 
      cd c:\Oracle\instantclient*
odbc_install.exe
  3. Open the 64 bit ODBC Administrator:

    Windows 10 (64 bit version) supports 32 and 64 bit ODBC sources — always use the 64 bit ODBC Administrator.

    • Search for ODBC and choose ODBC Data Sources (64 bit):
      search for ODBC
    • Right Click and choose Run as Administrator:
      run as administrator
  4. Open the System DSN tab and click Add:
    odbc system dsn
  5. Choose the latest Oracle ODBC driver and click Finish:
    select oracle driver
  6. Enter the ODBC credentials:
    • Use any Data Source Name and Description you prefer.
    • Enter the Network Name (or IP) of the Oracle Server in the TCP/IP Server field:
      Note: If you are unsure of this ask your DBA (database administrator).
    • Enter the Oracle service name in TNS Service Name:
      Note: If you are unsure of this ask your DBA (database administrator).
    • Enter the user login in the User ID field:
      Note: If you are unsure of this ask your DBA (database administrator).
    • Optional: Choose your default Database:
      odbc oracle credentials
  7. Test the data source connection:
    • Click Test Connection:
      test oracle odbc connection
    • Enter your password and click OK:
      test oracle odbc connection
    • And if it works you are good to go:
    • Click OK to create the ODBC data source:
      create oracle ODBC source
  8. If the connection does not work speak to your DBA (database administrator) about how to login to the database (user authentication).

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.

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

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

    Login authentication errors:

    Authentication errors usually relate to how the Oracle Server you are connecting to is setup. You will need to talk to the DBA (database administrator) that manages the Oracle server.

  • Using integrated security:

    If you wish to use Windows integrated security speak to your DBA (database administrator).

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

More Information [top]

Tagged: