| External Tables [message #599720] |
Mon, 28 October 2013 06:39  |
ind9
Messages: 65
Registered: January 2009
|
Member |
|
|
Hi All,
I have a query regarding External tables. When I try to work with a flat file of fixed positions then I am receiving error but if I change the external table definition as fields separated with a delimiter and change the source file to delimited file format then it works.
I guess the problem is with Chineese characters but couldn't able to resolve it.
Please suggest.
Table definition with fixed position results into ERROR
CREATE TABLE customer_load
(
store NUMBER(7) ,
CUST_NO number(12) ,
CUST_LAST_NAME varchar2(100 CHAR) ,
cust_first_name VARCHAR2(100 char)
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY receive_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
CHARACTERSET UTF8
LOGFILE receive_dir:'custxxx.log'
LOAD WHEN (1:2) != '@@'
NODISCARDFILE
FIELDS
LRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
store POSITION( 1:7 ) ,
CUST_NO POSITION( 7:18 ) ,
CUST_LAST_NAME POSITION( 20:119 ) CHAR,
cust_first_name POSITION( 121:220 ) CHAR
)
)
LOCATION ('custxxx.dat')
)
REJECT LIMIT UNLIMITED;
datafiles (custxxx.dat) attached and log file error is
LOG file opened at 10/28/13 12:10:07
Field Definitions for table CUSTOMER_LOAD
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Reject rows with all null fields
Load when ((1:2) != @@)
Fields in Data Source:
STORE CHAR (7)
Record position (1, 7)
Trim whitespace from left and right
CUST_NO CHAR (12)
Record position (7, 18)
Trim whitespace from left and right
CUST_LAST_NAME CHAR (100)
Record position (20, 119)
Trim whitespace from left and right
CUST_FIRST_NAME CHAR (100)
Record position (121, 220)
Trim whitespace from left and right
KUP-04021: field formatting error for field CUST_LAST_NAME
KUP-04101: record 1 rejected in file /1004/receive_dir/custxxx.dat
KUP-04021: field formatting error for field CUST_LAST_NAME
KUP-04101: record 2 rejected in file /1004/receive_dir/custxxx.dat
KUP-04021: field formatting error for field CUST_LAST_NAME
KUP-04101: record 3 rejected in file /1004/receive_dir/custxxx.dat
KUP-04021: field formatting error for field CUST_LAST_NAME
KUP-04101: record 4 rejected in file /1004/receive_dir/custxxx.dat
KUP-04021: field formatting error for field CUST_LAST_NAME
KUP-04101: record 5 rejected in file /1004/receive_dir/custxxx.dat
KUP-04021: field formatting error for field CUST_LAST_NAME
KUP-04101: record 7 rejected in file /1004/receive_dir/custxxx.dat
KUP-04021: field formatting error for field CUST_LAST_NAME
KUP-04101: record 8 rejected in file /1004/receive_dir/custxxx.dat
KUP-04021: field formatting error for field CUST_LAST_NAME
KUP-04101: record 9 rejected in file /1004/receive_dir/custxxx.dat
error processing column CUST_NO in row 6 for datafile /1004/receive_dir/custxxx.dat
ORA-01722: invalid number
Delimited table structure works
CREATE TABLE customer_load
(
store NUMBER(7) ,
CUST_NO number(12) ,
CUST_LAST_NAME varchar2(100 CHAR) ,
cust_first_name VARCHAR2(100 char)
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY receive_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
CHARACTERSET UTF8
LOGFILE receive_dir:'custxxx_delim.log'
LOAD WHEN (1:2) != '@@'
NODISCARDFILE
FIELDS TERMINATED BY '|'
LRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION ('custxxx_delim.dat')
)
REJECT LIMIT UNLIMITED;
Could you please suggest what is the difference?
Database Characterset is
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET UTF8
Files are spooled with UTF8
Thanks in advance for your help!
-
Attachment: custxxx.dat
(Size: 2.09KB, Downloaded 939 times)
|
|
|
|
| Re: External Tables [message #599756 is a reply to message #599720] |
Mon, 28 October 2013 11:52  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Positions are all messed up based on your data file. Should be something like:
SCOTT@orcl > CREATE TABLE customer_load
2 (
3 store NUMBER(7) ,
4 CUST_NO number(12) ,
5 CUST_LAST_NAME varchar2(200 CHAR) ,
6 cust_first_name VARCHAR2(100 char)
7 )
8 ORGANIZATION EXTERNAL
9 (
10 TYPE oracle_loader
11 DEFAULT DIRECTORY temp
12 ACCESS PARAMETERS
13 (
14 RECORDS DELIMITED BY NEWLINE
15 CHARACTERSET UTF8
16 LOGFILE 'custxxx.log'
17 LOAD WHEN (1:2) != '@@'
18 NODISCARDFILE
19 FIELDS
20 LRTRIM
21 MISSING FIELD VALUES ARE NULL
22 REJECT ROWS WITH ALL NULL FIELDS
23 (
24 store POSITION( 1:7 ) ,
25 CUST_NO POSITION( 8:20 ) ,
26 CUST_LAST_NAME POSITION( 21:180 ) CHAR,
27 cust_first_name POSITION( 181:300 ) CHAR
28 )
29 )
30 LOCATION ('custxxx.dat')
31 )
32 REJECT LIMIT UNLIMITED;
Table created.
SCOTT@orcl > select * from customer_load;
STORE CUST_NO CUST_LAST_NAME CUST_FIRST_NAME
---------- ---------- ------------------------------ ----------------------
99 158513 ??????????????? Tested
99 157551 ???????????? Tested
99 157558 ??????????? Tested
99 150635 ??????????????? Tested
99 156616 ?????????????????? Tested
99 151563 ?????????? Tested
99 156687 ?????????????? Tested
99 150567 ?????? Tested
99 158461 ?????????????? Tested
9 rows selected.
SCOTT@orcl >
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
