I need to run a report with a user that uses objets from a diferent schema. I have used the before_parameter_form trigger to call a database procedure that executes an "alter session set current_schema = XXX" and with simple reports with only some querys in the data model the schema change works fine.
The problem comes when I try to run a report with some program units, for example a formula column that defines a cursor on a table from that schema. This kind of report fails during the first execution, throwing a "REP-1247: Report contains uncompiled PL/SQL" exception. After that first fail, the report works just fine while the database session that the reports server opens is alive.
My guess is that reports server first compiles the report, and if we make use of an object from another schema, it fails, but somehow also executes the before_parameter_form trigger so the default schema gets changed for that session, so afterwards the report keeps working just fine.
Is there any way of forcing the default_schema change before the report compilation?
Using synonyms is not an option, that's the main goal of all those changes.
Thanks!