ORA-22813 when running utPLSQL with code coverage #1304
Description
Describe the bug
Running utPLSQL with code coverage fails with ORA-22813, most probably due to a very large result set.
Provide version info
- CLI: utPLSQL-cli 3.1.9.local with Java-Version: 17.0.11
- UtPLSQL core: v3.1.13.3592
- Oracle Database: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.26.0.0.0
Additional context
IMO it is very difficult to reproduce in a different environment. However the following information should point to the root cause
Call Stack:
ORA-22813: Operandwert übersteigt Systemgrenzwert
ORA-06512: in "UT3.UT_RUNNER", Zeile 158
ORA-06512: in "UT3.UT_OUTPUT_TABLE_BUFFER", Zeile 58
ORA-06512: in "UT3.UT_OUTPUT_REPORTER_BASE", Zeile 58
ORA-06512: in "UT3.UT_COVERAGE_HTML_REPORTER", Zeile 37
ORA-06512: in "UT3.UT_REPORTER_BASE", Zeile 193
ORA-06512: in "UT3.UT_EVENT_MANAGER", Zeile 70
ORA-06512: in "UT3.UT_EVENT_MANAGER", Zeile 80
ORA-06512: in "UT3.UT_RUN", Zeile 66
ORA-06512: in "UT3.UT_SUITE_ITEM", Zeile 50
ORA-06512: in "UT3.UT_RUNNER", Zeile 151
ORA-06512: in Zeile 1
java.sql.SQLException: ORA-22813: Operandwert übersteigt Systemgrenzwert
ORA-06512: in "UT3.UT_RUNNER", Zeile 158
ORA-06512: in "UT3.UT_OUTPUT_TABLE_BUFFER", Zeile 58
ORA-06512: in "UT3.UT_OUTPUT_REPORTER_BASE", Zeile 58
ORA-06512: in "UT3.UT_COVERAGE_HTML_REPORTER", Zeile 37
ORA-06512: in "UT3.UT_REPORTER_BASE", Zeile 193
ORA-06512: in "UT3.UT_EVENT_MANAGER", Zeile 70
ORA-06512: in "UT3.UT_EVENT_MANAGER", Zeile 80
ORA-06512: in "UT3.UT_RUN", Zeile 66
ORA-06512: in "UT3.UT_SUITE_ITEM", Zeile 50
ORA-06512: in "UT3.UT_RUNNER", Zeile 151
ORA-06512: in Zeile 1
at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:1157)
at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:1054)
at oracle.jdbc.driver.T2CCallableStatement.executeForDescribe(T2CCallableStatement.java:764)
at oracle.jdbc.driver.T2CCallableStatement.executeForRows(T2CCallableStatement.java:1007)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4251)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081)
at org.utplsql.api.testRunner.DynamicTestRunnerStatement.execute(DynamicTestRunnerStatement.java:84)
at org.utplsql.api.TestRunner.run(TestRunner.java:223)
at org.utplsql.cli.RunTestRunnerTask.call(RunTestRunnerTask.java:45)
at org.utplsql.cli.RunTestRunnerTask.call(RunTestRunnerTask.java:24)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:840)
Caused by: Error : 22813, Position : 0, Sql = BEGIN ut_runner.run(a_paths => :1 , a_reporters => :2 , a_color_console => (case :3 when 1 then true else false end), a_coverage_schemes => :4 , a_fail_on_errors => (case :5 when 1 then true else false end), a_client_character_set => :6 , a_random_test_order => (case :7 when 1 then true else false end));END;, OriginalSql = BEGIN ut_runner.run(a_paths => ?, a_reporters => ?, a_color_console => (case ? when 1 then true else false end), a_coverage_schemes => ?, a_fail_on_errors => (case ? when 1 then true else false end), a_client_character_set => ?, a_random_test_order => (case ? when 1 then true else false end));END;, Error Msg = ORA-22813: Operandwert übersteigt Systemgrenzwert
ORA-06512: in "UT3.UT_RUNNER", Zeile 158
ORA-06512: in "UT3.UT_OUTPUT_TABLE_BUFFER", Zeile 58
ORA-06512: in "UT3.UT_OUTPUT_REPORTER_BASE", Zeile 58
ORA-06512: in "UT3.UT_COVERAGE_HTML_REPORTER", Zeile 37
ORA-06512: in "UT3.UT_REPORTER_BASE", Zeile 193
ORA-06512: in "UT3.UT_EVENT_MANAGER", Zeile 70
ORA-06512: in "UT3.UT_EVENT_MANAGER", Zeile 80
ORA-06512: in "UT3.UT_RUN", Zeile 66
ORA-06512: in "UT3.UT_SUITE_ITEM", Zeile 50
ORA-06512: in "UT3.UT_RUNNER", Zeile 151
ORA-06512: in Zeile 1
at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:1170)
... 16 more
The error is thrown here.
insert /*+ no_parallel */ into ut_output_buffer_tmp(output_id, message_id, text, item_type)
select /*+ no_parallel */ self.output_id, self.last_message_id + rownum, t.column_value, a_item_type
from table(a_text_list) t
where t.column_value is not null or a_item_type is not null;So the problem is most probably that the arraysize of a_text_list is exceeding the limit. I don't know what the limit is, it was once 4177919 according to Bug 19720346 - ORA-22813: operand value exceeds system limits on TABLE() collection (Doc ID 19720346.8). The bug was fixed in 12.2. The bug contains a test case. I ran the following:
CREATE OR REPLACE TYPE SUBS_ID_COLLECTION_T AS TABLE OF NUMBER(10);
/
DECLARE
l_changed_subs_ids SUBS_ID_COLLECTION_T := SUBS_ID_COLLECTION_T(); --initialize
nr NUMBER(30);
BEGIN
FOR i IN 0 .. 41779190 -- 10 times more than in the original case
LOOP
l_changed_subs_ids.extend;
l_changed_subs_ids(l_changed_subs_ids.last) := i;
END LOOP;
SELECT count(*) INTO nr FROM TABLE(l_changed_subs_ids);
DBMS_OUTPUT.PUT_LINE(l_changed_subs_ids.COUNT);
END;
/ which causes
DECLARE
*
ERROR at line 1:
ORA-22813: operand value exceeds system limits
ORA-06512: at line 10https://docs.oracle.com/error-help/db/ora-22813/
Suggested Solution Approach
We know the limit lies somewhere between 4177919 and 41779190. IMO it does not make sense to determine the exact limit or open an SR. Instead I suggest to change the code so that the array to be written to the table does not get that large since this might become also a memory issue.
Notes
The issue sounds similar to #368 where also an ORA-22813 is thrown. Looks like a similar issue in another code area but otherwise unrelated to this issue.