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

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

>NetComrade wrote:
>> 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?

Probably I confuse you a bit here.. the actual implementation is an array of records.. Difference between records and objects, if I recall, is that objects have methods.. i think :) sorry for the confusion. As comparing it to a table.. you can say a record is a row of a table.. but that's the thing.. I don't want to return a row of a table, I want to return an array of records, since the array would be populated from different sources.

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

I am almost sure, that some of the things we are doing could be done in SQL, since a lot of it has been written years ago. But when you look thru one sql statement, and have to select from different tables based on each row, and extra fetching can produce multiple additional rows (cancelling the option of a function), we are forced to look into an array of records (note, ideally, the record would have a maximum length equal to maximum number of columns returned in record-set, so a return could be something like)

field1	fileld2	filed3	field4	field5	field6	field7	field8
abc	098	987	78	987	98	123	12
dba	32	243
zvq	123	323	23	08	78	98
abk	12	12	32	098	
zds	kkk	
bil	asd	13	123	32

Any of those rows can come from a number of queries, and some 'fields' within each record can be empty, as you can see

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

That is what we are trying to accomplish, but are facing higher overhead than we expected.

>- PL/SQL block embedded in the Pro*C code

I think we looked into this, but I'll take another look. How will that help with network traffic?

>- OraParamArray (not sure which version this was introduced)

Will look into

>- dropping to OCI

Um.. my understanding was that Pro*C is just some wrap around OCI (once again, i am not a developer), and any oci call should be available in pro*c.. and how would we be able to copy arrays of records in bulk in OCI :)
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Tue Dec 02 2003 - 15:10:58 CST