Home » RDBMS Server » Server Utilities » Problem in loading data for clob columns
Problem in loading data for clob columns [message #417395] Fri, 07 August 2009 16:18 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member


Hi,

I am having problem in loading data for tables having clob column.


Could anyone help me in correcting the below script for ctrl file inorder to load the data which is in mentioned format.

Any help really appreciated.



Table Script
------------

Create table samp
(
no number,
col1 clob,
col2 clob
);


Ctrl File
---------
options (skip =1)
load data
infile 'c:\1.csv' 
Replace into table samp
fields terminated by "," 
trailing nullcols
(
no,
col1 Char(100000000) ,
col2 Char(100000000) enclosed by '"' and '"'
)

Data File(1.csv)
----------------
1,asdf,"assasadsdsdsd""sfasdfadf""sdsdsa,ssfsf"
2,sfjass,"dksadk,kd,ss""dfdfjkdjfdk""sasfjaslaljs"


Error Encountered
-----------------
ORA-01461: can bind a LONG value only for insert into a LONG column


Table samp





Thanks in advance
Re: Problem in loading data for clob columns [message #417398 is a reply to message #417395] Fri, 07 August 2009 16:41 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Try as follows:

options (skip =1)
load data
infile 'c:\1.csv' 
Replace into table samp
fields terminated by "," 
trailing nullcols
(
no,
col1 Char,
col2 Char enclosed by '"' and '"'
)

Re: Problem in loading data for clob columns [message #417400 is a reply to message #417398] Fri, 07 August 2009 16:53 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Thanks for your reply.

If I inserted a huge data for col2.
I am encountered with below error.

column col2.
Field in data file exceeds maximum length
Table SAMP


Any help appreciated

Thanks in advance
Re: Problem in loading data for clob columns [message #417401 is a reply to message #417395] Fri, 07 August 2009 17:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Problem in loading data for clob columns [message #417403 is a reply to message #417395] Fri, 07 August 2009 17:53 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Any help to over come below error


ORA-01461: can bind a LONG value only for insert into a LONG column 


Thanks in advance
Re: Problem in loading data for clob columns [message #417404 is a reply to message #417395] Fri, 07 August 2009 18:13 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

http://www.orafaq.com/forum/?SQ=5720174b6a70c8eb805f50bc8d3adb59&t=search&srch=ORA-01461&btn_submit=Search&field=all& amp;forum_limiter=10&search_logic=AND&sort_order=DESC&author=
Re: Problem in loading data for clob columns [message #417405 is a reply to message #417403] Fri, 07 August 2009 18:18 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
As BlackSwan pointed out, it helps to know what version you are on. Works fine for me on 10gR2 (10.2.0.3).

E:\>type samp.ctl
options (skip =1)
load data
infile 'e:\1.csv'
Replace into table samp
fields terminated by ","
trailing nullcols
(
no,
col1 Char(100000000),
col2 Char(100000000) enclosed by '"' and '"'
)

E:\>type 1.csv
Skip_this
1,asdf,"assasadsdsdsd""sfasdfadf""sdsdsa,ssfsf"
2,sfjass,"dksadk,kd,ss""dfdfjkdjfdk""sasfjaslaljs"

E:\>sqlldr test/test control=samp.ctl

SQL*Loader: Release 10.2.0.3.0 - Production on Fri Aug 7 19:15:46 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2

E:\>sqlplus test/test

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 7 19:15:52 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> select * from samp;

        NO COL1         COL2
---------- ------------ ----------------------------------------
         1 asdf         assasadsdsdsd"sfasdfadf"sdsdsa,ssfsf
         2 sfjass       dksadk,kd,ss"dfdfjkdjfdk"sasfjaslaljs

SQL>
Re: Problem in loading data for clob columns [message #417408 is a reply to message #417405] Fri, 07 August 2009 18:48 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Thanks for your reply.

SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE	10.2.0.3.0	Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
 


I have a huge data (app 20 pages of data) for each row of col2. Data format for col2 is in the format of given example.

In that case I am encountered with below mentioned error

ORA-01461: can bind a LONG value only for insert into a LONG column



Thanks in advance
Re: Problem in loading data for clob columns [message #417412 is a reply to message #417395] Fri, 07 August 2009 20:30 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It is amazing what can be found when SEARCH or GOOGLE are used

http://www.orafaq.com/forum/t/26226/0/
Re: Problem in loading data for clob columns [message #417413 is a reply to message #417412] Fri, 07 August 2009 22:47 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
hi,

Thanks for your reply.

I am not using any long column in table creation script.

I came to know its a bug in oracle.

Is there any other way. Where i can load clob data into table having huge data for each clob column.

Any help on this really appreciated.

Thanks in advance
Re: Problem in loading data for clob columns [message #417414 is a reply to message #417395] Fri, 07 August 2009 22:52 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I came to know its a bug in oracle.
Post Bug# and/or URL to Metalink page

>Is there any other way.
To be determined.

>Where i can load clob data into table having huge data for each clob column.
Quantify "huge" to within 2 to 4 powers of 10

Is there a threshold size below which no error occurs?

Does behaviour change when only 1 CLOB per table?

[Updated on: Fri, 07 August 2009 23:31]

Report message to a moderator

Previous Topic: SQLLoader date formatting help
Next Topic: import 10g to 9i
Goto Forum:
  


Current Time: Thu Dec 08 22:19:10 CST 2016

Total time taken to generate the page: 0.09803 seconds