时间戳

首页 > 解决方案 > JMeter: How to deal with implicit result in Oracle 12C? ORA-20999: Oracle ERROR:: ORA-29478: Implicit result cannot be returned through this statement

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

问题描述

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?

标签： oraclejmeter

解决方案

经过一番挖掘，我发现这不是驱动程序问题。这实际上是另一回事。我使用错误的语法来调用存储过程。

所以我原来的语法是： 

call _stored_procedure_()  
call office_hierarchy()

但是，正确的语法是： 

BEGIN  
_stored_procedure_();  
END;  

BEGIN  
office_hierarchy();  
END;

这将解决这个问题，JMeter 现在返回结果集没有任何问题。

感谢大家的帮助。根据网站规则，我将在 48 小时后接受我的所有者答复。当然，总是欢迎其他输入！

推荐阅读