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