Call Oracle Stored Procedure Using createNativeQuery

Question

I need to call a stored procedure using JPA and found this article:

http://www.oracle.com/technology/pub/articles/vasiliev-jpql.html

which explains how to use EntityManager.createNativeQuery. However, the example actually calls a function that has a return argument. I've tried searching for an example of calling a stored procedure that doesn't have a return, but haven't been able to find anything.

Can I use createNativeQuery to call a procedure? Or does the procedure need to be modified to be a function (perhaps returns a success/failure result)?

Thanks!

Answer 1

From the JPA wiki:

1.4 Stored Procedures

JPA does not have any direct support for stored procedures. Some types of stored procedures can be executed in JPA through using native queries. Native queries in JPA allow any SQL that returns nothing, or returns a database result set to be executed. The syntax to execute a stored procedure depends on the database. JPA does not support stored procedures that use OUTPUT or INOUT parameters. Some databases such as DB2, Sybase and SQL Server allow for stored procedures to return result sets. Oracle does not allow results sets to be returned, only OUTPUT parameters, but does define a CURSOR type that can be returned as an OUTPUT parameter. Oracle also supports stored functions, that can return a single value. A stored function can normally be executed using a native SQL query by selecting the function value from the Oracle DUAL table.

Some JPA providers have extended support for stored procedures, some also support overriding any CRUD operation for an Entity with a stored procedure or custom SQL. Some JPA providers have support for CURSOR OUTPUT parameters.

Example executing a stored procedure on Oracle

EntityManager em = getEntityManager();
Query query = em.createNativeQuery("BEGIN VALIDATE_EMP(P_EMP_ID=>?); END;");
query.setParameter(1, empId);
query.executeUpdate();

So my advices would be:

• do some experimentations (i.e. try it)
• if required (and if possible) modify the stored procedure
• consider provider specific extensions (as last resort)

Answer 2

If it is possible, you'll likely need to wrap the procedure call this way:

em.createNativeQuery("BEGIN yourprocedure; END;")

Getting return values back may be problematic with procedures. Passing them in should be easy.

Answer 3

As already stated, the JPA spec does not yet support StoredProcedures, however the EclipseLink JPA provider does:

http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_(ELUG)#Using_EclipseLink_JPA_Extensions_for_Stored_Procedure_Query