cx_Oracle Get Boolean Return Value

I have been working hard all day attempting to get a boolean value from a PL/SQL function using cx_Oracle. I've seen posts talking about using some other data type like char or integer to store the return value, but when I attempt to use such solutions, I get an incorrect data type error. First, let me show the code.

def lives_on_campus(self):
  cursor = conn.cursor()
  ret = cursor.callfunc('students_api.lives_on_campus', bool, [self.pidm])
  return ret

If I use the 11.2.0.4 database client, I get the following error.

File "student-extracts.py", line 134, in <module>
    if student.lives_on_campus():
  File "student-extracts.py", line 58, in lives_on_campus
    ret = cursor.callfunc('students_api.lives_on_campus', bool, [self.pidm])
cx_Oracle.DatabaseError: DPI-1050: Oracle Client library is at version 11.2 but version 12.1 or higher is needed

If I use the 12.1.0.2 database client or later, I get this error.

Traceback (most recent call last):
  File "student-extracts.py", line 134, in <module>
    if student.lives_on_campus():
  File "student-extracts.py", line 58, in lives_on_campus
    ret = cursor.callfunc('students_api.lives_on_campus', bool, [self.pidm])
cx_Oracle.DatabaseError: ORA-03115: unsupported network datatype or representation

Basically, it errors out no matter which version of the SQL Client I use. Now, I know the above code will work if the database version is 12c R2. Unfortunately, we only have that version in our TEST environment and PROD uses only the 11g database. Is there any I can make that function work with an 11g database? There must be a workaround.

~ Bob

Solution

Try a wrapper anonymous block like:

with connection.cursor() as cursor:
  outVal = cursor.var(int)

  sql="""
  begin
    :outVal := sys.diutil.bool_to_int(students_api.lives_on_campus(:pidm));
  end;
  """

  cursor.execute(sql, outVal=outVal, pidm='123456')

  print(outVal.getvalue())