Re: ORA-01460 error

From: <>
Date: Wed, 22 Oct 2008 23:08:26 +0200
Message-ID: <>

On Wed, 22 Oct 2008 20:43:36 +0000 (UTC), Aya the Vampire Slayer <> wrote:

>All I can say is that at least for Oracle,
>INTEGER (sql*loader) cannot be cast to NUMBER (db). Either use CHAR in
>the CTL file even if the field is a NUMBER in the DB, define a default
>value for it in the DB and omit it from the CTL file, or use some other
>suggestion from someone else here who knows more about it than I.

You are misinterpreting the documentation. First of all, you should NOT, I repeat NOT use Oracle documentation from non-Oracle sites. Those sites are bound to be out-of-sync, as Oracle corrects documentation tacitly,
Secondly, you should read the section 'Native Datatypes' AND 'Character Datatypes'
My bets are you have INTEGER EXTERNAL data in your file, which means ... plain ol' Ascii, NOT binary. So, yes, if you set up your column as INTEGER all will go wrong. Sql*loader treats all data as weakly typed, it doesn't perform any checks, so there will be data ending up in incorrect columns.

Sybrand Bakker
Senior Oracle DBA
Received on Wed Oct 22 2008 - 16:08:26 CDT

Original text of this message