0 Replies Latest reply: Sep 17, 2012 9:39 AM by user521219 RSS

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

      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.

      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.