Home » Developer & Programmer » Precompilers, OCI & OCCI » OCI and bulk insert to stored procedure (Oracle Database 10g Enterprise Edition Release - 64bit Production)
OCI and bulk insert to stored procedure [message #402855] Tue, 12 May 2009 17:47 Go to next message
Messages: 4
Registered: May 2009
Junior Member

I have an array of C structs say

struct S
int a, long b, double c;

S s[100];

Further suppose I have an Oracle table T like this:
create table T
a number(10),
b number(10),
c float

I want to bulk insert all 100 instances of S from a client application into T. I've seen code that does this for *one* field or column. The code defines a stored procedure which accepts a single argument which is a TABLE and then does a FORALL ... insert. The client application passes in the array of data.

What I need is N columns. In my example above struct S has N=3 fields which conform to the N=3 columns in T. In reality my N will be 50+. I am trying to avoid creating stored procedures which will take the 50 or so arguments it will eventually need.

So does my stored procedure need to accept N TABLE arguments? Or can I cajole OCI/OTL/ODBC and PL/SQL so that the stored procedure can take an array of rows which the type of row conforms to T by defining a record or something? That is, do I need:

Option 1: // declares one type and one argument each for N cols
create or replace procedure insert_S(
a_array IN A_TABLE, -- type A_TABLE is TABLE of number;
b_array IN B_TABLE, -- type B_TABLE is TABLE of number;
c_array IN C_TABLE) -- type C_TABLE is ...
begin ... end

Option 2: // this somehow accepts an array compatible with T
// if I could get a OCI/OCCI/OTL/ODBC application
// to send this data, this procedure would have
// only one argument
create or replace procedure insert_S(
row_array IN ?????????? type -- some sort of array of rows
begin ... end

Or should I pass the whole memory chunk of data in as an image or varchar array -- basically an opaque block of data -- and then internally decypher/decode the memory block inside the stored procedure as discussed on www . codeproject . com.

In sum, what's the best way to pass an array of N C-structs of M fields to a stored procedure for insertion into a table with M compatible columns? One TABLE per column? with an array of a custom type compatible with a row in T? As glob of data? Another option is to populate some host variables ... but, again, I'd need N host variables.


[Updated on: Tue, 12 May 2009 18:04]

Report message to a moderator

Re: OCI and bulk insert to stored procedure [message #402860 is a reply to message #402855] Tue, 12 May 2009 19:43 Go to previous message
Messages: 4
Registered: May 2009
Junior Member
What I'm reading on other sites ( asktom . oracle . com) is that 3GL languages like C/C++ via OCI/OCCI cannot stuff records or instances of C-structs into a PL/SQL stored procedure. One either has to (*) pass N arrays of scalars (*) start using the Oracle Objects.

I have no experience at all with Oracle objects and need to weight performance over slick/cool technology anyway.

The only other way I see to do this is pass a byte array (image or varchar array) and internally decipher the array back into a set of N records of M fields.
Previous Topic: database connectivity
Next Topic: OCI programming (merged 3)
Goto Forum:

Current Time: Mon Nov 23 16:25:15 CST 2020