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:

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

   DBMS_OUTPUT.put_line (v_title);
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);

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 ...

Get Oracle PL/SQL Programming, 5th Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.