Preserving blanks when using Oracle(LOAD Option)

ssunda6 · Post by **ssunda6** » Mon Jul 16, 2007 4:06 am

Hi All,

I went through the posts related to preserving blank spaces(adding env variables APT_ORACLE_PRESERVE_BLANKS) and tried them out. But still my job is getting aborted.

I have blanks in some of the character fields coming from input file and want to store them as blanks instead of NULL in oracle table.

When the above environment variable is not added, the job ran fine but the blanks were stored as null in the database.
When I set the APT_ORACLE_PRESERVE_BLANKS to True, the job aborted with the following error message(in sql loader log file):

Code: Select all

Record 1: Rejected - Error on table "APPS"."XXX", column POS_VOID_CD.
ORA-12899: value too large for column "APPS"."XXX"."POS_VOID_CD" (actual: 3, maximum: 1)

This column is defined as Char(1) in input file. The defintion in oracle table is VARCHAR2(1 CHAR).
When I import the metadata from table in the oracle stage, the column definition was imported as VARCHAR -UNICODE -LENGTH 3.

I do not understand, why the metadata imported to the stage is having length 3, though table definition in oracle is showing as Varchar2(1 char).

Thanks in advance.

Regards,
ssunda

chulett · Post by **chulett** » Mon Jul 16, 2007 6:06 am

So you have a metadata problem, not a 'preserve blanks' one, it seems. How did you import the metadata? What happens if you 'fix' it, set it to varchar(1)?

ssunda6 · Post by **ssunda6** » Mon Jul 16, 2007 11:16 pm

I have imported the metadata for table using
Load->Import->Plug-in Metadata Definitions

I tried fixing it, by changing the column definition from varchar(3) to varchar(1) manually in the oracle stage. It is still giving the same error-ORA-12899.

When I dont use the env variable (APT_ORACLE_PRESERVE_BLANKS), the job is running fine but the blanks are being stored as null. Then can it be the problem of metadata?

Regards,
ssunda

ArndW · Post by **ArndW** » Mon Jul 16, 2007 11:21 pm

ssunda - please note that VarChar2() cannot be used to store strings with trailing blanks or containing all blanks. I can't remember if leading blanks are trimmed out as well but I think they are.

ssunda6 · Post by **ssunda6** » Mon Jul 16, 2007 11:47 pm

Arnd,

In one of the sql loader documents, I found the following statement:

Code: Select all

Although VARCHAR fields also contain character data, these fields are never trimmed. A VARCHAR field includes all whitespace that is part of the field in the datafile.

That means, the blanks should have been inserted as blanks in the database but that is not happening.

Some columns are Char(1) in the input file. In the transformer they are changed to Varchar. Does this has anything to do with the error?

Regards,
ssunda.

ArndW · Post by **ArndW** » Tue Jul 17, 2007 12:42 am

Please note the difference between VARCHAR and VARCHAR2.

ssunda6 · Post by **ssunda6** » Tue Jul 17, 2007 4:01 am

I am able to insert a blank field(one space) manually into one of the Varchar2(1 char) field of the table.

Yuan_Edward · Post by **Yuan_Edward** » Tue Jul 17, 2007 5:46 pm

Can you post the sqlldr control and log files? It would be helpful for us to identify the issue.

ssunda6 wrote:I am able to insert a blank field(one space) manually into one of the Varchar2(1 char) field of the table.

ArndW · Post by **ArndW** » Tue Jul 17, 2007 8:50 pm

ssunda6 - and what length is it when you read it again?