Oracle OCI array fetch of simple data types?

Question

I cannot understand the Oracle documentation. :-(

Does anybody have any pointers to an example how to fetch multiple rows of simple data from Oracle via OCI?

I currently use OCIDefineByPos to define single variables (I only need to do this for simple integers -- SQLT_INT/4-byte ints) and then fetch a single row at a time with OCIStmtExecute/OCIStmtFetch2. This is ok for small amounts of data but it takes around .5ms per row so when reading a few ten thousand rows this is too slow.

I just don't understand the documentation for OCIBindArrayOfStruct etc. and cannot find any example code that just fetches data a few thousand rows at a time.

Answer 1

Have you looked at the sample code in $ORACLE_HOME/oci/samples (if you don't have them installed, then run the Oracle Installer and tell it to install sample code). There are several that use the bulk interfaces.

You may want to seriously consider using a library instead. I've coded Pro*C (hate it), straight OCI, and used 3rd party libraries. The last is the best, by a large margin. The OCI syntax is really hairy and has options you will probably never use. At the same time it is very, very rigid and will crash your code if you do things even slightly wrong.

If you're using C++ then I can recommend OTL; I've done some serious performance testing and OTL is just as fast as hand coding for the general case (you can beat it by 5-10% if you know for certain that you have no NULLs in your data and thus do not need indicator arrays). Note -- do not try to comprehend the OTL code. It's pretty hideous. But it works really well.

There are also numerous C libraries out there that wrap OCI and make it more usable and less likely to bite you, but I haven't tested any of them.

If nothing else, do yourself a favor and write wrapper functions for the OCI code to make things easier. I did this in my high performance scenario and it drastically reduced the number of issues I had.

Answer 2

Good luck! OCI is incomprehensible.

Answer 3

You can use OCIDefineArrayOfStruct to support fetching arrays of records. You do this by passing the base of the array to OCIDefineByPos, and use OCIDefineArrayOfStruct to tell Oracle about the size of the records (skip size). I believe that you then call OCIFetch telling it to fetch the array size.

An alternative is to set the statement attribute , OCI_PREFETCH_ROWS, before it is executed. This tells Oracle how many rows to fetch at a time, it defaults to 1. Using this approach, Oracle makes fewer round trips and buffers the rows for you.

OCIBindArrayOfStruct is used with DML statements. It works in a similar fashion to OCIDefineArrayOfStruct except that it works with bind variables.

You can find sample code on the Oracle website.

Answer 4

I agree, the OCI documentation is rubbish (although there is a lot of text and numerous copies hosted everywhere). Like you, I had trouble finding example code but finally found the source for most available here: http://files.edin.dk/php/win32/dev/php_build/oci805/samples/