Re: Dev2000:Executing a pl/sql block from a form

Raj Pannala wrote:
>
> Hi,
> Thanx a lot for responding.
> I have a little problem. I have a report that i need to develop using
> Oracle Reports 2.5. Since the report was extremely complicated and since
> the information on the report required the usage of multiple views, i
> decided to write a pl/sql block which basically selects and
> inserts information from all the tables that i need to use into a
> temporary table. This temporary table has the information in the way i
> want, so now i can query this
> temporary report and then i would have the report.
>
> Now, the way this would work is i would create a table in the pre-form
> trigger of the form that calls the report using a forms-ddl built-in
> routine and then when the report is called from the when button pressed
> trigger in the form, the code in the pre-form trigger of the form
> would execute which is actually the pl/sql block and then the temporary
> table is populated even before the report comes up on the screen and then
> when the report is run, the select query in the query editor of the
> report would actually query the temporary table.
>
> well, anyway this is to give you the complete scenario, but the problem
> is that the code works fine and inserts records when run at the SQL
> prompt using the _at_c:\filename syntax BUT does not work when run from the
> pre-form trigger in the form.. i get an error called ORA-01002 called
> fetch out of sequence. I think there are no errors in the code itself
> since it works in the sql prompt. i also tried using the same code in a
> when button pressed trigger and when new form instance trigger but to no
> avail. I also tried putting the pl/sql block code in a program unit and
> then calling the program unit from the pre-form trigger, but i still get
> the same error.
>
> I have a Jan 8 deadline to meet and would greatly appreciate any help in
> this regard. Thanx a lot.
> I would have also sent the pl/sql block code with this but it is huge.
>
> U can please email to me at my friends email address:
> rpannala_at_worldnet.att.net
>
> bye!!
>
> Naveen Vedula
> >
When you used Forms_ddl built-in, you issued an implicit commit which is a restricted function. In a pre- or post-form trigger, you can not use any restricted function. Of course you do need an explicit commit after you populate your temp table. So one possible solution is to move your DDL and/or DML functions into a when-new-form-instance trigger instead of pre- and post-form triggers. But be careful when you use those DML statements in that kind trigger. Another choice is to move them into your when-button-press trigger starting the reports. In this way you may need to create a facility to avoid to populate the temp table twice. Good luck.

Dan Chen
PHRI Received on Tue Jan 07 1997 - 00:00:00 CET