Oracle DBA Lesson 21C — Characters, Bytes, and Text-Column Limits

José is four characters and, in an AL32UTF8 database, five bytes. LENGTH counts the characters. LENGTHB counts the bytes. A VARCHAR2(4 BYTE) column rejects the name. A VARCHAR2(4 CHAR) column accepts it. Character semantics change the unit of the limit. They do not change how many bytes Oracle writes.

Oracle DBA Lesson 21C — Characters, Bytes, and Text-Column Limits

Lesson 21 is why those bytes exist: the database character set is the map, and in AL32UTF8 the é in José is c3,a9. Lesson 21B is which column uses that map. VARCHAR2 uses the database character set. This part is the limit on that column, and why "four letters" is not four bytes. The videos are Lesson 21 and Lesson 21B.

Count characters, then count bytes

In AL32UTF8, a character from the ASCII range is one byte. J, o, and s are one byte each. é is two. José is four characters and five bytes: 4a,6f,73,c3,a9. Caf is the same shape, four characters and five bytes. The accent is not a decoration that rides for free on e.

-- After the client bytes have been converted into the
          -- database character set. On AL32UTF8, honest input:
          -- characters = 4, bytes = 5.
          SELECT LENGTH('José')  AS characters,
                 LENGTHB('José') AS bytes
                 FROM   dual;

LENGTH uses the database character set's idea of a character. LENGTHB uses storage. They match on a single-byte character set such as WE8MSWIN1252, where é is the single byte e9. They diverge as soon as the database character set is AL32UTF8. If the client declaration is wrong, you are measuring the wrong string. Lesson 21 showed UTF-8 bytes for é declared as WE8ISO8859P1 landing as two characters. LENGTH and LENGTHB then describe that damaged value, not José. Fix the declaration before you trust the counts.

Two cousins are worth knowing and easy to misuse. LENGTHC counts Unicode complete characters, so a base character plus a combining mark can come back as one. LENGTH2 counts UCS-2 code units. A character outside the basic multilingual plane, the ones that take four bytes in AL32UTF8, counts as two there and as one in LENGTH. A grinning face emoji is one character, four bytes, and two UCS-2 code units. VARCHAR2(n CHAR) follows character length, the same unit as LENGTH, not grapheme clusters and not LENGTH2. A name that looks like one accented letter can still be two characters if the accent is a combining mark rather than the precomposed é.

BYTE rejects the name. CHAR accepts the count.

The default unit for VARCHAR2(n) and CHAR(n) is bytes. That default is NLS_LENGTH_SEMANTICS, and the instance value is BYTE. A column written as VARCHAR2(4) is VARCHAR2(4 BYTE) unless the session that created it had changed the parameter. Jos at five bytes does not fit. The insert fails with ORA-12899, value too large for the column. The error names the actual length and the maximum. Read it. Actual 5, maximum 4, is this bug and not a character-set rejection. AL32UTF8 can store the name. The column cannot hold the bytes.

-- Say the unit. Do not inherit it from the session.
          -- José is 4 characters and 5 bytes.
          -- (4 BYTE) raises ORA-12899. (4 CHAR) stores those 5 bytes.
          CREATE TABLE customer_name (
            name_byte VARCHAR2(4 BYTE),
              name_char VARCHAR2(4 CHAR)
              );

VARCHAR2(4 CHAR) allows four characters. José is four characters, so the insert succeeds. The file still holds five bytes. Character semantics changed the comparison Oracle makes at the door. They did not encode é as one byte, and they did not pad or shrink the value. LENGTH on the stored name is 4. LENGTHB is 5.

The column's declared ceiling is wider than that one value. For AL32UTF8, Oracle sizes a character-length column for the widest character in the set, which is four bytes. VARCHAR2(4 CHAR) therefore has CHAR_LENGTH 4, CHAR_USED C, and DATA_LENGTH 16. Sixteen is the reservation, not the size of José. VARCHAR2(4 BYTE) has CHAR_USED B and DATA_LENGTH 4. Look at those three columns before you argue with an application about "the field is four."

-- DATA_LENGTH is the byte reservation, not the size of one name.
          -- CHAR_USED: B = byte semantics, C = character semantics.
          -- On AL32UTF8, VARCHAR2(4 CHAR) reserves 16 bytes.
          SELECT column_name, data_length, char_length, char_used
          FROM   user_tab_columns
          WHERE  table_name = 'CUSTOMER_NAME'
          ORDER  BY column_id;

The byte cap does not go away

With MAX_STRING_SIZE at its default, STANDARD, a VARCHAR2 value cannot exceed 4,000 bytes. CHAR cannot exceed 2,000 bytes. EXTENDED, from 12c on, raises the VARCHAR2 cap to 32,767 bytes. Character semantics do not raise either cap. A value that is legal as a character count and illegal as a byte count still fails. ORA-12899 is that failure on insert or update. ORA-00910 is the earlier one, at CREATE or ALTER, when the declaration itself cannot exist.

Oracle checks the declaration against the widest character. On AL32UTF8 that width is four bytes, so a character-length VARCHAR2 under STANDARD cannot be declared longer than 1,000 characters: 1,000 times 4 is 4,000, and 1,001 times 4 is over the cap. VARCHAR2(4000 CHAR) on that database is not a 4,000-character name. It is an illegal column. Under EXTENDED the same arithmetic stops at 8,191 characters, because 8,192 times 4 is 32,768. People coming from a single-byte database ask for VARCHAR2(4000 CHAR) and the statement dies before any row is loaded. The fix is a character length that fits in the byte cap, or EXTENDED if you have actually planned for it. EXTENDED is not a session switch. It is a database migration of the string-size mode, with its own downtime and dictionary steps.

