Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: sql*loader datatype question

RE: sql*loader datatype question

From: Mercadante, Thomas F <>
Date: Thu, 30 Aug 2001 12:33:22 -0700
Message-ID: <>

I agree with Joe.  I always have
declared the data to be char, even if I am loading a number field.  Always seems to work.
Tom Mercadante Oracle Certified

  face=Tahoma>-----Original Message-----From: JOE TESTA   []Sent: Thursday, August 30, 2001 2:48   PMTo: Multiple recipients of list ORACLE-LSubject: Re:   sql*loader datatype question
  Lisa, in the modeling world, unless you are performing a calculation(like   on phone number), i've always been told to make them character.    
>>> 08/30/01 01:33PM

  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. <FONT   face="Book Antiqua">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.    Received on Thu Aug 30 2001 - 14:33:22 CDT

Original text of this message