Can a stored procedure receive a VB array

[IQ]

I have a scenario where I need to insert approx. 2000 records into an Oracle 8i database from a VB6 application.

The simplest way is to call a stored procedure which inserts a single record 2000 times but performance will not be very good and will kill my network.

Is there any way in which I can pass an array of 2000 records to a stored procedure that could then loop internally to insert all of the records.

Any help would be gratefully received.

Thanks

IQ

 

[chiph]

The simple answer is: No. Oracle stored procs don't understand how to access individual elements of a VB array.

If I recall correctly, there's a way to insert bulk records into an Oracle table. Unfortunately, it's been too long since I've done serious Oracle work, and I can't recall the command to do it. Maybe someone else here can supply my missing memory.

Chip H.

 

[Guest_imported]

The easiest way to do this is as follows,

Get your records (lets say it is a name and telephone number)

Package them in an agreed format, (in the past I have used comma deleimted fields, although XML is more flexible), in reasonable sized chuncks (Say 500 records).

Pass them to a storted proc, which parses, the records, and then invokes your stored proc to insert them individually.

Regards

Onkar

http://www.objectnet.co.uk

 

[bobhilt]

Or you could consider using a recordset with batchupdate.
(Create the recordset with a select with a where clause like 'WHERE 1 = 2'. With this empty recordset, you can then .add in vb, the .batchupdate. Bypasses the stored proc, so if you need to munge the data on the oracle side, this method won't do you much good.

A year or two ago I tried to do what you're asking, and was ultimately able to get the table into the stored proc, but unfortunately I got blocked after that. It goes in as a pl/sql table, the moral equivalent of an array, and can't be used for joining, filtering, or anything else except raw data after that. The performance gains would've been wiped out by the need to parse everything field by field and then do inserts....