Oracle external tables

Question

I'm struggling with an Oracle external table, although I researched the Oracle forums. Still, no success.

Let's suppose I have a simple table

DESCRIBE PRODUCTS
Name                           Null     Type                                                                                                                                                                                          
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
ID                             NOT NULL NUMBER                                                                                                                                                                                        
NAME                                    VARCHAR2(30)                                                                                                                                                                                  
VALUE                                   NUMBER(5,2)                                                                                                                                                                                   
DEP                                     VARCHAR2(30)                                                                                                                                                                                  
COUNT                                   NUMBER(3)     

Then, I created an oracle folder:

CREATE OR REPLACE DIRECTORY ext_prod_dir AS 'c:\';

I save the content of that table in a .lst file

spool c:\products.lst
select p.id || ';' || p.name || ';' || p.value  || ';' || p.dep || ';' || p.count FROM products p;
spool off;

P.ID||';'||P.NAME||';'||P.VALUE||';'||P.DEP||';'||P.COUNT                                                                                                                                
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1;Settlers of Catan;29,95;Toys;3                                                                                                                                                         
2;DVD Player;82,97;Electronics;2                                                                                                                                                         
3;Red Shirt;12,49;Clothes;3                                                                                                                                                              
4;Black Leather Couch;399,99;Furniture;5                                                                                                                                                 
5;Oak Cofee Table;223,99;Furniture;5                                                                                                                                                     
6;Technodrome;27,99;Toys;4                                                                                                                                                               
7;Oh Cereal;3,95;Foods;1                                                                                                                                                                 
8;Game Console;299,95;Toys;2                                                                                                                                                             
9;Video Game;29,95;Toys;3                                                                                                                                                                
10;Lawn Chair;34,99;Furniture;11                                                                                                                                                         
11;Dog Toy Bone;34,99;Toys;9                                                                                                                                                             
12;Heated Blanket;27,95;Toys;8                                                                                                                                                           
13;Flux Capacitor;27,95;Toys;7                                                                                                                                                           
14;Chocolate Pie;3,14;Foods;7

Then I tried to create the external table:

CREATE TABLE products_ext 
  (ID NUMBER, 
  NAME VARCHAR2(30),
  VALUE NUMBER(5,2),
  DEP VARCHAR2(30),
  COUNT NUMBER(3)) 
  ORGANIZATION EXTERNAL 
  (TYPE oracle_loader DEFAULT DIRECTORY ext_prod_dir 
  ACCESS PARAMETERS 
      (RECORDS DELIMITED BY NEWLINE 
      FIELDS TERMINATED BY ';'
      MISSING FIELD VALUES ARE NULL 
      BADFILE ext_prod_dir:'products.bad_xt'
      LOGFILE ext_prod_dir:'products.log_xt'

      (ID CHAR(6),
      NAME CHAR(30),
      VALUE CHAR(8),
      DEP CHAR(30),
      COUNT CHAR(3))) 
      location ('products.lst')
      ) REJECT LIMIT UNLIMITED

So far so good. Then when I select data from the external table, I got:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout 
ORA-29400: data cartridge error 
KUP-00554: error encontered while parsing access parameters 
KUP-01005: syntax error: found "badfile": expecting one of: "column (,reject"
KUP-01007: at line 4 column 7

I tried a huge amount of things, but I got variations on this error. Best thing I accomplished was that I got rid of error, but the table was empty. I would be very much indebted If someone with more experience can point me in the right direction.

Answer 1

BADFILE and LOGFILE are not part of the FIELDS clause. So, move them above the FIELDS TERMINATED.

CREATE TABLE products_ext
  (ID NUMBER,
  NAME VARCHAR2(30),
  VALUE NUMBER(5,2),
  DEP VARCHAR2(30),
  COUNT NUMBER(3))
  ORGANIZATION EXTERNAL
  (TYPE oracle_loader DEFAULT DIRECTORY ext_prod_dir
  ACCESS PARAMETERS
      (RECORDS DELIMITED BY NEWLINE
      BADFILE ext_prod_dir:'products.bad_xt'
      LOGFILE ext_prod_dir:'products.log_xt'
      FIELDS TERMINATED BY ';'
      MISSING FIELD VALUES ARE NULL
      (ID CHAR(6),
      NAME CHAR(30),
      VALUE CHAR(8),
      DEP CHAR(30),
      COUNT CHAR(3)))
      LOCATION ('products.lst')
      ) REJECT LIMIT UNLIMITED

Also, you said when you got rid of the error, the table was empty. Did you check the logfile? If the error is with the VALUE column, then check NLS_NUMERIC_CHARACTERS parameter in view v$nls_parameters.

select * from v$nls_parameters;

Check if the decimal marker is indeed a comma. If not either update this parameter or change it in the data file.