how do i get one column result and pass one parm to prapareCall method.

[rekha sen]

i wrote a function in oracle ,which return a number value by taking one parm value.

In Java I call that function as,

public static int checkPreviousLoad(int Id) {
		int countPrev = 0;
		//Here we run the Function to get the existing count of to be loaded Id.
		try {
			CallableStatement proc_stmt = connection.prepareCall(" {? = call F_CHK_PREVIOUSLOAD(?)}");
			proc_stmt.setLong(1, Id);
			// Register the type of the return value
			proc_stmt.registerOutParameter(1, OracleTypes.NUMBER);
			// Execute and retrieve the returned value.
			proc_stmt.execute();
			ResultSet rs = (ResultSet) proc_stmt.getObject(1);
			rs.next();
			countPrev = rs.getInt(1);
			System.out.println("the count is: "+countPrev);
			rs.close();
			proc_stmt.close();
		} catch(SQLException e) {
			String temp = e.getMessage();
			System.out
					.println("ERROR: SQL Exception when executing F_CHK_PREVIOUSLOAD \n");
			System.err.println("ERROR MESSAGE IS: " + temp);
			System.err.println("SQLState: " + e.getSQLState());
		}
		
		return countPrev;
	}

This is throwing an SQLException.

Error Message is like this:
ERROR: SQL Exception when executing F_CHK_PREVIOUSLOAD

ERROR MESSAGE IS: Missing IN or OUT parameter at index:: 2
SQLState: null

Please tell me where I went wrong. Thanks.

[Swastik Dey]

Should this

proc_stmt.setLong(1, Id);

be

proc_stmt.setLong(2, Id);

[rekha sen]

After making the necessary change of changing the input parm to 2, I get this error:

Exception in thread "main" java.lang.ClassCastException: java.math.BigDecimal cannot be cast to java.sql.ResultSet

The code used is :

public static int checkPreviousLoad(int Id) {
		int countPrev = 0;
		//Here we run the Function to get the existing count of to be loaded ID.
		try {
			CallableStatement proc_stmt = connection.prepareCall(" {? = call F_CHK_PREVIOUSLOAD(?)}");
			// Register the type of the return value
			proc_stmt.registerOutParameter(1, OracleTypes.NUMBER);
			proc_stmt.setLong(2, dcnId);		
			// Execute and retrieve the returned value.
			proc_stmt.execute();
			ResultSet rs = (ResultSet) proc_stmt.getObject(1);
			rs.next();
			countPrev =  rs.getInt(1);
			System.out.println("the count is: "+countPrev);
			rs.close();
			proc_stmt.close();
		} catch(SQLException e) {
			String temp = e.getMessage();
			System.out
					.println("ERROR: SQL Exception when executing F_CHK_PREVIOUSLOAD \n");
			System.err.println("ERROR MESSAGE IS: " + temp);
			System.err.println("SQLState: " + e.getSQLState());
		}
		
		return countPrev;
	}

Please help in solving the error message.

[Dave Tolls]

Your PL/SQL function returns a number, not a ResultSet.

[Swastik Dey]

public static int checkPreviousLoad(int Id) {
        int countPrev = 0;
        //Here we run the Function to get the existing count of to be loaded ID.
        try {
            CallableStatement proc_stmt = connection.prepareCall(" {? = call F_CHK_PREVIOUSLOAD(?)}");
            // Register the type of the return value
            proc_stmt.registerOutParameter(1, OracleTypes.NUMBER);
            proc_stmt.setLong(2, dcnId);        
            // Execute and retrieve the returned value.
            proc_stmt.execute();
           // ResultSet rs = (ResultSet) proc_stmt.getObject(1);
            //rs.next();
            countPrev =  proc_stmt.getInt(1);
            System.out.println("the count is: "+countPrev);
           // rs.close();
            proc_stmt.close();
        } catch(SQLException e) {
            String temp = e.getMessage();
            System.out
                    .println("ERROR: SQL Exception when executing F_CHK_PREVIOUSLOAD \n");
            System.err.println("ERROR MESSAGE IS: " + temp);
            System.err.println("SQLState: " + e.getSQLState());
        }
         
        return countPrev;
    }