Passing Arrays to Oracle stored procedures

[Srihari Injeti]

Hi ,
I would like to pass an array of values ( objects ), lets say an array of Strings, to a stored procedure in Oracle. How can I accomplish that?. Can anyone help me with a code sample. I need to know whether this can be done.
Thanks
Sri

[Raajesh Chandran]

Hi Injeti!!
I can't get U what U really wants.Instead of Using single statment.Try to use the callable Statement inside the forloop.Hope it will work.If U want that coding sample let me know.I will send it to U.
Best Wishes,
chandran..

[Srihari Injeti]

Hi Rajesh,
I am using a Callable Statement for calling the stored proc. But instead of passing a single value as a parameter ( I only have a single parameter), I want to send an array. Oracle stored procs can receive arrays as input parameters. Is there any way of doing that without using loops?.
Thanks for your help !!
Srihari

Originally posted by Raajesh Chandran:
Hi Injeti!!
I can't get U what U really wants.Instead of Using single statment.Try to use the callable Statement inside the forloop.Hope it will work.If U want that coding sample let me know.I will send it to U.
Best Wishes,
chandran..

[Poornachandran R]

Possible, use oracle.sql.ARRAY and ArrayDescriptor.
You have to create either VARRAY type or TABLE type in the database side.
Poorna

[Chris Brat]

Hi,

Am trying to do the same operation - i.e.

Passing an array of strings (single dimension array) to an oracle stored proc.

I also get a ClassCastException when I create the ArrayDescriptor object..

The application is running on JRUN and the connection is obtained from a connection pool using DBCP connection pooling resources(org.apache.commons.dbcp.PoolableConnection).

I know that a bug exists in Oracle 8.1.7 while trying to connect with Websphere - does anyone know if this bug also exists when using DBCP pools?

Here is my code (strArray is a simple string array), which fails when trying to create the ArrayDescriptor :


ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( "p_array", con );

ARRAY array_to_pass = new ARRAY( descriptor, con, strArray );

stmt.setArray(1,array_to_pass);

stmt.execute();



Any help will be greatly appreciated.

thanks
Chris

[Avi Abrami]

Sri,
Allow me to suggest searching the Ask Tom Web site. Perhaps the passing arrays into pl/sql stored procedures discussion solves your problem?

Also one (or more) of the code samples from Oracle's OTN Web site may be of help.

Good Luck,
Avi.