Get Output parameter from stored procedure without calling execute()

Question

I want to call a PL/SQL stored procedure from within a Java program via an entity manager:

StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("someProcedure");

Now my understanding was that I have to call execute() on this storedProcedureQuery in order to execute the procedure and be able to retrieve the OUT-parameters. However, I can retrieve these values without calling the execute()-Method by simply calling

someValue1 = (Integer)storedProcedureQuery.getOutputParameterValue(1);
someValue2 = (Integer)storedProcedureQuery.getOutputParameterValue(2);

How is this possible ? When exactly will the stored procedure be executed in this code snippet? Will the procedure be executed every time when getOutputParameterValue() is called ? Is there any official documentation for this behaviour ?

user_13878023 · Answer

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:
> Return true if the first result corresponds to a result set,
> and false if it is an update count or if there are no results
> other than through IN OUT and OUT 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
    public void testStoredProcedureQuery() {
        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).

Get Output parameter from stored procedure without calling execute()

1 Answers