Choose a method for passing arrays to the Oracle Provider for OLEDB
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
Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.