Saturday, September 21, 2013

Inserting CLOB data which is more than 4000 characters into Oracle Table

I was trying to insert 4000+ characters data into an Oracle table having CLOB as data type, but could not do that.. using simple insert statement

Table Structure which I used is as below

create table ClobTest
(id number,
clobData clob
);

Table structure is pretty simple, having only 2 columns a number and a clob data type.

Now... if you run below query it will get execute without any error

insert into ClobTest values(1, 'ClobTestData');

However, when you increase the length of your data set 4000+ it will not succeed.and will give error as below

Error report:
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.

After googling this issue, came to know that, if you want to insert 4000+ char data into CLOB data type, try to do that using PL-SQL block.

Root cause of this is, Oracle SQL do not support char / varchar data type which is more than 4000 characters; however PL-SQL does so.

I used below anonymous pl-sql to insert 4000+ character data into CLOB data type

declare

vClob varchar(8000);

begin

vClob := '<Charater data having 4000+ characters>';

insert into ClobTest values(1, vClob);

end;

To verify if really 4000+ characters got inserted into target executed below query

select length(clobData) from ClobTest

13 comments:

  1. This was really helpful. Thanks for Posting!

    ReplyDelete
  2. Great and simple solution ...Thanks man

    ReplyDelete
  3. Yes it is simple and it worked for me and saved the time. Thanks again

    ReplyDelete
  4. E para ler isso com mais de 4000, pois não adianta gravar 8000 e só consultar 4000 ?
    Exemplo:
    select dbms_lob.substr( t.TEMPLATE_CLOB, 8000, 1 )
    from TIPORELATORIO T

    ORA-06502: PL/SQL: erro: character string buffer too small numérico ou de valor
    ORA-06512: em line 1

    ReplyDelete
  5. Thanks dude for such a simple solution

    ReplyDelete
  6. This throws a syntax error like if cant declare that variable and is expecting other thing

    ReplyDelete
  7. thank you so much buddy, this is exactly I want.. was stuglling from past several days..you saved me. thanks a lot man.

    ReplyDelete
  8. did not worked for me I have to insert 60500 characters.

    error:
    Error report -
    ORA-06550: line 4, column 10:
    PLS-00172: string literal too long
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:

    ReplyDelete
  9. This was really helpfull much appreciated. :)

    ReplyDelete

All about CSS

From book HTML & CSS - Design and Build Websites - Jon Duckett CSS works by associating rules with HTML elements. These rules govern how...