DML Array

Using of array binding feature can greatly speed up the execution of the application on insert or update big volume of data. The main advantage is that array binding allows you to execute several INSERT SQL statements with the different parameters at once. Note that you access server only once - that increases speed of update a lot.

The following is a sample of using DML Array.

The following table is used in this sample.

CREATE TABLE dept
(
  deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY,
  dname VARCHAR2(14),
  loc VARCHAR2(13)
);

At first, you should open a session:

  OraSession.UserName := 'scott';  
  OraSession.Password := 'tiger';  
  OraSession.Server := 'Ora';  
  OraSession.Connect;  

After that you should specify SQL statement for the execution:

  OraSQL.SQL.Text := 'INSERT INTO dept VALUES(:deptno_p, :dname_p, :loc_p)';  

Colons in the SQL text mean parameters with the values which will be specified later.

Now you should specify parameter type for each parameter from the SQL text and the Length property of the parameters, which should be equal to the number of SQL statement executions.

  OraSQL.ParamByName('deptno_p').DataType := ftInteger;  
  OraSQL.ParamByName('deptno_p').Length := 4;  
     
  OraSQL.ParamByName('dname_p').DataType := ftString;  
  OraSQL.ParamByName('dname_p').Length := 4;  
     
  OraSQL.ParamByName('loc_p').DataType := ftString;  
  OraSQL.ParamByName('loc_p').Length := 4;  

You should call Prepare method before specifying values for the highest efficiency.

  OraSQL.Prepare;  

Each item of the array must correspond to the single execution of the SQL statement.

  OraSQL.ParamByName('deptno_p').ItemAsInteger[1] := 10;  
  OraSQL.ParamByName('dname_p').ItemAsString[1] := 'ACCOUNTING';  
  OraSQL.ParamByName('loc_p').ItemAsString[1] := 'NEW YORK';  
     
  OraSQL.ParamByName('deptno_p').ItemAsInteger[2] := 20;  
  OraSQL.ParamByName('dname_p').ItemAsString[2] := 'RESEARCH';  
  OraSQL.ParamByName('loc_p').ItemAsString[2] := 'DALLAS';  
     
  OraSQL.ParamByName('deptno_p').ItemAsInteger[3] := 30;  
  OraSQL.ParamByName('dname_p').ItemAsString[3] := 'SALES';  
  OraSQL.ParamByName('loc_p').ItemAsString[3] := 'CHICAGO';  
     
  OraSQL.ParamByName('deptno_p').ItemAsInteger[4] := 40;  
  OraSQL.ParamByName('dname_p').ItemAsString[4] := 'OPERATIONS';  
  OraSQL.ParamByName('loc_p').ItemAsString[4] := 'BOSTON';  

After accomplishing previous steps you should call Execute method that assumes a parameter specifying how many times SQL statement will be executed. Note that the value of this method argument must be equal to the number of parameters value elements. Now you can execute SELECT * FROM Dept with any Oracle tool (you can use dbForge Studio for Oracle for this purpose) and see four new records appended.

  OraSQL.Execute(4);