Re: SQL Loader problem

From: Tim Gorman <tim_at_sagelogix.com>
Date: Tue, 29 Jul 2003 15:09:29 -0800
Message-ID: <F001.005C7B4F.20030729150929@fatcity.com>

Sliced bread or not, the syntax problem will be the same since external tables are modeled on SQL*Loader syntax... :-)

Anna, I suspect that there is confusion on the SQL*Loader concepts of "external datatypes" and "internal datatypes", which incidentally is shared by external tables currently. SQL*Loader's "internal datatypes" match the standard Oracle RDBMS datatypes, but the "external datatypes" are something else altgoether...

I don't have docs available, but specifying an "external datatype" of "VARCHAR" or "VARCHAR2" probably doesn't mean what you want it to mean. If memory serves, I think it is supposed to be a two-byte binary integer (which is the data length) to be followed by the data itself. Or something like that...

Try "CHAR(4000)", perhaps? The "Server Utilities" manual from the standard Oracle doc-set on "http://otn.oracle.com" has all this stuff in it...

Hope this helps...

-Tim

on 7/29/03 3:19 PM, Mladen Gogala at mgogala_at_adelphia.net wrote:

> Try with external tables. The best thing since sliced bread.
> On 2003.07.29 17:39, Anna Li wrote:

>> Hi All,
>>
>> I'm trying to use SQL Loader to load data from a text file into a table.
>> However, I always get error as column SKILL_DESCRIPTION field in data file
>> exceeds maximum length where the column SKILL_DESCRIPTION is declared as
>> varchar2(4000). I know the data is <4000 characters, but >1000 characters.
>> It was inserted successfully when I execute insert statement in TOAD as:
>>
>> insert into skills_tmp values(......);
>>
>> Is there any option in SQL Loader I have to set in order to load a long
>> string? Thanks for all the inputs in advance.
>>
>> Anna
>>
>> _________________________________________________________________
>> Protect your PC - get McAfee.com VirusScan Online
>> http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
>>
>> --
>> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>> --
>> Author: Anna Li
>> INET: anna15888_at_hotmail.com
>>
>> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>> San Diego, California -- Mailing list and web hosting services
>> ---------------------------------------------------------------------
>> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: tim_at_sagelogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Tue Jul 29 2003 - 18:09:29 CDT