My Oracle Support Banner

OCI 11: Code Sample Using Multiple Bind and Define buffers (Doc ID 466549.1)

Last updated on JUNE 27, 2023

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.


As of version 11, OCI now incorporates the use of Multiple Bind and Define buffers. This means it is now possible to specify multiple buffers for use with a single bind or define call. 

The Oracle Documentation states the following: 

NOTE: Performance is improved because the number of round trips is decreased, data stored at different non-contiguous addresses is not copied to one contiguous location. CPU time spent and memory used are thus reduced.

This is feasible due to the new datatype (OCIIOV) which is defined as: 

This sample does an Insert and a Select. The Insert places 6 rows into a table with two fields. The first field (FIELD1) is a Number and the second field (FIELD2) is a Varchar2. This sample uses an array of 6 elements to insert 6 rows of numbers for the first field (FIELD1) . For the Varchar2 field (FIELD2) it uses two buffers with 3 elements each and uses OCIIOV to associate the two. After the Insert completes, the sample will call a procedure that will Select these 6 rows. All 6 of the Number fields (FIELD1) are placed into a single array. The second field (FIELD2) has 3 rows placed in one buffer and the second 3 rows placed into another buffer.  The OCIIOV is used to associate the two.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
 Sample Code
 Sample Output

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.