Long DataType - Oracle datatype not presently supported
Moderators: chulett, rschirm, roy
Long DataType - Oracle datatype not presently supported
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.
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.
Clob
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.
a big VARCHAR
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?
Re: a big VARCHAR
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.1stpoint wrote:Since DS/OCI Stage does not support LongVarChar, I specified a Varchar with a length of 9,999,999.
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".
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
interesting
has anyone reported this as a bug?
It's a problem with both ORAOCI8 and ORAOCI9I plugins.
It's a problem with both ORAOCI8 and ORAOCI9I plugins.
Found this in Knowledge base
Apparently this was reported back in 1997. Why hasn't this been fixed on ORAOCI?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.
anyone?
Update
Ascential support has entered an enhancement request for the ORAOCI9 plugin to support long datatypes.
Re: Update
Does Ascential now support the LONG,CLOB,BLOB Character fields ??
If no then is there any work around available for this ...
Thanks
Ravi
If no then is there any work around available for this ...
Thanks
Ravi
1stpoint wrote:Ascential support has entered an enhancement request for the ORAOCI9 plugin to support long datatypes.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Example:
Declare the data type in DataStage as VarChar with precision 4000.
Code: Select all
SELECT CAST(CLOB_column AS VARCHAR2(4000)) FROM Table;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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 ?
ray.wurlod wrote:Example:Declare the data type in DataStage as VarChar with precision 4000.Code: Select all
SELECT CAST(CLOB_column AS VARCHAR2(4000)) FROM Table;
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.
raviyn wrote: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 ?
ray.wurlod wrote:Example:Declare the data type in DataStage as VarChar with precision 4000.Code: Select all
SELECT CAST(CLOB_column AS VARCHAR2(4000)) FROM Table;
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.