CmpdOdsDataLoader..CInsOrUpdStage: ORA-01461: can bind a LONG value only for insert into a LONG column
I keep receiving the following error through datastage. I'm not sure if it is DS problem or an Oracle problem. The source table has a field that is VARCHAR2(4000) and I have set up a destination table with VARCHAR2(4000). However, when I move the data across I receive this error. The only way it works is by setting the column size in DS to 254.
Now the weird part. A former colleague has a duplicate job moving VARCHAR2(4000) field across to a table with VARCHAR2(4000). He has the DS transform stage setup as VARCHAR2(4000)...AND IT WORKS!!! He is no longer here so I can't ask him what he did. Anyone have any ideas?
Is your metadata imported exactly from the source and target tables, respectively? No manual twiddling going on there? Are you comparing apples to apples, meaning the working instance is the same Oracle version and client?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
kcbland wrote:Is your metadata imported exactly from the source and target tables, respectively? No manual twiddling going on there? Are you comparing apples to apples, meaning the working instance is the same Oracle version and client?
Yes, we load oracle to oracle...source is the same version as the destination. The only twiddling is a NVL statment in the transform to set null to 'UNKNOWN'.
your error msg means that Oracle is detecting a LONG datatype string when you insert the value into your target field. this is not allowed in Oracle.
VARCHAR2 datatype can accept 4000 bytes max while LONG datatype can accept up to 2GB.
do you concatenate anything to your string input so that it exceeds the 4000-byte limit? perhaps, you can also try a simple job to do a direct move from source to target - if you still hit the error in a simple direct job design, then you need to raise this issue to tech support.
I have a simple job that moves it from the source to the destination that works fine (varchar2(4000). I know for a fact that the column does not go over that and that nothing is being concatenated on the end.
If I set the field on my job (not the simple one) to VARCHAR2(255) it will error out with the same. If I set it to VARCHAR(254) it works!
Well,
DS 7 is supposed to support CLOB datatype in Oracle (Up to 2GB). It does it by using a datatype (in DS) of LongVarChar. It does work up to 256,000 bytes (that's what I checked). I don't have DS 5 installed so I don't know if ot exists and working but it's worth checking.
Even though i don't get the above error i have a problem with Varchar(4000).
We use Oracle 8 with DS 7.5 version.
I am trying to load from source to target with out any transformations. Just a plain mapping from source oracle to target oracle using OCI stages. One of the fields is Short_Description Varchar(4000). The job runs fine with out any error. But the data loaded in the Short_Description column is incorrect. In the sense the data is truncated i can not view the data (using Toad). The columns shows a blank in Toad even though data is loaded (which i am very much sure because i can see data from datastage by right clicking on the target). Both source and target columns are Varchar(4000).
Then i changed the target to Varchar(3999). This time it worked fine. Any value below 4000 the job runs fine and the data is loaded accurately and i can view it from Toad. But it is not the same case when the size is 4000.
Anyone can explain me why this behavior in my job?
the job used to work fine earlier when the target column is given as varchar(4000). But at that time the DS version is 7.1
Last week we upgraded datastage to 7.5 and i found this problem. Is it something to do with upgrade of datastage to 7.5 or is it some problem with the data i am getting from the source?
