Search code examples
python-2.7cx-oracleoracleclient

How to fix 'ORA-19011' error in Python cx_Oracle

HI~ I want to query xml data from Oracle db with cx_Oracle, but it doesn't work with Ora-19011 error message. I think size of query data is larger than string buffer, but i don't know how to solve this problem

Oracle DB is an external DB and it's not my own DB, So i can't access directly. Therefore, I want to fix my problem on my code and print query data on python terminal.

(my software version)
windows 10 64bit
python 2.7 64bit
oracle-instant client 19.3 64bit
cx_oracle 7.2.2

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import cx_Oracle
import sys
import csv
import codecs


printHeader = True

conn = cx_Oracle.connect('id/passwd@ip:port/orcl')
print(conn.version)

curs = conn.cursor()
curs.execute('SELECT * FROM tablename')

for record in curs:
    print(record)

Error occured at line 18(for record in curs) and here are error messages.

11.2.0.4.0
We've got an error while stopping in unhandled exception: <class 'cx_Oracle.DatabaseError'>.
Traceback (most recent call last):
  File "c:\Users\goo41\.vscode\extensions\ms-python.python-2019.8.30787\pythonFiles\lib\python\ptvsd\_vendored\pydevd\pydevd.py", line 1740, in do_stop_on_unhandled_exception
    self.do_wait_suspend(thread, frame, 'exception', arg, is_unhandled_exception=True)
  File "c:\Users\goo41\.vscode\extensions\ms-python.python-2019.8.30787\pythonFiles\lib\python\ptvsd\_vendored\pydevd\pydevd.py", line 1615, in do_wait_suspend
    with self._threads_suspended_single_notification.notify_thread_suspended(thread_id, stop_reason):
  File "C:\Python27\lib\contextlib.py", line 17, in __enter__
    return self.gen.next()
  File "c:\Users\goo41\.vscode\extensions\ms-python.python-2019.8.30787\pythonFiles\lib\python\ptvsd\_vendored\pydevd\pydevd.py", line 360, in notify_thread_suspended
    with AbstractSingleNotificationBehavior.notify_thread_suspended(self, thread_id, stop_reason):
  File "C:\Python27\lib\contextlib.py", line 17, in __enter__
    return self.gen.next()
  File "c:\Users\goo41\.vscode\extensions\ms-python.python-2019.8.30787\pythonFiles\lib\python\ptvsd\_vendored\pydevd\pydevd.py", line 308, in notify_thread_suspended
    self.send_suspend_notification(thread_id, stop_reason)
  File "c:\Users\goo41\.vscode\extensions\ms-python.python-2019.8.30787\pythonFiles\lib\python\ptvsd\_vendored\pydevd\pydevd.py", line 354, in send_suspend_notification
    py_db.writer.add_command(py_db.cmd_factory.make_thread_suspend_single_notification(py_db, thread_id, stop_reason))
  File "c:\Users\goo41\.vscode\extensions\ms-python.python-2019.8.30787\pythonFiles\lib\python\ptvsd\_vendored\pydevd\_pydevd_bundle\pydevd_net_command_factory_json.py", line 309, in make_thread_suspend_single_notification
    return NetCommand(CMD_THREAD_SUSPEND_SINGLE_NOTIFICATION, 0, event, is_json=True)
  File "c:\Users\goo41\.vscode\extensions\ms-python.python-2019.8.30787\pythonFiles\lib\python\ptvsd\_vendored\pydevd\_pydevd_bundle\pydevd_net_command.py", line 57, in __init__
    text = json.dumps(as_dict)
  File "C:\Python27\lib\json\__init__.py", line 244, in dumps
    return _default_encoder.encode(obj)
  File "C:\Python27\lib\json\encoder.py", line 207, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "C:\Python27\lib\json\encoder.py", line 270, in iterencode
    return _iterencode(o, 0)
UnicodeDecodeError: 'utf8' codec can't decode byte 0xb9 in position 11: invalid start byte
Traceback (most recent call last):
  File "c:\Users\goo41\.vscode\extensions\ms-python.python-2019.8.30787\pythonFiles\ptvsd_launcher.py", line 43, in <module>
    main(ptvsdArgs)
  File "c:\Users\goo41\.vscode\extensions\ms-python.python-2019.8.30787\pythonFiles\lib\python\ptvsd\__main__.py", line 432, in main
    run()
  File "c:\Users\goo41\.vscode\extensions\ms-python.python-2019.8.30787\pythonFiles\lib\python\ptvsd\__main__.py", line 316, in run_file
    runpy.run_path(target, run_name='__main__')
  File "C:\Python27\lib\runpy.py", line 252, in run_path
    return _run_module_code(code, init_globals, run_name, path_name)
  File "C:\Python27\lib\runpy.py", line 82, in _run_module_code
    mod_name, mod_fname, mod_loader, pkg_name)
  File "C:\Python27\lib\runpy.py", line 72, in _run_code
    exec code in run_globals
  File "c:\PythonWorkspace\oraclePrc\test1.py", line 18, in <module>
    for record in curs:
cx_Oracle.DatabaseError: ORA-19011: Character string buffer too small
Solution

  • When you connect to the database, try using this code instead:

    conn = cx_Oracle.connect('id/passwd@ip:port/orcl', encoding="UTF-8", nencoding="UTF-8")

    This will ensure that you are using a universal encoding -- which may eliminate the first error, and possibly the second as well. If not, adjust the code sample and error messages noted above.