I am unable to comprehend the Oracle documentation. :-(
Does anybody have pointers for an example how you can fetch multiple rows of simple data from Oracle via OCI?
I presently use OCIDefineByPos to define single variables (I only have to do that for straightforward integers -- SQLT_INT/4-byte ints) after which fetch just one row at any given time with OCIStmtExecute/OCIStmtFetch2. This really is suitable for small quantities of data however it takes around .5ms per row then when reading through a couple of 10000 rows this really is not fast enough.
I simply do not understand the documentation for OCIBindArrayOfStruct etc. and can't find any example code that simply brings data a couple of 1000 rows at any given time.
Perhaps you have checked out the sample code in $ORACLE_HOME/oci/samples (without having them installed, then run the Oracle Installer and tell it to set up sample code). You will find several which use the majority connects.
You might want to seriously get a library rather. I have coded Professional*C (hate it), straight OCI, and used third party libraries. The final is the greatest, with a large margin. The OCI syntax is actually hairy and it has options you'll most likely never use. Simultaneously it's very, very rigid and can crash your code should you choose things even slightly wrong.
If you are using C++ i quickly can suggest OTL I have done some serious performance testing and OTL is equally as fast as hands coding for that general situation (you can better it by 5-10% knowing for several you have no NULLs inside your data and therefore don't need indicator arrays). Note -- don't try to understand the OTL code. It's pretty hideous. However it works very well.
You will find also numerous C libraries available that wrap OCI making it more functional and fewer prone to bite you, however i haven't examined them.
If little else, do your favor and write wrapper functions for that OCI code to create things simpler. Used to do this during my high end scenario also it drastically reduced the amount of issues I'd.
You should use OCIDefineArrayOfStruct to aid fetching arrays of records. You need to do this by passing the bottom of the array to OCIDefineByPos, and employ OCIDefineArrayOfStruct to inform Oracle about how big the records (skip size). In my opinion that you simply then call OCIFetch telling it to fetch the array size.
An alternate would be to set the statement attribute , OCI_PREFETCH_ROWS, prior to it being performed. This informs Oracle the number of rows to fetch at any given time, it defaults to at least one. By using this approach, Oracle makes less round outings and buffers the rows for you personally.
OCIBindArrayOfStruct can be used with DML claims. It really works similarly to OCIDefineArrayOfStruct with the exception that it really works with bind variables.
You'll find sample code around the Oracle website.
Best of luck! OCI is incomprehensive.
To be sure, the OCI documentation is rubbish (although there's lots of text and diverse copies located everywhere). As if you, I'd trouble finding example code but finally found the origin for many available here: http://files.edin.dk/php/win32/dev/php_build/oci805/samples/