Configuring QuerySurge Connections: Advanced Oracle JDBC Options
When you create a QuerySurge Connection, the Connection Wizard guides you through the process. Different types of QuerySurge connections require different types of information. For Oracle Connections, different configuration options are available. The Connection Wizard gives three common options for the Oracle "thin" JDBC driver. This article provides details about these options and how to access them in the Connection Wizard.
The Oracle Connection options available in the Connection Wizard are:
- SID - creates a Connection based on the Oracle SID
- Service Name - creates a Connection based on the Oracle Service Name
- TNS-based - creates a Connection using the the Oracle TNS formalism with the Service Name
In the first section of this article, we show how to access the different Oracle options in the Connection Wizard. In the second section, we provide details on the differences between the options in terms of the JDBC URLs that they create.
Different Oracle Options in the Connection Wizard
- Log into QuerySurge as a QuerySurge Admin user.
- To configure a Connection, select Configuration > Connection in the Administrative View tree (at the left).
- Click on the Add button at the bottom left of the page to launch the Connection Wizard. Click Next.
- Check the Advanced Mode checkbox (in the opening Wizard dialog, at the lower left).
- By default, the Connection Wizard builds a Connection based on the Oracle Service Name. If you create an Oracle Connection using Regular Mode (i.e. without selecting Advanced Mode), this is the type of connection that the Connection Wizard builds.
In the follow-on dialogs after you make this choice (not shown here), you'll enter the details of the Connection: credentials, Database Server name, Port, and the Oracle Service Name.
- A different option is to create a Connection based on the Oracle SID:
In the follow-on dialogs after you make this choice (not shown here), you'll enter the details of the Connection: credentials, Database Server name, Port, and the Oracle SID.
- A final option is to create a Connection based on Oracle's TNS syntax. The TNS syntax offered by the Connection Wizard uses the Oracle Service Name, and specifies other Oracle connection parameters, which are fixed by the Wizard (see below). Your Oracle instance may require the full TNS syntax, with the explicitly defined parameters, in order to connect.
In the follow-on dialogs after you make this choice (not shown here), you'll enter the details of the Connection: credentials, Database Server name, Port, and the Oracle Service Name.
JDBC URL Syntax and the Oracle Options
The table below shows the three Oracle Connection options that are available via the Connection Wizard Advanced Mode, and the JDBC URL's that each choice generates.
| Connection Type | URL Syntax | |
| 1 | SID | jdbc:oracle:thin:@<ServerName>:<Port>:<SID> |
| 2 | Service Name | jdbc:oracle:thin:@//<ServerName>:<Port>/<ServiceName> |
| 3 | TNS-based | jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<ServerName>)(PORT=<Port>))(LOAD_BALANCE=YES)(FAILOVER=on)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<ServiceName>)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5)))) |
As we noted above, there are multiple parameters that are part of the TNS syntax (there are several parameter options beyond those used by the Connection Wizard) and the Connection Wizard lets you adjust only a key subset in this option. If you want full control over the TNS-based URL (or any other URL), use the Connection Wizard's Connection Extensibility feature, which you can find out about here.