Stored Procedure java code error

[Srikanth Ravuri]

Hi

I am new to java. I am trying to call data from Oracle using java code but got few errors. Can you please help me where I am going wrong.

Thanks in advance.

Code:

package com.srikanth.bank.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;



public class EmpDAO {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try
		{
		Class.forName("oracle.jdbc.driver.OracleDriver");
		}
		catch(ClassNotFoundException ex)
		{
		ex.printStackTrace();
		}
		Connection con=null;
		CallableStatement cstmt=null;
		try
		{
		con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "SYS", "nani123");
		cstmt=con.prepareCall("{call Net_Pay(d_id, e_id, e_n, b_salary, hra, ob, tax, net}"); //called the procedure 

		cstmt.setInt(1, 501);
		cstmt.setInt(2, 0);
		cstmt.registerOutParameter(2,java.sql.Types.INTEGER);
		cstmt.registerOutParameter(3,java.sql.Types.VARCHAR);
		cstmt.registerOutParameter(4,java.sql.Types.INTEGER);
		cstmt.registerOutParameter(5,java.sql.Types.INTEGER);
		cstmt.registerOutParameter(6,java.sql.Types.INTEGER);
		cstmt.registerOutParameter(7,java.sql.Types.INTEGER);
		cstmt.registerOutParameter(8,java.sql.Types.INTEGER);
				ResultSet rs = cstmt.executeQuery();
				
				while(rs.next ())
				{
				int e_id = cstmt.getInt(2);
				String e_n = cstmt.getString(3);
				int b_salary = cstmt.getInt(4);
				int hra = cstmt.getInt(5);
				int ob = cstmt.getInt(6);
				int tax = cstmt.getInt(7);
				int net = cstmt.getInt(8);

				System.out.println("Employee id: " + e_id + "Name: " + e_n + "Base Salary: " + b_salary + "HRA: " + hra + 
									"Other Benifits:" + ob + "Tax: " + tax + "Net Salary:" + net);
				}}
				
		catch(SQLException ex)
		{
		ex.printStackTrace();
		}
		finally
		{
		try
		{
		if(cstmt!=null) //close the callablestatement
		{
		cstmt.close();
		cstmt=null;
		}
		}
		catch(SQLException ex)
		{
		ex.printStackTrace();
		}
		try
		{
		if(cstmt!=null) //close the connection
		{
		cstmt.close();
		cstmt=null;
		}
		}
		catch(SQLException ex)
		{
		ex.printStackTrace();
		}
		}
		}
}

Error:

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at com.srikanth.bank.dao.EmpDAO.main(EmpDAO.java:19)
java.sql.SQLException: No suitable driver found for jdbc:oracle:thin @localhost:1521:XE
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at com.srikanth.bank.dao.EmpDAO.main(EmpDAO.java:29)


Please help me where I am going wrong.

[Anand Bhatt]

Do you have classes12.jar file in your application path?

[Srikanth Ravuri]

Anand Bhatt wrote:Do you have classes12.jar file in your application path?

Thanks Anand for the reply.

I am new to java. I am using eclipse to call the stored procedures. As you said I downloaded classes12.jar file and uploaded my java lib folder and still I couldn't see that on eclipse. Any suggestion of where I am doing the mistake.

Please help.

Thank you,

[Campbell Ritchie]

Welcome to JavaRanch

Please always put code in code tags (Since you are new I have edited your post and you can see how much better it looks) and indent your code correctly.

If you didn't update your classpath for the project on Eclipse®, that .jar will be invisible to your application. I am not quite sure how to do it, but try

right-click project name

import

General

Archive File

Browse for file

Import into a folder

Can't promise that will work. You should be able to find some sort of help or tutorial for Eclipse® which will help you.

[Tom Reilly]

Actually, it's:
1. Right click on the project
2. Select Properties
3. Select Java Build Path
4. Select the Libraries tab
5. Click the Add External JARs button
6. Navigate to, and select classes12.jar

[Srikanth Ravuri]

