Jan 12th, 2013, 05:52 PM
trouble with stored proc execution from a spring app.
I am not sure if this is Database issue or can be solved by some changes in java code so i am writing my issue here also.
I am using Oracle DB(Lets say DB1) and apache tomcat and JNDI datasource with Spring app.
We have stored procedures in packages and normal queries executed from spring app using SimpleJdbcCall, JdbcTemplate.
Everything works fine.
Now as per requirement, we modified database part little.
We create a User Role (MYROLE) and granted all the permissions to this role on DB1.
We created another db schema/user - DB2 ,create public synonyms and assigned user role MYROLE to this user DB2.
And in spring app, we changed our jndi settings to datasource : DB2.
Motive is : to prevent direct access and this solution was proposed.
It worked only for normal sql execution(where we have direct sql) but for stored proc in packages ,we get error messages-
Wrong number of types/arguments.
I can execute the stored proc by logging into to to sqlplus as 'DB2' But cannt run from Java app.
If some aware of solution , please help.