Connecting to Oracle 19c database

Problem

To connect to the Oracle 19c database and use the data in Loftware software, install and configure the Oracle 19c client software on each computer where Loftware is installed.

Solution

There are two providers for the Oracle 19c database. Microsoft OLE DB provider for Oracle 19c, which is discontinued and not supported anymore DO NOT USE THIS PROVIDER.

Use Oracle 19c Provider for OLE DB.

Installing the client

The Oracle 19c Provider for OLE DB is included in the bundle download from the Oracle 19c site. Provider's name is "Oracle 19c Data Access Components (ODAC)".

Install the appropriate release (32 or 64 bit) according to your version of Windows.

Configuring the client

After running the installer and completing the installation, you still can't connect to your database.

Edit a config file named tnsnames.ora, where you specify the address and port of the database you want to connect to.

To edit this file, navigate to the folder where Oracle 19c is installed. The default location is "C:\app".

The tnsnames.ora file is located in

C:\app\client\[user]\product\[version]\client\Network\Admin\tnsnames.ora

Where:

  • [user] is the Windows user who installed the client.

  • [version] is the version of the client installed.

Usually, the administrator in your company provides this file.

Example of tnsnames.ora content:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = W2003Oracle.GALAXY.europlus.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE_SERVICE)
    )
  )

  • XE: The custom name for this connection. This name is used when you connect the database from Loftware Desktop Designer.

  • ADDRESS: The address of the Oracle 19c server.

  • PORT: 1521 by default, but you can change it.

  • SERVICE_NAME: The administrator provides the server service name.

Testing the Connection

After you set up tnsnames.ora, test if the connection between clients and the Oracle 19c server is established. Test the connection without using Loftware Desktop Designer.

Run the following executable file on your computer:

C:\app\client\[user]\product\[version]\client\sqlplus.exe

When prompted for a username, enter the username for the database and the custom name you gave this connection in tnsnames.ora. For example, Production@XE (where XE is the custom name in the tnsnames.ora).

If everything is OK, you can make a connection in Loftware Desktop Designer.

In Desktop Designer, set the Data Source name to the custom name used in tnsnames.ora.

oracle.png