The Oracle Provider for OLEDB has some nice
methods for retrieving an array from a PL/SQL procedure call and
making it appear to be a Recordset object. It’s a bit trickier to
go in the other direction. I’ve seen a lot of requests for help
from developers looking for a way to pass an array of values to a
PL/SQL stored procedure.

Unfortunately, the current versions of the
Oracle Provider for OLEDB, OraOLEDB, doesn’t support array bind
variables or PL/SQL record arguments. You must use a technique to
wrap an array into the proper PL/SQL structure on the server at
runtime.

In the following examples, I’ll use Visual
Basic Script because it’s available on Windows platforms with
Windows Shell Scripting environment. I’ll use a simple schema for
storing a sequence of 2-dimensional graphic points and a simple
PL/SQL procedure for inserting an array of points into the
table:

drop table points;
create table points
(
    x   number not null,
    y   number not null
);

create or replace package point_pkg
as
    type point_tbl is table of points%rowtype
index by pls_integer;
    procedure add_points(p_points
point_tbl);
end point_pkg;
/
show errors;

create or replace package body point_pkg
as
    procedure add_points(p_points
point_tbl)
    is
    begin
        — array
insert
        forall i in
1..p_points.count
            insert
into points values p_points(i);
    end add_points;
end point_pkg;
/
show errors;

The simplest way, which you can use with any
programming environment that doesn’t support array bind variables,
is to build the PL/SQL table in an anonymous PL/SQL block to
execute:

dim x(3),y(3)
‘
x(0) = 0: y(0) = 0
x(1) = 1: y(1) = 1
x(2) = 2: y(2) = 4
x(3) = 3: y(3) = 9
‘
set con = CreateObject(“ADODB.Connection”)
con.Provider = “OraOLEDB.Oracle”
con.Open , “scott”, “tiger”
sql = “declare ary point_pkg.point_tbl; begin”
for i = 1 to 3
    sql = sql & ” ary(” & i & “).x
:= ” & x(i) & “;”
    sql = sql & ” ary(” & i & “).y
:= ” & y(i) & “;”
next
sql = sql & ” point_pkg.add_points(ary); end;”
con.Execute sql
con.Close
set con = Nothing

The biggest drawback for this method is that it
rapidly fills up the SQL buffer with raw data and generates extra
network traffic by passing arguments and SQL syntax for each
value.

Another method is to use a global temporary
table to pass the array of values. In this method, you can use a
Recordset object to post the data into the temporary table. (The
anonymous block used to construct the PL/SQL table from the
temporary table has a fixed size.)

drop table points_tmp;
create global temporary table points_tmp
(
    x   number not null,
    y   number not null
)
on commit preserve rows;

const adCmdText = 1
const adCmdTable = 2
const adLockOptimistic = 3
‘
dim x(3),y(3)
‘
x(0) = 1: y(0) = 1
x(1) = 2: y(1) = 4
x(2) = 3: y(2) = 9
x(3) = 4: y(3) = 16
‘
set con = CreateObject(“ADODB.Connection”)
con.Provider = “OraOLEDB.Oracle”
con.Open , “scott”, “tiger”

set rs = CreateObject(“ADODB.Recordset”)
rs.Open “points_tmp”,con,,adLockOptimistic,adCmdTable
for i = LBound(x) to UBound(x)
    rs.AddNew
    rs(“x”) = x(i)
    rs(“y”) = y(i)
next
rs.Update

set cmd = CreateObject(“ADODB.Command”)
cmd.ActiveConnection = con
cmd.CommandType = adCmdText
sql = “declare l_points point_pkg.point_tbl; i pls_integer :=
0;”
sql = sql & “begin”
sql = sql & ” for row in (select * from points_tmp)
loop”
sql = sql & ” i := i + 1;”
sql = sql & ” l_points(i).x := row.x;”
sql = sql & ” l_points(i).y := row.y;”
sql = sql & ” end loop;”
sql = sql & ” point_pkg.add_points(l_points);”
sql = sql & ” end;”
cmd.CommandText = sql
cmd.Execute

con.Close
set con = Nothing

This method is appropriate for large amounts of
data. The temporary table will be cleared out at the end of the
session or after a “truncate” command is issued against the
table.

Since the array will be stored in PL/SQL memory
anyway, it’s also possible to skip the temporary table and make
repeated calls to one PL/SQL procedure to populate a global PL/SQL
table (which is visible only to the current session) that can be
referenced by a second PL/SQL table directly.

create or replace package point_pkg
as
    type point_tbl is table of points%rowtype
index by pls_integer;
    l_points point_tbl;
    procedure add_points(p_points
point_tbl);
    procedure clear;
    procedure add_point(x number,y
number);
    procedure add_points2;
end point_pkg;
/
show errors;

create or replace package body point_pkg
as
    procedure add_points(p_points
point_tbl)
    is
    begin
        — array
insert
        forall i in
1..p_points.count
            insert
into points values p_points(i);
    end add_points;
    —
    procedure clear
    is
    begin
        l_points.delete;

    end clear;
    —
    procedure add_point(x number,y
number)
    is
        i pls_integer :=
l_points.count + 1;
    begin
        l_points(i).x :=
x;
        l_points(i).y :=
y;
    end add_point;
    —
    procedure add_points2
    is
    begin
        add_points(l_points);

    end add_points2;
end point_pkg;
/
show errors;

const adCmdText = 1
const adNumeric = 131
‘
dim x(3),y(3)
‘
x(0) = 1: y(0) = 1
x(1) = 2: y(1) = 4
x(2) = 3: y(2) = 9
x(3) = 4: y(3) = 16
‘
set con = CreateObject(“ADODB.Connection”)
con.Provider = “OraOLEDB.Oracle”
con.Open “”, “scott”, “tiger”

set cmd = CreateObject(“ADODB.Command”)
cmd.ActiveConnection = con
cmd.CommandType = adCmdText
cmd.CommandText = “begin scott.point_pkg.add_point(?,?);
end;”
cmd.Parameters.Append cmd.CreateParameter(“x”,adNumeric)
cmd.Parameters.Append cmd.CreateParameter(“y”,adNumeric)

for i = LBound(x) to UBound(x)
    cmd.Parameters(“x”) = x(i)
    cmd.Parameters(“y”) = y(i)
    cmd.Execute
Next

cmd.CommandText = “begin point_pkg.add_points2; end;”
cmd.Execute

con.Close
set con = Nothing

TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!