Oracle PL/SQL Programming, 5th Edition

Character Semantics

Undoubtedly, one of the first issues you will run into when localizing your application is support for multibyte characters. When you pass your first Japanese characters to a VARCHAR2 variable and experience an ORA-6502 error, you will likely spend an hour debugging your procedure that “should work.”

At some point, you may realize that every declaration of every character variable or character column in your application will have to be changed to accommodate the multibyte character set. You will then, if you are anything like me, consider for a moment changing careers. Don’t give up! Once you work through the initial challenges, you will be in a very strong position to guide application implementations in the future.

Consider the following example:

DECLARE
   v_title   VARCHAR2 (30);
BEGIN
   SELECT title
     INTO v_title
     FROM publication
    WHERE publication_id = 2;

   DBMS_OUTPUT.put_line (v_title);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;

It returns the following exception:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

It failed because the precision of 30 is in bytes, not in characters. A number of Asian character sets have up to 3 bytes per character, so it’s possible that a variable with a precision of 2 will actually not support even a single character in your chosen character set!

Using the LENGTHB function I can determine the actual size of the string:

DECLARE v_length_in_bytes NUMBER (2); BEGIN SELECT ...