org.springframework.jdbc.BadSqlGrammarException

There are no available Samebug tips for this exception. Do you have an idea how to solve this issue? A short tip would help users who saw this issue last week.

  • I created the following oracle function: CREATE OR REPLACE FUNCTION test_function (p_p1 VARCHAR2, p_p2 VARCHAR2, p_p3 VARCHAR2) RETURN SYS_REFCURSOR AS l_result SYS_REFCURSOR; BEGIN OPEN l_result FOR SELECT p_p1 || ' ' || p_p2 || ' ' || p_p3; FROM sys.dual; RETURN l_result; END; Now here is the java code: public static void main(String[] args) throws Exception { oracle.jdbc.pool.OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource(); // replace the settings below in the url with the ones specific to your environment ods.setURL("jdbc:oracle:thin:<username>/<password>@<server name>:1521:<db name>"); // DefaultPlSqlProcedureMetaDataProvider metaDataProvider = new DefaultPlSqlProcedureMetaDataProvider(ods); // System.out.printf("List %s", String.valueOf( // metaDataProvider.getProcedureParameters("utils_pkg", "get_procedure_parameters", null))); SimpleJdbcCall procedureParametersCall = new SimpleJdbcCall(ods); procedureParametersCall .withFunctionName("test_function") .withoutProcedureColumnMetaDataAccess() .declareParameters(new SqlReturnResultSet("return", new RowMapper() { public Object mapRow(ResultSet rs, int rowNum) throws SQLException { return rs.getString(1); } }) , new SqlParameter("p1", Types.VARCHAR) , new SqlParameter("p2", Types.VARCHAR) , new SqlParameter("p3", Types.VARCHAR)); Map<String, Object> result = procedureParametersCall.execute(new MapSqlParameterSource() .addValue("p1", "p1value") .addValue("p2", "p2value") .addValue("p3", "p3value")); System.out.println(result); } The exception that I get is below. Please note the call string. I think the bug is in the CallMetaDataContext.java, createCallString. In the loop at line 542 parameterCount is not incremented if parameter.isResultsParameter() is true. Exception in thread "Main Thread" org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{? = call TEST_FUNCTION(?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 13: PLS-00905: object TEST.TEST_FUNCTION is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:111) at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:949) at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:982) at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:362) at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:336) at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:166) at org.bchousing.common.jdbc.spring.DefaultPlSqlProcedureMetaDataProvider.main(DefaultPlSqlProcedureMetaDataProvider.java:194) Caused by: java.sql.SQLException: ORA-06550: line 1, column 13: PLS-00905: object BOGDA.TEST_FUNCTION is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:626) at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:184) at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:873) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1090) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2905) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:2996) at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4120) at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:984) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:933) ... 5 more Now, if I use this call instead - this time I used SqlOutParameter instead of SqlReturnResultSet - it works fine: public static void main(String[] args) throws Exception { oracle.jdbc.pool.OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource(); ods.setURL("jdbc:oracle:thin:bogda/sara@genie01:1521:orcl"); // DefaultPlSqlProcedureMetaDataProvider metaDataProvider = new DefaultPlSqlProcedureMetaDataProvider(ods); // System.out.printf("List %s", String.valueOf( // metaDataProvider.getProcedureParameters("utils_pkg", "get_procedure_parameters", null))); SimpleJdbcCall procedureParametersCall = new SimpleJdbcCall(ods); procedureParametersCall .withFunctionName("test_function") .withoutProcedureColumnMetaDataAccess() .declareParameters(new SqlOutParameter("return", OracleTypes.CURSOR, new RowMapper() { public Object mapRow(ResultSet rs, int rowNum) throws SQLException { return rs.getString(1); } }) , new SqlParameter("p1", Types.VARCHAR) , new SqlParameter("p2", Types.VARCHAR) , new SqlParameter("p3", Types.VARCHAR)); Map<String, Object> result = procedureParametersCall.execute(new MapSqlParameterSource() .addValue("p1", "p1value") .addValue("p2", "p2value") .addValue("p3", "p3value")); System.out.println(result); } I am not too sure what is the semantics of the SqlReturnResultSet parameter, but I thought it can be used to for returning resultsets or cursors.
    via by Costa Basil,
  • I created the following oracle function: CREATE OR REPLACE FUNCTION test_function (p_p1 VARCHAR2, p_p2 VARCHAR2, p_p3 VARCHAR2) RETURN SYS_REFCURSOR AS l_result SYS_REFCURSOR; BEGIN OPEN l_result FOR SELECT p_p1 || ' ' || p_p2 || ' ' || p_p3; FROM sys.dual; RETURN l_result; END; Now here is the java code: public static void main(String[] args) throws Exception { oracle.jdbc.pool.OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource(); // replace the settings below in the url with the ones specific to your environment ods.setURL("jdbc:oracle:thin:<username>/<password>@<server name>:1521:<db name>"); // DefaultPlSqlProcedureMetaDataProvider metaDataProvider = new DefaultPlSqlProcedureMetaDataProvider(ods); // System.out.printf("List %s", String.valueOf( // metaDataProvider.getProcedureParameters("utils_pkg", "get_procedure_parameters", null))); SimpleJdbcCall procedureParametersCall = new SimpleJdbcCall(ods); procedureParametersCall .withFunctionName("test_function") .withoutProcedureColumnMetaDataAccess() .declareParameters(new SqlReturnResultSet("return", new RowMapper() { public Object mapRow(ResultSet rs, int rowNum) throws SQLException { return rs.getString(1); } }) , new SqlParameter("p1", Types.VARCHAR) , new SqlParameter("p2", Types.VARCHAR) , new SqlParameter("p3", Types.VARCHAR)); Map<String, Object> result = procedureParametersCall.execute(new MapSqlParameterSource() .addValue("p1", "p1value") .addValue("p2", "p2value") .addValue("p3", "p3value")); System.out.println(result); } The exception that I get is below. Please note the call string. I think the bug is in the CallMetaDataContext.java, createCallString. In the loop at line 542 parameterCount is not incremented if parameter.isResultsParameter() is true. Exception in thread "Main Thread" org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{? = call TEST_FUNCTION(?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 13: PLS-00905: object TEST.TEST_FUNCTION is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:111) at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:949) at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:982) at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:362) at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:336) at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:166) at org.bchousing.common.jdbc.spring.DefaultPlSqlProcedureMetaDataProvider.main(DefaultPlSqlProcedureMetaDataProvider.java:194) Caused by: java.sql.SQLException: ORA-06550: line 1, column 13: PLS-00905: object BOGDA.TEST_FUNCTION is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:626) at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:184) at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:873) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1090) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2905) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:2996) at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4120) at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:984) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:933) ... 5 more Now, if I use this call instead - this time I used SqlOutParameter instead of SqlReturnResultSet - it works fine: public static void main(String[] args) throws Exception { oracle.jdbc.pool.OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource(); ods.setURL("jdbc:oracle:thin:bogda/sara@genie01:1521:orcl"); // DefaultPlSqlProcedureMetaDataProvider metaDataProvider = new DefaultPlSqlProcedureMetaDataProvider(ods); // System.out.printf("List %s", String.valueOf( // metaDataProvider.getProcedureParameters("utils_pkg", "get_procedure_parameters", null))); SimpleJdbcCall procedureParametersCall = new SimpleJdbcCall(ods); procedureParametersCall .withFunctionName("test_function") .withoutProcedureColumnMetaDataAccess() .declareParameters(new SqlOutParameter("return", OracleTypes.CURSOR, new RowMapper() { public Object mapRow(ResultSet rs, int rowNum) throws SQLException { return rs.getString(1); } }) , new SqlParameter("p1", Types.VARCHAR) , new SqlParameter("p2", Types.VARCHAR) , new SqlParameter("p3", Types.VARCHAR)); Map<String, Object> result = procedureParametersCall.execute(new MapSqlParameterSource() .addValue("p1", "p1value") .addValue("p2", "p2value") .addValue("p3", "p3value")); System.out.println(result); } I am not too sure what is the semantics of the SqlReturnResultSet parameter, but I thought it can be used to for returning resultsets or cursors.
    via by Costa Basil,
  • Transaction deadlock TX
    via Stack Overflow by Naini Archana
    ,
  • AIA Order to Cash Implementation Issue
    via by Bharathy,
  • Error while navigating in OIC
    via by 676255,
    • org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{? = call TEST_FUNCTION(?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 13: PLS-00905: object TEST.TEST_FUNCTION is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:111) at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:949) at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:982) at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:362) at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:336) at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:166) at org.bchousing.common.jdbc.spring.DefaultPlSqlProcedureMetaDataProvider.main(DefaultPlSqlProcedureMetaDataProvider.java:194) Caused by: java.sql.SQLException: ORA-06550: line 1, column 13: PLS-00905: object BOGDA.TEST_FUNCTION is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:626) at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:184) at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:873) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1090) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2905) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:2996) at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4120) at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:984) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:933) ... 5 more

    Users with the same issue

    Unknown visitor
    Unknown visitor1 times, last one,
    Unknown visitor
    Unknown visitor1 times, last one,
    Unknown visitor
    Unknown visitor1 times, last one,
    Unknown visitor
    Unknown visitor1 times, last one,
    Unknown visitor
    Unknown visitor1 times, last one,
    76 more bugmates