Home » RDBMS Server » Server Utilities » Dataloading Problem in Sql Ldr for CLOB column
Dataloading Problem in Sql Ldr for CLOB column [message #383392] Wed, 28 January 2009 11:39 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

I am having a problem in loading a '#' delimited file into oracle table having CLOB column in it.

Here Below is the table script.


create table backup_ec
( BACKUP_ID NUMBER(10,0) ,
TYPE VARCHAR2(7) ,
CREATED TIMESTAMP ,
USER_ID NUMBER(10,0) ,
PROCESS_ID NUMBER(10,0),
CONTENT_ID NUMBER(10,0),
CONTENT CLOB NOT NULL DISABLE,
TITLE VARCHAR2(100) ,
ISDISPLAYED NUMBER(1,0) ,
CONTENTTYPE_ID NUMBER(10,0),
USER_ID_FK NUMBER(10,0),
PROCESS_ID_FK NUMBER(10,0));





Control File:

load data
infile 'C:\Shortcuts\Backup_ec\backup_ec.txt'
append into table backup_ec
fields terminated by "#"
trailing nullcols
(
BACKUP_ID,
TYPE,
CREATED "(to_timestamp(substr(:created,1,19), 'yyyy-mm-dd hh:mi:ss'))",
USER_ID,
PROCESS_ID,
CONTENT_ID ,
CONTENT ,
TITLE,
ISDISPLAYED "Decode(:Isdisplayed,'True',1,'False',0)",
CONTENTTYPE_ID,
USER_ID_FK,
PROCESS_ID_FK
)


Datafile:
1#Process#2008-03-14 09:29:53.527000000#33#2989##1$2989$495$60$testing commas in library question$Title$$2$$$$$$$|2$2989$469$148$A test question... testing commas$LibraryQuestion$1$2.2
2.1$$$$$$$72|1$2$2989$-142$54$Yes test1 test2$$3$72$1|2$2$2989$135$87$No$$$72$2|3$2989$164$241$$Question$2.1$3.2 3.1$$$$$$$|1$3$2989$-50$70$Yes$$$$|2$3$2989$50$70$No$$$$#testing commas in library question#False##33#2989


I am encountered with following error.


Record 1: Rejected - Error on table BACKUP_EC, column CONTENT.
Field in data file exceeds maximum length




If I have a string of lesser size for CLOB column I am able to load data withpout any difficulties.

Any help really appreciated.

Thanks in advance.
Re: Dataloading Problem in Sql Ldr for CLOB column [message #383416 is a reply to message #383392] Wed, 28 January 2009 16:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
If you do not specify field lengths in your control file, they default to 255, so use:

CONTENT CHAR(4000),
Re: Dataloading Problem in Sql Ldr for CLOB column [message #383545 is a reply to message #383416] Thu, 29 January 2009 02:37 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

It can have a data more than 4000 characters.

So please anyone help me to load data for clob column in oracle from csv file with an example.

Really appreciated.

Thanks in advance
Re: Dataloading Problem in Sql Ldr for CLOB column [message #383660 is a reply to message #383545] Thu, 29 January 2009 09:59 Go to previous message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
4000 was just an example. You can specify up to 2 gigabytes in Oracle 11g. Here is a link to a section of the utilities guide with explanation and example:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/ldr_loading.htm#sthref1451
Previous Topic: converting images from long raw to jpeg.
Next Topic: import / Load SQL Server 2000 Data inTO Oracle 8
Goto Forum:
  


Current Time: Sat Dec 03 11:44:27 CST 2016

Total time taken to generate the page: 0.20362 seconds