Oracle DB - How To Install the Oracle 19C Full Client, ODBC, and SQL Developer

Objective

Following these steps will install the Oracle Client 19C onto your machine.

Environment

Oracle Client 19C

Before You Begin

You will need to be logged in to MSU's VPN (link) to complete these instructions.

Verify Oracle Client Version

Verify which version of the Oracle client you have installed using the below instructions. If you are using versions 12 through 18 we strongly encourage that you upgrade to the latest version. If you are using any version older than 12, you are required to upgrade to the latest version.

Windows

• Open Command Prompt (CMD) and type "sqlplus /nolog" (without quotations) to display the version of Oracle installed.

macOS

• Open Terminal and type "sqlplus -version" (without quotations). This command will display the SQL*Plus client version, which corresponds to the Oracle client version installed.

Steps

Download Client Packages

1. If you will be using ODBC, you must ensure that the client you download matches the bit architecture of your Microsoft Office installation. To check your version:
   1. Open MS Excel.
   2. Go to File > Account.
   3. Select About Excel to see the version details. It should say 32-bit or 64-bit at the end of the version number.
2. Select your client below:
   • Oracle_Client_32bit_19_3 (link)
   • Oracle_Client_64bit_19_3 (link)
3. To download, hover your mouse over the file you want and select ..., then choose Download
   Screenshot

   

   If instead you select the file name, it may lead to a partial download which will not successfully install
4. Once the download completes, unzip (Extract All…) the file to Download folder and navigate down through the folders until you find the setup.exe file.
5. Run (double-click) setup.exe, this will take a minute to complete, then follow the installation steps below.

Installation Steps

When the installer appears, perform the following actions:

• Step 1 of 7 - Select Installation Type
  1. Select Administrator
  2. Select Next
     Screenshot

     

     The default Installation Type is Instant Client, but this document does not cover Instant Client installations.
• Step 2 of 7 - Oracle Home User Selection
  1. Select Use Windows Built-in Account
  2. Select Next
     Screenshot

     

• Step 3 of 7 - Specify Installation Location
  • Enter the following:
    • Oracle Base: C:\app\<username>\
    • Software Location: C:\app\<username>\product\19.0.0\client_1
  • Select Next
    Screenshot

    

• Step 4 of 7 - Perform Prerequisite Checks
  • This is an automatic step performed by the Installer. Fix any issues if necessary.
  • If no issues are present, the Installer proceeds to step 5 automatically.
    Screenshot

    

• Step 5 of 7 - Summary
  • Review the summary.
  • Select Install.
    Screenshot

    

• Step 6 of 7 - Install Product
  • A progress bar will appear, indicating the status of the installation. This may take several minutes depending on your system's performance and the components being installed.
  • During the installation, you may encounter a Windows Security Alert for Java. This alert is to ensure that Java can make changes to your system, which is necessary for the Oracle client installation. Select Allow access to permit Java to proceed with the installation.
    Screenshot

    

• Step 7 of 7 - Finish
  • Once the installation process is complete, the Installer will display a completion screen indicating that the Oracle client installation has finished successfully.
  • Select Close.
    Screenshot

    

TNSNAMES.ORA Setup

1. Locate and Save the tnsnames.ora File:
   • Using the tnsnames.ora file that was attached to the MSUEDW access notification email, save the file on following folder on your local machine. (Oracle Base/Home)
   • C:\app\client\<username>\product\19.0.0\client_1\network\admin\.
2. Handling Existing tnsnames.ora File:
   • If a tnsnames.ora file already exists in the specified directory:
     1. Open the provided tnsnames.ora file in Notepad.
     2. Append the required MSUEDW and MSUEDWQ database entries to the existing file.
        The tnsnames.ora file is a simple text file containing a list of database descriptions.
3. Testing Oracle Client Connectivity:
   • To test the Oracle client’s ability to reach the desired Oracle instance without logging in, follow these steps:
     1. Type “cmd” in the search box next to the Start/Windows button and click on the “Command Prompt” app.
     2. Once the “Command Prompt” window is displayed, enter the following command and press Enter: tnsping MSUEDW
     3. You should receive a result indicating “OK” in the last line of the output, confirming that the Oracle client can reach the MSUEDW instance.

Configuring ODBC

Setting Up ODBC Data Source:

1. Go to Control Panel > System and Security > Administrative Tools or type in the windows search: ODBC
2. Open ODBC Data Sources.
   Ensure that the ODBC Data Source Admin matches the bit architecture of the Oracle Instant Client ODBC driver you downloaded (32-bit or 64-bit)
   Screenshot

   

3. Click Add… to create a new data source.
4. Select the “Oracle” driver from the list.
   Caution: Do not select Microsoft or IBM Oracle driver or SQL Server
5. Configure the data source as required and test the connection.
   Screenshot

   

6. Enter your credentials (e.g., MSUEDW Password) and click OK.
   Screenshot

   

Oracle SQL Developer Setup

1. Download this Zip File (Click Save if prompted in the web browser).
2. Unzip the downloaded file to C:\, which will create C:\sqldeveloper with files and folders in and under it.
3. To start SQL Developer, go to the sqldeveloper directory under the SQL Developer installation directory (for example, on a Windows system this is in C:\sqldeveloper), and double-click sqldeveloper.exe.
   If you are asked to enter the full pathname for the JDK, click Browse and find java.exe. For example, the path might have a name similar to C:\Program Files\Java\jdk1.7.0_51
4. At this point you may be offered the opportunity to migrate your configuration from an earlier version of SQL Developer. Click Yes when asked to migrate settings from a Previous release.
5. On windows 7/8/10 right-click on the sqldeveloper.exe and select Pin to taskbar or Pin to Start or create a desktop shortcut for easy access to the SQL developer application.
6. TNS Directoy Path Setup
   • After starting the SQL developer tool you are required to set up the TNSnames directory path since this is a stand-alone installation.
     1. From the top-left navigation menu, select Tools > Preferences
     2. In the Preferences window, expand Database from the left menu and select Advanced.
     3. Next to Tnsnames Directory, select Browse... and find the folder path for the tnsnames.ora file on your machine. For example, it may be similar to: C:\app\client\jayaseka\product\12.1.0\client_1\network\admin
     4. Click OK... and test the existing database connections.
7. Set Up a Connection in SQL Developer:
   1. Right-click Connections and select + New Connection.
      Screenshot

      

   2. Enter the Database Connection details:
      • Connection Name: A name of your choice.
      • Username: Your database username.
      • Password: Your database password (you can elect to save the password if you like).
      • Connection Type: Select TNS.
      • Network Alias: Select the appropriate alias from your local tnsnames.ora file.
        Screenshot

        

      • Click the Test button to test the connection.
      • Check the result of the test (you should see Status: Success).
      • Click the Save button to save the connection settings.
      • Click the Connect button to connect to the database.
8. Execute a Query:
   • Once connected, you can enter a SQL statement in the appropriate tab (e.g., MSUEDW -).
   • For example, enter: SELECT * FROM FINANCE.KFS_CA_ACCOUNT_V;
   • Press the Green arrow to execute the query.
     This query will only work if you have been given access to the CA-Chart of Accounts data in the MSUEDW.
     Screenshot

     

Additional Info

Help and Documentation:

• SQL Developer provides documentation to help you understand the tool.
  • Use the Help menu option on the menu bar:
  • Select Help then Start Page to access links to tutorials and documentation.