Passing Array to Oracle : JDBC

[Soumen Ghosh]

Hello,

Suppose I want to call a procedure which would take an array of string and insert it into a specific table.

I can get a resultset via an out parameter by declaring a ref cursor and executing some select statement for it in the procedure. But how to do the reverse i.e. passing an array to the procedure?

Any ideas?

Thanks in advance.

Soumen Ghosh

[Yogeeraj_1]

Hello,

From my notes:


You just need to declare new types. You can have types that are tables of
a RECORD or types that are tables of SCALARS. I'll show both below. See the
plsql guide for how to declare record types -- you can do them based on an
existing table structure, a cursor structure or by explicity defining your own
record type.

Note that if you are calling emp_report from a 3gl such as Pro*C, OCI, VB, etc
(eg: anything OTHER then plsql) you will need to use tables of scalars -- NOT a
table of records as tables of records cannot be bound to by a 3gl. Also, I
suggest only use charArray's if you are going to be calling this from a 3gl --
if you made a table of NUMBER or a table of DATE -- we would expect you to send
the INTERNAL format of a number (22 byte field in an obscure format) or a date
(7 byte, equally obscure format). Best to send strings and let the conversions
take place.

yd@8i> create or replace package demo_pkg
2 as
3 type empArray is table of emp%rowtype index by binary_integer;
4
5 type charArray is table of varchar2(255) index by binary_integer;
6
7
8 procedure emp_report( p_inputs in empArray );
9
10 procedure emp_report( p_empno in charArray,
11 p_deptno in charArray,
12 p_sal in charArray,
13 p_job in charArray );
14
15 end;
16 /

Package created.

yd@8i>
yd@8i>
yd@8i>
yd@8i> create or replace package body demo_pkg
2 as
3 procedure emp_report( p_inputs in empArray )
4 is
5 begin
6 for i in 1 .. p_inputs.count loop
7 dbms_output.put_line( 'Empno = ' || p_inputs(i).empno ||
8 ' Deptno = ' || p_inputs(i).deptno );
9 end loop;
10 end;
11
12
13 procedure emp_report( p_empno in charArray,
14 p_deptno in charArray,
15 p_sal in charArray,
16 p_job in charArray )
17 is
18 begin
19 for i in 1 .. p_empno.count loop
20 dbms_output.put_line( 'Empno = ' || p_empno(i) ||
21 ' Deptno = ' || p_deptno(i) );
22 end loop;
23 end;
24
25
26
27 end;
28 /

Package body created.

yd@8i>
yd@8i> set serveroutput on
yd@8i> declare
2 my_data demo_pkg.empArray;
3 begin
4 my_data(1).empno := 1234;
5 my_data(1).deptno := 10;
6
7 my_data(2).empno := 4567;
8 my_data(2).deptno := 20;
9
10 demo_pkg.emp_report( my_data );
11 end;
12 /
Empno = 1234 Deptno = 10
Empno = 4567 Deptno = 20

PL/SQL procedure successfully completed.

yd@8i>
yd@8i> declare
2 my_empnos demo_pkg.charArray;
3 my_deptno demo_pkg.charArray;
4 empty demo_pkg.charArray;
5 begin
6 my_empnos(1) := 1234;
7 my_deptno(1) := 10;
8
9 my_empnos(2) := 4567;
10 my_deptno(2) := 20;
11
12 demo_pkg.emp_report( my_empnos, my_deptno, empty, empty );
13 end;
14 /
Empno = 1234 Deptno = 10
Empno = 4567 Deptno = 20

PL/SQL procedure successfully completed.



Hope this helps
Yogeeraj