sql loader - varchar2 with more than 255 chars

[PaulWalterAsh]

Can anyone tell me how to load varchar2 fields with more than 255 chars using sql loader?

 

[ddrillich]

"If you try to insert a value into a character datatype column that is larger than its maximum size, Oracle will return an error" (OCA/OCP Introduction to Oracle9I SQL, page 374).

You can truncate the value using the substr function. For example -
select table_name, substr(table_name,1,10) from all_tables

Cheers,
Dan

 

[Turkbear]

Hi,
Maybe I'm missing something here ( won't be the first time) but why are you having problems with that length?
Varchar2 fields can be up to 4K .

 

[ddrillich]

When creating a table, one has to specify the maximum length of a varchar2 column.
In our case, it might have been defined as 255.

Dan

 

[Turkbear]

Oh, that would explain it - can you control the data in the input file to make it the correct length? If it is delimited, can you instead use positional notation to specify what to load ?

 

[ddrillich]

You can either control the data in the input file or when loading the data via SQL Loader you can use the Oracle functions to trim it (the substr function).

Dan

 

[sem]

If your table structure doesn't suit your needs (I suppose you don't want data loss), why don't you alter it?

Regards, Dima

 

[PaulWalterAsh]

I am using a delimited file to load and the data can be more than 255 chars. The table has several fields that are defined as varchar2(1000) and varchar2(2000). SQL Loader appears to limit the imput to 255 chars.

 

[sem]

255 is default field length unless other is specified. Set it explicitly in control file.

Regards, Dima

 

[ddrillich]

I really doubted what Dima said (Sorry Dima), but she is absolutely right.
The thing is that I don’t see a way to set the default length in the control file.
If you look at -

http://www.oreilly.com/catalog/orsqlloader/chapter/ch01.html

It says -
"The following three datatypes are used in this control file. They have no bearing on, or relationship to, the database datatypes of the columns being loaded. The purpose of the datatypes in the control file is to describe the data being loaded from the input data file:
CHAR Tells SQL*Loader that a field is a text field.
INTEGER EXTERNAL Tells SQL*Loader that a field is an integer represented using the text digits "0" through "9".
DECIMAL EXTERNAL Tells SQL*Loader that a field is a decimal value represented using the text digits "0" through "9" and an optional decimal point (".&quot. "

It doesn’t specify the syntax for the column length.

Any ideas?

Thanks,
Dan

 

[ddrillich]

Here is a working control file -

LOAD DATA
APPEND INTO TABLE tstVarchar2
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
Column_1 CHAR(500)
)

Cheers,
Dan

 

[sem]

Dan, first of all, I'm male, so you don't need to apologize for not understanding me I'm from Ukraine, have no English practice except here, and work with Oracle for 8 years, thus my English is not excellent; the same is probably with the ability to explain things obvious for me.

I meant exactly the same: without specifying length (500 in your case) and type, the type is defaulted to 255 symbols character string.

You may read about "Maximum Length of Delimited Data" here:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch05.htm

Regards, Dima

 

[ddrillich]

You are right Dima. I was just happy to find out about the exact syntax – CHAR(500).
Thanks a lot for the link!

Dan