Page 1 of 1
Trimming all VarChar Fields
Posted: Thu Mar 04, 2010 8:06 pm
by 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 .
Posted: Thu Mar 04, 2010 8:53 pm
by chowdhury99
You may try this:
If Trim(Column_Name) = '' Then '' Else Trim(Column_Name)
Posted: Thu Mar 04, 2010 9:00 pm
by chulett
You need to trim them where / why / of what? I
think that APT variable may only apply to sqlldr operations but not positive.
![Confused :?](./images/smilies/icon_confused.gif)
Posted: Thu Mar 04, 2010 9:16 pm
by 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 ?
Posted: Thu Mar 04, 2010 9:39 pm
by chulett
nagarjuna wrote: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.
Posted: Thu Mar 04, 2010 9:46 pm
by 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 .
Posted: Thu Mar 04, 2010 10:08 pm
by 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.