Re: Database & Oracle Reports 10g - passing large param lists

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
--
http://www.freelists.org/webpage/oracle-l

Other related posts: