Skip to content

ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes #93

Closed
Closed
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes#93
Assignees
PhilippSalvisberg
Labels
bug
Milestone
v1.1.1

Description

@wolframhaussig
wolframhaussig
opened on Mar 12, 2020

Describe the bug
I started yesterday with writing unittests using utPLSQL. Yesterday, everything worked fine but starting this afternoon I cannot use the sqldeveloper plugin to create coverage reports anymore(test run still works).

Executing the script results in:

EXECUTE dbms_session.reset_package;
SET SERVEROUTPUT ON SIZE UNLIMITED
CLEAR SCREEN
EXEC ut.run('[redacted]',ut_coverage_html_reporter())

Fehler beim Start in Zeile: 4 in Befehl -
BEGIN ut.run('[redacted]',ut_coverage_html_reporter()); END;
Fehlerbericht -
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: in "SYS.DBMS_OUTPUT", Zeile 32
ORA-06512: in "SYS.DBMS_OUTPUT", Zeile 97
ORA-06512: in "SYS.DBMS_OUTPUT", Zeile 112
ORA-06512: in "UNITTESTS.UT_OUTPUT_BUFFER_BASE", Zeile 59
ORA-06512: in "UNITTESTS.UT_OUTPUT_REPORTER_BASE", Zeile 80
ORA-06512: in "UNITTESTS.UT", Zeile 504
ORA-06512: in "UNITTESTS.UT", Zeile 623
ORA-06512: in Zeile 1
20000. 00000 - "%s"
*Cause: The stored procedure 'raise_application_error'
was called which causes this error to be generated.
*Action: Correct the problem as described in the error message or contact
the application administrator or DBA for more information.

Using the sqldeveloper menu entry the logging of sqldeveloper shows this log after a while:
Error when running code coverage: StatementCallback; uncategorized SQLException for SQL [BEGIN
ut.run(
a_paths => ut_varchar2_list(
'[redacted]'
),
a_reporter => ut_coverage_html_reporter()
);
END;
]; SQL state [72000]; error code [20000]; ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: in "SYS.DBMS_OUTPUT", Zeile 32
ORA-06512: in "SYS.DBMS_OUTPUT", Zeile 97
ORA-06512: in "SYS.DBMS_OUTPUT", Zeile 112
ORA-06512: in "UNITTESTS.UT_OUTPUT_BUFFER_BASE", Zeile 59
ORA-06512: in "UNITTESTS.UT_OUTPUT_REPORTER_BASE", Zeile 80
ORA-06512: in "UNITTESTS.UT", Zeile 504
ORA-06512: in Zeile 2
; nested exception is java.sql.SQLException: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: in "SYS.DBMS_OUTPUT", Zeile 32
ORA-06512: in "SYS.DBMS_OUTPUT", Zeile 97
ORA-06512: in "SYS.DBMS_OUTPUT", Zeile 112
ORA-06512: in "UNITTESTS.UT_OUTPUT_BUFFER_BASE", Zeile 59
ORA-06512: in "UNITTESTS.UT_OUTPUT_REPORTER_BASE", Zeile 80
ORA-06512: in "UNITTESTS.UT", Zeile 504
ORA-06512: in Zeile 2

Provide version info
Information about utPLSQL and Database version,
12.1.0.2.0
12.1.0.2.0

PL/SQL-Prozedur erfolgreich abgeschlossen.

UT_VERSION

v3.1.10.3349

BANNER CON_ID

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0

PARAMETER VALUE

NLS_LANGUAGE GERMAN
NLS_TERRITORY GERMANY
NLS_CURRENCY ?
NLS_ISO_CURRENCY GERMANY
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD.MM.RR HH24:MI:SS
NLS_DATE_LANGUAGE GERMAN
NLS_SORT GERMAN
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF

PARAMETER VALUE

NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY ?
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE

17 Zeilen gewählt.

PORT_STRING

x86_64/Linux 2.4.xx

Information about client software
SQLDeveloper with installed utPlsql plugin

Expected behavior
I would expect that this call works and creates the coverage report.

Activity

  1. PhilippSalvisberg commented on Mar 12, 2020

    @PhilippSalvisberg
    PhilippSalvisberg
    on Mar 12, 2020
    Member

    This is a SQL Developer and SQLcli issue.

    You can run the following successfully in SQL*Plus 19.5:

    set serveroutput on size unlimited
begin
   for i in 1..500000 loop
      dbms_output.put_line(i || ': some text');
   end loop;
end;
/

    The result ends with:

    ...
499997: some text
499998: some text
499999: some text
500000: some text

PL/SQL procedure successfully completed.

SQL>

    If you run the same script in SQLcl 19.4 or SQL Developer 19.4 you get the following result:

    ...
63191: some text
63192: some text
63193: some text
63194: some text


Error starting at line : 1 in command -
begin
   for i in 1..500000 loop
      dbms_output.put_line(i || ': some text');
   end loop;
