aspose file tools*
The moose likes JDBC and the fly likes Passing Arrays to Oracle stored procedures Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Passing Arrays to Oracle stored procedures" Watch "Passing Arrays to Oracle stored procedures" New topic
Author

Passing Arrays to Oracle stored procedures

Srihari Injeti
Ranch Hand

Joined: Jan 05, 2001
Posts: 31
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


Srihari Injeti<BR>SCJP2 MCP CIW
Raajesh Chandran
Ranch Hand

Joined: May 04, 2001
Posts: 103
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..


chandran..
Srihari Injeti
Ranch Hand

Joined: Jan 05, 2001
Posts: 31
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
Ranch Hand

Joined: Sep 11, 2002
Posts: 47
Possible, use oracle.sql.ARRAY and ArrayDescriptor.
You have to create either VARRAY type or TABLE type in the database side.
Poorna
Chris Brat
Ranch Hand

Joined: May 22, 2003
Posts: 108
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


SCJP 1.2, SCJP 5, SCBCD
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Passing Arrays to Oracle stored procedures
 
Similar Threads
Passing java arrays to PL/SQL and vice versa
Call a stored Proc from JavaSript? Is it possible?
Passing an integer array to a Oracle Stored Procedure
how to pass an array to Stored proc call thru hibernate
Retrieve byte Array stored in Oracle BLOB