Varchar data type Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Varchar data type

  1. #1
    Join Date
    Nov 2006

    Varchar data type

    I have a column with varchar(10) as datatype, I want to load data into it using SQL* Loader.

    When I create a control file using CHAR it works, but when I put Varchar(10) it fails.

    How do I make it work using Varchar(10) in control file. Or forcefully I have to use CHAR only.

  2. #2
    Join Date
    Aug 2002
    Colorado Springs
    Be careful of the difference between data types in tables and those in SQL*Loader. This is a good example -- in SQL*Loader VARCHAR is a length-value datatype in which ... well here's what the docs say ...


    A VARCHAR field is a length-value datatype. It consists of a binary length subfield followed by a character string of the specified length. The length is in bytes unless character-length semantics are used for the datafile. In that case, the length is in characters....

    VARCHAR fields can be loaded with correct results only between systems where a SHORT data field INT has the same length in bytes. If the byte order is different between the systems, or if the VARCHAR field contains data in the UTF16 character set, use the appropriate technique to indicate the byte order of the length subfield and of the data. The byte order of the data is only an issue for the UTF16 character set. ...

    The size of the length subfield is the size of the SQL*Loader SMALLINT datatype on your system (C type SHORT INT). See SMALLINT for more information.


    A maximum length specified in the control file does not include the size of the length subfield. If you specify the optional maximum length for a VARCHAR datatype, then a buffer of that size, in bytes, is allocated for these fields. However, if character-length semantics are used for the datafile, the buffer size in bytes is the max_length times the size in bytes of the largest possible character in the character set. ...

    The default maximum size is 4 KB. Specifying the smallest maximum length that is needed to load your data can minimize SQL*Loader's memory requirements, especially if you have many VARCHAR fields.

    The POSITION clause, if used, gives the location, in bytes, of the length subfield, not of the first text character. If you specify POSITION(start:end), the end location determines a maximum length for the field. Start is subtracted from (end + 1) to give the length of the field in bytes. If a maximum length is specified, it overrides any length calculated from POSITION.

    If a VARCHAR field is truncated by the end of the logical record before its full length is read, a warning is issued. Because the length of a VARCHAR field is embedded in every occurrence of the input data for that field, it is assumed to be accurate.

    VARCHAR data cannot be delimited.
    So in short this is expected and documented behaviour.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width