HELP ! OCI v7.3 to v8.0 conversion problems
Date: Wed, 7 Jul 1999 23:33:05 +1000
Message-ID: <%GIg3.4004$yD2.7779_at_newsfeeds.bigpond.com>
[Quoted] Hi OCI Gurus (hopefully simple questions - just long descriptions),
I've got a generic unload program currently written in Oracle OCI v7.3 and am wondering (after a day of hitting brick walls) whether or not it is worth upgrading it to Oracle OCI v8.0.
The reason I was initially trying to upgrade it was for the promised "performance increases and improved scalability" as we are using this utility to unload tables with several hundred million rows on a Solaris platform, and any performance increases would be a bonus - however I've been presented with several headaches..
ONE...
v7.3 API flow is like this - logon, initialise, parse SQL (which is fed
through a parm card), describe all columns & bind them to large chunk of
memory for array fetching then fetch everything into an array.
When converting to v8.0, the parse step has gone and I now just execute -
the problem is I can't determine what my columns are before executing, and
hence can't give the execute API the size of my array, so I can't use array
fetch !!! Is this a big hole in the new API ?? Alternatives are to do
execute with describe only, get all the details and then cancel
(re-initialise I assume) and do the array allocation prior to a second
execute - the manual strongly suggests you don't do this - is there a reason
apart from the clumsiness ??
Other option is to use the OCIAttrSet() on the statement handle, set up a large pre-fetch buffer and just use standard 1 row fetches after my post-execute bind steps - how does this perform when compared with an internal array (we currently use an internal array of around 2Mb between all the columns - any more than this seems to blow up the program. Can I use more with the prefetch buffer ?)
Also, when trying to set the prefetch memory (OCI_ATTR_PREFETCH_MEMORY), I can't work out what the OCIAttrSet parms should be - the manuals don't say and there is no examples of calling OCIAttrSet with a statement handle...
TWO
Not worrying about the above problem, I pushed on to find yet another big
hastle. When doing the odescr() in v7.3, I was getting back a number of
useful sizes, namely the internal size of the column (which I can use to
allocate space for the read buffer) and the external/expanded size of the
column (which I can use directly - apart from 2 or three small exceptions -
when I write the external format data to a file. The manual was very dodgy
& didn't actually have one of the codes I was using, but I found it in the
header (OCI_ATTR_DISP_SIZE). The only remaining problem is that I can't
work out from the manual which type code is which - does OCI_ATTR_DATA_TYPE
give you the C type code (get INT, LONG INT)or the Oracle type code (NUMBER)
? What then does OCI_ATTR_TYPE_NAME give you ?
Also, what's the difference between OCI_ATTR_NAME and OCI_ATTR_DISP_NAME ?
Regards (in frustration),
Don
PS: We are using the program instead of BMC / Platinum as it is 3-4 times quicker than SQL*Plus and allows us to do joins, etc whereas BMC / Platinum unloads dont.
PPS: Architectural question - I see that you can have several threads doing
different SQL at once, but can you have several threads fetching from the
same cursor at once ?? One of the bottlenecks in our program is write time
(which we have optimised by using big output buffers), but if we were able
to 'parallelise' our writing to different files then it may speed us up even
further.
Received on Wed Jul 07 1999 - 15:33:05 CEST