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: Weaver, Walt <>
Date: Thu, 30 Aug 2001 13:49:58 -0700
Message-ID: <>

A .bad
file with the offending record should have been created when sqlloader abended. Even at 1500 characters you should be able to eyeball the single bad record in a good editor and figure out what values are in the phone number part of the record.
Bozeman, Montana

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Koivu, Lisa   []Sent: Thursday, August 30, 2001   1:46 PMTo: Multiple recipients of list ORACLE-LSubject:   RE: sql*loader datatype question
  Good explanation but no.... 
  it's not   

    -----Original Message----- <FONT
    face=Arial size=1>From:   Ron
    Rogers [] <FONT face=Arial     size=1>Sent:   Thursday, August
    30, 2001 2:48 PM <FONT face=Arial

    size=1>To:     <FONT face=Arial 
    size=1>Multiple recipients of list ORACLE-L <FONT face=Arial 
    size=1>Subject:        <FONT 
    face=Arial size=1>Re: sql*loader datatype question 
    Lisa, Is the
    field you are attempting to load the last field in the string? is the     delimited in place? The reason being is the EXTERNAL INTEGER data might be     interpreted at char including the "space" at the end of the string. That has     bit me before and now all non-numbers are enclosed in quotes.     ROR mª¿ªm
    >>> 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 <FONT face=Arial     size=2>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 <FONT face=Arial     size=2>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 <FONT face=Arial     size=2>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 <FONT face=Arial     size=2>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 <FONT face=Arial

    size=2>> 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 <FONT     face=Arial size=2>> reliance on it is prohibited and may be     unlawful. > <FONT
    face=Arial size=2>> 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 <FONT face=Arial     size=2>> 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 <FONT face=Arial     size=2>> attachments. >

Received on Thu Aug 30 2001 - 15:49:58 CDT

Original text of this message