data conversion error was encountered in Oracle bulk load

[ureddy]

I am encountering the below error in the load job(Dataset->Transformer->Oracle). Prior there is a extract job (CFF->Transfomer->Dataset).
Actually error is occured , due to data issue , as its pulled from Mainframes , seems like there are low values (special charactes).

Db_ODS_Acct_summary,8: A data conversion error was encountered in bulk load mode for row 1, column 3. (CC_OraLoad::loadData, file CC_OraLoad.cpp, line 1,618)

Please , let me know , how to successfully use Bulk load

[SURA]

Db_ODS_Acct_summary,8: A data conversion error was encountered in bulk load mode for row 1, column 3. (CC_OraLoad::loadData, file CC_OraLoad.cpp, line 1,618)

So you are aware it is due to data issue!! The log says column 3. you know what is the data type in source and target.

Why cant start to find out ?

Try something and come to us with what you have tried so far!

[ureddy]

In Source mainframe the column is Char & the same in the target Oracle. In fact I have tried to resolve by using the CHAR () , but could not succeed .
Actually , the baseline is there are some junk characters/special characters in data , which is causing the issue. I have changed few NLS settings also , but in vain. Are there any functions to handles these data issues which arise while pulling data from mainframe.

[ureddy]

one more point to add to give clarity, using the Char() function, i am able to load into oracle , but there is no data for that particular column in Table.

If IsNull(colname) Or colname = Space(80) Then Space(80) Else Char(colname)

Please , let me know the right approach to deal with this conversion error & load successfully into Oracle using BULK LOAD mode.

One more test , I have done is my default "ABC" to above column & its successfully able to load using BULK load mode.

[SURA]

Actually , the baseline is there are some junk characters/special characters in data , which is causing the issue.

So you need to know what all are those charters.

Why can't you try to find more about APT_SRTING_PADCHAR

[ureddy]

I have included this environmental variable , let me know , if there is any ideal solution for this problem.

Its basically the low values/junk /special characters which are causing the problem , is there any of handling those, so that I can load using bulk load option into Oracle

[SURA]

You marked it as a workaround.

[ureddy]

By mistake , I have marked , let me know , if there is a solution for this problem.

[chulett]

The CHAR() function would not be appropriate here. It "generates an ASCII character from its numeric code value". For example, Char(65) would generate an "A".

You haven't been very specific about the nature of your problem - where are these "junk" characters being generated? Are they coming to you from the source system? Is DataStage introducing them at a particular step? Since we're talking about the CHAR data type, I'm assuming the latter and if that is the case then the proper use of $APT_STRING_PADCHAR is the solution. By default DataStage uses an ASCII null (low values) to pad a short CHAR string but you can override that with the variable noted. Typical solution would be to specify "0x20" so the fields are space padded.

And this would be a general loading issue, not something specific to a "bulk" load.