Inserted large strings in CLOB column.

[arielt]

Hello All:
I'm tryoing to insert a large string in a clob column using sqlplus and the insert statement:
INSERT INTO MYTABLE VALUES( 'large string');
and I get an error saying 'string literal too long'.
Is there anyway to solve this?
thanks

 

[Bimalaggarwal]

Writing into CLOB column looks a little bit tricky.
The usual insert string works if its length is less than 2000 b.

In case it exceeds, I guess we have to follow a work-around.
I have not worked on CLOBs as yet. But you may have to use the built-in empty_clob() to initialize the CLOB ,and dbms_lob.write (or writeappend) somehow.
May be somebody can enlighten us further on this.

 

[SirCharles]

I've run into same problem trying to insert a clob as I get 'literal too long' error on string/field >4000 chars.

Ex. insert into table (char_field1, char_field2, long_field or clob_field) values ('$value1', '$value2', '$big_value3')

I tried using the empty_clob() instead of long_field and the returning long_field into :long_field to no avail.

Though it compiled (in PHP), the 'literal too long' was thrown again with the returning ...

 

[SirCharles]

I solved problem with concatenation.

 

[LOLLA]

Hi arielt

Im facing now the same problem and im wondering if you find a solution to it, Please help me.

Thanks

 

[issahar]

Hi!
I used this sprintf template for strings till 32000 length:

BEGIN EXECUTE IMMEDIATE 'Declare newHsRecID NUMBER(10);
bigNewValue VARCHAR2(32000);
BEGIN
bigNewValue:=SUBSTR(''%s'', 1, 32000);
insert into TABLE1 (ID, DATA)
values (SEQ.NextVal, bigNewValue); END;';
END;

For bigger values, I think, it can help to concatenate them.
Please, notify me if it helped you.


Issahar Noam Gourfinkel
senior software engineer

Warecase

http://www.warecase.com