MyBatis calling Oracle stored procedures

-

Lesson learned today: Use MyBatis update statement for calling Oracle stored procedures with input and output parameters.

Fragment of my MyBatis mapper xml file before:

  <select id="getPasswordVerificationStatus" statementType="CALLABLE" parameterType="getPasswordVerificationStatus">
  {call WEB.IFC_CORE.GETUSERPASSWORDSTATUS(
   #{idEmp,mode=IN},
   #{login,mode=IN},
   #{password,mode=IN},
   #{expiry,jdbcType=INTEGER,mode=OUT},
   #{expiryCode,jdbcType=INTEGER,mode=OUT})}
 </select>

I've tried to implement a stored procedure call (using Oracle jdbc driver and Oracle Universal Connection Pool in background), taking an inspiration from some working examples. According the Ibatis / myBatis migration guide we can use select, update or insert when calling stored procedure. Since my procedure only asked for a status and didn't insert or modify any data I expected a select element to be the appropriate one to use.

Caused by: java.lang.RuntimeException: unexpected invocation exception: unexpected invocation exception: null at oracle.ucp.jdbc.proxy.PreparedStatementProxyFactory.invoke ...

The exception I got was caused by the invocation of getResultSet method on oracle.ucp.jdbc.proxy.CallableStatementProxyFactory - it took me a while to debug the code since it is using a lot proxy objects.

Finally I've found that somebody had the same problem using Ibatis and there was also an issue in MyBatis tracker.

Final working example:

  <update id="getPasswordVerificationStatus" statementType="CALLABLE" parameterType="getPasswordVerificationStatus">
  {call WEB.IFC_CORE.GETUSERPASSWORDSTATUS(
   #{idEmp,mode=IN},
   #{login,mode=IN},
   #{password,mode=IN},
   #{expiry,jdbcType=INTEGER,mode=OUT},
   #{expiryCode,jdbcType=INTEGER,mode=OUT})}
 </update>

I'm not sure if the issue is related to the Oracle jdbc driver only or it is a general issue. If you have tested it in other environments don't hesitate to share your experiences in comments. If it is not the Oracle specific problem, please share use cases of select when calling a stored procedure.

Comments

Post a Comment

Popular posts from this blog

Hibernate delete and update queries with joins

-
From Hibernate 4.1.9 manual - section about bulk update and deletes : No implicit or explicit joins can be specified in a bulk HQL query. Sub-queries can be used in the where-clause, where the subqueries themselves may contain joins. Unsupported (implicit join) DELETE A a WHERE a.relation.property = "dummy"; A way to go DELETE A a WHERE a.relation.id IN (SELECT r.id FROM Relation r WHERE r.property = "dummy");
Read more

Access application Google Drive account from javascript without client side authorization dialog

-
Use case We want to create a regular Google Drive account owned by an application. The application enables users to upload or view files within that account. Since Google Drive API provides javascript library the goal is to use javascript for all the logic (upload and view files) with minimum engagement of server side processing. For the purpose of creating an application owned account Google provides two options: SERVICE ACCOUNT Application is authorized by .pk12 certificate; the drawback is that only the application can see documents it created - documents cannot be viewed for example through Google Drive UI. REGULAR GOOGLE ACCOUNT used by the application only This option is our choice because we want to access stored files using Google Drive UI too. More information about the two options is available in Google Drive SDK documentation . To access a Google account using Google API we have to ensure correct handling of authentication and authorization procedures - Google A...
Read more

Hibernate schema export with Hibernate Validator constraints accepted and Spring configured persistence

-
What is the goal: using Spring Framework for configuration of persistence settings (JPA 2.0 with Hibernate provider) using the configuration for Hibernate schema export tool apply Hibernate Validator constraints to generated schema Spring persistence configuration // define datasource - hsqldb for testing purposes - it requires hsqldb to be on classpath EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder(); EmbeddedDatabase database = builder.setType(EmbeddedDatabaseType.HSQL).build(); // standard way how to configure persistence in Spring (usually included as a part of some configuration class) LocalContainerEntityManagerFactoryBean lcemfb = new LocalContainerEntityManagerFactoryBean(); lcemfb.setPersistenceXmlLocation("classpath:example/path/to/persistence.xml"); lcemfb.setJpaVendorAdapter(new HibernateJpaVendorAdapter()); lcemfb.setDataSource(database); lcemfb.afterPropertiesSet(); Create Hibernate configuration from Spring configured persistence Ej...
Read more