Failing to Load All Data from csv file because Field in data file exceeds maximum length

Posted on 2009-02-23
Last Modified: 2013-12-18
I built a 10G database to import all data from a MS SQL 2005 database; in MS SQL, I used the 'MAX' datatype to accept large strings of text. In Oraclae, I set the format to 'Long' and still recieved this error on the import:

Field in data file exceeds maximum length

I tried to set the field to BLOB but the system wouldn't let me change the format to Blob.  I need to import large amounts of text, 10K, and need some help here.

Here's my table elements:

MAINID      NUMBER      
[Yes] [No]       FACILITYID      NUMBER      
[No] [No]       FACILITY_NAME      VARCHAR2      4000      
[No] [No]       FACILITY_DESCRIPTION      LONG      
[No] [No]       CENTERID      VARCHAR2      20      
[No] [No]       BUILDING_NOS      VARCHAR2      4000      
[No] [No]       ROOMS      VARCHAR2      4000
Question by:Glen_D
    LVL 23

    Expert Comment

    Seems to me that character long (CLOB) datatype may be what you're looking for.  FWIW LONG is deprecated after 9i.

    Author Comment

    Thx but when I change the field to CLOB, I receive the following error:

    SQL Error
    Failed to commit: ORA-22858: invalid alteration of datatype
    LVL 23

    Accepted Solution

    Here's the error detail:
    ORA-22858 - invalid alteration of datatype

    Cause - An attempt was made to modify the column type to object, REF, nested table, VARRAY or LOB type.

    Action - Create a new column of the desired type and copy the current column data to the new type using the appropriate type constructor.
    Don Burleson provides this example at
    ALTER TABLE users ADD about_me_txt2 CLOB;
    UPDATE users SET about_me_txt2 = about_me_txt;
    ALTER TABLE users drop about_me_txt;
    ALTER TABLE users rename about_me_txt1 TO about_me_txt;

    Author Comment

    by:Glen_D more question; I created another table, similiar, and successfully added the CLOB field; when I sue the OEM Grid for importing the csv file, the fields from the csv file are not aligning with the table fields (structure); how do I match the fields from the csv import file to the tabel structure in the OEM Grid format?


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now