Tom Reilly wrote:Actually, it's:
1. Right click on the project
2. Select Properties
3. Select Java Build Path
4. Select the Libraries tab
5. Click the Add External JARs button
6. Navigate to, and select classes12.jar

Thank you Tom

I got hold up with DB connectivity error saying:

        java.sql.SQLException: ORA-01045: user SRIWORK lacks CREATE SESSION privilege; logon denied

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:304)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:271)
	at oracle.jdbc.driver.T4CTTIoauthenticate.receiveOauth(T4CTTIoauthenticate.java:644)
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:298)
	at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:343)
	at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:147)
	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:31)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:545)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at com.srikanth.bank.dao.EmpDAO.main(EmpDAO.java:31)

any suggestion at to keep me on a right track.

Thank you

[Anand Bhatt]

You are on track now:

This error is thrown if a user wants to log on a database but lacks the create session system privilege.
In order to give someone this privilege, use:
grant create session to the_user;

[Srikanth Ravuri]

Anand Bhatt wrote:You are on track now:

This error is thrown if a user wants to log on a database but lacks the create session system privilege.
In order to give someone this privilege, use:
grant create session to the_user;

Thanks Anand for quick response.

I have a new problem now. I can print 1st row of the data and the rest it stops with an error message

java.sql.SQLException: ORA-01002: fetch out of sequence

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:304)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:271)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:622)
	at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:179)
	at oracle.jdbc.driver.T4CCallableStatement.fetch(T4CCallableStatement.java:841)
	at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:264)
	at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:196)
	at com.srikanth.bank.dao.EmpDAO.main(EmpDAO.java:43)

Here is my new java code.

package com.srikanth.bank.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

//import Oracle.jdbc.driver.OracleDriver;

public class EmpDAO {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try {
			// DriverManager.registerDriver(new OracleDriver());
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		}
		Connection con = null;
		CallableStatement cstmt = null;
		try {
			con = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521:XE", "SYS", "nani123");
			cstmt = con.prepareCall("{call Net_Pay(?, ?, ?, ?, ?, ?, ?, ?)}"); 
			
			cstmt.setInt(1, 501);
			cstmt.setInt(2, 0);
			cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
			cstmt.registerOutParameter(3, java.sql.Types.VARCHAR);
			cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
			cstmt.registerOutParameter(5, java.sql.Types.INTEGER);
			cstmt.registerOutParameter(6, java.sql.Types.INTEGER);
			cstmt.registerOutParameter(7, java.sql.Types.INTEGER);
			cstmt.registerOutParameter(8, java.sql.Types.INTEGER);
			ResultSet rs = cstmt.executeQuery();

//			System.out.println("in while " + rs.getFetchSize());
			while(rs.next()) {
			//	System.out.println("in while " + rs.getRow());

				int e_id = cstmt.getInt(2);
//				System.out.println(e_id);
				String e_n = cstmt.getString(3);
				int b_salary = cstmt.getInt(4);
				int hra = cstmt.getInt(5);
				int ob = cstmt.getInt(6);
				int tax = cstmt.getInt(7);
				int net = cstmt.getInt(8);

				System.out.println("Employee id: " + e_id  + "Name: " + e_n
						+ "Base Salary: " + b_salary + "HRA: " + hra
						+ "Other Benifits:" + ob + "Tax: " + tax
						+ "Net Salary:" + net);
			}
		}

		catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (cstmt != null) // close the callablestatement
				{
					cstmt.close();
					cstmt = null;
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			try {
				if (cstmt != null) // close the connection
				{
					cstmt.close();
					cstmt = null;
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
	}
}

Any comments or suggestions on where I went wrong.

Thank you,

[Tom Reilly]

A few comments:
1. You only get back one set of output parameters. Your code suggests that you expect multiple sets because you attempt to get them in a loop.

2. Here's a link to some Oracle documentation. It says:

After all values have been retrieved from ResultSet objects (using ResultSet.getXXX methods), and after it has been determined that there are no more update counts, values from OUT parameters can be retrieved (using CallableStatement.getXXX methods).

3. Despite the comment in the code that says "close the connection", you are not closing your connection. Do you see the error?