Casting a column value as VARCHAR2() that holds 9 characters (TOPIC: ORACLE SQL & PL for Developers)

Question

I've cast a column value as VARCHAR2(9) in my query to make it as a varchar2() that hold 9 characters.

It's been suggested to me that VARCHAR2(10) works better and should be a solution to any requirement that asks for VARCHAR2 capable of holding 9 characters.

Expert advice?

Answer 1

• If you need to hold 9 bytes of characters then use VARCHAR2(9 BYTE).
• If you need to hold 9 characters (of any byte width) then use VARCHAR2(9 CHAR).
• If you need to hold 10 bytes of characters then use VARCHAR2(10 BYTE).
• If you need to hold 10 characters (of any byte width) then use VARCHAR2(10 CHAR).

We can't advise you what to use but if you only want 9 characters then use VARCHAR2(9 BYTE) or VARCHAR2(9 CHAR) (as appropriate for the type of characters you will be storing).

It's been suggested to me that VARCHAR2(10) works better and should be a solution to any requirement that asks for VARCHAR2 capable of holding 9 characters.

I suggest you get that person to explain why they are suggesting adding another character; because if you only need to store 9 characters then specifying a limit of 10 seems to be wrong.

---

For example:

If you have the table:

CREATE TABLE table_name ( value VARCHAR2(10) );

And try to insert 9 unicode characters (at 3-bytes each):

INSERT INTO table_name ( value )
  VALUES ( UNISTR( '\2600\2601\2602\2603\2604\2605\2606\2607\2608' ) );

An exception is raised:

ORA-12899: value too large for column "SCHEMA_NAME"."TABLE_NAME"."VALUE" (actual: 27, maximum: 10)

However:

CREATE TABLE table_name ( value VARCHAR2(9 CHAR) );

INSERT INTO table_name ( value )
  VALUES ( UNISTR( '\2600\2601\2602\2603\2604\2605\2606\2607\2608' ) );

Works without error and inserts the 9 unicode characters.

db<>fiddle here