Long DataType - Oracle datatype not presently supported

[chinek]

Hi,

We built a new job to access an Oracle 7 database table that has a column with the LONG datatype. From DS Manager I used the Plugin to Import the metadata and the metadata suggest a LONGVARCHAR.
So I used this in the job. The metadata for that column does not provide a Length. So I gave it a value of 4200.
When I ran the job , it aborted with an Oracle error "Oracle datatype not presently supported" ?

What should I used for a LONG datatype in a DS Job ? Please advise.

Thanks.

[chulett]

You have to retrieve it using a VARCHAR data type. A big VARCHAR.

[chinek]

Thanks for your quick reply and help!!

If that is the case then DS Manager is "wrong" in picking LongVarChar
as the datatype when one imports the metadata.
Is this fixed in v7 ?

[chulett]

Nope. I'm running 7.0.1 and it still works like that.

[1stpoint]

I recently had issues with the Long datatype as well. We ended up using CLOB instead because there are alot of restrictions with Long in oracle.

[1stpoint]

Since DS/OCI Stage does not support LongVarChar, I specified a Varchar with a length of 9,999,999. Will the CLOB be truncated if the length is longer? Is it possible to specify an <unlimited> size?

[chulett]

Since DS/OCI Stage does not support LongVarChar, I specified a Varchar with a length of 9,999,999.

Have you tried running a job with these settings and does Oracle mind? For 9i, max varchar2 length is 4K from what I recall and there isn't any way to declare it as unlimited in size in DataStage.

And yes, if the actual size exceeds what you have configured it to be it will be truncated - and you'll get a lovely little message to that effect: "OCI has fetched truncated data".

[1stpoint]

has anyone reported this as a bug?
It's a problem with both ORAOCI8 and ORAOCI9I plugins.

[1stpoint]

Topic:
Oracle LONG DataTypes not supported in DataStage OCI plug-in.

Product: DataStage
Release: 2.2
Date Entered: 10/31/1997 Fixed in Release:
Date Closed:

Full Description:
Oracle LONG DataTypes not supported in DataStage
OCI plug-in stage.

Resolution:
None given.

Workaound Provided:
None given.

Apparently this was reported back in 1997. Why hasn't this been fixed on ORAOCI?
anyone?

[1stpoint]

Ascential support has entered an enhancement request for the ORAOCI9 plugin to support long datatypes.

[raviyn]

Does Ascential now support the LONG,CLOB,BLOB Character fields ??

If no then is there any work around available for this ...

Thanks
Ravi

Ascential support has entered an enhancement request for the ORAOCI9 plugin to support long datatypes.

[ray.wurlod]

Example:

Code: Select all

SELECT CAST(CLOB_column AS VARCHAR2(4000)) FROM Table;

Declare the data type in DataStage as VarChar with precision 4000.

[raviyn]

I tried using the CAST operator on the Long Data Type but it is giving inconsistent data type error . So should I be first converting the LONG to a CLOB object and then use the CAST command in Oracle to acheive the results ?

Example:

Code: Select all

SELECT CAST(CLOB_column AS VARCHAR2(4000)) FROM Table;

Declare the data type in DataStage as VarChar with precision 4000.

[raviyn]

I found a way out while pulling this data from the source i have given the datatype at the source as VarBinary 100000 and extracting the data and Loading the data as Longvarchar Field . The job ran without any warnings and the data looks good.

I tried using the CAST operator on the Long Data Type but it is giving inconsistent data type error . So should I be first converting the LONG to a CLOB object and then use the CAST command in Oracle to acheive the results ?

Example:

Code: Select all

SELECT CAST(CLOB_column AS VARCHAR2(4000)) FROM Table;

Declare the data type in DataStage as VarChar with precision 4000.

[ray.wurlod]

Yes, mine was only an example, showing how you would do it with a CLOB. Trying to CAST a LONG as VARCHAR is not appropriate, so it's good to see that you worked out that a large enough VARBINARY was an appropriate cast.