Execute procedure in Oracle

mpedrotti · May 2, 2025, 3:35pm

I’m trying to execute a procedure in Oracle, but using the code as I do in Postgres, which works very well.

However, in Oracle the procedure works, testing it with plSql, but in Jmix it returns an error.

I’ve tried with getResultStream, executeUpdate() and each one returns a different error.

Below I will put the code for calling the procedure, this procedure is inserted and updates records in the database.

I ask for help in knowing what is the best way, or the correct way to call this procedure.

 private void atualizarListaEnvios(String filial, String tipoInfo) {
    try {
        //TODO:a função continua retornando -1
        //String sComando = "begin REDEMAIS.RM_REVISAR_DELIVERY(PCODFILIAL, PCODTIPO); end;";
        String sComando = """
                declare
                    retvar varchar2(4);
                begin
                   REDEMAIS.RMDELIVERY.REGISTROSATUALIZAR('<PCODFILIAL>',<PCODTIPO>);
                end;
                """;

        sComando = sComando.replace("<PCODFILIAL>", filial);
        sComando = sComando.replace("<PCODTIPO>", tipoInfo);
         em.createNativeQuery(sComando).executeUpdate();
    } catch (Exception e) {
        log.error(e.getMessage());
        e.printStackTrace();
        throw new RuntimeException("Erro ao atualizar lista de envios: " + e.getMessage());
    }
}

below is the error that returns when executing it this way

2025-05-02T12:19:07.871-03:00 ERROR 30604 --- [nio-9898-exec-8] br.com.pedromas.prm.app.Delivery         : 
Exception Description: No transaction is currently active
jakarta.persistence.TransactionRequiredException: 
Exception Description: No transaction is currently active
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionWrapper.throwCheckTransactionFailedException(EntityTransactionWrapper.java:93)
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionWrapper.checkForTransaction(EntityTransactionWrapper.java:53)
	at org.eclipse.persistence.internal.jpa.EntityManagerImpl.checkForTransaction(EntityManagerImpl.java:2174)
	at org.eclipse.persistence.internal.jpa.QueryImpl.executeUpdate(QueryImpl.java:296)
	at io.jmix.eclipselink.impl.JmixEclipseLinkQuery.executeUpdate(JmixEclipseLinkQuery.java:304)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:419)
	at jdk.proxy2/jdk.proxy2.$Proxy222.executeUpdate(Unknown Source)

f.zehnder · May 2, 2025, 3:43pm

Hi Marcos

You need a Transaction

        try {
            transactionTemplate.execute(transactionStatus -> {
                entityManager.createNativeQuery(sqlString)
                    .executeUpdate();
                transactionStatus.flush();
                return null;
            });
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }

Let me know, if this did help

Best regards
Felix

mpedrotti · May 2, 2025, 5:38pm

same error still,
the code was like this

private void atualizarListaEnvios(String filial, String tipoInfo) {
    try {
        String sComando = """
                declare
                    retvar varchar2(4);
                begin
                   REDEMAIS.RMDELIVERY.REGISTROSATUALIZAR('<PCODFILIAL>',<PCODTIPO>);
                end;
                """;

        sComando = sComando.replace("<PCODFILIAL>", filial);
        sComando = sComando.replace("<PCODTIPO>", tipoInfo);

        String finalSComando = sComando;
        transactionTemplate.execute(transactionTemplate -> {
            em.createNativeQuery(finalSComando).executeUpdate();
            transactionTemplate.flush();
            return null;
        });

    } catch (Exception e) {
        log.error(e.getMessage());
        e.printStackTrace();
        throw new RuntimeException("Erro ao atualizar lista de envios: " + e.getMessage());
    }
}

f.zehnder · May 3, 2025, 5:23am

I have hard time to believe it is the same error ;/

Can you check, if it is really at this place and attach the error message again ?

mpedrotti · May 3, 2025, 4:15pm

I understand your question, and I appreciate your attention,
below is the initial code of the class, and below is the error it returns

