Array DML and Stored Procedures

[jabarreras]

Could I use Array DML calling a Stored Procedure?.
If the answer is yes, what kind of parameter would have to be defined in the stored procedure ?

 

[Marco Kalter]

I assume you want to call a procedure many times with different parameter values in a single execution? This is possible, if the parameters have scalar data types. You can use the exact same method as with Array Inserts. Supply variant arrays for all variables that are bound to the parameters, and Execute it. It may be necessary to set TOracleQuery.Optimize to False on some SQL*Net / Net8 versions.

------------------
Marco Kalter
Allround Automations

 

[jabarreras]

It could be an approach, but could it be possible to send array of records to the stored procedure?. Imagine we have in the client side a ListView with columns from different tables. The user can select several ListView rows and click a button. This button inserts those rows in other table. The columns of the ListView are an image of that table (some kind of

 

[Marco Kalter]

For arrays or records you can either use multiple PL/SQL Tables of a scalar data type, or -more elegantly- a nested table or varray collection type. These collection types are implemented by the TOracleObject class, and can be passed to a stored procedure through an otObject variable by using the SetComplexVariable method.

------------------
Marco Kalter
Allround Automations