Skip to main content
ExLibris
  • Subscribe by RSS
    • Back
    Voyager
    Ex Libris Knowledge Center

    Using sqlplus to test ODBC connection to Voyager server

    1. Last updated
    2. Save as PDF
    • 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 Powershell1:

     

    clipboard_e72d142025b2f95c666fe112b16365271.png

     

    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

    clipboard_e053cbd765a05772d6fb78ba445c333b5.png

     

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

     

    clipboard_e1a2db2910ade12fbbfe2078d13d181f7.png

     

    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';

    clipboard_e19f364515152dac0278f3c115d289093.png

     

    Additional Information

    1Pro 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