Connect to oracle db from windows

• 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:Oracleinstantclient*
      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.
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:
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

Oracle Database is one of the foremost DBMS (Database Management Systems) for Windows. However, Windows 10 doesn’t include any built-in Oracle ODBC connection tool.

So, you’ll need to install an Oracle ODBC driver to connect to an Oracle database with an ODBC-compliant application.

You basically have to do a bit more than simply download and install an ODBC package to connect to an Oracle database with an application.

On top of that, you’ll also need to add new environment variables and set up a DSN for the driver before you can connect with the Oracle database.

How can I install an Oracle ODBC driver in Windows 10?

1. Download and extract the Oracle packages 

1. First, open the Oracle Instant Client page in your browser.
   
2. Next, click the Instant Client for Microsoft Windows 32 or 64-bit link (or an alternative one that matches your PC’s platform).
3. Click the Basic package download link.
4. Scroll down and click the SDK package hyperlink to download that archive.
5. In addition, click the download link for the ODBC package.
6. Open the File Explorer window by clicking the folder taskbar button for it.
7. Click the C: root drive in File Explorer.
8. Then right-click a space within the C: drive and select New > Folder.
   
9. Enter Oracle as the new folder name. Then you should have a new folder with this path: C:Oracle
10. Move all three of the downloaded Oracle ZIP archives into the Oracle folder. You can do that by selecting the ZIP files, clicking the Move to button, and selecting Choose location within File Explorer.
11. Next, extract all three of the ZIP archives into the Oracle folder. To extract a ZIP, select it and click the Extract all button on File Explorer’s Compressed Folder Tools tab.
    
12. Then you can click the Browse button in the Extract Compressed window to select the Oracle folder. Click the Extract button.
13. Open the extracted instantclient-basic-windows.x64-19.11.0.0.0dbru folder.
14. Move the instantclient_19_11 subfolder from there into the Oracle folder.
15. Then erase the now empty instantclient-basic-windows.x64-19.11.0.0.0dbru folder that previously included the instantclient_19_11 subfolder by right-clicking it and selecting Delete. Thereafter, you’ll have a single instantclient_19_11 folder within the Oracle directory.

The ODBC and basic ZIP archives must be unzipped to exactly the same folder. Select to extract the ODBC ZIP to the path of your unzipped Basic package.

Delete instantclient_19_11 at the end of the path in the Extract Compressed window so that an additional subfolder isn’t included. 

The SDK package is a non-essential optional one. However, it’s recommended you download that package too.

The SDK ZIP archive should also be extracted to the same instantclient_19_11 folder that includes the Basic and ODBC package files. In case that did not help, you can give a try to DriverFix that has a big database of drivers and might have the right ones for Oracle, besides that, updating your drivers is a common good practice of maintaining a functional PC.

2. Add an Oracle Client directory environment variable

1. Press the Windows and S keys simultaneously to open a search box.
2. Type the keyword system environment variables with the search text box.
3. Click Edit the system environment variables to open the window in the screenshot directly below.
   
4. Press the Environment Variables button.
   
5. Click the New button for system variables.
6. Next, input OCI_LIB32 within the Variable name box.
7. Input your Oracle folder path in the Variable value box. If you followed the guidelines above, that folder path would be this: C:Oracleinstantclient_19_11.
   
8. Next, Double-click Path in system variables to open the window in the snapshot directly below.
   
9. Click the New button, and enter the same folder path you input within the Variable value box.
10. Press the OK button.

3. Install your ODBC Oracle client package

To install the ODBC client, open the instantclient_19_11 folder you extracted the ODBC package to in File Explorer. Then right-click odbc_install.exe and select the Run as administrator option.

Note that there isn’t any kind of GUI installation wizard for that file. So, that’s all you need to do here.

4. Set up the DSN for Oracle driver

1. Bring up the search tool within Windows.
2. Enter ODBC Data Sources in the search box.
3. Click ODBC Data Sources to open the window in the screenshot directly below.
   
4. Next, click the Add button on the User DSN (or System) tab to open the Create New Data Source window.
   
5. Select your Oracle ODBC driver, and press the Finish button.
6. Fill out the required details on the ODBC configuration window.
7. Click Test Connection to check your Oracle Database Server connection.
8. Press the OK button to exit.

5. Install missing Visual C++ libraries

You can’t set up data sources for an Oracle driver with the ODBC Data Source Administration if a required Visual C++ package for it is not installed on your PC.

If an error message pops up when your try to set up an Oracle driver data source, you might be missing a required Visual C++ package.

