Oracle Stored Procedure and Hibernate

Question

I have to call a stored procedure in pl sql from my db in-memory. I placed the script in my sql file

create or replace PROCEDURE delete_from_db
(
abi IN ASSEGNO.ABI_ASSEGNO%type,
cab IN ASSEGNO.CAB_ASSEGNO%type,
nAss IN ASSEGNO.NUMERO_ASSEGNO%type
)
IS
BEGIN
delete FROM ASSEGNO WHERE ABI_ASSEGNO = abi AND CAB_ASSEGNO= cab AND NUMERO_ASSEGNO = nAss;
COMMIT;
END delete_from_db;

and I tried to call it from my implementation using Hibernate like this:

@Override public void deleteAssegno(AssegnoId idAssegno) {

Session s = getHibernateTemplate().getSessionFactory().openSession();
s.getNamedQuery("delete_from_db").setString(0, idAssegno.getAbiAssegno()).setString(1, idAssegno.getCabAssegno()).setLong(2, idAssegno.getNumeroAssegno());
s.close();

}

but when I go with test I've got this error:

org.hibernate.MappingException: Named query not known: delete_from_db
    at org.hibernate.impl.AbstractSessionImpl.getNamedQuery(AbstractSessionImpl.java:93)
    at org.hibernate.impl.SessionImpl.getNamedQuery(SessionImpl.java:1407)
    at it.bnl.btre.orchass.busin.dao.impl.AssegnoDaoServiceImpl.deleteAssegno(AssegnoDaoServiceImpl.java:75)
    at it.bnl.btre.orchass.busin.facade.service.impl.CancellazioneDocumentaleServiceImpl.deleteFromDb(CancellazioneDocumentaleServiceImpl.java:109)
    at it.bnl.btre.orchass.busin.facade.service.impl.CancellazioneDocumentaleServiceImpl.deleteFromDoc(CancellazioneDocumentaleServiceImpl.java:70)
    at it.bnl.btre.orchass.busin.facade.CancellazioneDocumentaleFacade.handle(CancellazioneDocumentaleFacade.java:32)
    at it.bnl.btre.orchass.busin.facade.test.CancellazioneDocumentaleFacadeTest.testOk(CancellazioneDocumentaleFacadeTest.java:26)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:95)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)
    at java.lang.reflect.Method.invoke(Method.java:620)
    at junit.framework.TestCase.runTest(TestCase.java:176)
    at junit.framework.TestCase.runBare(TestCase.java:141)
    at junit.framework.TestResult$1.protect(TestResult.java:122)
    at junit.framework.TestResult.runProtected(TestResult.java:142)
    at junit.framework.TestResult.run(TestResult.java:125)
    at junit.framework.TestCase.run(TestCase.java:129)
    at junit.framework.TestSuite.runTest(TestSuite.java:255)
    at junit.framework.TestSuite.run(TestSuite.java:250)
    at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:84)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

I searched on internet but all the tutorial were about MySql db or other.

Thank You all!

score 1 · Accepted Answer · answered Jul 04 '17 at 11:12

You need to call the stored procedure directly, e.g. by creating a org.springframework.jdbc.object.StoredProcedure:

StoredProcedure proc = new StoredProcedure(jdbcTemplate, "delete_from_db");

define your parameters:

proc.declareParameter(new SqlParameter(Types.VARCHAR));
...

and finally call the prodedure

proc.execute(<params>);

For a full example, see e.g. Spring JDBC Template for calling Stored Procedures

Oracle Stored Procedure and Hibernate

1 Answers1