Use cx_oracle.LONG_STRING /LONG_BINARY for CLOB/BLOB

Changed the implementation of fetching CLOB and BLOB objects to use
cx_Oracle's native implementation which fetches CLOB/BLOB objects inline
with other result columns, rather than performing a separate fetch. As
always, this can be disabled by setting auto_convert_lobs to False.

As part of this change, the behavior of a CLOB that was given a blank
string on INSERT now returns None on SELECT, which is now consistent with
that of VARCHAR on Oracle.

Fixes: #5314
Change-Id: I7b46c91704b6f5d6c157e083505dac6e0cb3ef6e

2 files changed, 27 insertions, 8 deletions

diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
index 4ff0f65ed..c61a1cc0a 100644
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
@@ -1003,17 +1003,16 @@ class OracleDialect_cx_oracle(OracleDialect):
                         dialect.encoding, errors=dialect.encoding_errors
                     )
                     return cursor.var(
-                        default_type,
+                        cx_Oracle.LONG_STRING,
                         size,
                         cursor.arraysize,
-                        outconverter=lambda value: outconverter(value.read()),
+                        outconverter=outconverter,
                     )
                 else:
                     return cursor.var(
-                        default_type,
+                        cx_Oracle.LONG_STRING,
                         size,
                         cursor.arraysize,
-                        outconverter=lambda value: value.read(),
                         **dialect._cursor_var_unicode_kwargs
                     )
 
@@ -1021,10 +1020,7 @@ class OracleDialect_cx_oracle(OracleDialect):
                 cx_Oracle.BLOB,
             ):
                 return cursor.var(
-                    default_type,
-                    size,
-                    cursor.arraysize,
-                    outconverter=lambda value: value.read(),
+                    cx_Oracle.LONG_BINARY, size, cursor.arraysize,
                 )
 
         return output_type_handler
diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py
index 7719a3b3c..00b5fab27 100644
--- a/lib/sqlalchemy/testing/suite/test_types.py
+++ b/lib/sqlalchemy/testing/suite/test_types.py
@@ -136,6 +136,15 @@ class _UnicodeFixture(_LiteralRoundTripFixture, fixtures.TestBase):
         for row in rows:
             assert isinstance(row[0], util.text_type)
 
+    def _test_null_strings(self, connection):
+        unicode_table = self.tables.unicode_table
+
+        connection.execute(unicode_table.insert(), {"unicode_data": None})
+        row = connection.execute(
+            select([unicode_table.c.unicode_data])
+        ).first()
+        eq_(row, (None,))
+
     def _test_empty_strings(self, connection):
         unicode_table = self.tables.unicode_table
 
@@ -164,6 +173,9 @@ class UnicodeVarcharTest(_UnicodeFixture, fixtures.TablesTest):
     def test_empty_strings_varchar(self, connection):
         self._test_empty_strings(connection)
 
+    def test_null_strings_varchar(self, connection):
+        self._test_null_strings(connection)
+
 
 class UnicodeTextTest(_UnicodeFixture, fixtures.TablesTest):
     __requires__ = "unicode_data", "text_type"
@@ -175,6 +187,9 @@ class UnicodeTextTest(_UnicodeFixture, fixtures.TablesTest):
     def test_empty_strings_text(self, connection):
         self._test_empty_strings(connection)
 
+    def test_null_strings_text(self, connection):
+        self._test_null_strings(connection)
+
 
 class TextTest(_LiteralRoundTripFixture, fixtures.TablesTest):
     __requires__ = ("text_type",)
@@ -202,6 +217,7 @@ class TextTest(_LiteralRoundTripFixture, fixtures.TablesTest):
         row = connection.execute(select([text_table.c.text_data])).first()
         eq_(row, ("some text",))
 
+    @testing.requires.empty_strings_text
     def test_text_empty_strings(self, connection):
         text_table = self.tables.text_table
 
@@ -209,6 +225,13 @@ class TextTest(_LiteralRoundTripFixture, fixtures.TablesTest):
         row = connection.execute(select([text_table.c.text_data])).first()
         eq_(row, ("",))
 
+    def test_text_null_strings(self, connection):
+        text_table = self.tables.text_table
+
+        connection.execute(text_table.insert(), {"text_data": None})
+        row = connection.execute(select([text_table.c.text_data])).first()
+        eq_(row, (None,))
+
     def test_literal(self):
         self._literal_round_trip(Text, ["some text"], ["some text"])