Re: problem insert on clob field

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 16 Jan 2007 15:22:31 -0800
Message-ID: <1168989745.786167@bubbleator.drizzle.com>

Bruintje Beer wrote:
> "sybrandb" <sybrandb_at_gmail.com> schreef in bericht
> news:1168967109.803189.156400_at_11g2000cwr.googlegroups.com...

>>
>> On Jan 16, 5:10 pm, "Bruintje Beer" <m..._at_knoware.nl> wrote:
>>> Hi,
>>>
>>> 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.
>>>
>>> My sql code
>>>
>>> insert into T1(id, xmlmsg) values(1, 'her comes a very big xml string');
>>>
>>> gives me the oracle error. How can I insert my xmlmsg column.
>>>
>>> thanks
>>>
>>> John
>> Please post
>> - your version
>> - the actual code
>> - the *exact* oracle error
>>
>> Your string is apparently treated as a varchar2 (4000 bytes) max.
>>
>>
>> -- 
>> Sybrand Bakker
>> Senior Oracle DBA
>>

> Hi,
>
> I am using Oracle 10g.
> Error code : ORA-01704: string literal too long
>
> sqlplus username/passwd_at_host/SID @mysql.sql
>
> and mysql.sql contains the sql
>
> insert into T1(id, xmlmsg) values(1, 'veryyyy long xml string');
> insert into T1(id, xmlmsg) values(2, 'veryyyy long xml string');
>
> John

Try TO_CLOB('veryyy long xml string')

But why are you storing XML in the database? It is hopelessly inefficient. Can't you store relationally and reconstitute the XML on the fly.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Jan 16 2007 - 17:22:31 CST