| OCI and bulk insert to stored procedure [message #402855] |
Tue, 12 May 2009 17:47  |
gshanemiller@verizon.net
Messages: 4
Registered: May 2009
|
Junior Member |
|
|
Hi,
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.
Shane
[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  |
gshanemiller@verizon.net
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
