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

Posted on 2009-02-23
Medium Priority
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
  • 2
  • 2
LVL 23

Expert Comment

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

Author Comment

ID: 23711338
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

David earned 2000 total points
ID: 23711433
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 http://dbaforums.org/oracle/index.php?showtopic=16913
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

ID: 23711739
OK...one 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month16 days, 7 hours left to enroll

850 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