Hi guys,
Please can someone help?
I have a problem where i'm calling a stored procedure with two IN paramters from
Java code and i keep on getting the same error.
Here is my code below:
/**
* This method is used to call a specific stored procedure by name as well as supplying it with a supplied
* accepted / rejected boolean value
*
* @param theDatabaseConnection- The database connection on which we call the stored procedure
* @param storedProcedureName- The
string value name of the stored procedure to be called here
* @param clientNumber- The Client number or CIR number string variable parameter
* @param acceptedRejectedParamater - A String value representing rejected or accepted
* @throws MCBCustomException- Custom Exception class for rethrowing any SQL exceptions that may occur
*/
public void callUpdateDualValidationStoredProcedure(Connection theDatabaseConnection, String storedProcedureName, String clientNumber, String acceptedRejectedParamater) throws MCBCustomException
{
System.out.println("CALLING STORED PROCEDURE (IN ACTUAL METHOD DUAL VALIDATION) UPDATE !");
CallableStatement callableStatement = null;
try
{
System.out.println("B4 creating the actaul callable statment UPDATE !");
String tempCallableStatementString = "{ call " + storedProcedureName + "(?, ?) }";
System.out.println("::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::");
System.out.println("STORED PROCEDURE : NAME...BELOW...");
System.out.println("::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::");
System.out.println("STORED PROCEDURE : NAME : " + tempCallableStatementString);
System.out.println("STORED PROCEDURE : NAME : " + tempCallableStatementString);
System.out.println("STORED PROCEDURE : NAME : " + tempCallableStatementString);
System.out.println("STORED PROCEDURE : NAME : " + tempCallableStatementString);
System.out.println("::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::");
System.out.println("::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::");
// Getting the callable statement by preparing it on the connection, to the database, supplied
callableStatement = theDatabaseConnection.prepareCall(tempCallableStatementString);
// connection.prepareCall("{ call set_death_age(?, ?) }");
System.out.println("just created the callable statment UPDATE !");
// Adding the boolean accepted / rejected parameter to the prepared callable statement
callableStatement.setString(1, clientNumber);
callableStatement.setString(2, acceptedRejectedParamater);
System.out.println("Just prepared the callable statment UPDATE !");
// Executing the callable statement
System.out.println("B4 executing the callable statment UPDATE !");
callableStatement.execute();
System.out.println("AFTER executing the callable statment UPDATE !");
this.traceToConsole("Callable statement prepared, variable set and executed successfully...");
}
catch(SQLException sqlException)
{
sqlException.printStackTrace();
this.traceToConsole("Callable statment processing failure...");
throw new MCBCustomException(sqlException.getMessage());
}
finally
{
// Closing callable statement - could throw an MCBCustomException
this.closeCallableStatment(callableStatement);
// Closing the
test database connection
this.closeConnection(theDatabaseConnection, "databaseConnectionToRunUpdateSymbolsStoredProcedure");
}
}
The connection to the oracle 9i database is made successfully and the callable statement gets created fine but as soon as i execute the statement it falls over. The code looks fine if i compare it to examples on the net but i may have missed something.
I also checked that the Oracle JVM has been installed on the server.
I'm using the database driver : ojdbc14.jar downloaded from otn.com
My Exceptions is:
2006-03-29 10:13:31,396 [SonoraAdminThread1576e70:10a44a08f89:-7ff4] ERROR MCBSonora.workflow.rules.MCBStoredProcedureUtilities - UAT02 ORA-06550: line 1, column 7:
PLS-00201: identifier 'UPD_DUAL_VAL_REQUEST_STATUS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
>>> ORA-06550: line 1, column 7:
PLS-00201: identifier 'UPD_DUAL_VAL_REQUEST_STATUS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
2006-03-29 10:13:31,506 [SonoraAdminThread1576e70:10a44a08f89:-7ff4] ERROR MCBSonora.Process.StoredProc - UAT02 StoredProc >>> MCBCustomException : ORA-06550: line 1, column 7:
PLS-00201: identifier 'UPD_DUAL_VAL_REQUEST_STATUS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
2006-03-29 10:13:31,506 [SonoraAdminThread1576e70:10a44a08f89:-7ff4] DEBUG MCBSonora.Process.StoredProc - UAT02 StoredProc >>> return set to : 0
2006-03-29 10:13:31,506 [SonoraAdminThread1576e70:10a44a08f89:-7ff4] DEBUG MCBSonora.Process.StoredProc - UAT02 StoredProc >>> Leaving
Thanking you in anticipation
Sean
