Skip to Main Content
  • Questions

  • How to call Oracle stored function from Java JPA Hibernate and pass Array input parameter?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Andrei.

Asked: January 23, 2017 - 10:17 am UTC

Last updated: January 27, 2017 - 12:03 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi all.

Is it possible to call oracle stored function from Java JPA Hibernate and pass ARRAY as parameter?

Here is my Oracle code:

create or replace TYPE "COMP" AS OBJECT
(
TABLE_NAME VARCHAR2(30),
RECORD_NAME VARCHAR2(30),
PARM_TYPE VARCHAR2(30),
PARM_VALUE VARCHAR2(30)
);

create or replace TYPE "COMP_ARRAY" AS VARRAY(200) OF COMP;

FUNCTION START_TEST (in_TEST_START TIMESTAMP, in_SCENARIO PERF_JOURNAL.SCENARIO%TYPE, in_PARMS COMP_ARRAY) RETURN NUMBER IS
v_TEST_ID NUMBER;
BEGIN
SELECT TEST_SEQ.NEXTVAL INTO v_TEST_ID FROM DUAL;

INSERT INTO PERF_JOURNAL (TEST_ID, TEST_START, SCENARIO, STATUS)
VALUES (v_TEST_ID, in_TEST_START, in_SCENARIO, 'RUNNING');
COMMIT;

RETURN v_TEST_ID;
--todo: SET_APIX_CONFIG, SET_BW_CONFIG_APPNODE, SET_BW_CONFIG_PROCESS
END START_TEST;



Java code:

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.StoredProcedureQuery;

import com.corelogic.call.service.model.CallInput;
import com.corelogic.call.service.model.CallOutput;
import com.corelogic.call.service.model.MyTable;

public class CallServiceRepositoryImpl implements CallServiceRepository {

@PersistenceContext

private EntityManager em;

@Override

public CallOutput callStartTest(CallInput callInput) {

StoredProcedureQuery query =
this.em.createNamedStoredProcedureQuery("START_TEST")
.setParameter("in_TEST_START", callInput.getInTestStart())
.setParameter("in_SCENARIO", callInput.getInScenario())
.setParameter("in_PARMS", callInput.getCompArray());

query.execute();


return new CallOutput("OK");

}


=================================================================================================================================================

import org.springframework.data.repository.CrudRepository;

//import org.springframework.jdbc.core.JdbcTemplate;
//import org.springframework.data.repository.query.Param;
//import org.springframework.jdbc.object.SqlFunction;
//import com.corelogic.application.model.Log;
import com.corelogic.call.service.model.CallInput;
import com.corelogic.call.service.model.CallOutput;
import com.corelogic.call.service.model.MyTable;

public interface CallServiceRepository extends CrudRepository<MyTable, Long> {

public CallOutput callStartTest(CallInput callInput);

}


=================================================================================================================================================


import java.io.Serializable;
import java.util.ArrayList;

import javax.persistence.Column;
import javax.persistence.Embeddable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedStoredProcedureQueries;
import javax.persistence.NamedStoredProcedureQuery;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.StoredProcedureParameter;
import javax.persistence.ParameterMode;

import com.corelogic.call.service.model.CompArray;

@Entity
@Embeddable
@Table(name = "MYTABLE")
@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(name = "START_TEST",
procedureName = "PERF_PACK.START_TEST",
resultClasses = { Long.class },
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "in_TEST_START", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "in_SCENARIO", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.IN, name = "in_PARMS", type = ArrayList.class)
})
})
public class MyTable implements Serializable {
/**
*
*/
private static final long serialVersionUID = 7432343985440028636L;

@Id
@Column(name = "ID")
private long id;

public long getId() {
return id;
}

public void setId(long id) {
this.id = id;
}
}


=================================================================================================================================================



import java.io.Serializable;

