Oracle OCI library hides NO_DATA_FOUND exception from database with Oracle backend.

[Jani Tiainen]

Attached minimal project to demonstrate the problem.

Traceback from Django:

Traceback (most recent call last):
  File "/home/jtiai/projects/django-oracle-bug/django_oracle_bug/django_oracle_bug/tests.py", line 6, in test_trigger_failure
    obj = MyModel.objects.create()
  File "/home/jtiai/.pyenv/versions/django-oracle-bug/lib/python3.6/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/jtiai/.pyenv/versions/django-oracle-bug/lib/python3.6/site-packages/django/db/models/query.py", line 394, in create
    obj.save(force_insert=True, using=self.db)
  File "/home/jtiai/.pyenv/versions/django-oracle-bug/lib/python3.6/site-packages/django/db/models/base.py", line 808, in save
    force_update=force_update, update_fields=update_fields)
  File "/home/jtiai/.pyenv/versions/django-oracle-bug/lib/python3.6/site-packages/django/db/models/base.py", line 838, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/home/jtiai/.pyenv/versions/django-oracle-bug/lib/python3.6/site-packages/django/db/models/base.py", line 924, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/home/jtiai/.pyenv/versions/django-oracle-bug/lib/python3.6/site-packages/django/db/models/base.py", line 963, in _do_insert
    using=using, raw=raw)
  File "/home/jtiai/.pyenv/versions/django-oracle-bug/lib/python3.6/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/jtiai/.pyenv/versions/django-oracle-bug/lib/python3.6/site-packages/django/db/models/query.py", line 1076, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/home/jtiai/.pyenv/versions/django-oracle-bug/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1114, in execute_sql
    return self.connection.ops.fetch_returned_insert_id(cursor)
  File "/home/jtiai/.pyenv/versions/django-oracle-bug/lib/python3.6/site-packages/django/db/backends/oracle/operations.py", line 245, in fetch_returned_insert_id
    return int(cursor._insert_id_var.getvalue())
TypeError: int() argument must be a string, a bytes-like object or a number, not 'NoneType'

Real error that happend:

ORA-01403: no data found
ORA-06512: at "ORA_TEST.TG_FAILING", line 4
ORA-04088: error during execution of trigger 'ORA_TEST.TG_FAILING'

[Jani Tiainen]

Minimal project to demonstrate the problem

[Mariusz Felisiak]

I attached minimal test in the Django test suite "28859.diff​". I'm not entirely sure if it's a Django issue or a driver issue. Accepted for further investigation.

[Mariusz Felisiak]

Other exceptions e.g. DUP_VAL_ON_INDEX, ZERO_DIVIDE, TOO_MANY_ROWS are raised properly.

[Jani Tiainen]

Replying to felixxm:

When testing using plain cx_Oracle exception is thrown correctly and visible on console.

$ ./manage.py shell

In [1]: from django.db import connections

In [2]: connections['default'].connect()

In [3]: conn=connections['default'].connection   # Get plain cx_Oracle connection instead of Django wrappers

In [4]: cur=conn.cursor()

In [5]: cur.execute('INSERT INTO MYMODEL(ID) VALUES (1)')
---------------------------------------------------------------------------
DatabaseError                             Traceback (most recent call last)
<ipython-input-5-39d4fbf2986d> in <module>()
----> 1 cur.execute('INSERT INTO MYMODEL(ID) VALUES (1)')

DatabaseError: ORA-01403: no data found
ORA-06512: at "ORA_TEST.TG_FAILING", line 4
ORA-04088: error during execution of trigger 'ORA_TEST.TG_FAILING'

[Mariusz Felisiak]

Replying to Jani Tiainen:

INSERT without RETURNING clause raises NO_DATA_FOUND exception also with a wrapped connection in Django, but when we add RETURNING clause, both a cx_Oracle plain connection and a wrapped connection in Django doesn't work properly, e.g.:

>>> cursor.execute('''
    INSERT INTO "BACKENDS_SQUARE"("ROOT", "SQUARE")
    VALUES(2,4) RETURNING "BACKENDS_SQUARE"."ID" INTO :v_id
''', {'v_id': v_id})

I raised the issue on cx_Oracle ​https://github.com/oracle/python-cx_Oracle/issues/131.

[Mariusz Felisiak]

​PR

[Mariusz Felisiak]

I added workaround in the Oracle back-end, since issue will not be fixed in the Oracle OCI library (see ​comment).

[GitHub <noreply@…>]

In 8f8a93a9:

Fixed #28859 -- Made Oracle backend raise DatabaseError if "no data found" exception is hidden by the Oracle OCI library.

Thanks Tim Graham for the review and Jani Tiainen for the report.

[GitHub <noreply@…>]

In 483cc1c4:

Refs #28859 -- Fixed "no data found" exception handling with cx_Oracle 6.3+.

[GitHub <noreply@…>]

In 85f924a9:

Refs #28859 -- Simplified fetch_returned_insert_id() by using int data type for binding variable on Oracle.