wood burning stoves*
The moose likes JDBC and the fly likes Problem calling IN parameters Stored Procedure from Java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem calling IN parameters Stored Procedure from Java" Watch "Problem calling IN parameters Stored Procedure from Java" New topic
Author

Problem calling IN parameters Stored Procedure from Java

Sean Nell
Greenhorn

Joined: Feb 03, 2005
Posts: 6
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
Sujith Kanaparthi
Ranch Hand

Joined: Sep 04, 2005
Posts: 45
Hi Sean,
PLS-00201: identifier 'UPD_DUAL_VAL_REQUEST_STATUS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I want you to make sure that the procedure your calling is compiled successfully without any errors.I think this is caused due to erors in the oracle proceudure that you are trying to call.

Regards,
Sujith
Sean Nell
Greenhorn

Joined: Feb 03, 2005
Posts: 6
Hi Sujith,

Thanks for the response. I do not have any access to the stored procedures or to the database. So i had to ask the lady whose procedure it is to compile and test it. She says that it is working fine from the command line. She apparently has an example on how to call the exact procedure from Java code for me although i reckon it will look exactly the same as mine (Going on what i've seen on the net!).

I agree that it looks like the procedure that is the cause of these exceptions. Would it have something to do with ownership of the procedure or some kind of access to call the procedure?

Sean
Sujith Kanaparthi
Ranch Hand

Joined: Sep 04, 2005
Posts: 45
Hi Sean,

Try to call your procedure like this,
callableStatement = conn.prepareCall("{call SCHEMA_NAME.PACKAGE_NAME.PROCEDURE_NAME(?,?)}");
I hope this may solve your problem.
I'm not sure about these privileges but cross-check whether the user with which your able to get connection object is granted the permission to execute the procedure.

Regards,
Sujith
Sean Nell
Greenhorn

Joined: Feb 03, 2005
Posts: 6
Hi Sujith,

That did it!

I just included the SCHEMA_NAME and the PACKAGE_NAME to the front of the PROCEDURE_NAME and it worked first time.

Thanks very much for all your help. I really appreciate it.

Regards
Sean
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem calling IN parameters Stored Procedure from Java