ORA-01461: Does SUBSTR() return a LONG if the input string is > 4000 characters?

Question

I have a NoteDetail table with a varchar(4000) field for NoteText and number(4,0) for NoteNumber. The idea is that if a note longer than 4000 characters comes in it's broken into multiple notes with incremented NoteNumber entries.

The logic for doing the inserts is below and it worked beautifully on Oracle 10. Recently the app was moved to Oracle 12c and I'm getting the error: "ORA-01461: can bind a LONG value only for insert into a LONG column" The Oracle DBA can't figure out why this is happening.

Below is the essence of what my insert code is; my best guess is that if the incoming string is long enough then the SUBSTR() function returns a long even when I'm assigning to a variable of the type varchar2(4000). For reference: Oracle SQL Developer indicates the error is happening at the point of the insert (in the loop of the else block below).

Does anyone know how to fix this?

DECLARE
  s_incoming_string  varchar2(32000);
  s_substring_value  varchar2(4000);
  i_note_iteration   number(4,0);
  i_note_iterations  number(4,0);
  i_substr_start     number(6,0);
  k_NoteId           number(19,0);
BEGIN
  SELECT noteid_seq.nextval INTO k_NoteId FROM dual;
  s_incoming_string := {a 7000 character long note};
  i_note_iterations := ceil(length(s_incoming_string)/4000);

  IF i_note_iteration = i_note_iterations THEN
    --I NEVER GET AN ERROR ON THIS BRANCH!!!
    INSERT INTO NoteDetail (NoteId, NoteNumber, NoteText)
    VALUES (k_NoteId, 1, s_incoming_string);
  ELSE
    FOR i_note_iteration IN 1..i_note_iterations
    LOOP
          i_substr_start := (4000 * (i_note_iteration - 1)) + 1;
                IF i_note_iteration = i_note_iterations THEN
                    --this is the last chunk of text; no need 
                    --to read past the end of the buffer
                  s_substring_value = SUBSTR(s_incoming_string, i_substr_start);
                ELSE
                  --I ONLY GET AN ERROR if this branch is executed before the insert:
                  s_substring_value = SUBSTR(s_incoming_string, 4000);
                END IF;

      INSERT INTO NoteDetail (NoteId, NoteNumber, NoteText)
      VALUES (k_NoteId, i_note_iteration, s_incoming_string);
    END LOOP;
  END IF;
END;

Table schema is:

    DCLARE TABLE NoteDetail (
      NoteId      number(19,0),
      NoteNumber  number(4,0),
      NoteText    varchar2(4000)
    );

Answer 1

Try using SUBSTRB and LENGTHB functions, if your text column is 4000 Bytes max. You would have to be careful piecing it back too.

Basically it comes down to multi byte character encoding. If you are using utf8 and your text contains multibyte characters, then 4000 chars is more than 4000 bytes (which is the max in your table column). You can do as krokodilko suggests and change that to 4000 char, or split the data into 4000 byte chunks (which is what the substrb and lengthb functions do).

Note that you may have an issue with a multibyte char at the very end of the 4000 byte chunk if your cutoff is exactly 4000 bytes, depending on how you want to display the data. If you piece the chunks back together first it should be fine, but test this fencepost case to be sure. I'll do a quick test myself tomorrow.

Answer 2

This is not an exact answer to your question, but rather some experiment I want to show to You.
My Oracle 12c installation has the same settings as yours:

SELECT * FROM NLS_DATABASE_PARAMETERS
WHERE parameter = 'NLS_LENGTH_SEMANTICS'
   OR  parameter like '%SET%'

PARAMETER                  VALUE        
-------------------------- ------------
NLS_NCHAR_CHARACTERSET     AL16UTF16    
NLS_CHARACTERSET           AL32UTF8   
NLS_LENGTH_SEMANTICS       BYTE

And now examine this code:

DECLARE
   s_incoming_string  varchar2(32000);
   s_substring_value  varchar2( 4000 );
BEGIN
   LOOP
      s_incoming_string := s_incoming_string || 'żaba';
      exit when length( s_incoming_string ) > 4500;
   END LOOP;
   DBMS_OUTPUT.put_line( 'Length in characters = ' || length( s_incoming_string ) );
   DBMS_OUTPUT.put_line( 'Length in bytes = ' || lengthb( s_incoming_string ) );

   s_substring_value := substr( s_incoming_string, 1, 4000 );

   DBMS_OUTPUT.put_line( 'Length in characters = ' || length( s_substring_value ) );
   DBMS_OUTPUT.put_line( 'Length in bytes = ' || lengthb( s_substring_value ) );
END;
/

If I run the above code, I get the following output:

Length in characters = 4504
Length in bytes = 5630

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 12
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

---

Now If I change a declaraion of s_substring_value to varchar2( 4000 char ); the code works without any error and it gives the following result:

DECLARE
   s_incoming_string  varchar2(32000);
   s_substring_value  varchar2( 4000 char );
BEGIN
   LOOP
      s_incoming_string := s_incoming_string || 'żaba';
      exit when length( s_incoming_string ) > 4500;
   END LOOP;
   DBMS_OUTPUT.put_line( 'Length in characters = ' || length( s_incoming_string ) );
   DBMS_OUTPUT.put_line( 'Length in bytes = ' || lengthb( s_incoming_string ) );

   s_substring_value := substr( s_incoming_string, 1, 4000 );

   DBMS_OUTPUT.put_line( 'Length in characters = ' || length( s_substring_value ) );
   DBMS_OUTPUT.put_line( 'Length in bytes = ' || lengthb( s_substring_value ) );

END;
/

Length in characters = 4504
Length in bytes = 5630
Length in characters = 4000
Length in bytes = 5000

PL/SQL procedure successfully completed.

---

More on this issue you can find here: NLS_LENGTH_SEMANTIC

---

Note: polish character ż is encoded as two bytes in UTF8;

select dump('ż') as x from dual;
X                    
---------------------
Typ=96 Len=2: 197,188

so the string: żaba (in english: frog) takes 5 bytes, not 4.

---

Note 2: you need also to alter a table definition:

ALTER TABLE NoteDetail 
MODIFY  NoteText    varchar2(4000 char);