Re: Help!!! Pl/SQL and Report

The trick is to store the data in the temporary table by executing PL/SQL [Quoted] in the before report trigger, something like this:

BEFORE_REPORT

function before_reporttrigger return boolean is
   cursor c_tab
   is select tab.col1
      ,         tab.col2
      ,         tab.col3
      from   my_table   tab
      where  <condition>
   ;
begin
   for r_tab in c_tab
   loop
       insert into temp_table
          ( col1
          , col2
          , col3
          ) values
          ( r_tab.col1
          , r_tab.col2
          , r_tab.col3
          )
      ;
   end loop;
   commit;
   return true;
end;

The return statement is necessary because the trigger is a function. If it returns false, the report won't run. This is convenient when the cursor doesn't return any rows and the report would end up empty anyway.

In the after report trigger you might want to delete the data from the temp table.

That's how it's done, generally. This is also a very nice trick to make reports generation from Designer a lot easier..

I hope this helped.

Pieter Steenhuis

Oracle consultant
Redwood Software & Services B.V.
The Netherlands
Learn about our information lifecycle management suite at www.redwoodsw.com

tianh_at_my-deja.com wrote:

I need to create a report, which has to incorporate the PL/SQL to
process the data.

But if in the procedure, either I use Cursor Or Tempory Table to store
the data which has been process, How can I refer to those data stored in
Cursor or Temp_Table? ----The report need to retrieve the data from
Temp_table or Cursor.

I am anxious to get help, and appreciate very much for any advice.

Helen

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.