@Component(“prm_Delivery”)
public class Delivery {
private static final Logger log = LoggerFactory.getLogger(Delivery.class);
private final ApiClient apiClient;
private final DataManager dataManager;
private final StockApi stockApi;
private final TransactionTemplate transactionTemplate;

@PersistenceContext(unitName = "redemais")
private EntityManager em;

public Delivery(ApiClient apiClient, DataManager dataManager, StockApi stockApi, TransactionTemplate transactionTemplate) {
    this.apiClient = apiClient;
    this.dataManager = dataManager;
    this.stockApi = stockApi;
    this.transactionTemplate = transactionTemplate;
}

private void atualizarListaEnvios(String filial, String tipoInfo) {
    try {
        String sComando = """
                declare
                    retvar varchar2(4);
                begin
                   REDEMAIS.RMDELIVERY.REGISTROSATUALIZAR('<PCODFILIAL>',<PCODTIPO>);
                end;
                """;

        sComando = sComando.replace("<PCODFILIAL>", filial);
        sComando = sComando.replace("<PCODTIPO>", tipoInfo);

        String finalSComando = sComando;
        transactionTemplate.execute(transactionTemplate -> {
            em.createNativeQuery(finalSComando).executeUpdate();
            transactionTemplate.flush();
            return null;
        });

    } catch (Exception e) {
        log.error(e.getMessage());
        e.printStackTrace();
        throw new RuntimeException("Erro ao atualizar lista de envios: " + e.getMessage());
    }
}

2025-05-03T13:05:24.254-03:00 DEBUG 23348 --- [nio-9898-exec-7] eclipselink.logging.sql                  : <t 1165944048, conn 1209042603> [1 ms] spent
2025-05-03T13:05:48.942-03:00 ERROR 23348 --- [nio-9898-exec-7] br.com.pedromas.prm.app.Delivery         : 
Exception Description: No transaction is currently active
jakarta.persistence.TransactionRequiredException: 
Exception Description: No transaction is currently active
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionWrapper.throwCheckTransactionFailedException(EntityTransactionWrapper.java:93)
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionWrapper.checkForTransaction(EntityTransactionWrapper.java:53)
	at org.eclipse.persistence.internal.jpa.EntityManagerImpl.checkForTransaction(EntityManagerImpl.java:2174)
	at org.eclipse.persistence.internal.jpa.QueryImpl.executeUpdate(QueryImpl.java:296)
	at io.jmix.eclipselink.impl.JmixEclipseLinkQuery.executeUpdate(JmixEclipseLinkQuery.java:304)
	at br.com.pedromas.prm.app.Delivery.lambda$atualizarListaEnvios$0(Delivery.java:143)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
	at br.com.pedromas.prm.app.Delivery.atualizarListaEnvios(Delivery.java:142)
	at br.com.pedromas.prm.app.Delivery.executarEnvios(Delivery.java:162)
	at br.com.pedromas.prm.view.delivery.rmitemdelivery.RmitemdeliveryListView.onRmitemdeliveriesDataGridEnviarPendencias(RmitemdeliveryListView.java:40)
	at io.jmix.flowui.kit.event.EventBus.fireEvent(EventBus.java:107)
	at io.jmix.flowui.kit.action.BaseAction.actionPerform(BaseAction.java:70)
	at io.jmix.flowui.kit.component.button.JmixButtonActionSupport.onButtonClick(JmixButtonActionSupport.java:176)
	at com.vaadin.flow.component.ComponentEventBus.fireEventForListener(ComponentEventBus.java:239)
	at com.vaadin.flow.component.ComponentEventBus.handleDomEvent(ComponentEventBus.java:488)
	at com.vaadin.flow.component.ComponentEventBus.lambda$addDomTrigger$dd1b7957$1(ComponentEventBus.java:298)
	at com.vaadin.flow.internal.nodefeature.ElementListenerMap.lambda$fireEvent$2(ElementListenerMap.java:475)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
	at com.vaadin.flow.internal.nodefeature.ElementListenerMap.fireEvent(ElementListenerMap.java:475)
	at com.vaadin.flow.server.communication.rpc.EventRpcHandler.handleNode(EventRpcHandler.java:62)
	at com.vaadin.flow.server.communication.rpc.AbstractRpcInvocationHandler.handle(AbstractRpcInvocationHandler.java:73)
	at com.vaadin.flow.server.communication.ServerRpcHandler.handleInvocationData(ServerRpcHandler.java:550)
	at com.vaadin.flow.server.communication.ServerRpcHandler.lambda$handleInvocations$6(ServerRpcHandler.java:531)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
	at com.vaadin.flow.server.communication.ServerRpcHandler.handleInvocations(ServerRpcHandler.java:531)
	at com.vaadin.flow.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:358)
	at com.vaadin.flow.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:137)
	at com.vaadin.flow.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:63)
	at com.vaadin.flow.server.VaadinService.handleRequest(VaadinService.java:1664)
	at com.vaadin.flow.server.VaadinServlet.service(VaadinServlet.java:398)
	at com.vaadin.flow.spring.SpringServlet.service(SpringServlet.java:106)
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:614)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:633)
	at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:409)
	at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:304)
	at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:268)
	at org.springframework.web.servlet.mvc.ServletForwardingController.handleRequestInternal(ServletForwardingController.java:142)
	at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:178)
	at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:51)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:914)
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:547)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885)
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:614)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at io.jmix.core.impl.logging.LogMdcFilter.doFilterInternal(LogMdcFilter.java:28)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:108)
	at org.springframework.security.web.FilterChainProxy.lambda$doFilterInternal$3(FilterChainProxy.java:231)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:365)
	at org.springframework.security.web.access.intercept.AuthorizationFilter.doFilter(AuthorizationFilter.java:101)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:126)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:120)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:131)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:85)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:100)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:114)
	at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:105)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:179)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:151)
	at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:129)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:227)
	at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:221)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:107)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:93)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:117)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:91)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90)
	at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.context.SecurityContextHolderFilter.doFilter(SecurityContextHolderFilter.java:82)
	at org.springframework.security.web.context.SecurityContextHolderFilter.doFilter(SecurityContextHolderFilter.java:69)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:62)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.session.DisableEncodeUrlFilter.doFilterInternal(DisableEncodeUrlFilter.java:42)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:233)
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:191)
	at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:113)
	at org.springframework.web.servlet.handler.HandlerMappingIntrospector.lambda$createCacheFilter$3(HandlerMappingIntrospector.java:243)
	at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:113)
	at org.springframework.web.filter.CompositeFilter.doFilter(CompositeFilter.java:74)
	at org.springframework.security.config.annotation.web.configuration.WebMvcSecurityConfiguration$CompositeFilterChainProxy.doFilter(WebMvcSecurityConfiguration.java:238)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:362)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:278)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:397)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:905)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
	at java.base/java.lang.Thread.run(Thread.java:1583)

