Re: Any way to disable UNDO (rollback) with temporary tables?

Err.. sure.. I don't actually develop these things.. But logic could go something like this

pro*c call PL/SQL proc

PL/SQL proc:
open a cursor
  fetch/loop
    if some variable fetch from other table     if not fetch from other_2 table
  <possibly deeper logic>
populate an array of objects (e.g. each object contains 20 columns, some of which could be null)
<hopefully return an array>
<if not, populate a temporary table that matches the object> end;

Pro*c needs to retrieve an array of objects. Best method we found so far is fetching from temporary table, since that allows batch fetches, but we'd like to do this with IN/OUT PL/SQL vars, which doesn't seem possible (and seems dumb to me that it's not, but Oracle says we can't)

All of this logic right now is in middle tier, and certain pages (applications) require 100's of round-trips to oracle, which we are trying to avoid. Oracle has showed us a case where we can copy objects back to Pro*C, but it does so one by one, which defeats the idea of eliminating network round trips, another things we tried is passing multiple arrays (e.g. one for each column of the object), but that seemed even worse (network traffic increased dramatically)

Hope this helps..
Thanks.

On Tue, 02 Dec 2003 04:20:25 GMT, Hans Forbrich <forbrich_at_yahoo.net> wrote:

>NetComrade wrote:
>>
>> I kind of understand the reason behind storing undo for temporary
>> tables, but in our case, I believe we just don't need it.
>>
>> We are using temporary tables as 'arrays', since it seems like Oracle
>> cannot pass back (in bulk) arrays of objects back to Pro*C. So we do a
>> lot of logic within PL/SQL, and populate a generic temporary table,
>> and then fetch from it in Pro*C. However, the overhead of writing UNDO
>> (and extra CPU processing that comes with it) seems unnecessary. I
>> looked at various 'events', but none of them seem to offer what we
>> need.
>>
>> Any way this could be done?
>>
>
>I don't know the answer to your question. But ...
>
>The last time I had a discussion about something like this with a
>developer, on digging into the entire requirement we found that he
>didn't even need to use procedural logic - just set management and
>moderately advanced SQL skills.
>
>Could you expand on what you are trying to accomplish, possibly with
>pseudo-code.

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Tue Dec 02 2003 - 10:19:54 CST