Re: ORA-01460 error

From: Aya the Vampire Slayer <>
Date: Wed, 22 Oct 2008 22:14:16 +0000 (UTC)
Message-ID: <gdo8jo$5av$> wa:
>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,

Then what's the point of anyone ever posting anything at all on the web about oracle, including this newsgroup? I see your point in that oracle can update their docs whenever things change, but that does not mean other sites are not valuable resources as well. Explain to me what is wrong with the information on the posted site, especially as it relates to this thread (this is rhetorical).

Anyway, your statement generalizes and includes things like, which I prefer to keep as a resource since I find D.A. Morgan's examples to be a lot better and the format of the pages to be a lot more readable than oracle's pages. YMMV, of course. Everyone's different.

>Secondly, you should read the section 'Native Datatypes' AND
>'Character Datatypes'

Okay so the site should've used INTEGER EXTERNAL for a NUMBER field instead? I will try it out. <...> Okay, it works for a NUMBER field.

>My bets are you have INTEGER EXTERNAL data in your file, which means
>... plain ol' Ascii, NOT binary.

I think you mean: the image_id/blob_id field defined as NUMBER in the database should've been defined as INTEGER EXTERNAL in the CTL file for SQL*Loader.

The "file" however is a bitmap, a blob, a binary file, whatever you want to call it, not an an ascii file. It was correct as specified, even from the original poster's linked webpage.

>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.

The columns were set as in the example on the website that the original poster (who is NOT me) posted. I tried out the example, found that I got the same error as s/he did, and sought to determine what was wrong with it since I had never used SQL*Loader before and thought this might be a good learning experience (which it was).

"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators

"Keep your fingers off the lens." --Elton Byington, English Translator
Received on Wed Oct 22 2008 - 17:14:16 CDT

Original text of this message