Xref: alice comp.databases.oracle.tools:32434 Path: alice!news-feed.fnsi.net!newsfeed.icl.net!colt.net!news.belnet.be!krypton.inbe.net!INbe.net!newsfeed2.news.nl.uu.net!sun4nl!news.worldonline.nl!not-for-mail From: Pieter Steenhuis Newsgroups: comp.databases.oracle.tools Subject: Re: Help!!! Pl/SQL and Report Date: Fri, 24 Sep 1999 00:08:38 +0200 Organization: WorldOnline News server Lines: 63 Message-ID: <37EAA4E6.B22BF349@worldonline.nl> References: <7se4n9$q2j$1@nnrp1.deja.com> Reply-To: pieter.steenhuis@worldonline.nl Mime-Version: 1.0 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: news.worldonline.nl 938124035 22080 195.241.236.14 (23 Sep 1999 22:00:35 GMT) X-Complaints-To: newsmaster@worldonline.nl NNTP-Posting-Date: 23 Sep 1999 22:00:35 GMT To: tianh@my-deja.com X-Mailer: Mozilla 4.61 [en] (Win95; I) X-Accept-Language: nl,nl-BE,en,en-GB,en-US Hi,

The trick is to store the data in the temporary table by executing PL/SQL 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@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.