CODEWITHSUNDEEP
oracle10g
dsm
dsm

Reputation: 137

Inserting data to a CLOB type in oracle

i have created a table 

create table test_clob(
  value clob
);

if i try to do an insert on this table with the data size grater than 4000 bytes i get following error

SQL Error: ORA-01704: string literal too long
01704. 00000 -  "string literal too long"
*Cause:    The string literal is longer than 4000 characters.
*Action:   Use a string literal of at most 4000 characters.
           Longer values may only be entered using bind variables.

I don't how to use the bind variables...

Upvotes: 3

Views: 14837

Answers (2)

Nallamachu
Nallamachu

Reputation: 1488

TO_CLOB converts NCLOB values in a LOB column or other character strings to CLOB values. char can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. Here is the Oracle official documentation about TO_CLOB. To insert the data into CLOB column, use the below query.

insert into tableName(
    column_name
) values (
    to_clob('charCount=>4000') || to_clob('after 4000 char but not more then 4000')
);

Upvotes: 0

Rajat Kumar
Rajat Kumar

Reputation: 1529

You can use to_clob function to insert large text in oracle database. Example like:String have more then 4000 char.then use query:

insert into tableName(fieldName)values(to_clob('charCount=>4000')||to_clob('after 4000 char but not more then 4000'));

Ex: if I have 6000 char then 4000 in first to_clob and next 2000 char next to_clob:

Upvotes: 3

Related Questions