Search code examples
sqlsql-serveroracle-databasevarcharclob

Query remote oracle CLOB data from MSSQL

I read different posts about this problem but it didn't help me with my problem. I am on a local db (Microsoft SQL Server) and query data on remote db (ORACLE). In this data, there is a CLOB type.

CLOB type column shows me only 7 correct data the others show me <null>

I tried to CAST(DEQ_COMMENTAIRE_REFUS_IMPORT AS VARCHAR(4000))

I tried to SUBSTRING(DEQ_COMMENTAIRE_REFUS_IMPORT, 4000, 1)

Can you help me, please ?

Thank you

Solution

  • No MSSQL but in my case we were pulling data into MariaDB using the ODBC Connect engine from Oracle.

    For CLOBs, we did the following (in outline):

    1. Create PLSQL function get_clob_chunk ( clobin CLOB, chunkno NUMBER) RETURN VARCHAR2.

    This will return the the specified nth chunk of 1000 chars for the CLOB.

    We found 1,000 worked best with multibyte data. If the data is all plain text single byte that chunks of 4,000 are safe.

    Apologies for the absence of actual code, as I'm a bit rushed for time.

    1. Create a Oracle VIEW which calls the get_clob_chunk function to split the CLOB into 1,000 char chunk columns chunk1, chunk2, ... chunkn, CAST as VARCHAR2(1000).

    We found that Oracle did not like having more than 16 such columns, so we had to split the views into sets of 16 such columns.

    What this means is that you must check what the maximum size of data in the CLOB is so you know how many chunks/views you need. To do this dynamically adds complexity, needless to say.

    1. Create a view in MariaDB querying the view.

    2. Create table/view in MariaDB that joins the chunks up into a single Text column.

    Note, in our case, we found that copying Text type columns between MariaDB databases using the ODBC Connect engine was also problematic, and required a similar splitting method.

    Frankly, I'd rather use Java/C# for this.