SQL Server LONG into Oracle CLOB

dataservices job design
1

I need to extract a SQL Server table with with a LONG column into a Oracle table, converting the LONG to a CLOB. This will then allow me to manipulate the column using a PL/SQL script.

I can’t use long_to_varchar because I don’t want to lose the data after 4000 characters.

The problem is the LONG source column contains carriage returns. I need to temporarily remove these during the extract but I can’t because DI won’t allow you to place functions on LONG columns.

I’ve tried extracting into a flat file and straight into an Oracle database table.

Thanks,
George

George :cyprus: (BOB member since 2003-06-27)

2

What does actually happen in case you just read the data from SQL Server as a LONG datatype and load the data into the Oracle CLOB as LONG? No conversion, nothing…

Werner Daehn :de: (BOB member since 2004-12-17)

3

As DI does not support CLOBs I can’t do this. DI can’t see the CLOB column at all.

What I’m doing, therefore, is an extract to a flat file. and then a second extract from that text file to Oracle.

I’m having a few problems with some of the entries in the free text LONG field though.

George :cyprus: (BOB member since 2003-06-27)

4

Does Oracle have a long-text datatype that is not a binary blob? I can’t recall…

dnewton :us: (BOB member since 2004-01-30)

5

Isn’t this a CLOB, character large object?

George :cyprus: (BOB member since 2003-06-27)

6

I managed to get round this by doing a simple extract from SQL Server to Oracle with no bulk load, leaving the LONG column as a LONG. The problem I had was that the rows per commit was set to 1 because of the LONG column, but increasing the number of loaders (to 10!) allowed the data to load in a reasonable amount of time.

Stage 2 was to copy the data into another table with the LONG converted to a CLOB (using to_lob) in a DI script.

Stage 3 was an Oracle stored procedure which did the necessary processing on the CLOB column.

George :cyprus: (BOB member since 2003-06-27)