ODAC PL/SQL Tables

ODAC allows you to use PL/SQL arrays known as PL/SQL Tables as parameters of anonymous PL/SQL blocks or as parameters of stored procedures. As ordinary arrays, PL/SQL arrays can be used for storing the same data accessible by index.

We will use standard Dept table in our sample. If you don't have this table at your database see SQL script at Demos\InstallDemoObjects.sql folder. Following sample demonstrates how to update several records from Dept table simultaneously using parameter of PL/SQL Table type.

Here is a PL/SQL block used in our sample:

DECLARE
  i INTEGER;
BEGIN
  i:= 1;
  FOR rec IN (SELECT DeptNo FROM Scott.Dept
    WHERE RowNum <= 10 ORDER BY DeptNo)
  LOOP
    UPDATE Scott.Dept
      SET DName = :NameArr(i)
      WHERE DeptNo = Rec.DeptNo;
    i:= i + 1;
  END LOOP;
END;

There is one parameter in the text of the sample PL/SQL block with NameArr name. It has PL/SQL Table type. This SQL updates DName field of Dept table with the values from NameArr array.

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.Add('DECLARE');
  OraSQL.SQL.Add('  i INTEGER;');
  OraSQL.SQL.Add('BEGIN');
  OraSQL.SQL.Add('  i:= 1;');
  OraSQL.SQL.Add('  FOR rec IN (SELECT DeptNo FROM Scott.Dept');
  OraSQL.SQL.Add('    WHERE RowNum <= 10 ORDER BY DeptNo)');
  OraSQL.SQL.Add('  LOOP');
  OraSQL.SQL.Add('    UPDATE Scott.Dept');
  OraSQL.SQL.Add('       SET DName = :NameArr(i)');
  OraSQL.SQL.Add('       WHERE DeptNo = Rec.DeptNo;');
  OraSQL.SQL.Add('    i:= i + 1;');
  OraSQL.SQL.Add('  END LOOP;');
  OraSQL.SQL.Add('END;');

The NameArr parameter value should be specified later.

Then you need to specify that the parameter with NameArr name has PL/SQL Table type. To do it, you should set Table property to True and Length property of the parameter to the required value. If Dept table has four records, the size of the array also must be four.

  OraSQL.ParamByName('NameArr').Table := True;
  OraSQL.ParamByName('NameArr').DataType := ftString;
  OraSQL.ParamByName('NameArr').Length := 4;

After that you need to set values for the array items of NameArr parameter. The amount of array items must be equal to the value of Length property.

  OraSQL.ParamByName('NameArr').ItemAsString[1] := 'London';
  OraSQL.ParamByName('NameArr').ItemAsString[2] := 'Berlin';
  OraSQL.ParamByName('NameArr').ItemAsString[3] := 'Geneva';
  OraSQL.ParamByName('NameArr').ItemAsString[4] := 'Vienna';

Now you can execute SQL by calling Execute method of TOraSQL component.

  OraSQL.Execute;