| EXTERNAL TABLE PROBLEM [message #563263] |
Fri, 10 August 2012 11:23  |
 |
w0rtez
Messages: 6
Registered: August 2012
|
Junior Member |
|
|
Hi im trying to create an external table, and i load my data without no problem, and everything is fine, but i got some behavior with one column that i would like to know whats behind scenes, ok let's get the example:
[*] Sample Data
Line 1:333 1111111112009100000000000080000000013450.33
Line 2:11111111111220091016000000004.48
Line 3:222222222 220091016000000004.48
Line 4:(This is a blank line left)
And this is my External Table Create Query:
CREATE TABLE EXT_TABLE_TEMP
(COL_A VARCHAR2(11),
COL_B VARCHAR2(1),
COL_C DATE,
COL_D NUMBER(12,2))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY TMP
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE TMP:'BAD_TEMPTABLE.bad'
LOGFILE TMP:'LOG_TEMPTABLE.log'
NODISCARDFILE
SKIP 1
LOAD WHEN (COL_A != BLANKS)
FIELDS LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
( COL_A CHAR(11),
COL_B CHAR(1),
COL_C CHAR( DATE_FORMAT DATE MASK "YYYY/MM/DD",
COL_D CHAR(12)
)
)
LOCATION ('TEMP_DATA.txt')
)REJECT LIMIT UNLIMITED;
As you can see i can upload my table with no problem but i always get 3 lines counting last blank line if i try LOAD WHEN COL_A != BLANKS, i dont know if its a problem of the blank space left between fixed fields length, but if i do
LOAD WHEN COL_B != BLANKS i get correct result 2 lines instead of 3, i want to know why (missing fields...) and (reject rows...) are not working...
Note: COL_A could be 9-11 length, if length its 9 then 2 spaces left before next one...
Thanks
|
|
|
|
|
|
|
|
| Re: EXTERNAL TABLE PROBLEM [message #563283 is a reply to message #563263] |
Fri, 10 August 2012 18:13  |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
w0rtez wrote on Fri, 10 August 2012 12:23but i always get 3 lines counting last blank line if i try [i]LOAD WHEN COL_A != BLANKS[/i
I can't reproduce it:
SQL> select *
2 from v$version
3 /
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> CREATE TABLE EXT_TABLE_TEMP
2 (COL_A VARCHAR2(11),
3 COL_B VARCHAR2(1),
4 COL_C DATE,
5 COL_D NUMBER(12,2))
6 ORGANIZATION EXTERNAL
7 (TYPE ORACLE_LOADER
8 DEFAULT DIRECTORY TEMP
9 ACCESS PARAMETERS
10 (RECORDS DELIMITED BY NEWLINE
11 BADFILE TMP:'BAD_TEMPTABLE.bad'
12 LOGFILE TMP:'LOG_TEMPTABLE.log'
13 NODISCARDFILE
14 SKIP 1
15 LOAD WHEN (COL_A != BLANKS)
16 FIELDS LDRTRIM
17 MISSING FIELD VALUES ARE NULL
18 REJECT ROWS WITH ALL NULL FIELDS
19 ( COL_A CHAR(11),
20 COL_B CHAR(1),
21 COL_C CHAR(8) DATE_FORMAT DATE MASK "YYYY/MM/DD",
22 COL_D CHAR(12)
23 )
24 )
25 LOCATION ('TEMP_DATA.txt')
26 )REJECT LIMIT UNLIMITED;
Table created.
SQL> select rownum,t.* from EXT_TABLE_TEMP t;
ROWNUM COL_A C COL_C COL_D
---------- ----------- - --------- ----------
1 11111111111 2 16-OCT-09 4.48
2 222222222 2 16-OCT-09 4.48
SQL>
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
