Facing ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes issue

[mrafivyr]

Tried executing cur.callproc("DBMS_OUTPUT.ENABLE") in each iteration to release buffer, but it not working.

Even tried cur.callproc("DBMS_OUTPUT.ENABLE", (None, )) to increase buffer size to unlimited.

import cx_Oracle
cx_Oracle.version
'5.3'

Python 3.4.5

[cjbj]

Review https://github.com/oracle/python-cx_Oracle/blob/master/samples/DbmsOutput.py

If you still have problems, then give the answers to the template questions: https://raw.githubusercontent.com/oracle/python-cx_Oracle/master/.github/ISSUE_TEMPLATE/general-questions-and-runtime-problems.md

[mrafivyr]

Thanks for your quick response.

I have implemented my initial logic by going through cx_Oracle docs, https://cx-oracle.readthedocs.io/. But unfortunately facing this buffer issue.

Providing requested details and sample code to replicate the issue.

1. What versions are you using?

Database Version:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

platform.platform: Linux-2.6.32-573.26.1.el6.x86_64-x86_64-with-centos-6.7-Final
sys.maxsize > 2**32: True
platform.python_version: 3.4.5

cx_Oracle.version: 5.3
cx_Oracle.clientversion: (11, 2, 0, 3, 0)

2. Describe the problem

We are only having readonly access on DB where we are trying to fetch data, so anonymous block is created by using logic provided in below link.
https://cx-oracle.readthedocs.io/en/latest/user_guide/plsql_execution.html?highlight=dbms_output#using-dbms-output

Its working fine with lesser volumes of data, but we are facing "ORA-20000" error with increase in volume of data the we fetch.

cx_Oracle.DatabaseError: ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes

In some stackflow articles saw that calling DBMS_OUTPUT.ENABLE will realse buffer, but its not working. Even tried to set buffer size to unlimited with below command
cur.callproc("DBMS_OUTPUT.ENABLE", (None,))

3. Include a runnable Python script that shows the problem.

# db_file.py
import cx_Oracle


def get_elder_db_data(_connection_string, _organization_id_string):
    anonymous_block = """DECLARE
                        BEGIN
                            FOR v_temp_org_id IN (SELECT TRIM(regexp_substr(:org_str, '[^,]+', 1, LEVEL))
                                                org_id FROM dual
                                                CONNECT BY LEVEL <= regexp_count(:org_str, ',')+1)
                            LOOP
                                DBMS_OUTPUT.PUT_LINE( RPAD(v_temp_org_id.org_id || '|' ||'PARENT', 1000, 'z'));
                            END LOOP;
                        END;"""

    with cx_Oracle.connect(_connection_string, encoding="UTF-8", nencoding="UTF-8") as conn:
        cur = conn.cursor()
        complete_data = []
        cur.callproc("DBMS_OUTPUT.ENABLE")
        chunk_size = 10
        # create variables to hold the output
        lines_var = cur.arrayvar(str, chunk_size)
        num_lines_var = cur.var(int)
        num_lines_var.setvalue(0, chunk_size)
        cur.execute(anonymous_block, org_str=_organization_id_string)
        cur.callproc("dbms_output.get_lines", (lines_var, num_lines_var))
        num_lines = num_lines_var.getvalue()
        lines = lines_var.getvalue()[:num_lines]
        print(lines)
        complete_data.extend(lines)
		
# calling_file.py
from db_file import get_elder_db_data
organization_id_string = '1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10'
connection_string = '******************************************'
for cnt in range(20):
    data = get_elder_db_data(connection_string, organization_id_string)
    for val in data:
        print(val)

[mrafivyr]

command : nohup python -u calling_file.py >> ./nohup_db_test 2>&1 &

Complete Error Trace:
nohup: ignoring input
Traceback (most recent call last):
File "calling_file.py", line 6, in
data = get_elder_db_data(connection_string, organization_id_string)
File "/home/rmohamme/ESG_temp/test_issue/db_file.py", line 25, in get_elder_db_data
cur.execute(anonymous_block, org_str=_organization_id_string)
cx_Oracle.DatabaseError: ORA-20000: ORU-10027: buffer overflow, limit of 20000 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 7
ORA-06512: at line 7

[anthony-tuininga]

The example you have given is using the default buffer size of 20,000 bytes -- so the error is not unexpected! You can add dbms_output.enable(null); to the top of your anonymous PL/SQL block. Doing so eliminated the error completely. I got another error in your processing of the results -- but that's easily solved!

[cjbj]

The simplest fix to your code is to change the existing cur.callproc("DBMS_OUTPUT.ENABLE") line to cur.callproc("DBMS_OUTPUT.ENABLE", [None]). However this still requires two callproc() calls from python, so it's not as efficient as @anthony-tuininga's suggestion of calling DBMS_OUTPUT.ENABLE in the same block that generates output. P.S. you are missing a if data: line in calling_file.py

Overall, your code feels like it should be able to be optimized a lot. I don't understand what exactly you are trying to do, but don't do it. Work out how to construct the data rows you want in a single query and don't use DBMS_OUTPUT at all. Ask on https://asktom.oracle.com/ if you need help with the SQL. That will get rid of multiple round-trips between python and Oracle DB, and avoid crossing the SQL-to-PL/SQL boundary (not to mention whatever overhead DBMS_OUTPUT has).

[mrafivyr]

Thanks for quick response @anthony-tuininga and @cjbj

cur.callproc("DBMS_OUTPUT.ENABLE", [None]) didn’t resolve the issue, placing in anonymous block itself has resolved the issue.

Now am not using any “DBMS_OUTPUT.ENABLE” in Python code, only in anonymous block. This way reducing number round trips to Database.

I think no need to have if data: condition in python code to check for existence of data in it. For loop will take care of it, if there is no data, it will not iterate over it.

We don’t have privilege on database to create functions or procedures and pass the entire data at once using type variables. I will check if there is way to pass type variables from anonymous blocks.

Thanks again for constructive feedback and quick response.

[cjbj]

cur.callproc("DBMS_OUTPUT.ENABLE", [None]) didn’t resolve the issue, placing in anonymous block itself has resolved the issue.

Worked for me - as expected.

I think no need to have if data: condition in python code to check for existence of data in it. For loop will take care of it, if there is no data, it will not iterate over it.

Without it I got:

Traceback (most recent call last):
  File "issue467.py", line 42, in <module>
    for val in data:
TypeError: 'NoneType' object is not iterable

But since nothing is being returned from get_elder_db_data, the whole code block is not needed.

I'm using Python 3.8.

What are you trying to do? Pass in an array of values? And then what?

[mrafivyr]

copy paste issue from my end. Missed last line return get_elder_db_data while copying.

I'm returning complete_data list variable from get_elder_db_data.

Get array of values from DB and get data from xml file with other process, then compare DB data and xml data.

[cjbj]

Keep looking for easier, more efficient ways. If the number of keys is large you could even use executemany() to upload the keys into a temp table, and the use a single SELECT to join with your data and return rows.

I'll close this issue now since we have solved your immediate question.