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

oracle stored procedure with clob return

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
I am relatively new to hibernate and am having some trouble calling an unmapped Oracle stored procedure. The sp creates a CLOB and sets it to out parameter, which is the sys_refcursor. Where it gets confusing for me is when I try to convert the returned object into a CLOB.

I have the sql-query configured as this:

<sql-query name="loadClob" callable="true">
{ call procedureName(?, aramA, aramB) }
</sql-query>

and reference it as follows:

org.hibernate.Query q = getSession().getNamedQuery("loadClob");
q.setString("paramA", "9");
q.setString("paramB", "bytes");

The three possible ways that I see to execute the query are list(), scroll() and iterate(). scroll() seems like the most appropriate option as it returns a ScrollableResults object which has the getClob(int) method.

ScrollableResults sr = q.scroll() creates a ScrollableResults object that is not null, but any attempt to convert to a clob throws a null pointer exception. I'm pretty sure my query returns a valid result, but don't know if im accessing it correctly (sr.getClob(0)).

Any recommendations or comments will be appreciated
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
"Spark Mritzler"

Please change your display name to match your real first and real last names.

Click on the My profile link above and change it immediately, or your account will be closed.

Mark
 
Mark Spritzler
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
Why I am answering your question after the insult on my name.

But you can't return a Clob in a stored procedure through Hibernate, you can only return an actual reference cursor, and it must be the first parameter. You can always use straight JDBC or the SQLQuery object.

Mark
 
Don't get me started about those stupid light bulbs.
    Bookmark Topic Watch Topic
  • New Topic