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

Question

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');

What application do you use to insert/update data? SQL*Plus, PHP, Java,...? Can you post your code? In [Performing basic string operations on CLOBs](http://www.java2s.com/Tutorial/Oracle/0660__Large-Objects/PerformingbasicstringoperationsonCLOBs.htm) you can view a sample. — dani herrera, Jan 10 '12 at 10:36

Sathyajith Bhat · Accepted Answer · 2012-01-10T11:39:54.857

25

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.

edited Jan 10 '12 at 11:39

answered Jan 10 '12 at 11:16

Sathyajith Bhat

21,321
22
95
134

1

Thank sathya but if it's 3,00,000 or greater it again shows error like ORA-06550 PL-sql-00172:string literal too long – Manohar Kulanthai vel Jan 10 '12 at 11:29
1

please give some tips to use bind variables – Manohar Kulanthai vel Jan 10 '12 at 11:51
Search using Google, you'll find plenty of examples http://www.java2s.com/Tutorial/Oracle/0440__PL-SQL-Statements/UsingMultipleBindVariables.htm http://psoug.org/reference/bindvars.html @ManoharKulanthaivel – Sathyajith Bhat Jan 10 '12 at 12:05
DECLARE v_long_text CLOB; BEGIN v_long_text := 'your long string of text'; update table set col1 =1 col2=:v_long_text where col3='in'); END;it is not working.please give write syntax. – Manohar Kulanthai vel Jan 11 '12 at 10:05
@ManoharKulanthaivel [search for execute immediate](http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/dynamic.htm#CHDGJEGD), if you want to use bind variables. I still don't believe that you'll enter the 32k+ character manually - if you're fetching the lengthy string from a table or XML file, my solution should work fine. – Sathyajith Bhat Jan 11 '12 at 10:18
i get that values from xml than i encrypt it.than i update to the table. but during update it hang. in sql*plus it show string literal too long.your insert statement working fine instead of please give update query. – Manohar Kulanthai vel Jan 11 '12 at 10:24
update pl/sql execute successfully but not affect the table.i'm using toad for oracle. – Manohar Kulanthai vel Jan 11 '12 at 12:22
@SathyajithBhat I am the co-maintainer of a tool that generates SQL to do database migrations (FluentMigrator). My tool can generate 32k+ characters. What code do you suggest I generate to avoid this problem? Can I concatenate two `CLOB`s together using `||` to get up to 64k characters, and so on? – John Zabroski Feb 17 '20 at 15:47
2

@JohnZabroski if you concatenate them - you'd still run into the issue, wouldn't you as this is a limit on the variable itself. I haven't been on PL/SQL for about half a decade now so I cannot answer that for sure – Sathyajith Bhat Mar 10 '20 at 18:18
@SathyajithBhat FluentMigrator 3.2.8 now supports this workaround transparently to the user of FluentMigrator, so it does indeed work. – John Zabroski Jul 30 '20 at 18:28
@JohnZabroski awesome to hear that – Sathyajith Bhat Aug 01 '20 at 10:14

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

1 Answers1

Linked