Bookmark Topic Watch Topic
  • New Topic

oracle stored procedure with clob return

 
Spark Mritzler
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • 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
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • 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
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • 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
 
    Bookmark Topic Watch Topic
  • New Topic