public class CompArray implements Serializable {

/**
*
*/
private static final long serialVersionUID = -408745309710097053L;

private String tableName;
private String recordName;
private String parmType;
private String parmValue;

public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getRecordName() {
return recordName;
}
public void setRecordName(String recordName) {
this.recordName = recordName;
}
public String getParmType() {
return parmType;
}
public void setParmType(String parmType) {
this.parmType = parmType;
}
public String getParmValue() {
return parmValue;
}
public void setParmValue(String parmValue) {
this.parmValue = parmValue;
}
@Override
public String toString() {
return "{"
+ (tableName != null ? "tableName=" + tableName + ", " : "")
+ (recordName != null ? "recordName=" + recordName + ", " : "")
+ (parmType != null ? "parmType=" + parmType + ", " : "")
+ (parmValue != null ? "parmValue=" + parmValue : "") + "}";
}

}


=================================================================================================================================================

2017-01-23 12:10:19.903 INFO 7084 --- [nio-8080-exec-1] c.c.c.service.executer.CallServiceImpl : start:

CallInput [inTestStart=2017-01-01 01:01:01.010101,
inScenario=inScenario,
compArray=[{tableName=tableName, recordName=recordName, parmType=parmType, parmValue=parmValue}]]

org.springframework.orm.jpa.JpaSystemException: Unknown entity: java.lang.Long; nested exception is org.hibernate.MappingException: Unknown entity: java.lang.Long
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:314)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:225)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:131)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
at com.sun.proxy.$Proxy98.callStartTest(Unknown Source)
at com.corelogic.call.service.executer.CallServiceImpl.executeJob(CallServiceImpl.java:30)
at com.corelogic.call.service.controller.CallController.executeJobs(CallController.java:32)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:222)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:814)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration$ApplicationContextHeaderFilter.doFilterInternal(EndpointWebMvcAutoConfiguration.java:242)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:111)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:87)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:103)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:217)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:673)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Caused by: org.hibernate.MappingException: Unknown entity: java.lang.Long
at org.hibernate.internal.SessionFactoryImpl.getEntityPersister(SessionFactoryImpl.java:1096)
at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.getSQLLoadable(SQLQueryReturnProcessor.java:374)
at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.processRootReturn(SQLQueryReturnProcessor.java:427)
at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.processReturn(SQLQueryReturnProcessor.java:394)
at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.process(SQLQueryReturnProcessor.java:196)
at org.hibernate.result.internal.OutputsImpl.buildSpecializedCustomLoader(OutputsImpl.java:245)
at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:66)
at org.hibernate.procedure.internal.ProcedureOutputsImpl.<init>(ProcedureOutputsImpl.java:49)
at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:426)
at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:378)
at org.hibernate.jpa.internal.StoredProcedureQueryImpl.outputs(StoredProcedureQueryImpl.java:251)
at org.hibernate.jpa.internal.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:234)
at com.corelogic.call.service.repository.CallServiceRepositoryImpl.callStartTest(CallServiceRepositoryImpl.java:27)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:483)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:458)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:440)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
... 74 more
2017-01-23 12:12:39.574 INFO 7084 --- [ Thread-3] ationConfigEmbeddedWebApplicationContext : Closing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@4856ebb1: startup date [Mon Jan 23 12:09:56 MSK 2017]; root of context hierarchy
2017-01-23 12:12:39.589 INFO 7084 --- [ Thread-3] o.s.c.support.DefaultLifecycleProcessor : Stopping beans in phase 2147483647
2017-01-23 12:12:39.589 INFO 7084 --- [ Thread-3] o.s.c.support.DefaultLifecycleProcessor : Stopping beans in phase 0
2017-01-23 12:12:39.605 INFO 7084 --- [ Thread-3] o.s.j.e.a.AnnotationMBeanExporter : Unregistering JMX-exposed beans on shutdown
2017-01-23 12:12:39.636 INFO 7084 --- [ Thread-3] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'


and Connor said...

I'm not Hibernate expert (not even a novice!) but my understanding is that Hibernate (when it comes to stored procedures) is pretty much limited to:

