Use of LONG data types

dataservices job design
1

I am designing a job in DI that needs to take data from a LONG datatype column in the source schema and map it to a to a LONG datatype column in the target schema. I have realised that I cannot do this as I am using a database function in the query transform that tries to do something with the LONG datatype, and LONG datatypes cannot be used in Oracle PL/SQL functions. So in order to get round this, I tried changed the function to use VARCHAR2 instead of LONG. I have estimated though that I would need DI to handle a VARCHAR2(8000) datatype as I am trying to concatenate two VARCHAR2(4000) types in the query transform and then ultimately put this in a LONG type column in the target schema. But DI has problems handling anything over VARCHAR2(4000), so this is a problem. DI also does not handle LOBs, which would be a way round the function problem I have with LONGs. Is there anything I can do in DI to solve this problem?

dataintegrator.net (BOB member since 2006-01-12)

2

Hi Mark,

Using two varchar(4000) columns and concatenating them into a varchar(8000) field seems to work with DI/SQL Server. Maybe try editing the Metadata of the table (double click on the table in your datastore, right click on the column) and change the type of the field (replace your varchar2(8000) by a varchar2(4000))… You might be able to fool the DI engine that way.

By the way, there is a section on the Technical Manual dedicated to issues/limitation in support of Long data types…

Sorry if this is not really helpful.

Nicolas

dataintegrator.net (BOB member since 2006-01-12)

3

Supposedly, support for CLOB > 8K as well as BLOBs are coming in the next major version. Hopefully this will make it in. The 8K CLOB limit (with SQL server at least) is hurting us.

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