0 Replies Latest reply: Jan 12, 2013 8:44 AM by 894590 RSS

    Cannt execute stored proc of one schema in another schema from java app.

    894590
      I am posting my problem in this forum as i i though it could be server-independent.

      I am working on apache tomcat and spring framework with Oracle db (schema/user A)

      We access oracle db from our java application by setting jndi and works fine.We have sqlstatements, stored procs and functions all run fine.

      Now we create a role (DBROLE) with all permissions to that original db schema/user(A) . We created another empty schema B and assigned that role(DBROLE) to that user B.

      (We grant all kind of permissions on tables/packages of schema A to user role DBROLE and also created synonyms)

      Intentions are: to access the schema A though schema B from application and avoiding direct access.

      In our spring application, we replaced database-settings with schema B.

      Things work fine: When its plain SQL statement is run from Java code but Stored proc wont run and we get
      'Wrong num of arguments/data types' error.

      Also all stored procs are in packages.To execute stored proc in java code, we use SimpleJdbcCall.

      I also checked run stored proc from schema B and its works. Only from web app, it doesnt work.

      Please suggest,what should be done to make this working or if there is other alternative.

      Thanks