how to insert records into long columns like varchar(4000)

[ramarao kavuri]

I am trying to load into my target column varchar(4000) using oracle ee stage but..it give fatal error
Fatal error
ORC_D_DMA_DPGC_PIANI_GEST_CAPITOLI,0: Array execute failed for insert:
insert is: INSERT INTO TES1_U_NSC_RAG2_DMA_COL.D_DMA_DPGC_PIANI_GEST_CAPITOLI (SEQU_DPGC_ID, DESC_DPGC_DESC_CORR_INT, DESC_DPGC_DESC_CORR_RID, DESC_DPGC_DESC_ASS_INT, DESC_DPGC_DESC_ASS_RID) VALUES ( :SEQU_DPGC_ID, :DESC_DPGC_DESC_CORR_INT, :DESC_DPGC_DESC_CORR_RID, :DESC_DPGC_DESC_ASS_INT, :DESC_DPGC_DESC_ASS_RID)
sqlcode is: -1461
esql complaint: ORA-01461: can bind a LONG value only for insert into a LONG column


can somebody clarify on this...its urgent

[ArndW]

I have been fighting with the same error message for while, so I'll respond even though you said it was "urgent" (nothing here is urgent to anyone but the person posting the problem, it is an all-volunteer site).

First off, did you explicitly declare the columns are VarChar(4000 CHAR), the default is bytes and an NLS string with multibyte characters will thus go over the magic 4000 character limit and the string will be interpreted as a long.

What are your Oracle NLS settings? If you trim the string to 4000 in DataStage (using a multibyte format) then have a single-byte NLS setting for Oracle, strings will get translated going into Oracle and potentially expanded past 4000 characters and once again the Oracle stage will complain.

[chulett]

Are you actually targeting an Oracle LONG data type, or is it a CLOB or just a large VARCHAR2?

[ArndW]

This would be a runtime error going to a VarChar column and it only occurs when the incoming VarChar data exceeds the 4000 limit; the error is raised during parsing of the insert statement. Even doing a Substr(bigString,1,4000) or a substrb(bigString,1,4000) doesn't help, either.

[ramarao kavuri]

filed coming from source varchar(4000) & load into target of varchar(4000)

[ArndW]

And what about the other questions? Particularly whether you did a VarChar2(4000 CHAR) or a VarChar2(4000) {which implies VarChar2(4000 BYTE)}

[chulett]

I guarantee you it was created in BYTES.

Perhaps simply changing the datatype in job to LongVarchar would help?

[ArndW]

No, I tried that as well. It is the SQL command which takes the > 4000 character long string and thinks of it as a long and then gives the BIND error message. I finally ended up having to use a BASIC stage with BYTELEN() to make sure that I only sent appropriately sized strings to the Oracle stage. Our issue is that the NLS settings at UNIX were 8859 as well, so that expansion from multibyte to singlebyte was what was causing the error.

[keshav0307]

oracle EE stage doesnot support LONG.
use Dynamic RDBMS stage .