f.zehnder · May 4, 2025, 8:11am

Nasty stuff ;/

As it is running with Postgres, there are little chances this is a Jmix problem but rather an oracle problem ?

I see https://docs.oracle.com/middleware/12212/osa/applications-develop/GUID-DCB2AFE4-3ABA-4912-806A-E6C2B4780CAB.htm oracle was at that time working with EclipseLink.

Sorry not being able to help you further, I have other databases ( MS SQL, mySql, MariaDb, Postgres ).

BTW I found this https://stackoverflow.com/questions/29601028/transaction-required-exception-jpa-spring where there is something about a Spring annotation
@Transactional(propagation = Propagation.REQUIRES_NEW), perhaps it helps.

krivopustov · May 5, 2025, 7:02am

Try to add the @Transactional annotation as @f.zehnder suggested, but make sure it’s a public method invoked from the outside of the bean. Perhaps it iss executarEnvios.

See also Using EntityManager :: Jmix Documentation.

ntha79 · May 5, 2025, 2:32pm

Hi team,
You can try SimpleJdbcCall with NamedParameterJdbcTemplate.
In our projects, we have implemented following steps:
Step 1. Register Bean for NamedParameterJdbcTemplate of your database store:

@Bean(name = "myStoreJdbcTemplate")
public NamedParameterJdbcTemplate myStoreJdbcTemplate(@Qualifier("myStoreDataSource") DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
}

