Return text from Oracle blob column from Oracle database using python

Question

I have this code to take the text from a blob column of my database:

import cx_Oracle
ip = 'your host'
port = 1521
SID = 'ORCL'
USER = 'user'
PASSWORD = 'password'
dsn_tns = cx_Oracle.makedsn(ip, port, SID)


dsn = cx_Oracle.makedsn(ip, port, SID)
orcl = cx_Oracle.connect(USER + '/' + PASSWORD + '@' + dsn)
curs = orcl.cursor()
sql = """SELECT blob_column from table"""
curs.execute(sql)
rows = curs.fetchall()


for x in rows:
   list_ = list(x)
   print(x[0].read)

But when i print with the for, i got this result:

<built-in method read of cx_Oracle.LOB object at 0x0547EAE8>
<built-in method read of cx_Oracle.LOB object at 0x0547EAD0>
<built-in method read of cx_Oracle.LOB object at 0x0711D770>

How can i return the text from my blob column?

Answer 1

For LOBS that fit in memory, you'll probably find it a lot faster to read them as strings or bytes, see https://cx-oracle.readthedocs.io/en/latest/user_guide/lob_data.html

def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
    if defaultType == cx_Oracle.CLOB:
        return cursor.var(cx_Oracle.LONG_STRING, arraysize=cursor.arraysize)
    if defaultType == cx_Oracle.BLOB:
        return cursor.var(cx_Oracle.LONG_BINARY, arraysize=cursor.arraysize)

idVal = 1
textData = "The quick brown fox jumps over the lazy dog"
bytesData = b"Some binary data"
cursor.execute("insert into lob_tbl (id, c, b) values (:1, :2, :3)",
        [idVal, textData, bytesData])

connection.outputtypehandler = OutputTypeHandler
cursor.execute("select c, b from lob_tbl where id = :1", [idVal])
clobData, blobData = cursor.fetchone()
print("CLOB length:", len(clobData))
print("CLOB data:", clobData)
print("BLOB length:", len(blobData))
print("BLOB data:", blobData)

Answer 2

Got it!

wkt = rows[0][0].read() # This works for me!
print(wkt.decode("utf-16")) #And my text back with utf-16 so i had to decode
orcl.close()