Get ORA-01460 when passing boolean in procedure parameter

[Miqy]

1. What versions are you using?

oracledb==2.2.1 Oracle database server 12.1 Oracle instant client 21.1.0.0

I have python code:

from some_module_with_db_connection import db
import oracledb
with db.engine.raw_connection().cursor() as cursor:
    cursor.callfunc("test_bool_insert", int, keyword_parameters={'d': True})

In the Oracle database i have function

CREATE OR REPLACE FUNCTION test_bool_insert(
, d boolean default false
)
   RETURN NUMBER DETERMINISTIC IS
BEGIN
  RETURN 0;
END;

Then i got stacktrace:

File /usr/local/lib/python3.12/site-packages/oracledb/cursor.py:623, in Cursor.callfunc(self, name, return_type, parameters, keyword_parameters, keywordParameters)
    617         errors._raise_err(
    618             errors.ERR_DUPLICATED_PARAMETER,
    619             deprecated_name="keywordParameters",
    620             new_name="keyword_parameters",
    621         )
    622     keyword_parameters = keywordParameters
--> 623 self._call(name, parameters, keyword_parameters, var)
    624 return var.getvalue()

File /usr/local/lib/python3.12/site-packages/oracledb/cursor.py:113, in BaseCursor._call(self, name, parameters, keyword_parameters, return_value)
    111 statement_parts.append("); end;")
    112 statement = "".join(statement_parts)
--> 113 return self.execute(statement, bind_values)

File /usr/local/lib/python3.12/site-packages/oracledb/cursor.py:701, in Cursor.execute(self, statement, parameters, **keyword_parameters)
    699 self._prepare_for_execute(statement, parameters, keyword_parameters)
    700 impl = self._impl
--> 701 impl.execute(self)
    702 if impl.fetch_vars is not None:
    703     return self

File src/oracledb/impl/thick/cursor.pyx:306, in oracledb.thick_impl.ThickCursorImpl.execute()

File src/oracledb/impl/thick/utils.pyx:456, in oracledb.thick_impl._raise_from_odpi()

File src/oracledb/impl/thick/utils.pyx:446, in oracledb.thick_impl._raise_from_info()

DatabaseError: ORA-01460: unimplemented or unreasonable conversion requested

Any suggestions?

[cjbj]

Your testcase runs fine with 23ai Oracle Client & Database.

In the old days, I used to recommend not using BOOLEAN in language drivers until Oracle Database 12.2. I recall Oracle bug 21660729 which was fixed in 12.2 (and doesn't look like it was backported to 12.1). That bug was really about interop between big & little endian client & server. But there may have been other issues.

[anthony-tuininga]

As Chris mentioned, this is a known mismatch between the Oracle Client libraries and the database which is not going to be repaired at this stage! You can do this until you are able to upgrade your database:

var = cursor.var(int)
cursor.execute("""
    begin
        :1 := test_bool_insert(:2 = 1);
    end;""",
    [var, 1])
result = var.getvalue()

If you have a variable that contains a boolean you can use int(value) to convert it to an integer for passing to the database.

You could also try thin mode. I don't recall whether thin mode supports this correctly or not. Since it never worked in thick mode I don't know whether we tried it or not and I don't have ready access to such an old database. Upgrade when you can!

[Miqy]

Thank you for your code. It works, but I resolved the issue by replacing the boolean function parameter with an integer parameter (0 and 1).

[anthony-tuininga]

Yep. That works, too. :-)