How do I insert more than 4000 characters to a CLOB type column?
--create test table s
create table s
(
a clob
);
insert into s values('>4000 char')
Results in an error:
ORA-01704:the string too long.
I want to insert a string of >4000 characters one time. How do I do it? Is it possible?
When I read the Oracle reference, CLOB
can save max 4GB(Gigabyte)?
The maximum for one time insertion is 4000 characters (the maximum string literal in Oracle). However you can use the lob function dbms_lob.append()
to append chunks of (maximum) 4000 characters to the clob:
CREATE TABLE don (x clob);
DECLARE
l_clob clob;
BEGIN
FOR i IN 1..10
LOOP
INSERT INTO don (x) VALUES (empty_clob()) --Insert an "empty clob" (not insert null)
RETURNING x INTO l_clob;
-- Now we can append content to clob (create a 400,000 bytes clob)
FOR i IN 1..100
LOOP
dbms_lob.append(l_clob, rpad ('*',4000,'*'));
--dbms_lob.append(l_clob, 'string chunk to be inserted (maximum 4000 characters at a time)');
END LOOP;
END LOOP;
END;