Step 2. Use SimpleJdbcCall :

@Component
public class TestOracleService {
    private final NamedParameterJdbcTemplate myStoreJdbcTemplate;
    public TestOracleService(@Qualifier("myStoreJdbcTemplate") NamedParameterJdbcTemplate myStoreJdbcTemplate) {
        this.myStoreJdbcTemplate= myStoreJdbcTemplate;
    }

    public List<MyEntity> callMyOracleProcedure(String param1, String param2) {
        if (param1 == null || param2 == null) {
            return Collections.emptyList();
        }
        SimpleJdbcCall jdbcCall = new SimpleJdbcCall(myStoreJdbcTemplate.getJdbcTemplate())
                .withSchemaName("MY_SCHEMA")
                .withCatalogName("MY_PACKAGE")
                .withProcedureName("MY_PROCEDURE")
                .declareParameters(
                        new SqlParameter("PARAM1", Types.STRING),
                        new Solarimeter("PARAM2", Types.STRING),
                        new SqlOutParameter("CURRETURN", Types.REF_CURSOR)
                );

        Map<String, Object> result = jdbcCall.execute(param1, param2);
        List<Map<String, Object>> mapList = (List<Map<String, Object>>) result.get("CURRETURN");

        if (!mapList.isEmpty()) {
            List<MyEntity> myList = new ArrayList<>();
            for (Map<String, Object> map : mapList) {
                MyEntity myEntity = metadata.create(MyEntity.class);
                //Do your code here   
                if (map.get("ID") != null)   myEntity.setId(map.get("ID").toString());
                if (map.get("NAME") != null)   myEntity.setName(map.get("NAME").toString());

                myList.add(myEntity);
            }
            return myList;
        } else {
            return Collections.emptyList();
        }
    }
}

mpedrotti · May 7, 2025, 12:04pm

Thank you all very much for your attention,

the suggestion from @ntha79 worked, below is the final code of the class, how it turned out.

a suggestion to the JMIX team, add documentation on how to do this type of action, or if there is a way to evolve the tool so that it is easier for developers.

but anyway, thank you very much.

package br.com.pedromas.prm.app;

import java.sql.Types;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Component;

import java.util.*;

@Component
    public class ExecutarBancoOracle {
    private static final Logger log = LoggerFactory.getLogger(ExecutarBancoOracle.class);
    private final NamedParameterJdbcTemplate myStoreJdbcTemplate;
    private String erroMsg;

    public ExecutarBancoOracle(@Qualifier("redemaisTemplate") NamedParameterJdbcTemplate myStoreJdbcTemplate) {
        this.myStoreJdbcTemplate = myStoreJdbcTemplate;
    }

    public boolean atualizarItensDelivery(String param1, String param2) {
        try {
            if (param1 == null || param2 == null) {
                return false;
            }
            //.
            SimpleJdbcCall jdbcCall = new SimpleJdbcCall(myStoreJdbcTemplate.getJdbcTemplate())
                    .withSchemaName("REDEMAIS")
                    .withCatalogName("RMDELIVERY")
                    .withProcedureName("REGISTROSATUALIZAR")
                    .declareParameters(
                            new SqlParameter("PCODFILIAL", Types.VARCHAR),
                            new SqlParameter("PCODTIPODELIVERY", Types.VARCHAR)
                            //new SqlOutParameter("CURRETURN", Types.REF_CURSOR)
                    );

            Map<String, Object> params = new HashMap<>();
            params.put("PCODFILIAL", param1);
            params.put("PCODTIPODELIVERY", param2);

            jdbcCall.execute(params);

            return true;
        }
        catch (Exception e) {
            log.error(e.getMessage());
            erroMsg = e.getMessage();
            return false;
        }
    }

    public String getErroMsg() {
        return erroMsg;
    }
}