Varchar2 Data Type

 Test Case 1: Insert exact length

Query: 

CREATE TABLE varchar2_test_1 (val VARCHAR2(5));

INSERT INTO varchar2_test_1 VALUES ('Hello');

SELECT val, LENGTH(val) FROM varchar2_test_1;

-- Output: 'Hello', LENGTH = 5

 

 Test Case 2: Insert shorter string

Query: 

CREATE TABLE varchar2_test_2 (val VARCHAR2(5));

INSERT INTO varchar2_test_2 VALUES ('Hi');

SELECT val, LENGTH(val) FROM varchar2_test_2;

-- Output: 'Hi', LENGTH = 2

-- Explanation: Stored as is, no padding

 

 Test Case 3: Insert longer string (error)

Query: 

CREATE TABLE varchar2_test_3 (val VARCHAR2(5));

INSERT INTO varchar2_test_3 VALUES ('Welcome');

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

 

 Test Case 4: Trailing spaces preserved and compared

Query: 

CREATE TABLE varchar2_test_4 (val VARCHAR2(5));

INSERT INTO varchar2_test_4 VALUES ('ABC  ');

SELECT val, LENGTH(val) FROM varchar2_test_4;

-- Output: 'ABC  ', LENGTH = 5

 

 Test Case 5: Comparison considers trailing spaces

Query: 

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

-- Output: Not Equal

-- Explanation: Unlike CHAR, VARCHAR2 does not ignore trailing spaces

 

 Test Case 6: NULL vs empty string

SELECT CASE WHEN '' IS NULL THEN 'NULL' ELSE 'Not Null' END AS result FROM dual;

-- Output: NULL

-- Oracle treats empty string as NULL

 

 Test Case 7: VARCHAR2(n CHAR) with multibyte characters

Query: 

CREATE TABLE varchar2_test_7 (val VARCHAR2(5 CHAR));

INSERT INTO varchar2_test_7 VALUES ('你好啊');  -- Chinese for "Hi" (3 characters)

SELECT val, LENGTH(val), LENGTHB(val) FROM varchar2_test_7;

-- Output: val = '你好啊', LENGTH = 3, LENGTHB = depends on character set (e.g., 9 bytes in UTF-8)

 

 Test Case 8: Insert special characters

Query: 

CREATE TABLE varchar2_test_8 (val VARCHAR2(20));

INSERT INTO varchar2_test_8 VALUES ('Hello$%^@!');

SELECT val FROM varchar2_test_8;

-- Output: 'Hello$%^@!'

 

Test Case 9: Leading and trailing spaces

Query: 

CREATE TABLE varchar2_test_9 (val VARCHAR2(10));

INSERT INTO varchar2_test_9 VALUES ('  abc  ');

SELECT val, LENGTH(val) FROM varchar2_test_9;

-- Output: '  abc  ', LENGTH = 7

-- Oracle preserves both leading and trailing spaces in VARCHAR2