The same reservation is what an index sees. The key uses DATA_LENGTH, the maximum bytes, not the characters you hope to store. A "short" name column in character semantics can still be too wide for an index key and raise ORA-01450. Check the byte reservation before you index the column, not after the build fails on a Friday.

Say the unit on the column

NLS_LENGTH_SEMANTICS supplies BYTE or CHAR only when the datatype omits both words. It does not rewrite columns that already exist. It does not apply to NCHAR or NVARCHAR2. Those are character length on the national lane, as Lesson 21B covered, and they still hit their own byte cap. It also does not apply to tables created in SYS. Those stay byte semantics even if the session says CHAR.

-- Instance default is BYTE. Leave it there.
          -- A session change affects later DDL in this session only,
          -- and not objects in SYS.
          SELECT value
          FROM   nls_session_parameters
          WHERE  parameter = 'NLS_LENGTH_SEMANTICS';

Oracle's own guidance is to leave the instance parameter at BYTE. Scripts and supplied packages were written for that default. Setting CHAR in the server parameter file makes later dictionary work create character-length columns by accident. For a name, an address, or a city, put CHAR on that column. Eighty characters is a name. Eighty bytes is forty é and twenty emoji, and the application will not discover which until a customer has that name.

Widening a column is the repair for ORA-12899 when the business length was simply too small:

-- Existing bytes stay as they are. The door gets wider.
          -- Shrinking raises ORA-01441 if any value no longer fits.
          ALTER TABLE customer_name
            MODIFY name_byte VARCHAR2(80 CHAR);

Do not "fix" the error by stripping accents in the application. That stores a different name. And do not switch the column to NVARCHAR2 because one value had an accent. On AL32UTF8 the database character set already holds José. The national lane is a second map, not a longer VARCHAR2.

Where else a byte cut shows up

SUBSTR counts characters. SUBSTRB counts bytes and can cut through é. The piece you get is not a character. Use SUBSTR on names. Use SUBSTRB when you are slicing a raw payload and you mean bytes. The same split exists for INSTR and INSTRB.

A bind from a client that thinks in bytes, or a column copied into a VARCHAR2(n BYTE) interface table, fails the same ORA-12899 even though the source column was character semantics. The target declaration is what counts. Data Pump moves the column definition, including CHAR or BYTE, and converts the bytes if the target database character set differs. A target that is still WE8MSWIN1252 can store José in one byte per character and then reject a later character that Latin-1 cannot hold. The length semantics did not save that character. The character set did the damage. Check both.

Gotchas

• On AL32UTF8, José is LENGTH 4 and LENGTHB 5. J, o, and s are one byte. é is c3,a9.
• VARCHAR2(4 BYTE) rejects that name with ORA-12899. VARCHAR2(4 CHAR) stores it. The stored value is still five bytes.
• VARCHAR2(4) means bytes, unless the session that created it had NLS_LENGTH_SEMANTICS=CHAR. Write BYTE or CHAR on the column.
• Leave the instance parameter at BYTE. It does not alter existing columns, it does not affect NVARCHAR2, and tables in SYS stay byte semantics anyway.
• DATA_LENGTH for VARCHAR2(4 CHAR) on AL32UTF8 is 16, the worst case of four bytes each. That is the reservation and the index width, not the size of José.
• Character semantics do not lift the byte cap. Under STANDARD, VARCHAR2(4000 CHAR) is illegal on AL32UTF8 because 4,000 characters times 4 bytes exceeds 4,000 bytes. The declarable maximum is 1,000 characters. ORA-00910 is that failure.
• EXTENDED raises the cap to 32,767 bytes. It is a database change, not an ALTER SESSION. A four-byte character still costs four bytes.
• An emoji is one character and four bytes. LENGTH2 counts it as two. LENGTHC can treat a base character plus a combining mark as one complete character. The column limit uses character length, not what the font draws as one glyph.
• SUBSTRB can split . SUBSTR does not. An index on a wide character-length column uses the byte reservation and can raise ORA-01450.
• Shrinking a column that already holds a long name raises ORA-01441. Widening to VARCHAR2(n CHAR) keeps the bytes and changes the door.

Quick quiz

1. The database character set is AL32UTF8, and the client declaration is honest. What do LENGTH('José') and LENGTHB('Jos') return?

4 and 4. An accent does not add a byte. 4 and 5. 5 and 4. LENGTH counts bytes. 8 and 8. AL32UTF8 stores every character in two bytes.

2. You insert José into VARCHAR2(4 BYTE) on an AL32UTF8 database. What happens?

It is stored. The column counts four characters. Oracle drops the accent and stores Jose. ORA-12899. The value is 5 bytes and the column allows 4. The value is silently moved to an NVARCHAR2 column.

3. The same name is inserted into VARCHAR2(4 CHAR). What is true?

The insert succeeds. The stored value is still 5 bytes. The insert succeeds and LENGTHB becomes 4. ORA-12899, because CHAR semantics also means four bytes. The insert succeeds only if NLS_CHARACTERSET is WE8MSWIN1252.

4. MAX_STRING_SIZE is STANDARD and the database character set is AL32UTF8. You run CREATE TABLE t (name VARCHAR2(4000 CHAR)). What happens?

The column is created and can hold 4000 characters of any kind. The column is created as VARCHAR2(4000 BYTE). It works, because character semantics removes the byte cap. The declaration is too long. Four bytes times 4000 characters exceeds 4000 bytes.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro