Trimming all VarChar Fields

[nagarjuna]

Hi Everyone ,

I have a requirement to trim all varchar fields . But this requirement came after completion of jobs . I thought of setting the environment variable APT_ORACLE_PRESERVE_BLANKS = FALSE and acheive the requirement .But , there are some not nullable fields . The job will abort if a space is coming that field because it will be trimmed before loading and error message will be "inserting null into non-nullable field " .Is there any way to achieve this other than changing all the jobs ?

Thanks a lot .

[chowdhury99]

You may try this:
If Trim(Column_Name) = '' Then '' Else Trim(Column_Name)

[chulett]

You need to trim them where / why / of what? I think that APT variable may only apply to sqlldr operations but not positive.

[nagarjuna]

Thanks craig for your response ....

I have to trim trailing , leading spaces of all varchar fields . When those records loaded into database all varchar fields shouldnt have spaces .

I am wondering if this can be acheived without changing the existing jobs .

Type of load may be direct or conventional . So , you mean to say that in conventional load this APT_ORACLE_PRESERVE_CHAR wont work ?

[chulett]

I have to trim trailing, leading spaces of all varchar fields. I am wondering if this can be acheived without changing the existing jobs.

Trailing and leading? No, adding leading to the equation eliminates the possibility of any kind of automatic trimming.

Double-checked and that "PRESERVE BLANKS" option only applies to sqlldr. So while a true 'load' option could leverage it, normal insert/update DML would not. And that's still just about trailing spaces.

[nagarjuna]

Thats interesting ...I dont that APT_ORACLE_PRESERVE_SPACE wont work for conventional load . Thanks a lot craig for the valuable info.

If I am setting that env variable to false and I am receiving a space in a not nullable field then in direct load job will abort . Could anyone confirm if my understanding is correct ? Thanks .

[chulett]

Just to clarify, a 'conventional load' is still sqlldr, just with DIRECT=FALSE. It would still apply there. I meant it wouldn't apply to conventional sql, plain old DML done outside of sqlldr.