How can I call a Oracle procedure with array parameters using Delphi/Ado for bulk insert?

I need to add millions of rows in a single table in a Oracle database. After some research I found that using bulk insert would give a better performance. A Delphi program reads and prepares rows for insertion. How can I call a procedure with array parameters in 开发者_开发知识库order to do a bulk insert?

With Oracle you can use the Array DML feature or Direct Path API. In past the Direct Path API was few times faster than Array DML, but in modern Oracle versions the Array DML is just few percents slower than Direct Path API. But the Array DML is far more flexible and has less restrictions than Direct Path API ! So, I will suggest to look into Array DML feature.

Delphi standard data access libraries - BDE, dbExpress, dbGo do not support Oracle Array DML amd Direct Path API. So, you have to look into 3d party libraries. I will suggest to try AnyDAC. It implements Array DML not only for Oracle, but for all supported DBMS's. For more information about Array DML and it speed check the articles:

• http://www.da-soft.com/anydac/docu/Very_High_Performance_using_the_Array_DML.html
• http://www.da-soft.com/anydac/docu/Array_DML.html

I don't think you can do that using ADO. Bulk insert from a variant array is Oracle-specific, and requires functionality in the Oracle client; ADO is intentionally fairly generic in nature.

You can do this using third-party components for Oracle Data Access. There are several, but the only one I have personal experience with and can recommend for the bulk array insert is Direct Oracle Access (DOA)..

If have such needs and can't use SQL*Loader the best path is to use OCI directly (complex...) or a Delphi library that wraps it. One is DOA (see White answer), another is ODAC. They use OCI directly (ODAC can even talk to Oracle directly) and allows you to exploit advance Oracle functionalities. The resulting code may not be portable, but it is fast.