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

Spring JIRA | Costa Basil | 9 years ago
tip
Your exception is missing from the Samebug knowledge base.
Here are the best solutions we found on the Internet.
Click on the to mark the helpful solution and get rewards for you help.
  1. 0

    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.

    Spring JIRA | 9 years ago | Costa Basil
    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
  2. 0

    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.

    Spring JIRA | 9 years ago | Costa Basil
    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
  3. 0

    Exception in executing SimpleJdbcCall for fetching resultset/REF cursor

    Coderanch | 8 years ago | Anadi Misra
    org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call GET_EVENT_BY_TITLE(?, ?, ?, ?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "APPUSER.GET_EVENT_BY_TITLE", line 10 ORA-06512: at line 1
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Java Language - java.sql.SQLException: ORA-06550

    Stack Overflow | 3 years ago | fedel1234
    java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00201: identifier 'ROCWEB.USERLIST_DATAPROV' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored
  6. 0

    Transaction deadlock TX

    Stack Overflow | 4 years ago | Naini Archana
    java.sql.SQLException: ORA-20011: FUNC_UPDATESERVICETIME : Error occured ORA-00060: deadlock detected while waiting for resource ORA-06512: at "ER.FUNC_UPDATESERVICETIME", line 154 ORA-06512: at line 1

    1 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. 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()
    2. Oracle jdbc
      OracleCallableStatement.execute
      1. oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
      2. oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
      3. oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
      4. oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:626)
      5. oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:184)
      6. oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:873)
      7. oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1090)
      8. oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2905)
      9. oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:2996)
      10. oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4120)
      10 frames
    3. Spring Framework
      SimpleJdbcCall.execute
      1. org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:984)
      2. org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:933)
      3. org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:982)
      4. org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:362)
      5. org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:336)
      6. org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:166)
      6 frames
    4. org.bchousing.common
      DefaultPlSqlProcedureMetaDataProvider.main
      1. org.bchousing.common.jdbc.spring.DefaultPlSqlProcedureMetaDataProvider.main(DefaultPlSqlProcedureMetaDataProvider.java:194)
      1 frame