question

BI DWH BALA avatar image
BI DWH BALA asked

Formatting error in Extrenal tables

Hi

I defined an external table with 2 columns. One column is Error_cd varchar2 (100) and another as Error_Desc varchar2(500).

The definition is as follows:

CREATE TABLE my_ext_tab ( error_cd
VARCHAR2(100), error_desc
VARCHAR2(500) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY my_dir ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE BADFILE my_bad_dir:'%a_%p.bad' LOGFILE my_log_dir:'%a_%p.log' FIELDS TERMINATED BY '~' MISSING FIELD VALUES ARE NULL ) LOCATION ('my_file.txt') ) REJECT LIMIT UNLIMITED /

While i am querying external tables, some records are rejected and placed in bad file. When I verify the log, it's showing the following error messages.

KUP-04021: field formatting error for field ERROR_DESC KUP-04026: field too long for datatype KUP-04101: record 14 rejected in file my_file.txt

Then I verified the length of Error_Desc field. But its lenght is 252. I am surprized why oracle throughs an error. Is there any charcterset problem?

Any ideas please.?

oracle
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
BI DWH BALA avatar image
BI DWH BALA answered

Hi,

I found the solution for this.

I updated my definition as follows:

CREATE TABLE my_ext_tab ( error_cd VARCHAR2(100), error_desc VARCHAR2(500) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY my_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE BADFILE my_bad_dir:'%a_%p.bad' LOGFILE my_log_dir:'%a_%p.log' FIELDS TERMINATED BY '~' MISSING FIELD VALUES ARE NULL (error_cd Char(100), error_desc Char(500) ) LOCATION ('my_file.txt') ) REJECT LIMIT UNLIMITED /

This solved the error.

Regards BI DWH BALA

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.