how to connect to oracle db table through java program using unix shell script?

[prasanna ganesh]

Hi all,

I have a small task where i need to connect to oracle table through a java program, where i'm calling the java program from a unix shell script.

My java program compiling and running fine if i run it individually, however when i'm trying to run through unix shell script i'm getting the following error,

"Unable to connect to database: java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver"

This is my scirpt which calling the java program TestDBOracle.class

#!/bin/ksh/

JAVA_HOME=/usr/bin:/usr/ucb:/etc:.
CLASSPATH=/www/a/downloads/
export JAVA_HOME
echo $CLASSPATH
java -cp .:CLASSPATH TestDBOracle

I kept my odbc14.jar and TestDBOracle.class file in the CLASSPATH =/www/a/downloads/


And this is my TestDBOracle.java file which connects to Oracle table and exporting the table into a csv file,

import java.io.*;
import java.sql.*;
import java.util.*;

public class TestDBOracle {

	public static void main(String[] args) {
		generateCsvFile("c:\\test.csv");

	}

	private static void generateCsvFile(String filename) {
		DatabaseMetaData dbMetaData = null;
		String columnNameQuote = "";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@//belkecdb71:1521/ECTRANQ";
			Connection conn = DriverManager.getConnection(url, "bm_external",
					"martini");
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt
					.executeQuery("select * from bm_external.t_sku_prices");
			ResultSetMetaData rsmd = rset.getMetaData();
			rset.next();
			FileWriter fname = new FileWriter(filename);
			BufferedWriter bwOutFile = new BufferedWriter(fname);
			PrintWriter out
			   = new PrintWriter(bwOutFile);
	//		String sbOutput = null;
			String sbOutput = "SKU_CODE,SKU_UPC,LIST_PRICE,SALE_PRICE";
			out.write(sbOutput);
			//bwOutFile.write(sbOutput);
			out.write(System.getProperty("line.separator"));
			//bwOutFile.flush();
			FileInputStream infile = null;
			FileOutputStream outfile = null;
			//String sbOutput;
			System.out.println("No of columns in the table:"
					+ rsmd.getColumnCount());
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				if (i == 1) {
					System.out.println("Table Name:" + rsmd.getTableName(i));
					// break;
				}
				   String  cname = rsmd.getColumnName(i);
				   
			}
			
			
			System.out.println();
			while (rset.next()) {
				System.out.println(rset.getString(1) + " " + rset.getString(2)
						+ " " + rset.getString(3) + " " + rset.getString(4));
				out.write(rset.getString(1) + "," + rset.getString(2) + "," + rset.getString(3) + "," + rset.getString(4));
				out.write(System.getProperty("line.separator"));
				out.flush();
				
			}
			bwOutFile.close();
			stmt.close();
			System.out.println("Ok.");
		}

		catch (Exception e) {
			System.err.println("Unable to connect to database: " + e);

		}

	}
}

Can someone please help me to fix this problem?

Thanks a lot for your help

[Paul Clapham]

Your classpath must specifically identify the jars which you want to be in it. Just pointing to a directory containing the jars isn't enough.

[prasanna ganesh]

Hi Paul,

As you mentioned now i'm explicitly mentioning my ojdbc14.jar file while calling my java program like below,

java -classpath ojdbc14.jar TestDBOracle
and as well as like this
java -classpath ojdbc14.jar .;TestDBOracle

but now i'm getting different ClassNotFoundException like below,

Exception in thread "main" java.lang.NoClassDefFoundError: TestDBOracle

If i ran this program alone its executing fine,

Could you please let me know where i'm going wrong and please correct my mistake and once again thanks for your help.

import java.io.*;
import java.sql.*;
import java.util.*;

public class TestDBOracle {

	public static void main(String[] args)throws ClassNotFoundException, IOException{
		generateCsvFile("c:\\test.csv");

	}

	private static void generateCsvFile(String filename)throws IOException {
		DatabaseMetaData dbMetaData = null;
		String columnNameQuote = "";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@//belkecdb71:1521/ECTRANQ";
			Connection conn = DriverManager.getConnection(url, "bm_external",
					"martini");
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt
					.executeQuery("select * from bm_external.t_sku_prices");
			ResultSetMetaData rsmd = rset.getMetaData();
			rset.next();
			FileWriter fname = new FileWriter(filename);
			BufferedWriter bwOutFile = new BufferedWriter(fname);
			PrintWriter out
			   = new PrintWriter(bwOutFile);
	//		String sbOutput = null;
			String sbOutput = "SKU_CODE,SKU_UPC,LIST_PRICE,SALE_PRICE";
			out.write(sbOutput);
			//bwOutFile.write(sbOutput);
			out.write(System.getProperty("line.separator"));
			//bwOutFile.flush();
			FileInputStream infile = null;
			FileOutputStream outfile = null;
			//String sbOutput;
			System.out.println("No of columns in the table:"
					+ rsmd.getColumnCount());
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				if (i == 1) {
					System.out.println("Table Name:" + rsmd.getTableName(i));
					// break;
				}
				   String  cname = rsmd.getColumnName(i);
				   
			}
			
			
			System.out.println();
			while (rset.next()) {
				System.out.println(rset.getString(1) + " " + rset.getString(2)
						+ " " + rset.getString(3) + " " + rset.getString(4));
				out.write(rset.getString(1) + "," + rset.getString(2) + "," + rset.getString(3) + "," + rset.getString(4));
				out.write(System.getProperty("line.separator"));
				out.flush();
				
			}
			out.close();
			stmt.close();
			System.out.println("Ok.");
		}
		
		catch (SQLException e)
		{
		System.err.println("SQL state: " + e.getSQLState());
		System.err.println("SQL error: " + e.getErrorCode());
		e.printStackTrace(System.err);
		}
		
		catch (Exception e)
		{
		e.printStackTrace(System.err);
		}


		/*catch (Exception e) {
			System.err.println("Unable to connect to database: " + e);

		}*/

	}
}

[Tim Holloway]

You need 2 separate classpath items

1. The path where the application classes reside.
2. The path where the Oracle code resides.

So, if you have your class in subdirectory com/mycorp/myapp/TestDBOracle.class, the command line would be something like this:

java -classpath .:ojdbc14.jar com.mycorp.myapp.TestDBOracle

Notice the ".:ojbbc.jar". "." is shorthand for the current directory. ":" is the classpath separator for Unix/Linux. Note that this is not the same in Windows!!!

In Unix, ";" (semicolon) is a command separator, used to stack multiple commands on the same line, so the ":" (colon) is used to separate components within a command.

In Windows ":" (colon) is part of the pathname "language" - for example "C:\myclasses", so they use the semicolon (";"), instead.

Actually, it's probably better to put the class file under a "classes" subdirectory instead of the actual current directory:

java -classpath classes:ojdbc14.jar com.mycorp.myapp.TestDBOracle

Or in a jar:

java -classpath myapp.jar:ojdbc14.jar com.mycorp.myapp.TestDBOracle

You can't use the "java -jar" option when you have 2 separate jars, since the default classloading scheme doesn't support that.