2

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:

  • createNamedStoredProcedureQuery
  • createStoredProcedureQuery
  • createStoredProcedureCall

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");
});
5
  • Maybe you should only call a stored procedure from db as "separation of concern" suggests and pass your parameters to that procedure. Not trying to build a query in your java code ? Commented May 28, 2020 at 22:32
  • @ismetguzelgun, the example is simplified, and can be a stored procedure. But me need exactly an anonymous plsql block. Commented May 28, 2020 at 22:50
  • 1
    stackoverflow.com/questions/5101529/… did you check this before ? It is not using hibernate but vanilla java can also give you an idea ? Commented May 28, 2020 at 22:53
  • 2
    @ismetguzelgun, Vanilla java is working fine (added P.S.). I can’t believe that there is no solution in the Hibernate. Commented May 28, 2020 at 23:19
  • In 2020 If you can not find any solution for your case on stackoverflow there are mainly two reasons. First your problem is so simple that nobody ever need to ask it and second your case is so overly complicated that you should design it again with much care. I am no Java expert but I think your case better suits second reason. Commented May 29, 2020 at 0:32

1 Answer 1

0

Hibernate is unable to accomplish this. Your only option is to declare a stored procedure and call it.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.