• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

CallableStatement * setObject

 
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello all...hopefully someone out here can square me away.

I recall being able to pass an array of Strings to a stored procedure via CallableStatement's setObject method (inherited) at one point in my career (just over a year ago). At the time, the shop I was working for used SQLServer on the backend and a driver from inet. Now I'm at an Oracle shop and it seems like I can't get away with the same syntax (using a 9i driver)...

Ideally, I'd like to be able to write something along the lines of...

--------------------
CallableStatement cst = conn.prepareCall("{call mypackage.proc(?)}");
cst.setObject(1, new String[] {"foo", "bar"});
cst.execute();
--------------------

The first requirement is that I be able to use setObject to pass an array into PL/SQL. I can do this with Oracle-specific code but the purist in me won't have it.

The second requirement is that the size of that array be flexible. I'm up for bending on proprietary-ness here.

When I try to pull this off today, I'm told that I'm passing in the wrong *type* - I believe PL/SQL is expecting a VARRAY. That could be right, it could be wrong...I'm looking for any sort of feedback as to how to pull this off with Oracle on the backend.

Thanks guys/girls.
 
Ranch Hand
Posts: 166
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have been through this same issue. Unfortunitely the solution I had to use contained the Oracle specific code using setArray with the Oracle Driver. Good luck in finding another way. I would be interested as well in knowing how to do it from a data base independent manner.
reply
    Bookmark Topic Watch Topic
  • New Topic