inserting CLOB to database selected from another

Question

I have two unrelated oracle 11g database instances. I need to select a value of type CLOB from the one database and insert it to the other with python using cx_Oracle.

When I'm trying this into the one direction it ends up with:

Traceback (most recent call last):
  File "<input>", line 1, in <module>
cx_Oracle.DatabaseError: ORA-00942: table or view does not exist

Oddly when trying it in the other direction it ends up with:

Traceback (most recent call last):
  File "<input>", line 1, in <module>
cx_Oracle.DatabaseError: ORA-00600: internal error code, arguments: [kglslod:subheap], [8], [0x14D9EA270], [], [], [], [], [], [], [], [], []

Selecting and inserting a CLOB value within the same database is no problem.

I found out that I could fix it with a custom output handler:

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

and setting

db_connection.outputtypehandler = OutputTypeHandler

So I wonder if this behavior is by design or what else leads to this?

---

Here some data for testing:

Create table and insert a row in database1

create table test_table_1
(
    column_1 clob
);

insert into test_table_1 (column_1) values ('test_data_1');

Create table and insert a row in database2

create table test_table_2
(
    column_2 clob
);

insert into test_table_2 (column_2) values ('test_data_2');

Python Script that failed for fetching one result from database1 and insert it in database2

import cx_Oracle


db_1 = cx_Oracle.connect(<user>, <passwd>, <host_db1>)
db_2 = cx_Oracle.connect(<user>, <passwd>, <host_db2>)

cursor_1 = db_1.cursor()
cursor_2 = db_2.cursor()

result = cursor_1.execute("select column_1 from test_table_1")
result1 = result.fetchone()

cursor_2.execute("insert into test_table_2(column_2) values (:val)", val=result1[0])

Result

Traceback (most recent call last):
  File "<input>", line 1, in <module>
cx_Oracle.DatabaseError: ORA-00600: Interner Fehlercode, Argumente: [kglslod:subheap], [8], [0x14DF5A0B0], [], [], [], [], [], [], [], [], []

Result when selecting from database2 and inserting in database1

Traceback (most recent call last):
  File "<input>", line 8, in <module>
cx_Oracle.DatabaseError: ORA-00942: table or view does not exist

Python script that works

import cx_Oracle

def OutputTypeHandler(cursor: cx_Oracle.Cursor, name, defaultType, size, precision, scale):
    """
    Convertes CLOB type to string on reading.
    """
    if defaultType == cx_Oracle.CLOB:
        return cursor.var(cx_Oracle.LONG_STRING, arraysize=cursor.arraysize)

db_1 = cx_Oracle.connect(<user>, <passwd>, <host_db1>)
db_1.outputtypehandler = OutputTypeHandler
db_2 = cx_Oracle.connect(<user>, <passwd>, <host_db2>)

cursor_1 = db_1.cursor()
cursor_2 = db_2.cursor()

result = cursor_1.execute("select column_1 from test_table_1")
result1 = result.fetchone()

cursor_2.execute("insert into test_table_2(column_2) values (:val)", val=result1[0])

db_2.commit()

Versions

• cx-Oracle 7.2.0
• oracle_instant_client 18.5 (Linux)
• python 3.6.8

Answer 1

You cannot simply pass a LOB value from one connection into another one. They are specific to a particular connection. I'll look into providing a better error message for that situation

As you noted in your own question, the first solution is to use STRING instead of CLOB by using an output type handler. That technique works with CLOB values up to 1 GB in size, but the downside is that the entire CLOB value must reside in contiguous memory.

If you need to stream the values due to very long lengths, you'll need to use something like this instead:

cursor_1 = db_1.cursor()
cursor_2 = db_2.cursor()

cursor_1.execute("select column_1 from test_table_1")
sourceLob, = cursor_1.fetchone()

lobVar = cursor_2.var(cx_Oracle.CLOB)
cursor_2.execute("insert into test_table_2 (column_2) values (empty_clob()) returning column_2 into :1", [lobVar])
destLob = lobVar.getvalue()

offset = 1
numBytesInChunk = 65536
while True:
    data = sourceLob.read(offset, numBytesInChunk)
    if data:
        destLob.write(data, offset)
    if len(data) < numBytesInChunk:
        break
    offset += len(data)
db_2.commit()