Oracle ODBC for Windows

This tutorial shows how to set up a data source and connect to an Oracle^® database using the Database Explorer app or the command line. This tutorial uses the OraClient11g_home1 ODBC driver to connect to an Oracle 11g Enterprise Edition database.

Step 1. Verify the driver installation.

The ODBC driver is typically preinstalled on your computer. For details about the driver installation or troubleshooting the installation, contact your database administrator or refer to your database documentation on ODBC drivers. For information about the Microsoft^® ODBC Data Source Administrator, see Driver Installation.

Note:

Database Toolbox™ no longer supports connecting to a database using a 32-bit driver. Use the 64-bit version of Oracle. If you have issues working with the ODBC driver, use the JDBC driver instead. For details, see Oracle JDBC for Windows. For details about working with the 64-bit version of Windows^®, see Using Previous MATLAB Releases.

Step 2. Set up the data source using the Database Explorer app.

The Database Explorer app accesses the Microsoft ODBC Data Source Administrator automatically when you configure an ODBC data source. Alternatively, you can access the Microsoft ODBC Data Source Administrator using the configureODBCDataSource function.

Open the Database Explorer app by clicking the Apps tab on the MATLAB^® Toolstrip. Then, on the right of the Apps section, click the Show more arrow to open the apps gallery. Under Database Connectivity and Reporting, click Database Explorer. Alternatively, enter databaseExplorer at the command line.
In the Data Source section, select Configure Data Source > ODBC.
In the ODBC Data Source Administrator dialog box, you define the ODBC data source.
Tip
When setting up an ODBC data source, you can specify a user data source name (DSN) or a system DSN. A user DSN is specific to the person logged into a machine. Only this person sees the data sources that are defined on the user DSN tab. A system DSN is not specific to the person logged into a machine. Any person who logs into the machine can see the data sources that are defined on the system DSN tab. Your ability to set up a user DSN or system DSN depends on the database and ODBC driver you are using. For details, contact the database administrator or refer to the ODBC driver documentation.
Click the System DSN tab, and then click Add.
The Create New Data Source dialog box opens and displays a list of installed ODBC drivers.
Select the ODBC driver Oracle in OraClient11g_home1.

Note
The name of the ODBC driver can vary.
Click Finish.
In the Oracle ODBC Driver Configuration dialog box, enter an appropriate name for the data source. You use this name to establish a connection to your database. Here, in the Data Source Name box, enter ORA as the data source name. In the Description box, enter a description for this data source, such as Oracle database. In the TNS Service Name box, enter the name of your database.
You can set up an ODBC data source with or without Windows authentication.
To establish the data source without Windows authentication, enter your username in the User ID box. Or, to establish the data source with Windows authentication, leave this box blank. Leave the Application, Oracle, Workarounds, and SQLServer Migration tabs with the default settings.
Click Test Connection to test the connection to your database. The Oracle ODBC Driver Connect dialog box opens. If you are establishing the data source with Windows authentication, the Testing Connection dialog box opens.
Enter your password in the Password box. Your database name and username are automatically entered in the Service Name and User Name boxes. Click OK. If your computer successfully connects to the database, the Testing Connection dialog box displays a message indicating the connection is successful. Click OK.
Click OK in the Oracle ODBC Driver Configuration dialog box. The ODBC Data Source Administrator dialog box shows the ODBC data source ORA.
After you complete the data source setup, connect to the Oracle database using the Database Explorer app or the command line with the native ODBC connection.

Step 3. Connect using the Database Explorer app or the command line.

Connect to Oracle Using Database Explorer App

On the Database Explorer tab, in the Connections section, click Connect and select the data source for the connection.
If you create a connection with Windows authentication, leave the Username and Password boxes blank in the connection dialog box, and click Connect. Otherwise, enter a username and password, and click Connect.
The Catalog and Schema dialog box opens.
In the Schema list, select the schema. Click OK.
The app connects to the database and displays its tables in the Data Browser pane. A data source tab appears to the right of the pane. The title of the data source tab is the data source name that you defined during the setup. The data source tab contains empty SQL Query and Data Preview panes.
Select tables in the Data Browser pane to query the database.
Close the data source tab to close the SQL query. In the Connections section, close the database connection by clicking Close Connection.
Note
If multiple connections are open, close the database connection of your choice by selecting the corresponding data source from the Close Connection list.

Connect to Oracle Using ODBC Driver and Command Line

To connect with Windows authentication, connect to the database with the authenticated ODBC data source name and a blank username and password. For example, this code assumes that you are connecting to a data source named Oracle_Auth.
```
conn = database('Oracle_Auth','','');
```
To connect without Windows authentication, connect to the database with the ODBC data source name and specify the username and the password. For example, this code assumes that you are connecting to a data source named Oracle.
When specifying your user credentials, the recommended practice is to store credentials in your MATLAB vault using setSecret instead of including them in your code. Retrieve your credentials by using the getSecret function.
Before R2024a: setSecret and getSecret are not available. Specify your username and password using character vectors or strings.
```
setSecret("usernameoracle");
setSecret("passwordoracle");
conn = database('Oracle',getSecret("usernameoracle"),getSecret("passwordoracle"));
```
Close the database connection.
```
close(conn)
```