char columns possing threat

[Ragunathan Gunasekaran]

Hi ,
I am having source as a mainframe file containing char columns and staging the mainframe file into the oracle table without any transformations.
during the next load , i am doing a change capture (explicit keys and all values)between the file and the staging table and loading only new and changed records with an date column.

The problem i am facing is as follows

All the char cols values short of width are getting padded with oX0 during the first run.While run second time for the same set of data in table and file all my records are moving as update records due to the padding happened in the first run.

Any clues of how to avoid these padded characters from table during the Change capture ? I used an user defined SQL to trim and read but thats converting all the trimmed cols as nullable and throwing warning as i have the column metadata as not null....
I should not get warnings ... any clues on this please

[ray.wurlod]

Use the APT_STRING_PADCHAR environment variable to set the pad character to space. Its default value is 0x00.

[Ragunathan Gunasekaran]

Hi,
The addition of a space again at the end of the values would still treat them as a update records as the file will not have any spaces to the value column...I would like to read these records from the oracle table itself without any spaces. as i have about two hundred columns to trim and check. Further if i use an user defined SQL to trim these spaces after using the APT_STRING_PADCHAR to space ,the metadat of all the columns are treated as nullable ....All these columns are non nullable one .. Hence i am getting warnings in the director... Any clues of how to solve this ....

[chulett]

You can't trim spaces from a CHAR. You need to fix the problem the first run caused, update those fields in the Oracle table to get rid of the pad character you used the first time and get the spaces back that it expects to be there.

If you trim them from the existing data, the spaces will come. Automagically. Then you can quite trimming and futzing with the incoming data and just let it match up properly.