Choose a method for passing arrays to the Oracle Provider for OLEDB

by Scott Stephens | Jul 26, 2005 7:00:00 AM

Tags: Programming languages, Databases, Oracle Corp., Scott Stephens, SQL, PL/SQL, Oracle Tips Newsletter

Takeaway: If you're looking for a way to pass an array of values to a PL/SQL stored procedure, you must use a technique to wrap an array into the proper PL/SQL structure on the server at runtime. Scott Stephens provides you with the necessary code.

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!

0 comment(s)