PLS-00306: wrong number or types of arguments in call when calling from Spring JDBC

Question

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar  
[{call pkg_name.procedure_name()}]; nested exception is java.sql.SQLException: ORA-06550: line 1,  column 7:
PLS-00306: wrong number or types of arguments in call to 'PROCEDURE_NAME'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I have two schemas- OwnerSchema and AppSchema. I have grant execute privileges on all the packages from OwnerSchema to AppSchema. When I try to execute any procedure from the back end of AppSchema, I am able to execute the procedure. When I try to execute the same procedure from Java Spring JDBC, I get above error. I am able to execute this procedure from OwnerSchema from Java.

I am able to access all the tables from Java Spring JDBC application with AppSchema.

I have used following trigger to point to the OwnerSchmea on LogOn.

 CREATE OR REPLACE TRIGGER FINAL_APP_USER.AFTER_LOGON_TRG
    AFTER LOGON ON FINAL_APP_USER.SCHEMA
    BEGIN
      DBMS_APPLICATION_INFO.SET_MODULE(USER, 'Initialized');
    EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=TEST'; --enter owner
    END;
    /

Here is the call to procedure

SimpleJdbcCall simpleJdbcCall = getSimpleJdbcCall()
                    .withCatalogName("pkg_name").withProcedureName(
                            "procedure_name").returningResultSet(
                            "C_Srf_1", new RowMapper<FinalCountForQuestions>() {
                                public FinalCountForQuestions mapRow(
                                        ResultSet rs, int rowNum)
                                        throws SQLException {

                                    ...

                                    return finalCountForQuestions;
                                }
                            });

            Map<String, Object> mapOfOutputParams = simpleJdbcCall.execute();

Answer 1

Spring JDBC template generates following query while making call to database procedure

SELECT PACKAGE_NAME AS PROCEDURE_CAT,
OWNER AS PROCEDURE_SCHEM,
OBJECT_NAME AS PROCEDURE_NAME,
ARGUMENT_NAME AS COLUMN_NAME,
DECODE(POSITION, 0, 5,
DECODE(IN_OUT, 'IN', 1,
'OUT', 4,
'IN/OUT', 2,
0)) AS COLUMN_TYPE,
DECODE (DATA_TYPE, 'CHAR', 1,
'VARCHAR2', 12,
'NUMBER', 3,
'LONG', -1,
'DATE', 91,
'RAW', -3,
'LONG RAW', -4,
'TIMESTAMP', 93, 
'TIMESTAMP WITH TIME ZONE', -101, 
'TIMESTAMP WITH LOCAL TIME ZONE', -102, 
'INTERVAL YEAR TO MONTH', -103, 
'INTERVAL DAY TO SECOND', -104, 
'BINARY_FLOAT', 100, 'BINARY_DOUBLE', 101, 1111) AS DATA_TYPE,
DECODE(DATA_TYPE, 'OBJECT', TYPE_OWNER || '.' || TYPE_NAME, DATA_TYPE) AS TYPE_NAME,
DECODE (DATA_PRECISION, NULL, DATA_LENGTH,
DATA_PRECISION) AS PRECISION,
DATA_LENGTH AS LENGTH,
DATA_SCALE AS SCALE,
10 AS RADIX,
1 AS NULLABLE,
NULL AS REMARKS,
SEQUENCE,
OVERLOAD,
DEFAULT_VALUE
from ALL_ARGUMENTS
where OWNER like '<USER>' escape  '/'
and OBJECT_NAME like '<PROCEDURE_NAME>' escape '/' 
and PACKAGE_NAME like '<PACKAGE_NAME>' ESCAPE '/'                   
AND (ARGUMENT_NAME LIKE 'C_SRF_1' ESCAPE '/'
OR (ARGUMENT_NAME IS NULL
and DATA_TYPE is not null))
--and
--other arguments
ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, OVERLOAD, SEQUENCE;

So when you are using an ApplicationUser to call the database procedure, it will look into ALL_ARGUMENTS view with you ApplicationUser name and will not find any arguments. So, solution for this is, when you make a call to a procedure, pass the schema name of owner as follows.

SimpleJdbcCall simpleJdbcCall = getSimpleJdbcCall()
                    .withSchemaName(<OwnerSchema>)
                    .withCatalogName("pkg_name").withProcedureName(
                            "procedure_name").returningResultSet(
                            "C_Srf_1", new RowMapper<FinalCountForQuestions>() {