UnicodeDecodeError: 'utf-8' codec can't decode byte (outputtypehandler encoding_errors="replace" not being honored)

[rh8056]

Hello,

I posted this question on StackExchange yesterday as well, but figured it would make sense to ask here as well.

I'm struggling to deal with what I think is corrupt data stored in an Oracle database when reading it in a python script. I have the following:

    def output_type_handler(cursor, metadata):
      if metadata.type_code is oracledb.DB_TYPE_VARCHAR:
        return cursor.var(metadata.type_code,
                          arraysize=cursor.arraysize,
                          encoding_errors="replace")

    mydb, mycursor = connectOracle()
    mycursor.outputtypehandler = output_type_handler

    mycursor.execute('''select file_id, md5_value,
                            case when file_content is not null
                                then
                                    utl_raw.cast_to_varchar2(dbms_lob.substr(file_content))
                                else
                                    ''
                                end as FILE_CONTENT from archive_file where archive_id = 123 and file_name = 'file_name.txt' ''')
    row = mycursor.fetchone()
    print(row)

When I run this, I'm getting the following:

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xc4 in position 647: invalid continuation byte
If I run this directly in SQL Developer, I get an output, with � in the file content. My SQL Developer settings are set to UTF-8, and my Oracle database NLS_NCHAR_CHARACTERSET is set to AL32UTF8. My understanding with the outputtypehandler change is that my python script would also output a � in this instance.

I added some print() commands inside of the def output_type_handler() to verify that it is indeed being called, and I saw output, so it appears that it is, but it also seems like the encoding_errors="replace" is being ignored. What am I missing here? Thanks!

[cjbj]

I'm going to leave the typehandler question to @anthony-tuininga to look at. A simple testcase for me works in cx_Oracle but not in python-oracledb.

But regarding the intent of the code snippet, what's the actual column type of file_content? Do you really need to return it as varchar - or was that an attempt to deal with the invalid data? If you simply query that column and get back a Lob or buffer (if the column is a RAW), can you then process it in Python?

[rh8056]

file_content is a CLOB field that stores mostly ASCII text files, but I have no control over the content of the file, so I made it CLOB and grab the file in Python like this:

plsqls content:

ins_archive_file = '''insert into archive_file (archive_id,file_name,file_type_cd,file_size
                        ,md5_value,file_content,active_ind,active_dt_tm,updt_dt_tm)
                    values(:ARCHIVE_ID,:FILE_NAME,get_value_by_display(:FILE_TYPE_DISP,28),:FILE_SIZE
                        ,:MD5_VALUE,:FILE_CONTENT,1,sysdate,sysdate)'''

                if file_type_meaning in ('TXT','CHECKSUM'):
                    file_content = open(current_file,'rb').read()
...
            mycursor.execute(plsqls.ins_archive_file
                , ARCHIVE_ID = archive_id
                , FILE_NAME = f
                , FILE_TYPE_DISP = file_type
                , FILE_SIZE = file_size
                , MD5_VALUE = md5checksum
                , FILE_CONTENT = file_content)

EDIT: Realized I was getting that error because I had a case statement around the file_content. If I remove it, then yes, I can select the content back as a LOB object. I'll dig into this and see if I can use that to do what I need next (which is to compare the database content with the actual file sitting on a server).

If I've gone about this all wrong, I'd appreciate any redirection!

[rh8056]

I'm guessing you're going to say the problem is that I'm reading binary data, and storing it as character data. I don't recall why that happened, but I think originally I planned to store it as character data, and ran into some encoding issue, then changed the way I was inserting it and didn't change my Oracle column type. I'm running through this now and have added a BLOB field instead of a CLOB field.

I think I'll need to redo a few APEX views now, but I assume this is the right approach anyway?

[cjbj]

Using BLOB is the best way.

[rh8056]

I added a new column to my Oracle table with a type of BLOB. I then made a new Python script that rescanned all 135k files in question to update the rows with the proper BLOB element in the database. With a few changes to my APEX app, I was good to go and now am in a much better place to do the comparisons to see if the files have changed:

                        if file_type_meaning in ('TXT','CHECKSUM'):
                            file_content = open(current_file,'rb').read()
                    mycursor.execute(plsqls.get_archive_file
                        , ARCHIVE_ID = archive_id
                        , FILE_NAME = f)
                    mycursor.rowfactory = makeDictFactory(mycursor)
                    row = mycursor.fetchone()
                    if mycursor.rowcount == 1:
                        file_id = row['FILE_ID']
                        if row['BLOB_FILE_CONTENT'] is not None and file_content != "":
                            if row['BLOB_FILE_CONTENT'] != file_content:

and my outputtypehandler is now:

def output_type_handler(cursor, metadata):
    if metadata.type_code is oracledb.DB_TYPE_VARCHAR:
        return cursor.var(metadata.type_code,
                          arraysize=cursor.arraysize,
                          encoding_errors="replace")
    if metadata.type_code is oracledb.DB_TYPE_BLOB:
        return cursor.var(oracledb.DB_TYPE_LONG_RAW, arraysize=cursor.arraysize)

Thanks!

[anthony-tuininga]

First, I can see that the encoding_errors support was missed when the migration to oracledb took place. I'll get that corrected. Second, you could have use the bypass_decode option which simply returns the data directly without decoding it. Finally, oracledb also has a way of fetching BLOB as bytes without a type handler. You can set oracledb.defaults.fetch_lobs to the value False which will have the same effect as your type handler.