SimpleJdbcCall - Error in executing oracle procedure

Question

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.

score 0 · Answer 1 · answered Jun 21 '16 at 07:01

0

This is because, double quotation string is case sensitive, some how spring JDBC is converting your string to uppercase however your actual procedure name is in small case. Try to change procedure name to upper case, your code should run.

answered Jun 21 '16 at 07:01

Chetan

1

score 0 · Accepted Answer · answered Mar 07 '17 at 09:06

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

SimpleJdbcCall - Error in executing oracle procedure

2 Answers2