JMeter: How to deal with implicit result in Oracle 12C? ORA-20999: Oracle ERROR:: ORA-29478: Implicit result cannot be returned through this statement

Question

When calling a stored procedure through JMeter in an Oracle database, I get the following error:
ORA-20999: Oracle ERROR:: ORA-29478: Implicit result cannot be returned through this statement

I don't have 10 reputation so I cannot post images. I'll describe the configuration.

This is my JDBC Connection information:

Max Number of Connections:0  
Max Wait (ms):10000  
Time Between Eviction Runs (ms): 60000  
Auto Commit: True  
Transaction Isolation:TRANSACTION_READ_COMMITTED  
Test While Idle: True  
Soft Min Evictable Idle Time (ms): 5000  
Validation Query: select 1 from dual  
JDBC Driver Class: oracle.jdbc.OracleDriver

This is my JDBC Request Sampler information:

Query Type: Callable Statement  
Procedure: call office_hierarchy()

This is the error that I get:

ORA-20999: Oracle ERROR:: ORA-29478: Implicit result cannot be returned through this statement  
ORA-06512: at 'XXXX.UTILS", line 2019  
ORA-06512: at "XXXX.OFFICE_HIERARCHY", line 39

I purposely replaced my user with XXXX.

I'm setting up a JMeter test script, in which I call stored procedures in our Oracle database. The Oracle database is on version 12C. I use the latest version ojdbc8.jar from the Oracle website. I have also tried ojdbc6.jar and ojdbc7.jar, also from the Oracle website. My Jmeter is on 5.0 r1840935. My java version is 1.8.0_191.

I have confirmed that the database connection can be made, for a select query can be made without problems.

Implicit results (or implicit cursors) have been introduced in Oracle 12c. Has anyone run into this issue so far? Should I change something in the way that I call the stored procedure? Or is this still perhaps a driver issue? Or could it be that JMeter doesn't support this feature yet?

show your JDBC Request,also try using Query type Callable Statement — user7294900
Hi user7294900, thanks for your suggestion. I have updated my initial question with additional information, I hope my question is now more clear. I can unfortunately not include images as I don't have 10 reputation yet. — JohanK
Try to change in Configuration the Transaction Isolation to DEFAULT — user7294900
Thanks user7294900 for your input. I tried changing the transaction isolation, but in the end it turned out that the error was related to my syntax. I have posted an answer below where I explain this in more detail. — JohanK

JohanK JohanK · Accepted Answer · 2019-01-15T09:29:12

After some digging, I found that this is not a driver issue. It was actually something else. I was using the wrong syntax to call the stored procedure.

So my original syntax was:

call _stored_procedure_()  
call office_hierarchy()

However, the correct syntax is:

BEGIN  
_stored_procedure_();  
END;  

BEGIN  
office_hierarchy();  
END;

This will resolve this issue, JMeter returns result sets without any issue now.

Thanks everyone for your help. I will accept my owner answer after 48 hours as per the site rules. Other input is always welcome of course!

JMeter: How to deal with implicit result in Oracle 12C? ORA-20999: Oracle ERROR:: ORA-29478: Implicit result cannot be returned through this statement

3 Answers