springoraclejdbcspring-jdbcjdbc-odbc

SimpleJdbcCall - Error in executing oracle procedure

I am trying to execute procedure from SimpleJdbcCall's execute() method (i.e. executing stored procedure in Spring), but it giving error because I have double quotes in my package name - "_test_package". Error says that, procedure much be declared even though it do exists.

I have following package having procedure in Oracle - 

Package Spec :-
CREATE OR REPLACE package GOVINDS."_test_package" is

    procedure createHelloMessage(namemsg in out VARCHAR2);

end "_test_package";

Package Body :-
CREATE OR REPLACE package body GOVINDS."_test_package" is

    procedure createHelloMessage(namemsg in out VARCHAR2) is
    begin
        namemsg := 'Hello '|| namemsg;
    end createHelloMessage;

end "_test_package";

I am trying to eexcute it from Spring's SimpleJdbcCall as below, but it giving error as PLS-00201: identifier 'GOVINDS._TEST_PACKAGE' must be declared.

SimpleJdbcCall simpleJdbcCallObject =
    new SimpleJdbcCall(jdbcTemplate).withSchemaName("GOVINDS").withCatalogName("\"_test_package\"").withProcedureName("createHelloMessage")
        .withoutProcedureColumnMetaDataAccess()
        .withNamedBinding()
        .declareParameters(new SqlParameter("namemsg", OracleTypes.VARCHAR));

SqlParameterSource in = new MapSqlParameterSource().addValue("namemsg", "Jim", OracleTypes.VARCHAR);

Map<String, Object> out = simpleJdbcCallObject.execute(in);

Error stackstrace :

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call GOVINDS."_TEST_PACKAGE".CREATEHELLOMESSAGE(namemsg => ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'GOVINDS._TEST_PACKAGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1094) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1130) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:405) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:365) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:198) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]
...
...
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'GOVINDS._TEST_PACKAGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1041) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]

If I try to execute using jdbcTemplate.call(), it works fine. I am preferring SimpleJdbcCall.execute() because for consistency I don't want to change 95-97% code.

Solution

  • Issue is spring jdbc converts calling statement string into the uppercase (even object name is provided in double quotes) and if package-name provided in double quotes is case-sensitive.

    Solution can be creating synonym for package-name.

    create or replace synonym GOVINDS.test_package for GOVINDS."_test_package";