Working with PL/SQL Tables

dotConnect for Oracle 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 general arrays, PL/SQL arrays can be used for common storing similar data types accessible by index.

At our sample we will be using standard Dept table. If you don't have this table at your database see SQL script at Samples\scott.sql folder.

Following sample demonstrates how to update several records from Dept table simultaneously using parameter of PL/SQL Table type.

Here is SQL statement used at 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;

At the text of given above SQL there is one parameter with NameArr name of PL/SQL Table type. This SQL updates DName field of Dept table with the values from NameArr array.

At first, you should create an object of OracleConnection class and open a session:

...
OracleConnection conn = new OracleConnection(
   "User Id=Scott;Password=tiger;Data Source=Ora");
conn.Open();
...

...
Dim conn As OracleConnection = new OracleConnection( _
   "User Id=Scott;Password=tiger;Data Source=Ora")
conn.Open()
...

Please note that it is only a cut of sample and you shouldn't try to compile it. The full text of the sample will be given at the end of this article.

After that you should create an instance of OracleCommand class associated with the OracleConnection object and specify SQL statement for the execution:

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

...
Dim cmd OracleCommand = conn.CreateCommand()
cmd.CommandText = "DECLARE\n" + _
  "  i INTEGER;\n" + _
  "BEGIN\n" + _
  "  i:= 1;\n" + _
  "  FOR rec IN (SELECT DeptNo FROM Scott.Dept\n" + _
  "    WHERE RowNum <= 10 ORDER BY DeptNo)\n" + _
  "  LOOP\n" + _
  "    UPDATE Scott.Dept\n" + _
  "      SET DName = :NameArr(i)\n" + _
  "      WHERE DeptNo = Rec.DeptNo;\n" + _
  "    i:= i + 1;\n" + _
  "  END LOOP;\n" + _
  "END;"
...

Text of SQL has NameArr parameter with the value which should be specified later.

Now for each parameter from SQL you should associate an object of OracleParameter class and add this object to the collection of the parameters of OracleCommand objects. Association is accomplished by the names of parameters in following way:

...
cmd.Parameters.Add("NameArr", OracleDbType.VarChar);
...

...
cmd.Parameters.Add("NameArr", OracleDbType.VarChar)
...

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

...
cmd.Parameters["NameArr"].ArrayLength = 4;
...

...
cmd.Parameters("NameArr").ArrayLength = 4
...

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

...
cmd.Parameters["NameArr"].Value = new srting[] { 
   "London", "Berlin", "Geneva", "Vienna" };
...

...
Dim valArr(4) As string
valArr(0) = "London"
valArr(1) = "Berlin"
valArr(2) = "Geneva"
valArr(3) = "Vienna"
cmd.Parameters("NameArr").Value = valArr
...

Now you can execute SQL calling ExecuteNonQuery() method of OracleCommand class.

...
cmd.ExecuteNonQuery();
...

...
cmd.ExecuteNonQuery()
...

Below full text of the sample.

public void UpdateThroughPlSqlTable()
{
  // Create connection object
  OracleConnection conn = new OracleConnection(
     "User Id=Scott;Password=tiger;Data Source=Ora");

  // Open connection
  conn.Open();

  // Create command object with previously opened connection
  OracleCommand cmd = conn.CreateCommand();

  // Set command text property
  cmd.CommandText = "DECLARE\n" +
                    "  i INTEGER;\n" +
                    "BEGIN\n" +
                    "  i:= 1;\n" +
                    "  FOR rec IN (SELECT DeptNo FROM Scott.Dept\n" +
                    "    WHERE RowNum <= 10 ORDER BY DeptNo)\n" +
                    "  LOOP\n" +
                    "    UPDATE Scott.Dept\n" +
                    "      SET DName = :NameArr(i)\n" +
                    "      WHERE DeptNo = Rec.DeptNo;\n" +
                    "    i:= i + 1;\n" +
                    "  END LOOP;\n" +
                    "END;"

  // Add parameters to command parameters collection
  cmd.Parameters.Add("NameArr", OracleDbType.VarChar);

  // Set Pl/SQL table length
  cmd.Parameters["NameArr"].ArrayLength = 4;

  // Set array parameter value
  cmd.Parameters["NameArr"].Value = new string[] { 
     "London", "Berlin", "Geneva", "Vienna" };

  // Update table
  cmd.ExecuteNonQuery();

  // Dispose command
  cmd.Dispose();

  // Close connection
  conn.Close();
}

Public Sub UpdateThroughPlSqlTable()

  ' Create connection object
  Dim conn As OracleConnection = new OracleConnection(
     "User Id=Scott;Password=tiger;Data Source=Ora")

  ' Open connection
  conn.Open()

  ' Create command object with previously opened connection
  Dim cmd As OracleCommand = conn.CreateCommand()

  ' Set command text property
  cmd.CommandText = "DECLARE\n" + _
                    "  i INTEGER;\n" + _
                    "BEGIN\n" + _
                    "  i:= 1;\n" + _
                    "  FOR rec IN (SELECT DeptNo FROM Scott.Dept\n" + _
                    "    WHERE RowNum <= 10 ORDER BY DeptNo)\n" + _
                    "  LOOP\n" + _
                    "    UPDATE Scott.Dept\n" + _
                    "      SET DName = :NameArr(i)\n" + _
                    "      WHERE DeptNo = Rec.DeptNo;\n" + _
                    "    i:= i + 1;\n" + _
                    "  END LOOP;\n" + _
                    "END;"

  ' Add parameters to command parameters collection
  cmd.Parameters.Add("NameArr", OracleDbType.VarChar)

  ' Set Pl/SQL table length
  cmd.Parameters("NameArr").ArrayLength = 4

  ' Set array parameter value
  Dim valArr(4) As string
  valArr(0) = "London"
  valArr(1) = "Berlin"
  valArr(2) = "Geneva"
  valArr(3) = "Vienna"
  cmd.Parameters("NameArr").Value = valArr

  ' Update table
  cmd.ExecuteNonQuery()

  ' Dispose command
  cmd.Dispose()

  ' Close connection
  conn.Close()
End Sub

If you have any open questions or problems please refer to the sample from Samples\PlSqlTable folder.