Re: HELP ! OCI v7.3 to v8.0 conversion problems

TWO:
OCI_ATTR_DATA_TYPE gives you back the internal oracle types: 2 = numeric
12 = date
...
(I never used OCI_ATTR_TYPE_NAME so can't tell you).

PPS:
As far as I know it's not possible to work from more than one thread on the same OCIStmt handle.

hope this helps ( a little )

regards
Markus

Don Elliott wrote:
>
> 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 Thu Jul 08 1999 - 10:13:06 CDT