Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to insert/update larger size of data in the Oracle tables?

I want to insert large size of data that character length is more than 10,000. I used CLOB data type to each column. I can't insert/update that large data it shows following error:

ORA-01704: string literal too long

My code

 insert into table1 value(1,'values>10000'); 
like image 316
Manohar Kulanthai vel Avatar asked Jan 10 '12 10:01

Manohar Kulanthai vel


People also ask

How can insert large volume data in Oracle?

Because the insert into select is the best bulk you can load. The fastest would be to disable the indexes (mark them unusable) and do this in a SINGLE insert: insert /*+ append */ into TARGET select COLS from SOURCE; commit; and rebuild the indexes using UNRECOVERABLE (and maybe even parallel).

Which is faster insert or update Oracle?

Insert is more faster than update because in insert there's no checking of data.

Can I update multiple rows in Oracle?

Can I update multiple rows in Oracle? Best Answer Ya. It can be done.


1 Answers

You'll have to assign the value to a variable & use the variable to insert the data

DECLARE
    v_long_text CLOB;
BEGIN
    v_long_text := 'your long string of text';

    INSERT INTO table
    VALUES      (1,
                 v_long_text);
END; 

To make it clear: there are limits set to character strings:

you cannot have a string literal over

  • 4000 bytes in SQL
  • 32k in PLSQL

If you want to go above this, you'll have to use bind variables.

like image 118
Sathyajith Bhat Avatar answered Oct 06 '22 00:10

Sathyajith Bhat