Using sqlplus to test ODBC connection to Voyager server

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

Product: Voyager
Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare

Question

Methods for testing/troubleshooting ODBC setup and configuration for Voyager Prepackaged Access Reports.

Answer

When you install the Full 10g ODBC driver, it installs an application called sqlplus on your PC. SQLPlus is the command line client to Oracle.

The sqlplus application can be very handy for testing ODBC connectivity. It requires that you have a good tnsnames.ora file on your PC configured to point to your server, and that you know the read-only user name and password, and the Service Name (likely "VGER") that is defined in your tnsnames.ora file (see example of tnsnames.ora file at the bottom of this article).

To run sqlplus, after installing the 10g driver, go to the Windows "Type here to search" box and type "powershell".

This should start up Windows Powershell¹:

At the PowerShell command prompt type a sqlplus connection command that looks something like this example (but substitute your read-only login/pw and Service Name:

sqlplus ro_voy1010db/ro_voy1010db@VGER

If successful, you'll see a "Connected to:" response:

At the sql prompt you can submit a simple sql statement to confirm you're connected to the database and can see data being returned.

For example:

SELECT title from bib_text where bib_id='3000';

Additional Information

¹Pro Tip: If you can't run PowerShell, just use CMD to open a DOS command prompt and enter your sqlplus connection command there.

Note that this procedure does not require that all the steps in the ODBC installation instructions are completed, it simply uses the correctly edited tnsnames.ora file and sqlplus.

It is helpful to use sqlplus to confirm that there are no connection problems (firewall/etc.).

You can also use this sqlplus application to run code shared on the Voyager customer listserv that won't run in Access because it is designed specifically to be run directly on the server.

Example of tnsnames.ora file:

VGER =

(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.1.11 )(PORT = 1521))
)
(CONNECT_DATA =
(SID = VGER)
)
)

Article last edited: 23-Dec-2020