Connecting Excel to Oracle

Devart Excel Add-in for Oracle allows you to connect Excel to Oracle databases, retrieve and load live Oracle data to Excel, and then modify these data and save changes back to Oracle. Here is how you can connect Excel to Oracle and load Oracle data to Excel in few simple steps.



To start linking Excel to Oracle, on the ribbon, click the DEVART tab and then click the Get Data button. This will display the Import Data wizard, where you need to create Excel Oracle connection and configure query for getting data from Oracle to Excel:

Oracle Excel add-in


1. Specify Connection Parameters

To connect Excel to Oracle database, you need to enter the necessary connection parameters in the Connection Editor dialog box. Two connection modes can be used in Excel Add-in for Oracle connections. The Direct connection mode allows connecting Oracle to Excel without any additional software. The OCI connection mode requires Oracle Client installed. The required connection parameters are different for different connection modes.

Direct Connection Mode

The following parameters are used for connecting Excel to Oracle database the Direct connection mode

  • Host - the DNS name or IP address of the Oracle server to which to connect. It also can accept a TNS descriptor or specify a secure protocol to use and optionally, a port after a colon.
  • SID - the unique name for an Oracle database instance.
  • Port - the number of a port to communicate with listener on the server. The default value is 1521.
  • User Id - your Oracle user name.
  • Password - your Oracle password.
  • Database - the name of SQL database to connect to Excel.
  • Connect as - allows opening a session with administrative privileges.

Direct mode also supports secure SSH and SSL connections. To enable use of SSH or SSL, you need to add the corresponding prefix to the Host parameter - ssh:// for the SSH protocol and tcps:// for SSL. Then you need to specify connection string parameters for the corresponding protocol in the Advanced parameters.

Excel Oracle connection

OCI Connection Mode

To use the OCI connection mode for connection to an Oracle database, you should have Oracle Client software installed on your PC. Clear the Direct check box to work with Oracle Client.

In this mode the SID and Port settings are not used, and you need to specify the Oracle Home to use instead. Besides, in the Client mode, the Host parameter must specify the name of TNS alias of Oracle database to which to connect instead of the IP address or DNS name of the server. Specify the Oracle Client you want to be used in the Home connection option.

Advanced Connection Parameters

If you need to configure your Excel Oracle connector in more details, you can optionally click the Advanced button and configure advanced connection parameters. There you can configure secure SSH and SSL connections for the Direct made, fixed char data types trimming, Oracle proxy authentication (for the OCI mode only), Unicode settings, etc.

To check whether you have connected Excel to Oracle correctly, click the Test Connection button.


2. Select whether to Store Connection in Excel Workbook

You may optionally change settings how the connection and query data are stored in the Excel workbook and in Excel settings:


3. Configure Query to Get Data

To import data from Oracle to Excel, you may either use Visual Query Builder to configure a query visually, or switch to the SQL Query tab and type the SQL Query. To configure query visually, do the following:

  1. In the Object list select the Oracle table to load its data to Excel.

  2. In the tree below clear check boxes for the columns you don't want to import data from.

  3. Optionally expand the relation node and select check boxes for the columns from the tables referenced by the current table's foreign keys to add them to the query.

  4. In the box on the right you may optionally configure the filter conditions and ordering of the imported data and specify the max number of rows to load from Oracle to Excel. For more information on configuring the query you may refer to our documentation, installed with the Excel Add-ins.

After specifying the query, you may optionally click Next and preview some of the first returned rows. Or click Finish and start data loading.

Import data from Oracle to Excel


Editing Live Oracle Data

After the data is loaded from Oracle to Excel spreadsheet, you can work with these data like with usual Excel worksheet. You can instantly refresh data from Oracle by clicking Refresh on the Devart tab of the ribbon, and thus, always have fresh live data from Oracle in your workbook.

If you want to edit Oracle data in Excel and save changes made in Excel to Oracle database, you need to click Edit Mode on the Devart tab of the ribbon first. Otherwise, the changes you make cannot be saved to Oracle.

After you start the Edit mode, you can edit the data as you usually do it in excel - delete rows, modify their cell values. Columns that cannot be edited in Oracle, will have Italic font, and you cannot edit values in these columns. To add a new row, enter the required values to the last row of the table that is highlighted with green.

Edit Oracle data in Excel


To apply the changes to actual data in the database, click the Commit button. Or click Rollback to rollback all the changes. Please note that the changes are not saved to the database till you click Commit, even if you save the workbook.