You’ll need to have Visual Studio 17 installed for the current Oracle 19 client. You can download Visual Studio 2017, and older redistributable packages, via the C++ downloads page.

Then click the VC EXE file you downloaded to the install Visual Studio package. So, that’s how you can install the Oracle ODBC driver on Windows 10.

When you’ve installed the current Oracle ODBC driver, you’ll be able to connect to Oracle Database DBMS versions from 11.2 up to 19.1 with the 19c client.

Moreover, if you need to fix Windows 10 ODBC issues, just check out our dedicated article. As for your opinion, the comments area is waiting for you.

 Summary: in this tutorial, you will learn how to connect to Oracle database server using SQL*plus and SQL developer tools.

Connect to Oracle Database Server using SQL*Plus

SQL*Plus is an interactive query tool installed automatically when you install Oracle Database Server or Client. SQL*Plus has a command-line interface that allows you to connect to Oracle Database server and execute statements interactively.

If you have worked with MySQL or PostgreSQL, SQL*plus is similar to mysql program in MySQL or psql in PostgreSQL.

To launch the SQL*Plus program, from the Linux or Window terminal, you enter the sqlplus command:

sqlplus
Code language: SQL (Structured Query Language) (sql)

You can also find the SQL*Plus program in the Program folder of the Start Menu on Windows as shown below:

When you launch the SQLPlus icon, it will prompt for username and password. Enter your username and password that you chose during the installation of the Oracle Database Server. If you don’t know which account to use, ask your Database Administrator.

Suppose you want to connect to the local Oracle Database Server using the sys account, you enter the following information:

Enter user-name: sys as sysdba
Enter password:
Code language: SQL (Structured Query Language) (sql)

After pressing enter, you should see a message followed by the SQL> command line as follows:

SQL>_
Code language: SQL (Structured Query Language) (sql)

It means that you have connected to the Oracle Database Server.

In Oracle 12c, when you connect to the Database Server, the default database is the ROOT container database with the name CDB$ROOT. To display database name, you use the SHOW command:

SQL> SHOW con_name;

CON_NAME
------------------------------
CDB$ROOT
Code language: SQL (Structured Query Language) (sql)

If you want to switch to a pluggable database, use the ALTER SESSION statement to set the current database to the pluggable database e.g., PDBORDL as follows:

SQL> ALTER SESSION SET CONTAINER = PDBORCL;

Session altered.
Code language: SQL (Structured Query Language) (sql)

Now, you connected to the PDBORCL database.

To disconnect a user from the Oracle Database Server, you use the EXIT command:

SQL> EXIT
Code language: SQL (Structured Query Language) (sql)

To connect the OT user to the sample database located in the PDBORCL pluggable database, you enter the following command:

>sqlplus OT@PDBORCL
Code language: SQL (Structured Query Language) (sql)

SQL*Plus prompts for the password of the OT user. Enter the password and you will be connected to the PDBORCL database in the Oracle Database Server.

Connect to Oracle Database Server using SQL Developer

SQL Developer is a free GUI tool for working with SQL in Oracle Databases. Like the SQL*Plus program, SQL Developer is installed automatically when you install the Oracle Database Server or Client.

To launch the SQL Developer program, you click the SQL Developer icon in the Oracle Program folder in Start Menu as shown in the following picture:

The following shows the SQL Developer program:

To create a new database connection, (1) first, click the New button or press Ctrl-N, and then (2) choose Database Connection option and click the OK button.

The following New / Select Database Connection dialog will display:

In this dialog, you need to enter the following information:

First, enter the following information:

• A connection name. It can be any name you like, we used OT as shown in the dialog.
• Username and its password, which is  ot/Orcl1234 in this case.
• Save password by checking the Save Password checkbox.

Second, choose TNS as the connection type.

Third, the Network Alias list will be displayed after you choose the TNS connection type. Just select the PDBORCL as the network alias. By doing this, you explicitly specify the pluggable database PDBORCL to which you want to connect.

Fourth, click the Test button to test the database connection. If you see status: Success message, you are ready to connect to the Oracle Database Server.

To save time re-entering all the connection parameters again, click the Save button. The connection will be saved and displayed on the left side of the dialog.  Whenever you want to reconnect, you just need to click the saved connection, all the parameters will be filled out automatically.

Now, you can click the Connect button to connect to the Oracle Database Server.

Double click the Connections > OT > Tables, you will see all tables in the OT sample database.

In this tutorial, you have learned how to connect to the Oracle Database Server using SQL*Plus and SQL Developer.