Re: Database & Oracle Reports 10g - passing large param lists
- From: malcolm arnold <malcolmarnold@xxxxxxxxx>
- To: bob_murching@xxxxxxxxx
- Date: Wed, 30 Nov 2005 11:02:39 +0000
Bob,
No one has responded yet, so thought I'd toss out a reply and let you
be the judge. ;)
I would personally favour doing exactly what you are reluctant to do:
'have the middle tier dump a list of these flagged records into the
database'.
I don't know of any tidy way to stream a large dataset from J2EE to
Reports. But in senario you describe, I think anything you do will
incurr a large workload on the backend. How would your report deal
with ten of thousands of IDs? I hope not by executing ten of
thousands of queries?
If you had a table containing these IDs in the database it could be
directly referenced (joined) into your report query. That would be
the most efficient way to do it.
Inserting a 15 character value into a un-indexed global temporary
table is probably the easiest of all DML for Oracle to perform. If
you used bulk binds, 100 records per insert say, you would need a slow
system for ten thousand records to take more than a second or so.
I think the code would be simpler and more maintainable too.
Keep us informed with what you decide to do.
Regards,
Malcolm.
On 29/11/05, Murching, Bob <bob_murching@xxxxxxxxx> wrote:
>
>
> This may be more of an application architecture or design question, and it's
> certainly an Reports 10g question; I'll just toss it out and you be the
> judge :)
>
> The environment: J2EE application, session management, etc. 10g back-end.
> User can pull up a list with potentially thousands or tens of thousands of
> records, paginated of course. They can arbitrarily flag or unflag records
> on this list at will, either one at a time with checkboxes, or by applying a
> variety of filters. The list of "flagged" records is maintained at the
> session level in the middle tier. The user clicks a button and Reports 10g
> is invoked to generate a mongo PDF for only those records which are flagged.
> The flags needn't be stored in the back-end, there is no need to persist
> them. At least, that's the idea.
>
> The problem: at the session level, user might end up flagging tens of
> thousands of records. How can I pass this to Reports so that it knows which
> records to include in its report? It can't be a URL parameter, of course.
> One solution is to have the middle tier dump a list of these flagged records
> into the database (a temp table, perhaps), where Reports 10g can get at 'em.
> Unfortunately then I'm incurring some overhead on the back-end *and* I'm
> faced with potentially millions of records of DML per day just for using the
> database as a temporary scratchpad.
>
> Is there a way to stream or send a large result set from J2EE to Reports 10g
> directly without using the RDBMS as an intermediary? Minimally we're
> talking about tens of thousands IDs, each potentially 15 characters long.
>
> Bob
--
//www.freelists.org/webpage/oracle-l
Other related posts:
