Re: Temporary work table
Date: Thu, 3 Dec 1998 22:59:50 -0000
Message-ID: <36675fe5.0_at_paperboy.telerama.com>
We dealt with exactly this problem. We have a work table with the key
columns user_name, report_name and seq_no. You can use either their login
name (given by the built-in function USER) or the osuser (from v$session)
for user_name. Report_name is the name of the PL/SQL report that is writing
to the work table. This way, many users can run the same report and not
stomp on each other. The seq_no is to keep the order in which rows were
inserted.
All of our report PL/SQL packages call an init routine which deletes rows
for user_name, report_name from the work table, so you have a clean start.
Once the work data is generated, we use Crystal reports to read it. You
could therefore generate the data once and run the report many times,
perhaps with additional filtering criteria.
As you might expect, this work table gets very large and heavily fragmented.
But writing complex PL/SQL reports, followed by a relatively simple Crystal
report, is much faster than building a very complex Crystal. And some things
you just can't do in Crystal, we can do in PL/SQL.
-- - Dan Clamage http://www.telerama.com/~dclamage If you haven't crashed the Server, you haven't been trying hard enough. Todd wrote in message <747ia3$sal$1_at_news.seed.net.tw>...Received on Thu Dec 03 1998 - 23:59:50 CET
>We are coding report for more than one user run at the same which use
>temporary work table to process and store data before format the report.
>There are two way to do this:
>
>1. One work table for all users, the data generated by individual user is
>identified by session id or user id.
>
>2. Users create their own table during report running, so we got
>more than one tables as the report executed by mutilple users.
>
>Since these data are only available during report running, the data became
>useless after report generated. So we would like to know which method is
>better.
>
>Any comment is welcome!
>
>
>