You only need one array -- a table of varchar2's
bind_array takes two subscripts -- the low and high bounds of the array to use for the i'th column. So, say you want to use an arraysize of 100, you can use 1..100 for column 1, 101..200 for column 2, 201..300 for column 3 and so on.
All you need to do is figure out how to build the insert, which you would just do based on the dictionary -- using to_number for the numbers, to_date for the dates with the appropriate format, and nothing on the strings.
Taking that very same example:
ops$tkyte@ORA9IR2> create or replace package load_data
2 as
3
4 procedure dbmssql_array( p_tname in varchar2,
5 p_arraysize in number default 100,
6 p_rows in number default 500 );
7
8 end load_data;
9 /
Package created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body load_data
2 as
3
4 procedure dbmssql_array( p_tname in varchar2,
5 p_arraysize in number default 100,
6 p_rows in number default 500 )
7 is
8 l_stmt long;
9 l_theCursor integer;
10 l_status number;
11 l_data dbms_sql.varchar2_table;
12 l_cnt number default 0;
13 begin
14 l_stmt := 'insert into ' || p_tname ||
15 ' q1 ( a, b, c ) values ( to_number(:a), to_date(:b,''yyyymmddhh24miss''), :c )';
16
17 l_theCursor := dbms_sql.open_cursor;
18 dbms_sql.parse(l_theCursor, l_stmt, dbms_sql.native);
19
20 for i in 1 .. p_rows
21 loop
22 l_cnt := l_cnt+1;
23 l_data( l_cnt+(0*p_arraysize) ) := i;
24 l_data( l_cnt+(1*p_arraysize) ) := to_char( sysdate+i, 'yyyymmddhh24miss' );
25 l_data( l_cnt+(2*p_arraysize) ) := to_char(i);
26
27 if (l_cnt = p_arraysize)
28 then
29 dbms_sql.bind_array( l_theCursor, ':a', l_data, 0*p_arraysize+1, 0*p_arraysize+l_cnt );
30 dbms_sql.bind_array( l_theCursor, ':b', l_data, 1*p_arraysize+1, 1*p_arraysize+l_cnt );
31 dbms_sql.bind_array( l_theCursor, ':c', l_data, 2*p_arraysize+1, 2*p_arraysize+l_cnt );
32 l_status := dbms_sql.execute( l_theCursor );
33 l_cnt := 0;
34 end if;
35 end loop;
36 if (l_cnt > 0 )
37 then
38 dbms_sql.bind_array( l_theCursor, ':a', l_data, 0*p_arraysize+1, 0*p_arraysize+l_cnt );
39 dbms_sql.bind_array( l_theCursor, ':b', l_data, 1*p_arraysize+1, 1*p_arraysize+l_cnt );
40 dbms_sql.bind_array( l_theCursor, ':c', l_data, 2*p_arraysize+1, 2*p_arraysize+l_cnt );
41 l_status := dbms_sql.execute( l_theCursor );
42 end if;
43 dbms_sql.close_cursor( l_theCursor );
44 end;
45
46 end;
47 /
Package body created.
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2> create table t ( a number, b date, c varchar2(30) );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec load_data.dbmssql_array( 'T', 10, 15 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select * from t;
A B C
---------- --------- ------------------------------
1 14-FEB-05 1
2 15-FEB-05 2
3 16-FEB-05 3
4 17-FEB-05 4
5 18-FEB-05 5
6 19-FEB-05 6
7 20-FEB-05 7
8 21-FEB-05 8
9 22-FEB-05 9
10 23-FEB-05 10
11 24-FEB-05 11
12 25-FEB-05 12
13 26-FEB-05 13
14 27-FEB-05 14
15 28-FEB-05 15
15 rows selected. See how:
dbms_sql.bind_array
( l_theCursor, ':a', l_data, 0*p_arraysize+1, 0*p_arraysize+l_cnt );
dbms_sql.bind_array
( l_theCursor, ':b', l_data, 1*p_arraysize+1, 1*p_arraysize+l_cnt );
dbms_sql.bind_array
( l_theCursor, ':c', l_data, 2*p_arraysize+1, 2*p_arraysize+l_cnt );
lets you bind non-overlapping slices of a single array?