Requirement
You want to connect to an Oracle database from another server. You server has no Oracle products installed.
Solution
"The JDBC Thin driver is a pure Java, Type IV driver that can be used in applications and applets. It is platform-independent and does not require any additional Oracle software on the client-side"
Both servers in my case are linux, but as the statement above implies, this should be platform independent.
Pre-requisites that you DON'T need
1. Oracle Database JDBC Developer's Guide
You don't need to read this waffle, but you can if you want to
2. Versions: "If you have JDK7 or JDK6 then you must use ojdbc7.jar or ojdbc6.jar from 12.1.0.2 or 12.1.0.1. Use "java -version" to check the JDK version that you have installed".
Again, I found this unhelpful. I plucked for ojdbc6.jar. I had Java version 1.8 connected to Oracle 12.2
Any oracle installation, paths etc on the source system.
I found so many forums talking about this. As you will see below, I didn't do any of that.
Pre-requisites that you DO need
An Oracle database to connect to, which has a configured TNS listener.
Steps:
1. Copy/download source script at:
2. Edit your connection details and table_name in DataSourceSample.java
/* Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.*/
/*
DESCRIPTION
The code sample shows how to use the DataSource API to establish a connection
to the Database. You can specify properties with "setConnectionProperties".
This is the recommended way to create connections to the Database.
Note that an instance of oracle.jdbc.pool.OracleDataSource doesn't provide
any connection pooling. It's just a connection factory. A connection pool,
such as Universal Connection Pool (UCP), can be configured to use an
instance of oracle.jdbc.pool.OracleDataSource to create connections and
then cache them.
Step 1: Enter the Database details in this file.
DB_USER, DB_PASSWORD and DB_URL are required
Step 2: Run the sample with "ant DataSourceSample"
NOTES
Use JDK 1.7 and above
MODIFIED (MM/DD/YY)
nbsundar 02/17/15 - Creation
*/
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.OracleConnection;
import java.sql.DatabaseMetaData;
public class DataSourceSample {
// The recommended format of a connection URL is the long format with the
// connection descriptor.
final static String DB_URL= "jdbc:oracle:thin:@999.999.999.999:9/oracle_sid";
// For ATP and ADW - use the TNS Alias name along with the TNS_ADMIN when using 18.3 JDBC driver
// final static String DB_URL="jdbc:oracle:thin:@wallet_dbname?TNS_ADMIN=/Users/test/wallet_dbname";
// In case of windows, use the following URL
// final static String DB_URL="jdbc:oracle:thin:@wallet_dbname?TNS_ADMIN=C:\\Users\\test\\wallet_dbname";
final static String DB_USER = "db_user";
final static String DB_PASSWORD = "db_pass";
/*
* The method gets a database connection using
* oracle.jdbc.pool.OracleDataSource. It also sets some connection
* level properties, such as,
* OracleConnection.CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH,
* OracleConnection.CONNECTION_PROPERTY_THIN_NET_CHECKSUM_TYPES, etc.,
* There are many other connection related properties. Refer to
* the OracleConnection interface to find more.
*/
public static void main(String args[]) throws SQLException {
Properties info = new Properties();
info.put(OracleConnection.CONNECTION_PROPERTY_USER_NAME, DB_USER);
info.put(OracleConnection.CONNECTION_PROPERTY_PASSWORD, DB_PASSWORD);
info.put(OracleConnection.CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH, "20");
OracleDataSource ods = new OracleDataSource();
ods.setURL(DB_URL);
ods.setConnectionProperties(info);
// With AutoCloseable, the connection is closed automatically.
try (OracleConnection connection = (OracleConnection) ods.getConnection()) {
// Get the JDBC driver name and version
DatabaseMetaData dbmd = connection.getMetaData();
System.out.println("Driver Name: " + dbmd.getDriverName());
System.out.println("Driver Version: " + dbmd.getDriverVersion());
// Print some connection properties
System.out.println("Default Row Prefetch Value is: " +
connection.getDefaultRowPrefetch());
System.out.println("Database Username is: " + connection.getUserName());
System.out.println();
// Perform a database operation
printEmployees(connection);
}
}
/*
* Displays first_name and last_name from the employees table.
*/
public static void printEmployees(Connection connection) throws SQLException {
// Statement and ResultSet are AutoCloseable and closed automatically.
try (Statement statement = connection.createStatement()) {
try (ResultSet resultSet = statement
.executeQuery("select name, version from table_name")) {
System.out.println("Name" + " " + "Version");
System.out.println("---------------------");
while (resultSet.next())
System.out.println(resultSet.getString(1) + " "
+ resultSet.getString(2) + " ");
}
}
}
}
2. Download the driver (in my case ojdbc6.jar).
You can dig around the Oracle website. Google sent me straight to http://www.java2s.com/Code/Jar/o/Downloadojdbc6jar.htm
Note: There are several versions of this jar file (ojdbc7.jar, ojdbc12.jar etc). I assume they all work in the same way. I plucked for version 6 and was able to connect from a very old version of Java to a (currently) very new version of Oracle.
3. Create directories on your source system.
Example: mkdir /home/interface
4. Copy/FTP your files to your new directory
ojdbc6.jar and DataSourceSample.java
* You might need to chmod your jar file if you get a permission denied step with any of the below.
5. Compile your Java file
I spent ages reading all sorts of rubbish about paths and homes. This command worked for me.
javac -classpath /home/interface/ojdbc6.jar:. DataSourceSample.java
Note the directory name, ":." after the jar name and file name with the ".java" extension.
ls -ltr you should see a class file
6. Run your Java file
java -classpath /home/interface/ojdbc6.jar:. DataSourceSample
i.e exactly like your compile command, without the ".java" extension.
7.Result
Success!
8. Program!
Now that you've got the handshake you need, get on with the business of SQL. Either create a new script using the same method as above, or edit your existing script, taking/adding as required.
Penty of examples at https://docs.oracle.com/database/121/JJDBC/getsta.htm#JJDBC28071
Ackowledgement:
https://www.oracle.com/uk/database/technologies/develop-java-apps-using-jdbc.html
Comments
Post a Comment