Re: External Tables....HELP!!!!

amerar_at_iwc.net wrote:
> Ok, I created a simple external table:
>
> CREATE TABLE allstar_ratings_ext (
> RATE_DATE VARCHAR2(8),
> ANALYST_ID NUMBER(7),
> INDUSTRY_ID NUMBER(5),
> INDUSTRY_RATING NUMBER(15))
> Organization external
> (type oracle_loader
> default directory indata_directory
> access parameters (records delimited by newline
> fields terminated by ',')
> location ('allstar_ratings.txt'))
> reject limit 1000;
>
> However, not matter what I select fom that table, I get the following
> error:
>
> error processing column INDUSTRY_RATING in row 1 for datafile /u01/
> NI00/prod/data/indata/allstar_ratings.txt
> ORA-01722: invalid number
>
>
> When I look at the log, I see this:
>
> Fields in Data Source:
>
> RATE_DATE CHAR (255)
> Terminated by ","
> Trim whitespace same as SQL Loader
> ANALYST_ID CHAR (255)
> Terminated by ","
> Trim whitespace same as SQL Loader
> INDUSTRY_ID CHAR (255)
> Terminated by ","
> Trim whitespace same as SQL Loader
> INDUSTRY_RATING CHAR (255)
> Terminated by ","
> Trim whitespace same as SQL Loader
>
> Excuse me??? I did not define anything as CHAR(255). I explicitly
> defined my datatypes. The data is simple and looks like this:
>
> 1-Jan-07,10933,3,-20.543
>
> So, what gives here? I've been all over looking for help on this.
>
> Thanks for any help given.

Where's you DDL?

Try recreating your table defining everything as VARCHAR2.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org