This discussion is archived
0 Replies Latest reply: Sep 17, 2012 7:39 AM by user521219 RSS

Reference different schema using “alter session set current_schema” in pkg

user521219 Newbie
Currently Being Moderated
Is it possible to do
alter session set current_schema=MySchema;
inside a package?

Our asp.net web application call Oracle packages. We'd like to connect to database with an Oracle user that is not the owner of MySchema. For that, we grant execute permissions on Other_User to package MyPackage.

Example:
grant execute on MySchema.MyPackage to Other_User
But when web app connects to Oracle and try to execute the stored procedures of MyPackage, it gets errors because tables don't belong to Other_User.

One way to avoid errors is creating synonyms, but we would prefere to use
alter session set current_schema=MySchema;
if possible, inside the package.

Legend

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