Win a copy of Succeeding with AI this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
  • Junilu Lacar
  • Tim Cooke
  • Jeanne Boyarsky
  • Knute Snortum
Saloon Keepers:
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
  • Piet Souris
  • salvin francis
  • fred rosenberger
  • Frits Walraven

Stored Procedure and Java

Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys
I need help with the following issue. I'm writing a web application which have to call a stored procedure using JDBC with IN object and receive output as OUT object. Here is the jist of the objects I have.

TestPkg in Oracle and the stored procedure I'm going to call from Java:




Now, I have the following code to send input to the stored procedure. I've used SQLData to send Java Objects to Sql.

Issue: The above code is working fine as long as I send null for the array in IN object to the stored procedure. If I follow the above code structure to send the Java array to SQL's stored procedure I'm facing the following exception when it is trying to create the ARRAY object in getOracleArray().

Question is: Can someone help me figure out how I can pass Java array as SQLArray to a object which is in turn passed as a parameter to stored procedure(SP)? If I am not successful with finding the solution for this, I may have to ask the DBA to change the structure of the SP like (object with only basic datatypes, inputarray, output). But I'm assuming there is a better and elegant way to handle this in Java. I appreciate any help or suggestions.

Note: There is no compilation errors in the above code, if I have missed something trivial please ignore it as my effort to make the question small.
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I can't offer any actual advise, but your error message returned this link, which looked good:
Su Chikki
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the link. I've followed this sample, along with many I found, to implement what I have now. As you can see, I'm doing the exact same thing for creating the oracle.sql.ARRAY. The difference in the example link is that, they set the created ARRAY directly to the CallableStatement to be executed. In my case, I have to set it as part of an Object which will be the only input to the stored procedure. So my question is, how can I set the Java Array to SqlType array in an Object which implements SQLData? I've included the code in my question.
Life just hasn't been the same since the volcano erupted and now the air is full of tiny ads.
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
    Bookmark Topic Watch Topic
  • New Topic