idomongo
Published in

idomongo

🔴 Oracle DB — VARCHAR2 and CLOB concatenation

When concatenating strings in Oracle PLSQL we need to keep in mind the following:

  • “If either string is of datatype VARCHAR2, the result has datatype VARCHAR2 and is limited to 4000 characters”
  • “If either argument is a CLOB, the result is a temporary CLOB.”
  • When concatenating CLOB with VARCHAR2 the result is VARCHAR2 and you will hit the 4000 limits!

So the following code will hit an exception:

DECLARE
my_v VARCHAR2(30000);
BEGIN
my_v := my_v
|| 'a very large text...'
|| 'b very large text...'
|| 'c very large text...';

...

UPDATE tbl_x
SET
text = myclob;
COMMIT;
END;

Bummer. 😕

How can I concatenate large texts?

In my opinion, the most elegant way is using dbms_lob like so:

DECLARE
my_clob CLOB;
BEGIN
dbms_lob.createtemporary(my_clob, true);
dbms_lob.append(my_clob, 'a very large text...');
dbms_lob.append(my_clob, 'b very large text...');
dbms_lob.append(my_clob, 'c very large text...');

...

UPDATE tbl_x
SET
text = my_clob;
COMMIT;
dbms_lob.freetemporary(c);
END;

Please note: It is recommended not to skip dbms_lob.freetemporary(c) because there is a limitation for the number of open lobs per session

Yay! you are all set to go 🙌

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store