title: Hibernate: how to call a stored function returning a varchar?
body: <p>I am trying to call a legacy stored function in an Oracle9i DB from Java using Hibernate. The function is declared like this:</p>
  
  <pre><code>create or replace FUNCTION Transferlocation_Fix (mnemonic_code IN VARCHAR2)
     RETURN VARCHAR2
  </code></pre>
  
  <p>After several failed tries and extensive googling, I found <a href="https://forum.hibernate.org/viewtopic.php?f=1&amp;t=976887&amp;view=previous" rel="nofollow noreferrer">this thread</a> on the Hibernate forums which suggested a mapping like this:</p>
  
  <pre><code>&lt;sql-query name="TransferLocationFix" callable="true"&gt;
      &lt;return-scalar column="retVal" type="string"/&gt;
      select Transferlocation_Fix(:mnemonic) as retVal from dual
  &lt;/sql-query&gt;
  </code></pre>
  
  <p>My code to execute it is</p>
  
  <pre><code>    Query query = session.getNamedQuery("TransferLocationFix");
      query.setParameter("mnemonic", "FC3");
      String result = (String) query.uniqueResult();
  </code></pre>
  
  <p>and the resulting log is</p>
  
  <pre><code>DEBUG (org.hibernate.jdbc.AbstractBatcher:366) -  - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
  DEBUG (org.hibernate.SQL:401) -  - select Transferlocation_Fix(?) as retVal from dual
  TRACE (org.hibernate.jdbc.AbstractBatcher:484) -  - preparing statement
  TRACE (org.hibernate.type.StringType:133) -  - binding 'FC3' to parameter: 2
  TRACE (org.hibernate.type.StringType:133) -  - binding 'FC3' to parameter: 2
  
  java.lang.NullPointerException
  at oracle.jdbc.ttc7.TTCAdapter.newTTCType(TTCAdapter.java:300)
  at oracle.jdbc.ttc7.TTCAdapter.createNonPlsqlTTCColumnArray(TTCAdapter.java:270)
  at oracle.jdbc.ttc7.TTCAdapter.createNonPlsqlTTCDataSet(TTCAdapter.java:231)
  at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1924)
  at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:850)
  at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2599)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963)
  at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
  at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:736)
  at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.execute(NewProxyCallableStatement.java:3044)
  at org.hibernate.dialect.Oracle8iDialect.getResultSet(Oracle8iDialect.java:379)
  at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:193)
  at org.hibernate.loader.Loader.getResultSet(Loader.java:1784)
  at org.hibernate.loader.Loader.doQuery(Loader.java:674)
  at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
  at org.hibernate.loader.Loader.doList(Loader.java:2220)
  at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
  at org.hibernate.loader.Loader.list(Loader.java:2099)
  at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
  at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
  at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
  at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
  at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:811)
  at com.my.project.SomeClass.method(SomeClass.java:202)
  ...
  </code></pre>
  
  <p>Any clues what am I doing wrong? Or any better ways to call this stored function?</p>
  
  <hr>
  
  <p><strong>Update:</strong> upon trying @axtavt's suggestion, I get the following error:</p>
  
  <pre><code>ORA-14551: cannot perform a DML operation inside a query
  </code></pre>
  
  <p>The function indeed does extensive inserts/updates, so I guess the only way to run it would be using the stored procedure syntax. I just have no clue how to map the return value:</p>
  
  <pre><code>&lt;sql-query name="TransferLocationFix" callable="true"&gt;
      &lt;return-scalar column="???" type="string"/&gt;
      { ? = call Transferlocation_Fix(:mnemonic) }
  &lt;/sql-query&gt;
  </code></pre>
  
  <p>What should be the <code>column</code>? I will try an empty value...</p>
  
  <hr>
  
  <p><strong>Update2:</strong> that failed as well, with an SQL Grammar Exception... So I tried the JDBC way as suggested by Pascal, and it seems to work! I added the code in an answer below.</p>