DSXchange

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.

Hi,
I was curious to know what are your input datatypes in the source Oracle table.
As I understand you are writing from Oracle source to Oracle target through datastage job.

Datastage doesnot support Oracle datatype Long?? Is it correct??

Ketfos

ketfos wrote:Datastage doesnot support Oracle datatype Long?? Is it correct??

Not natively. They are accessed as large varchars, typically. Hence my questions about the specifics of what they are doing.

Hi,
Is it correct translation .
If the datatype is defined as Long in Oracle, define it as VarBinary while reading in datastage?

Ketfos

Hi,
Is it correct translation .
If the datatype is defined as Long in Oracle, define it as VarBinary while reading in datastage?

Ketfos

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.

Client version installed on DS-Server is 8.1.7.0.0

I've tried using a transformer between oracle stages. same prob.
and I've reported to Ascential.
They have reproduced the scenario, But it has been almost a week, and no further updates.

Well, at least they've reproduced it. Has it been assigned an eCase?

From what I recall, the 8.1.7.0 client was notoriously buggy. Any chance you can upgrade it to some flavor of 8.1.7.4?

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