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

On Nov 21, 10:42 pm, "ame..._at_iwc.net" <ame..._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.

Apparently your trips didn't take you to the documentation. And why are you storing dates as varchar2 strings? And why aren't these strings in a consistent format? I expect that you have 'dates' formatted in varying formats:

1-Jan-07
...
12-Jan-07
...
30-Mar-07
...

As such your VARCHAR2(8) definition isn't correct. And you should be storing these as DATE, not VARCHAR2(8), for various reasons. Possibly you should try this instead:

CREATE TABLE allstar_ratings_ext (

   RATE_DATE DATE,
   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 ','
(rate_date char date_format date mask "dd-Mon-yy", analyst_id, industry_id, industry_rating)
)
location ('allstar_ratings.txt'))
reject limit 1000;

Then edit your data file to ensure all of your date strings match the given format:

01-Jan-07,10933,3,-20.543

You MAY then have a usable external table.

David Fitzjarrell Received on Thu Nov 22 2007 - 14:38:23 CST