Re: Calling REPORT (with PL/SQL) from FORM4.5

DanHW wrote:

> I'm not sure exactly what the problem is, but there are 2 things that deserve
> some further investigation. You said that you set the role in the trigger.
> Remember that the set-role function REPLACES the old roles with the new
> one(s).Perhaps you no longer have the roles you started with and can no longer
> see the objects you originally were using.
>
> Secondly, I have had the most success with making sure that the Oracle user who
> generates the form has the appropriate access to **ALL** tables granted to it
> explictly, not via roles. It looks like it is related to the 'can't create
> stored procedure using privileges granted by roles' error, but SQL*Reports
> doesn't give you an error because it just assumes it will recompile it when it
> runs. Also, check that you are not referencing any views or packages that are
> invalid.
>

Thank's for your help.
Today I have phoned the oracle customer support. They said, that it is to late to set the role in the BEFORE PARAMETER FORM trigger. The report was compiled at first, all pl/sql was checked an then the reports server is started. The set role - statement works to late and so it comes to the error message "there is uncompiled pl/sql". If the reports server is running, the second run of the report works fine.
Oracle tells me three possibilities to solve the problem:

1.) Write a dummy-report with only the set role statement to set the permissions and start the reports server. Then start the real report.

2.) Use the SRW-package (SRW.DO_SQL('Statement')). All PL/SQL-Statements have to write with srw.do_sql, then the first compilation works without errors an the report runs at the first trying.

3.) Use reports 3.0. There you have the possibility to set a role as a property of the report in the object navigator. But reports 3.0 works only
with forms 5.

Bye..Sigrid Received on Mon Apr 20 1998 - 00:00:00 CEST