How to take the longest string that will fit in a VARCHAR2(4000) from a NCLOB?

Question

I would like to take the most I can of the first 4000 characters to fit into a 4000-byte maximum field (paradoxically, 4000 bytes is the limit even for misleading VARCHAR2(4000 CHAR) fields in Oracle 11).

The alternatives I see are:

• substr(NCLOB_COLUMN, 1, 4000) -- no good, 4000 characters can often be more than 4000 bytes

• dbms_lob.substr(NCLOB_COLUMN, 4000, 1) -- no good, 4000 characters can often be more than 4000 bytes (see also How to Copy Only First 4000 Characters from Oracle NCLOB to NVARCHAR2(4000) Value?)

• substr(NCLOB_COLUMN, 1, 1000) -- no good, I can usually get a lot more than 1000 characters

• substrb(NCLOB_COLUMN, 1, 4000) -- no good, generates ORA-22998 error

Any other ideas?

Answer 1

Presumably you don't want to end up with a partial character - e.g. the first byte of a multibyte character - so you could get the first 4000 characters (which may be more than 4000 bytes) and then repeatedly strip off the last one until the number of bytes hits 4000 or lower:

create or replace function nclob_4k(p_nclob in nclob)
return varchar2
as
  l_varchar2 varchar2(32767);
begin
  l_varchar2 := dbms_lob.substr(p_nclob, 4000, 1);
  while lengthb(l_varchar2) > 4000 loop
    l_varchar2 := substr(l_varchar2, 1, length(l_varchar2) - 1);
  end loop;
  return l_varchar2;
end;
/

Then you can select nclob_4k(your_nclob_column) from your table, or use that for an insert.