Hi all,
I want to load to an external table the following file (name:1.txt directory:TEMP):
document_id;period;contract_id;contract_desc;eof;drug_short_description;drug_description;quantity;amount;nt_dt_amount;final_amount;vat;vat_cost;atc_4;atc_5;ant_id;ant_desc;kak_id;kak_desc
6385304;2/1/2019;954;AAAAAAAA;280297840101;TEST 1/ 45;TEST 1/ 45;9;1872,621;1971,18;1872,621;6;112,36;L01CD;L01CD01;1292;CMP_1;13407; CMP_2
6385471;2/1/2019;990;BBBBBBBB;280297840101;TEST 1/ 42; TEST 1/ 42 ;80;16645,52;17521,6;16645,52;6;998,73;L01CD;L01CD01;1292;CMP_3;13407;CMP_2
638542371;19/1/2019;990;BBBBBBBB;280297840101;TEST 11/ 42; TEST 1/ 42 ;80;16645,52;16645,52;;6;998,73;L01CD;L01CD01;1292;CMP_3;13407;CMP_2
628542371;19/1/2019;990;DDDDGF;280297840101;TEST 11/ 42; TEST 1/ 42 ;80;16645,52;16645,52;NULL;6;998,73;XXXX;XXX;1292;CMP_3;13407;CMP_2
I have made the following table but something goes wrong.
CREATE TABLE LOADED_TXT_DATA
(
document_id NUMBER(12),
period DATE,
contract_id NUMBER(20),
contract_desc VARCHAR2(300),
eof VARCHAR2(30),
drug_short_description VARCHAR2(150),
drug_description VARCHAR2(300),
quantity NUMBER(10),
amount NUMBER(12,2),
nt_dt_amount NUMBER(12,2),
final_amount NUMBER(12,2),
vat NUMBER(5,2),
vat_cost NUMBER(12,2),
atc_4 VARCHAR2(30),
atc_5 VARCHAR2(30),
ant_id NUMBER(20),
ant_desc VARCHAR2(300),
kak_id NUMBER(20),
kak_desc VARCHAR2(300)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY TEMP
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE TEMP:'txt%a_%p.bad'
LOGFILE TEMP:'txt%a_%p.log'
NODISCARDFILE
NOLOGFILE
SKIP 1
FIELDS TERMINATED BY ';'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
document_id Char,
period Char,
contract_id Char,
contract_desc Char,
eof Char,
drug_short_description Char,
drug_description Char,
quantity Char,
amount Char,
nt_dt_amount Char,
final_amount Char,
vat Char,
vat_cost Char,
atc_4 Char,
atc_5 Char,
ant_id Char,
ant_desc Char,
kak_id Char,
kak_desc Char
) )
LOCATION (TEMP:'1.txt')
)
REJECT LIMIT Unlimited
NOPARALLEL
NOMONITORING;
The table is created, but when I try to read data with select I get the following error :
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "eof": expecting one of: "double-quoted-string, identifier, single-quoted-string"
KUP-01007: at line 15 column 10
Moreover, I want to make the following actions :
1. Trim all the fields
2. If final_amount is 'NULL' (as string) then convert it to NULL
3. The numbers which are defined with 2 decimals to be rounded to 2 decimals
4. In atc_4, atc_5 fields if the values are all the same letter ie. xxxx should become null.
Could you help me do it please ?
Thanks in advance