wood burning stoves*
The moose likes JDBC and the fly likes Problem while setting multiple Oracle Arrays as input to a proc Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem while setting multiple Oracle Arrays as input to a proc" Watch "Problem while setting multiple Oracle Arrays as input to a proc" New topic
Author

Problem while setting multiple Oracle Arrays as input to a proc

Vijay Bhore
Greenhorn

Joined: May 15, 2006
Posts: 12
Hi All,

I am using oracle.sql.ARRAY in call to a database procedure.
I am providing the code snippet below.

First I have created an object of ArrayDescriptor 'desc'.

ArrayDescriptor desc = ArrayDescriptor.createDescriptor("HOST_ARRAY", conn);

Further I am using the same descriptor to create three Oracle arrays..

ARRAY inArrayCustIps = new ARRAY(desc, conn, changeSipsRequest.getCustIps());
ARRAY inArrayTotalHostIps = new ARRAY(desc, conn, changeSipsRequest.getTotalHostIps());
ARRAY inArraySelectedHostIps = new ARRAY(desc, conn, changeSipsRequest.getHostIps());

Further i have set the Arrays as input to proc according to correct index as

ocs.setARRAY(8, inArrayCustIps);
ocs.setARRAY(9, inArrayTotalHostIps);
ocs.setARRAY(10, inArraySelectedHostIps);

But the problem i am facing is that values are not being passed to proc.
Can anybody suggest, what is the problem and how can i resolve it.

Thanks in advance.

Regards,
Vijay
Vijay Bhore
Greenhorn

Joined: May 15, 2006
Posts: 12
I will follow the same thread,

It's working after i made two changes

1) Created three different descriptors for three different oracle arrays as

ArrayDescriptor descOne = ArrayDescriptor.createDescriptor("HOST_ARRAY", conn);
ArrayDescriptor descTwo = ArrayDescriptor.createDescriptor("SUBNET_ARRAY", conn);
ArrayDescriptor descThree = ArrayDescriptor.createDescriptor("NEW_HOST_ARRAY", conn);

2) I had initialized String arrays of size 20 whereas arrays can contain only 1-10 values.
Now i have initialized arrays dynamically according to number of actual number of elements in array.
Hence the chances of the oracle array containing null values got minimized.

Regards,

Vijay Bhore
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Problem while setting multiple Oracle Arrays as input to a proc
 
Similar Threads
java.sql.SQLException: invalid name pattern
Only for JDBC tallents
getting underlying oracle connection from Poolable connection
Getting array of Oracle Objects
trouble passing array to stored procedure in spring HELP