Re: How to populate a Long Field with Sql*Loader ?
Date: Fri, 06 Aug 1999 05:23:20 GMT
Message-ID: <7odrg1$i0m$1_at_nnrp1.deja.com>
hi there,
I am assuming that you are loading comma delimited variable length data file with character fields optionally enclosed by either single or double quotes.
SQL*Loader uses its own data field types(don't confuse with oracle column data type of the table). After loading data according to the field definitions defined in the control file for each column, it is converted to the column type of the corresponding column name in the target table and inserted by the server.
For variable length delimited data values, you may like to avoid using native datatypes to avoid errors in loading due to inappropriate length. Here is a sample control file
Load data
infile *
Append
INTO TABLE customer_table
-- add your when clause here for selective insert FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' -- if the last column is null, total fields will be less one TRAILING NULLCOLS
(CUSTOMER_ID Integer external ,
CUSTOMER_NAME char(30),
CUSTOMER_DESCRIPTION char(32767) , <<<<< your long column CREATED_DATE DATE 'dd-mon-yyyy' NULLIF CREATED_DATE=BLANKS, ORDER_AMOUNT Float external )
BEGINDATA
1,"cust_name 1","long value 1",01-JUN-1999, 2,"cust_name 2","long value 2",19-AUG-1997,1275.50
The default loader type CHAR is 255. For columns where you know the max possible length like customer_name, it is better to override the default and specify char(30) to reduce the record size and better load performance.
For LONG columns, you can define CHAR(32767) as a generic setting. I guess the latest version can accommodate 64K.
For NUMERIC EXTERNAL fields , it is better to specify a field length rather than going with the default 255. I am not sure what is the best way to do it. May be some SQL*LOADER Gurus out there would like to comment on this.
regards
-Lokesh
In article <7oa3ab$75$1_at_wanadoo.fr>,
"Mathieu KREMBEL" <m.krembel_at_wanadoo.fr> wrote:
> Hi,
>
> Is it possible to populate a table containing a Long Field
> with Sql*Loader ?
> Did anybody already success it ? How ?
>
> My Ascii datafile will probably have variable records length with
delimited
> data format.
> If Sql*Loader does not load the full long field, is it another tool
to do
> this ?
>
> Thank your for your answers
>
> Matthieu Krembel
> E-Mail : m.krembel_at_ch-rouffach.fr
>
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Fri Aug 06 1999 - 07:23:20 CEST