• Introduction
• Connect to Oracle
• Setup ODBC
• Troubleshooting
• More Information

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]

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.

   • Follow the same procedure used for Oracle Easy Connect: Setup the Windows Oracle OCI drivers.
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:
         
      2. Browse and choose the folder containing the instantclient_X_Y folder where you installed the OCI drivers and click Extract:
         
      3. You should see the ODBC driver files in the instantclient_X_Y folder, something like this:
         
   3. Open a command prompt as administrator:
      1. Search for “cmd”.
      2. Right click Command Prompt and Choose Run 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):
     
   • Right Click and choose Run as Administrator:
     
4. Open the System DSN tab and click Add:
   
5. Choose the latest Oracle ODBC driver and click Finish:
   
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:
     
7. Test the data source connection:
   • Click Test Connection:
     
   • Enter your password and click OK:
     
   • And if it works you are good to go:
   • Click OK to create the ODBC data 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]

• Using Database Connection Objects
• Connect to Oracle with Easy Connect
• Connect to Oracle with OCI
• Connect to Oracle from Linux or Mac with ODBC