CHAR Data Type

 Test Case 1: Insert exact length

Query: 

CREATE TABLE char_test_1 (val CHAR(5));

INSERT INTO char_test_1 VALUES ('Hello');

SELECT val, LENGTH(val) FROM char_test_1;

-- Output: 'Hello', LENGTH = 5

 

 Test Case 2: Insert shorter string (padding)

Query: 

CREATE TABLE char_test_2 (val CHAR(5));

INSERT INTO char_test_2 VALUES ('Hi');

SELECT val, LENGTH(val), DUMP(val) FROM char_test_2;

-- Output: 'Hi   ', LENGTH = 5

-- Explanation: Oracle pads with 3 spaces

 

 Test Case 3: Insert longer string (error)

Query: 

CREATE TABLE char_test_3 (val CHAR(5));

INSERT INTO char_test_3 VALUES ('Welcome');

-- ORA-12899: value too large for column "CHAR_TEST_3"."VAL" (actual: 7, maximum: 5)

 

 Test Case 4: Trailing space preserved

Query: 

CREATE TABLE char_test_4 (val CHAR(5));

INSERT INTO char_test_4 VALUES ('ABC');

SELECT val, LENGTH(val) FROM char_test_4;

-- Output: 'ABC  ', LENGTH = 5

 

 Test Case 5: Comparison ignores trailing spaces

Query: 

SELECT CASE WHEN 'ABC' = 'ABC     ' THEN 'Equal' ELSE 'Not Equal' END AS result FROM dual;

-- Output: Equal

-- Explanation: Oracle ignores trailing spaces in `CHAR` comparison

 

 Test Case 6: CHAR vs VARCHAR2 comparison

Query: 

SELECT 

 CASE WHEN CAST('ABC' AS CHAR(5)) = CAST('ABC' AS VARCHAR2(5))

 THEN 'Equal'  

 ELSE 'Not Equal'  

 END AS result FROM dual;

-- Output: Equal

-- Even though CHAR(5) stores 'ABC  ', Oracle trims trailing spaces for comparison.

 

 Test Case 7: CHAR(n CHAR) vs CHAR(n BYTE)

Query: 

-- For multi-byte character sets (like UTF-8),

n CHAR reserves n characters regardless of byte size.

CREATE TABLE char_test_7 (val CHAR(5 CHAR));

INSERT INTO char_test_7 VALUES ('こんにちは');  -- Japanese "Hello" (5 characters, >5 bytes)

SELECT val FROM char_test_7;

-- Output: 'こんにちは'