[cx-oracle-users] cx_Oracle 5.1, LONG columns and unicode

[Walter D. <wa...@li...>]

Hello all!

I'm currently trying to port ll.orasql 
(http://www.livinglogic.de/Python/orasql/) to cx_Oracle 5.1. Previously 
(i.e. with cx_Oracle 5.0.4) ll.orasql was using cx_Oracle in UNICODE mode.

I'm having trouble getting proper unicode data out of LONG columns. I'm 
using the outputtypehandler Anthony demonstrated in

 
http://sourceforge.net/mailarchive/forum.php?thread_name=ijo3ea%246am%241%40dough.gmane.org&forum_name=cx-oracle-users

like this:

import cx_Oracle

def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
    if defaultType in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR):
       return cursor.var(unicode, size, cursor.arraysize)

db = cx_Oracle.Connection("user/password@...")
db.outputtypehandler = OutputTypeHandler

c = db.cursor()

src = u"create or replace view evil as {}".format(
    u" union all ".join(
       u"select '{}' as a from dual".format(u"\u3042"*250) for i in 
xrange(250)
    )
)

c.execute(src)

c.execute("select text from user_views where view_name='EVIL'")

d = c.fetchone()[0]

if isinstance(d, str):
    print type(d), len(d), len(d.decode("utf-8"))
else:
    print type(d), len(d.encode("utf-8")), len(d)

This gives me the output

    <type 'str'> 196239 71239

i.e. the data I get is an 8bit string. When I change OutputTypeHandler 
to this:

def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
    if defaultType in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR, 
cx_Oracle.LONG_STRING):
       return cursor.var(unicode, size, cursor.arraysize)

I get

    <type 'unicode'> 65535 23787

i.e. I get unicode output, but the data seems to be truncated to 64K of 
encoded bytes.

(BTW, the database encoding is utf-8, i.e.

   select value from nls_database_parameters
   where parameter='NLS_CHARACTERSET';

returns AL32UTF8)

What am I doing wrong?

Servus,
    Walter