Re: ORA9i (on HP-UX 11.0) create external table problem!

From: Daniel Roy <danielroy10_at_hotmail.com>
Date: 25 Aug 2002 12:11:18 -0700
Message-ID: <1b061893.0208251111.3caea173@posting.google.com>

I'm not an expert on the topic, but it seems strange to me that the fields displayed at the bottom (deptno, dname, loc) are not part of the definition of the table at all. I tried to create my own external table, and each time I listed a different field name at the bottom and at the top, I received an error message: "KUP-04043: table column not found in external source: bla-bla". I therefore made sure to create the same fields at the top and the bottom, and everything worked fine. Here is the statement I used

CREATE TABLE products_ext
(prod_id NUMBER, prod_name VARCHAR2(50), prod_desc VARCHAR2(4000),

prod_category VARCHAR2(50), prod_category_desc VARCHAR2(4000), 
list_price NUMBER(6,2), min_price NUMBER(6,2),
last_updated DATE)

ORGANIZATION EXTERNAL
(

TYPE oracle_loader
DEFAULT DIRECTORY stage_dir
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE

LOGFILE log_dir:'log_products_ext'
BADFILE bad_dir:'bad_products_ext'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' MISSING FIELD VALUES ARE NULL
(prod_id, prod_name, prod_desc, prod_category,
prod_category_desc, list_price, min_price, last_updated char date_format date mask "dd-mon-yyyy") )
LOCATION ('new_prod1.txt','new_prod2.txt') )
REJECT LIMIT UNLIMITED; Previously, I created the directories stage_dir, log_dir and bad_dir. My data file contained this data:

1,Junk,"Junk Product","Junk Category","Junk Category Description",100,2.99,"25-AUG-2002"
2,Junk2,"Junk Product","Junk Category","Junk Category Description",100,3.99,"26-AUG-2002"

HTH Daniel Received on Sun Aug 25 2002 - 14:11:18 CDT