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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dimefx.canada@gmail.com - 30 Mar 2006 23:14 GMT

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~

Reply to this Message

Arto Viitanen - 31 Mar 2006 07:00 GMT

> Hi all,
>
[quoted text clipped - 12 lines]
>
> 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.

Signature

Arto Viitanen, CSC Ltd.
Espoo, Finland

Reply to this Message

DA Morgan - 31 Mar 2006 18:00 GMT

> Hi all,
>
[quoted text clipped - 12 lines]
>
> thank you all~

You can create an array that way but it might be better to create
it as a permanent schema object using CREATE OR REPLACE TYPE.

To pass it reference the type as the data type of the parameter.

Signature

Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Reply to this Message