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

>DanHW wrote:
>>
>> We have the same situation - calling a report that needs to have roles set
>> before it reports anything. What we do is in the BEFORE-PARAMETER-FORM
 trigger,
>> set the appropriate roles. We have lots of PL/SQL in the report, and all
 works
>> fine. We also only give the users the RPT version of the form, generated by
 a
>> user with default access to the tables. The users do not get run the REP
>> version.
>>
>
>Hallo,
>
>I'm also using the BEFORE-PARAMETER-FORM trigger to set the role,
>nevertheless I have problems with a CF-Field (Column-Field).
>In the trigger of this field I also set the role and I want
>to execute a select - statement to get additional informations.
>The select based on a field (it is part of the where-clause) that
>is a part of another reports-query and the select should only be
>executed if this field is not null. So I want to emulate an outer join.
>
>When the report (*.rdf - File) runs for the first time a compilation
>error occur "...there is no compiled pl/sql". It seems like not enouth
>permissions for the user. The second run of report works fine and the
>only one difference to the first is that the reports server is running.
>What's wrong?
>
>
>Regards,
>
>Sigrid
></PRE></HTML>

[Quoted] 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.

Good luck

Dan Received on Mon Apr 20 1998 - 00:00:00 CEST