Re: sql*loader datatype question

Lisa,

A suggestion, and I hope it's not too late. :)

Change the datatype to varchar2. Phone numbers are not actually numbers, they're character fields.

My rule of thumb is: if you don't do math on it, it isn't a number.

Jared

On Thursday 30 August 2001 10:33, Koivu, Lisa wrote:
> I'm hitting a wall here. I'm trying to load data into a large table. Two
> of the fields are phone numbers (10 digits). The database field is defined
> as number(10).
>
> When I try to load these records, they err out with an error similar to
> below.
>
>
> Record 858: Rejected - Error on table INV_OWNER.FPSITE, column
> FPS_AFFIL_LTD_PTS_FPS.
> ORA-01722: invalid number
>
> So I change the fields to character, get the data in and it truly is all
> 10-digit numbers. I'm wondering if I'm missing something in the format.
> The doco says that INTEGER EXTERNAL is to be used for "human readable"
> numbers, non-binary, when you want to specify the length. The length I've
> chosen is correct. I can even create a table with number(10) columns and
> insert the varchar(10) phone number data into it!
>
> What am I missing? I really do not want to change all my number fields to
> character. It would defeat the purpose. I have a feeling I'm missing some
> sort of conversion in SQL*Loader but I don't see it off the top of my head.
>
> Thanks in advance for any suggestions.
>
> > Lisa Koivu
> > wanna-be DBA Hand Holder (that's what I've been doing this morning with
> > an ex-coworker) and DBA
> > Ft. Lauderdale, FL, USA
> >
> > The information in the electronic mail message is Cendant confidential
> > and may be legally privileged, it is intended solely for the addressee(s)
> > access to this internet electronic mail message by anyone else is
> > unauthorized. If you are not the intended recipient, any disclosure,
> > copying, distribution or any action taken or omitted to be taken in
> > reliance on it is prohibited and may be unlawful.
> >
> > The sender believes that this E-mail and any attachments were free of any
> > virus, worm, Trojan horse, and/or malicious code when sent. This message
> > and its attachments could have been infected during transmission. By
> > reading the message and opening any attachments, the recipient accepts
> > full responsibility for taking protective and remedial action about
> > viruses and other defects. Cendant Corporation or Affiliates are not
> > liable for any loss or damage arising in any way from this message or its
> > attachments.

Content-Type: text/html; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description: 
----------------------------------------

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).