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

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

What I'm getting at ...

>
> 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)

Up to this point, it MIGHT have been possible to accomplish this in single, but moderately complex, SELECT. Possibly with the use of PL/SQL functions called in the SELECT or WHERE clause, and often using inline views and maybe the 'WITH' clause.  

Possible point of confusion - what do you mean by 'object'? And why does this need to be an object, as compared to a table? Are you using collections?

> <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)

This is the part that I was thinking about - in some cases people are using Pro*C or other procedural code, just 'cause they can't see a way to do it using SQL.

>
> 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)

A generalism (which may not be appropriate for your case) is that anything that needs more than 1/2 dozen round trips should be carefully re-evaluated to determine whether more can be resolved at the database side.

Other things you might also want to investigate:

• PL/SQL block embedded in the Pro*C code
• OraParamArray (not sure which version this was introduced)
• dropping to OCI

HTH
/Hans Received on Tue Dec 02 2003 - 11:48:04 CST