How to transfer CLOB text fields from Oracle Database to SQL Server without problems
Certifications:
If at some point we transfer many records with fields CLOB (large text) from Oracle Database to SQL Server with a database link that uses odbc driver there is a high probability of transferring the data incorrectly. More specifically we will see that the CLOB fields do not match the correct row they belong to but are mixed up between the records. In this article we will see a solution that solves the problem.
The solution
The solution to bypass this problem is to break the CLOB field when creating and transferring the table to SQL Server. in many VARCHAR fields of 4,000 characters depending on the maximum number of characters the field has. In the next step, let's add a field of type VARCHAR(MAX) which can hold up to 65,535 characters with the final name we want to bring the CLOB field. Finally to we update the new field by joining all the VARCHAR2 fields that we separated at the beginning.
The code
With the following code we transfer the table by splitting the CLOB field into VARCHAR fields of 4,000 characters each:
select * into DBNAME.SCHEMA1.CUSTOMERS from openquery (LNKNAME,'select CAST(DBMS_LOB.SUBSTR(TEXT, 4000,1) as VARCHAR2(4000)) as DATA_CLOB, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,4001) as VARCHAR2(4000)) as DATA_CLOB1, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,8001) as VARCHAR2(4000)) as DATA_CLOB2, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,12001) as VARCHAR2(4000)) as DATA_CLOB3, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,16001) as VARCHAR2(4000)) as DATA_CLOB4, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,20001) as VARCHAR2(4000)) as DATA_CLOB5, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,24001) as VARCHAR2(4000)) as DATA_CLOB6, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,28001) as VARCHAR2(4000)) as DATA_CLOB7, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,32001) as VARCHAR2(4000)) as DATA_CLOB8, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,36001) as VARCHAR2(4000)) as DATA_CLOB9, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,40001) as VARCHAR2(4000)) as DATA_CLOB10, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,44001) as VARCHAR2(4000)) as DATA_CLOB11, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,48001) as VARCHAR2(4000)) as DATA_CLOB12, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,52001) as VARCHAR2(4000)) as DATA_CLOB13, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,56001) as VARCHAR2(4000)) as DATA_CLOB14, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,60001) as VARCHAR2(4000)) as DATA_CLOB15, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,64001) as VARCHAR2(4000)) as DATA_CLOB16, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,68001) as VARCHAR2(4000)) as DATA_CLOB17, CAST(DBMS_LOB.SUBSTR(TEXT, 4000,72001) as VARCHAR2(4000)) as DATA_CLOB18, , ID, CUSTOMER from SCHEMA1.CUSTOMERS where 1=1');
With the following code we create in the table we transferred the CLOB field with the name it had as VARCHAR(MAX):
alter table DBNAME.SCHEMA1.CUSTOMERS add TEXT varchar(max);
Finally, with the following code, we update the VARCHAR(MAX) field by joining the VARCHAR fields that we broke during the transfer:
UPDATE DBNAME.SCHEMA1.CUSTOMERS SET TEXT = ISNULL(CAST(DATA_CLOB AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB1 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB2 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB3 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB4 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB5 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB6 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB7 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB8 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB9 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB10 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB11 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB12 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB13 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB14 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB15 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB16 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB17 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB18 AS VARCHAR(MAX)), '') + ISNULL(CAST(DATA_CLOB19 AS VARCHAR(MAX)), '');
Of course now if we want we can delete the columns we created for the temporary fragmentation of the CLOB field.
