simpleJdbcCall , org.springframework.jdbc.BadSqlGrammarException

[sze sze chan]

Hi everyone,

I'm very new to java and spring frame work, and i'm currently working on a java code enhancement program
.
And i'm having some trouble when calling the oracle package.
any help will be much appreciated ! thanks
Below is the error occurs:

Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{? = call ADMIN.PK_UTILITY.SETNBR()}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'SETNBR'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:969)
	at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1003)
	at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:391)
	at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:365)
	at org.springframework.jdbc.core.simple.SimpleJdbcCall.executeFunction(SimpleJdbcCall.java:144)
	at net.CodeEnhancement.JobMgmt.main(JobMgmt.java:278)
Caused by: java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'SETNBR'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:742)
	at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:214)
	at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:953)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1167)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3315)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3421)
	at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4393)
	at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:1005)
	at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:1)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:953)

the error occurs when the package is called,
the package name stored as below:

	
	        String packageName = "PK_UTILITY";
	        String get_set_nbr = "Proc_SetNbr";

and the package is called by using simpleJdbcCall:

SimpleJdbcCall sjc = new SimpleJdbcCall(JobMgmt.getDBHelper().getDb().getDataSource())
	            .withSchemaName(JobMgmt.getDBHelper().getSchemaName()).withCatalogName(packageName).withFunctionName(get_set_nbr);

the package generates and return a set_nbr and the error occurs at this line:

String n_setNum = (String)sjc.executeFunction(Class.forName("java.lang.String"));

i'm wondering is there anyway to write this variable : String n_setNum = (String)sjc.executeFunction(Class.forName("java.lang.String"));
as Integer ?
because it is going to store in integer and call another class

[Nathan Pruett]

That error occurs because the parameters you're trying to use to call the stored procedure don't match the parameters that are actually declared on the stored procedure - either it expects some "in" parameters you aren't providing, or the the "out" parameter is declared as a different type than "String".

[sze sze chan]

Nathan Pruett wrote:That error occurs because the parameters you're trying to use to call the stored procedure don't match the parameters that are actually declared on the stored procedure - either it expects some "in" parameters you aren't providing, or the the "out" parameter is declared as a different type than "String".

Hi, thank you for your reply!
are you saying that the String n_setNum = (String)sjc.executeFunction(Class.forName("java.lang.String")); is a different type as the "out" inside the oracle package?
is there anyway I can write String n_setNum = (String)sjc.executeFunction(Class.forName("java.lang.String")); as an integer ?

[Nathan Pruett]

You could call it with java.lang.Integer to be able to cast it to an int... but what are the actual parameters defined on the stored procedure?

[sze sze chan]

Nathan Pruett wrote:You could call it with java.lang.Integer to be able to cast it to an int... but what are the actual parameters defined on the stored procedure?

Should be String in the stored procedure. I've tried to convert it into int but the error is still there

[Nathan Pruett]

OK, some more things to try -

You can try to specify the out parameter when you're creating the SimpleJdbcCall -

    SimpleJdbcCall sjc = new SimpleJdbcCall(
            JobMgmt.getDBHelper().getDb().getDataSource()).
            withSchemaName(JobMgmt.getDBHelper().getSchemaName()).
            withCatalogName(packageName).
            withFunctionName(get_set_nbr).
            declareParameters(
                    new SqlOutParameter("out_param_name", Types.VARCHAR));

Second, I think supplying java.lang.Integer will attempt to cast the value returned to an Integer - but you can try to do this yourself to see if it's causing the problem -

    String setNumString = (String)sjc.executeFunction(Class.forName("java.lang.String"));  
    int setNum = Integer.parseInt(setNumString);