Get Output Parameter From Stored Procedure Without Calling Execute()

I want to call a PL/SQL stored procedure from within a Java program via an entity manager: StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery('som

Solution 1:

I am a new learner in his field and as curious as you with the question. However i have taken this opportunity to test it and below is my observation,

According to the documentation execute() returns:
>Returntrue if the firstresult corresponds to a resultset,
>andfalse if it is an update count or if there areno results
> other than through INOUTandOUT parameters, if any.

therefore I would say, the returned true or false doesn't mean a successful or not execution.

Again we must register the parameters before we call to getOutputParameterValue. If we looked into the implementation of getOutputParameterValue, we would able to find exactly where the hibernate provider ( which is JPA in case of mine) calls to the actual execution.

Further to how many times the execution happened I tested it in a way to check it by inserting to another table inside the calling procedure.

create table test_procedure_call(msg varchar2(100));

CREATE OR REPLACE PROCEDURE test (
    p_in_1  IN    NUMBER,
    p_out_1 OUT   VARCHAR2,
    p_out_2 OUT   VARCHAR2
) AS
BEGIN
    insert into test_procedure_call values ('Executed..');
    commit;
    select'FirstName'||' '||'LastName','HR'into p_out_1,p_out_2 
      from dual 
     where p_in_1=1;
END;
/

@Test
    publicvoidtestStoredProcedureQuery() {
        StoredProcedureQuery sp = em.createStoredProcedureQuery("test");
        // set parameters
        sp.registerStoredProcedureParameter("p_in_1", Integer.class, ParameterMode.IN);
        sp.registerStoredProcedureParameter("p_out_1", String.class, ParameterMode.OUT);
        sp.registerStoredProcedureParameter("p_out_2", String.class, ParameterMode.OUT);
        sp.setParameter("p_in_1", 1);

        String name = sp.getOutputParameterValue("p_out_1").toString();
        String dept = sp.getOutputParameterValue("p_out_2").toString();

        System.out.println("Name : " + name);
        System.out.println("Department : " + dept);
    }

select * from test_procedure_call;

MSG                                                                                                 
----------------------------------------------------------------------------------------
Executed..

By this out of the table test_procedure_call, we can confirm it executes only once per test. (as we saw in above example).