Re: create varray in oracle trigger and pass it to java stored procedure

dimefx.canada_at_gmail.com wrote:
> Hi all,
>
> I got a few question to here, and I am new to oracle and plsql.
>
> 1. will you be able to create varray in oracle trigger or stored
> procedure?
> something like
>
> CREATE OR REPLACE TYPE tempArray AS VARRAY(20) of VARCHAR2(10)
>
> 2. how do you put data into tempArray?
>
> 3. how to pass this array (tempArray) to a java stored procedure or
> function. What data type should the java function accept as parameter?
>
> thank you all~
>

I have used VARRAYs on JDBC, so I am not sure how they bahave in PL/SQL, but I guess you can use the tempArray as a type in PL/SQL. So for 1 and 2.

   DECLARE
     v_ta tempArray;
   BEGIN
     v_ta := tempArray('foo','bar','fiu')    END; should work on PL/SQL block

For 3, use JPublisher. With the command jpub you can generate wrapper classes for the types defined in a schema. Then only define the function to take the wrapper type. Only catch is, that IN types should be declared as arrays of the wrapper class; in the code the array has only one element.

-- 
Arto Viitanen, CSC Ltd.
Espoo, Finland