1

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.

bakoyaro
  • 2,550
  • 3
  • 36
  • 63
user3139925
  • 11
  • 3

1 Answers1

2

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.

Noel
  • 10,152
  • 30
  • 45
  • 67
  • Ok, so I got rid of the error after moving them above the FIELDS TERMINATED, but now the table it's again empty. In the log file it says KUP-04021: field formatting error for field ID. What could be wrong with the id field? – user3139925 May 31 '14 at 08:59
  • 1
    @user3139925 - does your `.lst` file still contain the column headers from your query? `P.ID||'` would error. You should remove the header, or `set heading off` when you generate the file. – Alex Poole May 31 '14 at 10:46
  • Hey, thanks very much! I removed the header. I replaced the "," character with "." And every row ends with ";". My .lst file looks like 1;Settlers of Catan;29.52;Toys;3;
    2;DVD Player;82;Electronics;2;
    ... Now, when I SELECT * FROM products_ext it displays only the first row. A step forward, though. What else could it be?     – user3139925 May 31 '14 at 13:58
  • @user3139925 - what does the log file say is wrong now? – Alex Poole May 31 '14 at 17:02
  • Now it doesn't give me any error at all. I will display the log file anyway: Record format DELIMITED BY NEWLINE Data in file has same endianness as the platform Rows with all null fields are accepted Fields in Data Source: ID CHAR (6) Terminated by ";" Trim whitespace same as SQL Loader And the same thing for NAME, VALUE, DEP, COUNT fields.(the character limit doesn't permit me to post the full log) – user3139925 Jun 01 '14 at 07:45
  • 2
    @user3139925 - with the control file from this answer and your data with no header I get 14 rows from the table. Does yours only show you the DVD player? The `NLS_NUMERIC_CHARACTERS` thing might be a red-herring; if your query is using `,` as the decimal separator then the external table will too, it needs to be consistent (for the session anyway). But your log file would show ORA-01722 errors if that was what was wrong now. (Also, consider using `set trimspool on` so you don't need to add a trailing `;`). – Alex Poole Jun 01 '14 at 18:36