How TO: External Tables
ame...@iwc.net
This is driving me crazy!
Ok, I have an external table that is tab delimited. I am not
interested in all of the fields, jsut a few of them. My table
definition is below. But, it is not working! Here is my definition
and what happends when I query the data:
CREATE TABLE TARGET_PRICE_EST_EXT
(
ID VARCHAR2(5),
REPORT_DATE VARCHAR2(8),
ESTIMATE VARCHAR2(8)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY INDATA_DIRECTORY
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL (
"ID" POSITION(1:5) CHAR(5),
"REPORT_DATE" POSITION(49:8) CHAR(8),
"ESTIMATE" POSITION(65:8) CHAR(8))
)
LOCATION (INDATA_DIRECTORY:'SnDailyUpload.txt')
)
REJECT LIMIT 50;
ID REPORT_D ESTIMATE
----- -------- --------
AAD A 7.20 11 5 00949
JON A 6.71 47. /2005 5
CCFG 0 8.20 0 7 5 0185
EERT 3.80 3.8 2007 -99
LLF A 82 10.00 007 3 03
Any thoughts???
Vince
If your file is tab delimited, you should not be using positional
information in defining columns and defining your delimiter as 0x'09'
If the data is not tab delimited, then your position arguments are
incorrect. The first digit is the starting character of your desired
string and the second digit is the last character you desire. (i.e.
(15:16) is 2 characters, (15:2) wont work)
If you need to skip columns and the file is tab delimited, you still
have to define them in your control file:
id char,
dummy1 char,
report_date char,
dummy2 char...
Mark D Powell
>
> - Show quoted text -
amerar, Vince is correct in that your definition is defitely wrong.
Either you have a fixed position input file or a delimited input
file. You must define the correct delimiter tab instead of pipe, and
you do have to account for the columns you do not want.
See the sqlldr section of the Utilities manual for control card
examples.
Here is a complete sample load of a tab delimited file via an external
table (though I do not skip any fields).
--
-- Alvin Tolliver 1976
-- Kenneth Baer M 1963
-- Mary Dube F 1973
--
UT1 > drop table loadtext2;
Table dropped.
UT1 > create table loadtext2 (
2 c1 varchar2(12),
3 c2 varchar2(12),
4 c3 varchar2(01),
5 c4 number
6 ) organization external
7 (type oracle_loader default directory markshome
8 access parameters
9 ( records delimited by newline
10 badfile markshome:'bad_load'
11 logfile markshome:'log_load'
12 fields terminated by 0x'09'
13 missing field values are null
14 (
15 c1,
16 c2,
17 c3,
18 c4
19 )
20 )
21 location('textfile.txt')
22 )
23 /
Table created.
UT1 > select * from loadtext2;
C1 C2 C
C4
------------ ------------ -
----------
Alvin Tolliver
1976
Kenneth Baer M
1963
Mary Dube F
1973
UT1 > spool off
HTH -- Mark D Powell --