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
  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

    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
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Io exception: End of TNS data channel

    Stack Overflow | 5 years ago | ıllıllı lק ıllıllı
    java.sql.SQLException: Io exception: End of TNS data channel
  6. 0

    AIA Order to Cash Implementation Issue

    Oracle Community | 7 years ago | Bharathy
    java.sql.SQLException: ORA-06531: Reference to uninitialized collection ORA-06512: at "APPS.OE_INBOUND_INT", line 4947 ORA-06512: at line 1 Error Code: 6531

    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