I cannot understand the Oracle documentation. :-(
我无法理解Oracle文档。 :-(
Does anybody know how to fetch multiple rows of simple data from Oracle via OCI?
有人知道如何通过OCI从Oracle获取多行简单数据吗?
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.
我目前使用OCIDefineByPos定义单个变量(我只需要对简单整数执行此操作 - SQLT_INT / 4字节整数),然后使用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.
这对于少量数据是可以的,但每行需要大约0.5毫秒,所以当读取几万行时,这太慢了。
I just don't understand the documentation for OCIBindArrayOfStruct. How can I fetch a few thousand rows at a time?
我只是不了解OCIBindArrayOfStruct的文档。我怎样才能一次获取几千行?
2 个解决方案
#1
2
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.
您可以使用OCIDefineArrayOfStruct来支持获取记录数组。您可以通过将数组的基础传递给OCIDefineByPos来完成此操作,并使用OCIDefineArrayOfStruct告诉Oracle有关记录的大小(跳过大小)。我相信你然后调用OCIFetch告诉它获取数组大小。
An alternative is to set the statement attribute, OCI_ATTR_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.
另一种方法是在执行之前设置语句属性OCI_ATTR_PREFETCH_ROWS。这告诉Oracle一次要获取多少行,默认为1.使用这种方法,Oracle减少往返次数并为您缓冲行。
OCIBindArrayOfStruct is used with DML statements. It works in a similar fashion to OCIDefineArrayOfStruct except that it works with bind variables.
OCIBindArrayOfStruct与DML语句一起使用。它的工作方式与OCIDefineArrayOfStruct类似,不同之处在于它适用于绑定变量。
You can find sample code on the Oracle website.
您可以在Oracle网站上找到示例代码。
#2
3
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.
您是否查看了$ ORACLE_HOME / oci / samples中的示例代码(如果您没有安装它们,请运行Oracle安装程序并告诉它安装示例代码)。有几种使用批量接口。
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.
您可能需要认真考虑使用库。我编写了Pro * C(讨厌它),直接OCI,并使用了第三方库。最后是最好的,大幅度的。 OCI语法非常多毛,有可能永远不会使用的选项。同时它非常非常严格,如果你做的事情稍微有点错误,你的代码就会崩溃。
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.
如果您正在使用C ++,那么我可以推荐OTL;我已经做了一些严重的性能测试和OTL是一样快的手编码一般情况下(您可以通过5-10%打败它,如果你肯定知道你在你的数据没有空值,因此不需要指标阵列)。注意 - 不要试图理解OTL代码。这很可怕。但它的效果非常好。
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.
还有许多C库包含OCI并使其更有用,更不容易咬你,但我还没有测试过它们。
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.
如果没有别的,请帮自己一个忙,并为OCI代码编写包装函数,以使事情变得更容易。我在高性能场景中做到了这一点,它大大减少了我遇到的问题数量。
#1
2
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.
您可以使用OCIDefineArrayOfStruct来支持获取记录数组。您可以通过将数组的基础传递给OCIDefineByPos来完成此操作,并使用OCIDefineArrayOfStruct告诉Oracle有关记录的大小(跳过大小)。我相信你然后调用OCIFetch告诉它获取数组大小。
An alternative is to set the statement attribute, OCI_ATTR_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.
另一种方法是在执行之前设置语句属性OCI_ATTR_PREFETCH_ROWS。这告诉Oracle一次要获取多少行,默认为1.使用这种方法,Oracle减少往返次数并为您缓冲行。
OCIBindArrayOfStruct is used with DML statements. It works in a similar fashion to OCIDefineArrayOfStruct except that it works with bind variables.
OCIBindArrayOfStruct与DML语句一起使用。它的工作方式与OCIDefineArrayOfStruct类似,不同之处在于它适用于绑定变量。
You can find sample code on the Oracle website.
您可以在Oracle网站上找到示例代码。
#2
3
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.
您是否查看了$ ORACLE_HOME / oci / samples中的示例代码(如果您没有安装它们,请运行Oracle安装程序并告诉它安装示例代码)。有几种使用批量接口。
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.
您可能需要认真考虑使用库。我编写了Pro * C(讨厌它),直接OCI,并使用了第三方库。最后是最好的,大幅度的。 OCI语法非常多毛,有可能永远不会使用的选项。同时它非常非常严格,如果你做的事情稍微有点错误,你的代码就会崩溃。
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.
如果您正在使用C ++,那么我可以推荐OTL;我已经做了一些严重的性能测试和OTL是一样快的手编码一般情况下(您可以通过5-10%打败它,如果你肯定知道你在你的数据没有空值,因此不需要指标阵列)。注意 - 不要试图理解OTL代码。这很可怕。但它的效果非常好。
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.
还有许多C库包含OCI并使其更有用,更不容易咬你,但我还没有测试过它们。
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.
如果没有别的,请帮自己一个忙,并为OCI代码编写包装函数,以使事情变得更容易。我在高性能场景中做到了这一点,它大大减少了我遇到的问题数量。