Toad World® Forums

Elapsed time outcome does not take in account fetching of ref cursors


If a tested program has ref cursor out parameters, the elapsed time outcome does not fetch these cursors before recording the elapsed time. Therefore, the elapsed time is only that of the initial program call (opening of cursors). From a testing standpoint, we would like to test the program completely as the application would call it, to include the fetching of the cursors. This would allow us to test that a complete call of the program does not exceed a certain elapsed time.

I have tried to add custom code to the “Post-execution code” section of a test case which would fetch the cursor but the generated code to get the end time gets executed before that piece of code. I would rather not use the “Execute this code instead of the generated execution code” section as this would almost defeat the purpose of having QCTO generate the code. Is there a workaround for this or a feature that I am missing? Thanks in advance.


I don’t see a workaround for this right now. Looks like you found a bug. I hope, though, that we can fix this for 1.8.3, which will be available in May. May I suggest that you log a bug with Quest Support, so you can be notified when it is fixed?

Thanks, SF


Quick follow up: yes, I can confirm what you suspected. We get the end time and then check the outcomes, as in:

        Q##end_time := DBMS_UTILITY.GET_TIME;
        check_non_exc_outcomes ();

So you feel that when the program returns ref cursors, the elapsed time should include the retrieval of data from the program?

I am not entirely convinced, or at least not convinced that this should ALWAYS be the case. The program did finish executing, after all.

Please tell me your thinking about this.

Thanks, Steven


I agree. The program did finish and I do not think that the elapsed time should ALWAYS take into account the fetching of the data. However, we would like to be able to have coverage on that aspect of a program. This would allow us to catch potential query issues that are a result of current development i.e. someone adds an index and a query’s plan changes causing the tested program+data fetch to run too long.

I think I have a couple possible workarounds (still need to test):

  1. In the “Post-execution code” section, I can execute the fetching of the data AND recapture the end time (Q##end_time := DBMS_UTILITY.GET_TIME). This should capture the end time again before it checks the outcomes.

  2. Create a new test case instead of an outcome specifically for the “elapsed time w/ data” test of a program using the “Execute this code instead of the generated execution code” section. I can then put the execute and fetch in the same block before the end time is captured.

I think right now, #1 would be the preferred of the two. However, I would like to achieve this with less manual intervention. I guess it could be argued that this is more of a performance test and not a unit test but it would be nice to catch it in development.

Message was edited by: drew.polhamus_1238095475031

Message was edited by: drew.polhamus


I agree that #1 is the best option and the only one at this time. I will add an enhancement request to somehow give you the option to include retrieval of data of any CVs (and what else? Hmmmm…), but that won’t happen until later this year.