how to insert records into long columns like varchar(4000)
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 2
- Joined: Fri May 23, 2008 3:07 am
- Location: hyderabad
how to insert records into long columns like varchar(4000)
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
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
RAMS KAVURI
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.
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.
-
- Participant
- Posts: 2
- Joined: Fri May 23, 2008 3:07 am
- Location: hyderabad
filed coming from source varche(4000) & load in
filed coming from source varchar(4000) & load into target of varchar(4000)
RAMS KAVURI
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.
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia