PickleType doesn't work in Oracle

[Former user]

Using the example in http://www.sqlalchemy.org/trac/wiki/New03Tricks (at bottom), with dburi oracle://me:my-pw@my-db, I get

  File "build\bdist.win32\egg\sqlalchemy\engine\default.py", line 126, in pre_exec
  File "build\bdist.win32\egg\sqlalchemy\engine\default.py", line 202, in _process_defaults
  File "build\bdist.win32\egg\sqlalchemy\sql.py", line 319, in __getitem__
  File "build\bdist.win32\egg\sqlalchemy\sql.py", line 823, in typeprocess
  File "C:\U\PYTHON24\lib\types.py", line 275, in convert_bind_param
  File "C:\U\PYTHON24\lib\types.py", line 250, in convert_bind_param

AttributeError: 'module' object has no attribute 'Binary'

This can be triggered on Linux and Win32.

[Mike Bayer]

binary has so far not been implemented in the oracle dialect, beacuse it deos not implement Binary in the way DBAPI specifies (i.e. the Binary type).

some searching revealed we have to make some adjustments to the Oracle dialect:

http://www.python.net/crew/atuining/cx_Oracle/README.txt

2) The constructor Binary()
   Not implemented. Simply pass a string where a binary value is required and
   use setinputsizes(value = cx_Oracle.BINARY) or setinputsizes(value =
   cx_Oracle.LONG_BINARY) to indicate that a binary value is being passed.

as a workaround for now, you might want to try:

   class OraclePickleType(PickleType):
        impl = String

not sure if that will work but it will bypass the usage of the binary type altogether.

[Mike Bayer]

hi -

well i did the best I could in changeset:2240. the author of cx_Oracle seems to say that he personally doesnt use binary types in oracle so its not very well supported (and for other DBAPI issues). So the OracleBinary type now "works", and the binary unit test, with some changes, passes for regular binaries and pickletypes. with two enormous caveats:

• max size is 4K, maybe less.
• when fetching rows with binary, you must read the column from the row before fetching the next row.

first item above i cant fix, the second item maybe I could eventually add some extra directives onto ResultProxy to "prefetch" data or something like that. will add a ticket.

[Mike Bayer]

also, to use binary with oracle you have to set "auto_setinputsizes=True":

    e = create_engine('oracle://...', auto_setinputsizes=True)

[Mike Bayer]

Removing milestone: 0.4.0 (automated comment)