This discussion is archived
0 Replies Latest reply: Jan 12, 2013 6:44 AM by 894590 RSS

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

894590 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points