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?

score 2 · Accepted Answer · answered Jan 08 '16 at 15:52

2

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.

answered Jan 08 '16 at 15:52

Alex Poole

183,384
11
179
318

How would you change this for Oracle 11? I believe the 32767 won't work. – Patrick Szalapski Jan 08 '16 at 17:22
1

I tested this in 11gR2. A `varchar2(32767)` is OK in PL/SQL, and always has been I think; it's the SQL limit that increased from 4000 to 32k in 12c. I used fake data though, forcing multibyte characters around the 4k mark to make sure it coped; testing with your real data might throw something up still. (And I'm sure you can come up with a better name for the function!) – Alex Poole Jan 08 '16 at 17:24

score 0 · Answer 2 · answered Dec 22 '18 at 18:56

This is also answered in Oracle note 1571041.1, which suggests creating a function like this

function clob_substrb(p_clob in clob, p_length in pls_integer, p_position in pls_integer) return varchar2 is
begin
  return rtrim(substrb(dbms_lob.substr(p_clob, p_length, p_position), p_position, p_length));
end clob_substrb;

score 0 · Answer 3 · answered Jun 02 '23 at 22:10

I wrote this solution for CLOBs:

FUNCTION get_max_substr (
  clob_ IN CLOB) RETURN VARCHAR2
IS
   out_ VARCHAR2(4000 BYTE);

   err_sig_from_parallel_server_ EXCEPTION;
   PRAGMA EXCEPTION_INIT(err_sig_from_parallel_server_, -12801);

   char_string_buffer_too_small_ EXCEPTION;
   PRAGMA EXCEPTION_INIT(char_string_buffer_too_small_, -6502);

   insertd_val_too_large_for_col_ EXCEPTION;
   PRAGMA EXCEPTION_INIT(insertd_val_too_large_for_col_, -1401);

   string_concatenation_too_long_ EXCEPTION;
   PRAGMA EXCEPTION_INIT(string_concatenation_too_long_, -1489);
BEGIN
   FOR loop_counter_ IN 0..4000 LOOP
      BEGIN
         out_ := dbms_lob.substr(clob_, 4000 - loop_counter_, 1);
         RETURN out_;
      EXCEPTION
         WHEN err_sig_from_parallel_server_ THEN
            NULL;
         WHEN char_string_buffer_too_small_ THEN
            NULL;
         WHEN insertd_val_too_large_for_col_ THEN
            NULL;
         WHEN string_concatenation_too_long_ THEN
            NULL;
      END;
   END LOOP;
   RETURN NULL; -- suppress PLW-05005
END get_max_substr;

I got the inspiration for this from another answer: DBMS_LOB.SUBSTR() throwing "character string buffer too small" error

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

3 Answers3