Oracle 10gR2 CLOB Data type

Question

I am having table with clob column & trying to insert SIGNED character data which contains =176048 characters, it throws error has Insert exception data transaction java.sql.SQLException: ORA-01704: string literal too long

Answer 1

You can only insert string literals of at most 4000 characters. Otherwise you need to use a bind variable.

Answer 2

From this AskTom answer:

Yes, you can insert upto 4000 bytes in an INSERT statement -- just

insert into t ( clob_col ) values ( 'Hello World' );

it works. For larger chunks, you would insert an EMPTY_CLOB() and then using that newly created lob locator write to it in chunks. It would look like this:

begin
  insert into t ( clob_col ) values ( empty_clob() )
  returning clob_col into Local_Variable;

  dbms_lob.write( local_variable, .... );

end; 
/

in plsql.