Re: problem insert on clob field

Bruintje Beer wrote:
> I have a problem inserting a clob field in a database table. My table
> create table T1(id int, xmlmsg clob);
> when I do an insert on this table with a big xmlmsg i get an error that he
> string literal is too long.
>
> insert into T1(id, xmlmsg) values(1, 'her comes a very big xml string');

Instead of a huge string literal, you can use SQL*Loader:

• CREATE TABLE tmpt1 ( id int, filename varchar2(4000), xmlmsg clob );
• Save XML text in "myxml1.xml", "myxml2.xml", etc.
• Create SQL*Loader control file "tmpt1.ctl" with these lines:
  • Cut here --------- load data infile * into table tmpt1 truncate fields terminated by ' ' ( id, filename, xmlmsg lobfile(filename) terminated by eof ) begindata 1 myfile1.xml 2 myfile2.xml
  • Cut here ---------
• Run sqlldr userid=myuser/passwd_at_mysid control=tmpt1.ctl
  • Then INSERT INTO t1 SELECT id, xmlmsg FROM tmpt1; DROP TABLE tmpt1;

This works for me. Your milesage may vary. Note that it may be better or worse that your XML files are separated out. Also note that single quotes would have to be escaped (doubled) using literals whereas w/ this they do not.

If you are always adding (not updating), you can just allow the "filename" column to live permanently in T1, then you dont' nee the temp table. But make sure to change "truncate" to "append" in the control file.

Hope that helps.

John Hinsdale Received on Tue Jan 16 2007 - 12:04:34 CST