ORA-01461: if there is more than 1 VARCHAR2(2000) field
Moderators: chulett, rschirm, roy
ORA-01461: if there is more than 1 VARCHAR2(2000) field
Hi
I've a datastage job Loading directly from Oracle to Oracle.
Source DB: Oracle8i Enterprise Edition Release 8.1.7.4.0
Target DB: Oracle9i Enterprise Edition Release 9.2.0.1.0
Problem encountered:
The job fails with the following error if target table has more than 1 field with VARCHAR2(2000),
ORA-01461: can bind a LONG value only for insert into a LONG column
Tested with using ORAOCI8 and ORAOCI9, same ORA-01461 error.
Job finish successfully only if Target Table has no or 1 field with datatype VARCHAR2(2000)
Anyone encounter this problem before?
Thanks
layyee
No, can't say that I have. 
The target table doesn't have any fields defined as LONG, just large VARCHARs when you get this message?
I'd also be curious how you are doing your loading. What stages are you using? It might help if you describe how your job is laid out.
The target table doesn't have any fields defined as LONG, just large VARCHARs when you get this message?I'd also be curious how you are doing your loading. What stages are you using? It might help if you describe how your job is laid out.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: ORA-01461: if there is more than 1 VARCHAR2(2000) field
table defintion for both source and target tables are the same:
e..g
CREATE TABLE test1_ALL (
LINE_ID NUMBER NOT NULL,
HEADER_ID NUMBER NOT NULL,
SHIPPING_INSTRUCTIONS VARCHAR2(2000 ),
PACKING_INSTRUCTIONS VARCHAR2(2000 )
)
in DS job, the 2 fields are defined as
SQLType=VARCHAR
Length=2000
I've tried the following:
- 1. Oraoci8 stage -> oraoci8
2. Oraoci9 -> Oraoci9
3a. oraoci8 -> SeqFile (Job ok)
3b. SeqFile -> oracle8i (job Fail)
4. in DS Job, change one of the fields to Length=1000 and the job is ok.
Does it matter if the target database characterset is UTF8, using US7ASCII ?? Actually both database are UTF8, but it seems like DS having prob with the target database.
layyee wrote:
Hi
I've a datastage job Loading directly from Oracle to Oracle.
Source DB: Oracle8i Enterprise Edition Release 8.1.7.4.0
Target DB: Oracle9i Enterprise Edition Release 9.2.0.1.0
Problem encountered:
The job fails with the following error if target table has more than 1 field with VARCHAR2(2000),
ORA-01461: can bind a LONG value only for insert into a LONG column
Tested with using ORAOCI8 and ORAOCI9, same ORA-01461 error.
Job finish successfully only if Target Table has no or 1 field with datatype VARCHAR2(2000)
Anyone encounter this problem before?
Thanks
layyee
What Oracle client are you using with DataStage? I guess for a Windows server, the correct question would be - which Client is your default home?
Also, are you using a Transformer between the stages you are indicating? Best Practice says you should always use an Active stage (like the Transformer) to connect two Passive stages (like the OCI stages), even if it seems like it "does nothing". In spite of the fact that DataStage adds one "invisibily" behind the scenes if you don't, I've seen way too many odd things happen that go away with the simple addition of a Transformer. Even if all it does is perform a "pass through" of the fields.
If you have, then this starts to smell like a bug. I'd report it to Ascential Support and see what they have to say.
Also, are you using a Transformer between the stages you are indicating? Best Practice says you should always use an Active stage (like the Transformer) to connect two Passive stages (like the OCI stages), even if it seems like it "does nothing". In spite of the fact that DataStage adds one "invisibily" behind the scenes if you don't, I've seen way too many odd things happen that go away with the simple addition of a Transformer. Even if all it does is perform a "pass through" of the fields.
If you have, then this starts to smell like a bug. I'd report it to Ascential Support and see what they have to say.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
It also depends on NLS_LENGTH_SEMANTICS
It also depends on NLS_LENGTH_SEMANTICS - could be set to BYTE or CHAR.
If it is set to CHAR then Oracle create a column defined as
VARCHAR2(1 CHAR) or VARCHAR2(1) (which is then the same)
by default as VARCHAR2(3 BYTE).
When you create a column longer than 1333 CHARs then you could get problems without getting an in any time of occurence.
UTF8 is then sometimes a problem
kr
Manfred
If it is set to CHAR then Oracle create a column defined as
VARCHAR2(1 CHAR) or VARCHAR2(1) (which is then the same)
by default as VARCHAR2(3 BYTE).
When you create a column longer than 1333 CHARs then you could get problems without getting an in any time of occurence.
UTF8 is then sometimes a problem
kr
Manfred