How to execute an anonymous plsql block with out parameters in hibernate?
Configuration configuration = new Configuration();
configuration.configure("hibernate.cfg.xml");
SessionFactory sessionFactory = configuration.buildSessionFactory();
Session session = sessionFactory.openSession();
//Example (as I would like):
Query q = session.createQuery("begin select user_name into :p_name from fnd_user where user_id = :p_id; end;");
q.registerParameter("p_name", String.class, ParameterMode.OUT);
q.registerParameter("p_id", String.class, ParameterMode.IN).bindValue(100);
q.execute();
But in Query there is no registration of parameters.
Existing methods with parameter registration are not suitable:
createNamedStoredProcedureQuerycreateStoredProcedureQuerycreateStoredProcedureCall
add code to execute the procedure (begin %ORIGINAL_SQL%(%PARAMS%); end;).
P. S.: Vanilla option is working fine:
String name = session.doReturningWork((Connection c) -> {
String sql = "declare v_str VARCHAR2(100); begin select user_name into :p_name
from fnd_user where user_id = :p_id; end;";
CallableStatement cs = c.prepareCall(sql);
cs.registerOutParameter("p_name", Types.VARCHAR);
cs.setInt("p_id", 1);
cs.execute();
return cs.getString("p_name");
});
anonymous plsql block.