Problem scenario
You have no desktop GUI and a limited ability to install new applications other than the Java compiler. You are not be allowed to install a SQL command line client such as sqlconnect or a utility such as tnsping. What should you do to test the credentials, server name and port number to ensure they work to allow access to an Oracle database?
Solution
The overview is to use a Java program with the code provided below.
Prerequisite
You need to be able to compile Java programs. The "javac" command must be recognized. To do this install a Java compiler. If you do have this and do not know how, see this posting.
Procedures
1. Find the jre/lib/ext directory path on your server. (e.g., sudo find / -name ext -type d | grep lib).
2. Place an ojdbc14.jar file (e.g., downloaded from Oracle.com or some trustworthy website) in that directory above. Here is one place to get it.
3. From any directory create a program such as this called OracleCon.java:
import java.sql.*; //first line of file
class OracleCon{
public static void main(String args[]){
try{
//step1 load the driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
//step2 create the connection object
Connection con=DriverManager.getConnection(
"jdbc:oracle:thin:@contintserver:1521:xe","jdoe","strongPassword");
//step3 create the statement object
Statement stmt=con.createStatement();
//step4 execute query
ResultSet rs=stmt.executeQuery("select * from all_users");
while(rs.next())
System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
//step5 close the connection object
con.close();
}catch(Exception e){ System.out.println(e);}
}
} // last line of file
(This code was largely taken from https://www.javatpoint.com/example-to-connect-to-the-oracle-database.)
In the above code, you need to make some modifications:
- Replace "jdoe" with the Oracle SQL user, and replace "strongPassword" with jdoe's password.
- Replace "contintserver" with the servername of the remote Oracle server.
- Replace "xe" with the SID. You may need to ask the DBA for this SID as it can be crucial.
- Under the comment "//step4 execute query" there is a SELECT statement in quotes. You can change this SQL command, but it often works as a test.
4. To compile it, use javac OracleCon.java
5. To run the compiled byte code, run java OracleCon without quotes. The output of running this Java program should give you an indication of whether or not the credentials work to connect to the database.
As a closing tip, we have seen a source (an article, http://glennpaulley.ca/conestoga/2015/06/configuring-sonarqube-with-oracle-12c/, published in June of 2015) indicated that the two forward slashes "//" are a necessary constructor for a JDBC connection string. We know in 2018 that this is not necessary in all instances.