- scalar values in and out
- ref cursors out

Otherwise its fall back to jdbc.

But I'd also try your question on the hibernate forums and see how you go

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How to call stored function in Oracle and pass ARRAY of STRUCT

Andrei Baranau, January 26, 2017 - 1:46 pm UTC

Thanks for answer. I decided to move to JDBC.

I created objects to implement Oracle data types:

import java.io.Serializable;
import java.sql.SQLException;
import java.sql.Struct;
import java.util.Map;

public class Comp implements Serializable, Struct {

/**
*
*/
private static final long serialVersionUID = -408745309710097053L;

private String tableName;
private String recordName;
private String parmType;
private String parmValue;



public Comp() {
super();
// TODO Auto-generated constructor stub
}



public Comp(String tableName, String recordName, String parmType,
String parmValue) {
super();
this.tableName = tableName;
this.recordName = recordName;
this.parmType = parmType;
this.parmValue = parmValue;
}



public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getRecordName() {
return recordName;
}
public void setRecordName(String recordName) {
this.recordName = recordName;
}
public String getParmType() {
return parmType;
}
public void setParmType(String parmType) {
this.parmType = parmType;
}
public String getParmValue() {
return parmValue;
}
public void setParmValue(String parmValue) {
this.parmValue = parmValue;
}
@Override
public String toString() {
return "{"
+ (tableName != null ? "tableName=" + tableName + ", " : "")
+ (recordName != null ? "recordName=" + recordName + ", " : "")
+ (parmType != null ? "parmType=" + parmType + ", " : "")
+ (parmValue != null ? "parmValue=" + parmValue : "") + "}";
}

}


===========================================================

import java.io.Serializable;
import java.sql.Array;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Map;

public class CompArray implements Serializable, Array {

/**
*
*/
private static final long serialVersionUID = -4930882512805000577L;

Comp[] compArray;

public Comp[] getCompArray() {
return compArray;
}

public void setCompArray(Comp[] compArray) {
this.compArray = compArray;
}

public CompArray() {
super();
// TODO Auto-generated constructor stub
}

public CompArray(Comp[] compArray) {
super();
this.compArray = compArray;
}


}


With this objects, I'm trying to call function and pass Object as input parameter with setObject() method.


Connection conn = datasource.getConnection();
java.util.Map myMap = conn.getTypeMap();



myMap.put("SYSTEM.COMP",
Class.forName("com.corelogic.call.service.model.Comp"));
myMap.put("SYSTEM.COMP_ARRAY",
Class.forName("com.corelogic.call.service.model.CompArray"));

LOGGER.info("\nMap output: '" + myMap.toString() + "'\n");

conn.setTypeMap(myMap);


CompArray compArray = new CompArray(callInput
.getCompArray().toArray(new Comp[callInput.getCompArray().size()]));



LOGGER.info("\ncompArray output: '" + compArray.toString() + "'\n");

CallableStatement cs = datasource.getConnection().prepareCall(
"{ ? = call PERF_PACK.START_TEST( ? , ? , ? ) }");
cs.registerOutParameter(1, Types.NUMERIC);
cs.setTimestamp(2, callInput.getInTestStart());
cs.setString(3, callInput.getInScenario());
cs.setObject(4, compArray, 1111);

cs.execute();
result = cs.getString(1);
} catch (Exception e) {
e.printStackTrace();
}



While execute I'm getting next error:

2017-01-26 09:33:51.425 INFO 10760 --- [nio-8080-exec-8] c.c.c.s.r.CallServiceRepositoryImpl :
compArray output: 'CompArray [Comp[] =[{tableName=string, recordName=string, parmType=string, parmValue=string}]]'

java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8516)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8034)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8584)
at oracle.jdbc.driver.OracleCallableStatement.setObject(OracleCallableStatement.java:4988)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:238)


What I'm doing wrong?
Connor McDonald
January 27, 2017 - 12:03 am UTC

try here

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8908169959941

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library