end;
Error report -
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 3
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.
SQL>

    It looks like SQLcl and SQL Developer limit the DBMS_OUTPUT to 1000000 even if you set UNLIMITED.

    @thatjeffsmith do you know about this restriction?

  2. jgebal commented on Mar 12, 2020

    @jgebal
    jgebal
    on Mar 12, 2020
    Member

    One way to fix this in the plugin would ne to use select * from table (ut.run...) function call instead of iding procedire call.
    That way you spool output of select instead of spooling dbms_output results.

  3. PhilippSalvisberg commented on Mar 12, 2020

    @PhilippSalvisberg
    PhilippSalvisberg
    on Mar 12, 2020
    Member

    @jgebal actually, utPLSQL for SQL Developer uses sys.dbms_output.enable(NULL); behind the scenes. This is equivalent to UNLIMITED. So the following should work as well as a workaround:

    EXECUTE dbms_session.reset_package;
SET SERVEROUTPUT ON SIZE UNLIMITED
EXECUTE sys.dbms_output.enable(NULL);
CLEAR SCREEN
EXEC ut.run('[redacted]',ut_coverage_html_reporter())

  4. jgebal commented on Mar 12, 2020

    @jgebal
    jgebal
    on Mar 12, 2020
    Member

    Ah. I thought it's issue with utPLSQL plugin, not with running from Worksheet.

    @wolframhaussig
    I assume you're all sorted with this issue as per solution provided by @PhilippSalvisberg

    Closing.

  5. jgebal
    closed this as completedon Mar 12, 2020

  6. wolframhaussig commented on Mar 13, 2020

    @wolframhaussig
    wolframhaussig
    on Mar 13, 2020
    Author

    Hello, @jgebal , I tried @PhilippSalvisberg s proposal and it did not fix it - I still get the same error in the worksheet And you were right: I get the error both in the worksheet and from the plugin - maybe I should have made it more clear. The second error I added in the initial post is from the sqldeveloper plugin.

  7. wolframhaussig commented on Mar 13, 2020

    @wolframhaussig
    wolframhaussig
    on Mar 13, 2020
    Author

    @jgebal I cannot reopen the issue, would you be so kind to do it?

  8. jgebal
    reopened this on Mar 13, 2020

  9. PhilippSalvisberg commented on Mar 13, 2020

    @PhilippSalvisberg
    PhilippSalvisberg
    on Mar 13, 2020
    Member

    I checked on my 12.1.0.2.190716OJVMPSU instance. I ran the following script in SQL*Plus, SQLcl and SQL Developer worksheet successfully:

    set serveroutput on size unlimited 
execute sys.dbms_output.enable(null);
begin
   for i in 1..500000 loop
      dbms_output.put_line(i || ': some text');
   end loop;
end;
/

    @wolframhaussig can you run this in your environment?

  10. jgebal commented on Mar 13, 2020

    @jgebal
    jgebal
    on Mar 13, 2020
    Member

    I'm transferring this issue to utPLSQL-SQLDeveloper, as it's related to HTML coverage report generation using utPLSQL plugin in SQLDeveloper .

  11. jgebal
    transferred this issue fromutPLSQL/utPLSQLon Mar 13, 2020
  12. PhilippSalvisberg
    self-assigned this
    on Mar 13, 2020
  13. PhilippSalvisberg
    added
    bug
    on Mar 13, 2020

  14. PhilippSalvisberg commented on Mar 13, 2020

    @PhilippSalvisberg
    PhilippSalvisberg
    on Mar 13, 2020
    Member

    Producing the code coverage report via DBMS_OUTPUT is a bad idea anyway. There is no way to suppress unwanted dbms_output produced by the test run itself. Hence I'm going to change the call as @jgebal has suggested to something like select ... from table(ut.run...). This should solve this issue.

  15. PhilippSalvisberg
    closed this as completedin ec0b73eon Mar 14, 2020
  16. PhilippSalvisberg
    added this to the v1.1.1 milestone on Mar 14, 2020

  17. fedor-khv commented on Oct 5, 2022

    @fedor-khv
    fedor-khv
    on Oct 5, 2022

    проблема в том, что у ut3 своя сессия, которая и имеет ограничения на свой вывод в 20000 байт. нужно создавать свой метод run, в котором нужно снять это ограничение:

    create procedure run_run (si_input varchar2) as
    buff ut_varchar2_rows := ut_varchar2_rows();
    begin
    for rec in (select t.column_value raw from table (ut.run(si_input)) t) loop
    ut_utils.append_to_list(buff, rec.raw);
    end loop;

    dbms_output.enable(null); --снимаем ограничение. можно поставить вместо null приемлимое значение
for i in 1 .. buff.count loop
  dbms_output.put_line(buff(i));
end loop;

    end;

    затем, просто пользуем run_run('[redacted]',ut_coverage_html_reporter())
    должно сработать

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

bug

Type

No type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions