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

On Nov 22, 2:38 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Also, you can't store a decimal number in a NUMBER(15) field; it should be a NUMBER(12,3) to account for the decimal numbers. And possibly you don't have a uniform 3 decimal places in every record, so you need to declare that NUMBER field with the maximum number of decimals your data contains.

As I said earlier the documentation obviously wasn't a stop on your trip to enlightenment. Of course, it never appears to be from the posts you've offered this newsgroup.

David Fitzjarrell Received on Thu Nov 22 2007 - 14:41:55 CST