Maximum length supported by Oracle varchar2

On the Internet, I often ask what is the maximum length of Oracle varchar2? In fact, this name is not accurate. varchar2 is used in both oracle SQL and pl/SQL. oracle points out in the SQL reference manual and pl/SQL reference manual: the maximum length of oracle SQL varchar2 is 4000 bytes, while that of oracle plsql varchar2 is 32767 bytes. This is why a friend asked why 32767 characters (character/byte) are defined in pl/SQL, and the table fields cannot be defined as more than 4000 bytes.

The following example shows the maximum length of varchar2 in oracle SQL and plsql respectively.

Example of the maximum length supported by varchar2 in oracle SQL-the maximum length is 4000

1. Drop TableIdb_varchar2;
2. Create TableIdb_varchar2
3. (Id number,
4. NameVarchar2 (1, 4000Char));
5. Insert IntoIdb_varchar2Values(1, lpad (Medium', 32767,Medium'));
6. Insert IntoIdb_varchar2Values(2, lpad ('A', 32767,'B'));
7. Commit;
8. SelectId, lengthb (Name), Length (Name)FromIdb_varchar2;

 

1. Drop TableIdb_varchar2;
2. Create TableIdb_varchar2
3. (Id number,
4. NameVarchar2 (1, 4000Char));
5. Insert IntoIdb_varchar2Values(1, lpad (Medium', 32767,Medium'));
6. Insert IntoIdb_varchar2Values(2, lpad ('A', 32767,'B'));
7. Commit;
8. SelectId, lengthb (Name), Length (Name)FromIdb_varchar2;

Output result:

Dw @ dw> drop table idb_varchar2; the table has been deleted. Dw @ dw> create table idb_varchar2 2 (id number, 3 name varchar2 (4000 char); the table has been created. Dw @ dw> insert into idb_varchar2 values (1, lpad (in, 32767, In); 1 row has been created. Dw @ dw> insert into idb_varchar2 values (2, lpad ('A', 32767, 'B'); 1 row has been created. Dw @ dw> commit; the submission is complete. Dw @ dw> select id, lengthb (name), length (name) from idb_varchar2; id lengthb (NAME) LENGTH (NAME) ---------- ------------- ------------ 1 4000 2000 2 4000 4000 2 rows have been selected.

Example of the maximum supported varchar2 length in oracle SQL-the design length is 4001

1. Drop TableIdb_varchar2;
2. Create TableIdb_varchar2
3. (Id number,
4. NameVarchar2 (4001 ));

 

1. Drop TableIdb_varchar2;
2. Create TableIdb_varchar2
3. (Id number,
4. NameVarchar2 (4001 ));

Result:

Dw @ dw> drop table idb_varchar2; the table has been deleted. Dw @ dw> create table idb_varchar2 2 (id number, 3 name varchar2 (4001); name varchar2 (4001) * row 3rd error: ORA-00910: the specified length is too long for the data type.

If the number exceeds 4001, an error is returned.

Example of maximum supported varchar2 length in oracle plsql

1. SetServeroutputOn
2. Declare
3. V_var varchar2 (32767 byte );
4. V_char varchar2 (32767Char);
5. Begin
6. V_var: = lpad ('A', 32767,'A');
7. Dbms_output.put_line (length (v_var ));
8. V_char: = lpad (Medium', 32767,Medium');
9. Dbms_output.put_line (lengthb (v_var ));
10. V_var: = lpad (Medium', 32768,Medium');
11. End;
12. /
14. -- If the number exceeds 32768, an error is returned.
15. Declare
16. V_var varchar2 (32768 );
17. Begin
18. Null;
19. End;
20. /

 

1. SetServeroutputOn
2. Declare
3. V_var varchar2 (32767 byte );
4. V_char varchar2 (32767Char);
5. Begin
6. V_var: = lpad ('A', 32767,'A');
7. Dbms_output.put_line (length (v_var ));
8. V_char: = lpad (Medium', 32767,Medium');
9. Dbms_output.put_line (lengthb (v_var ));
10. V_var: = lpad (Medium', 32768,Medium');
11. End;
12. /
14. -- If the number exceeds 32768, an error is returned.
15. Declare
16. V_var varchar2 (32768 );
17. Begin
18. Null;
19. End;
20. /

Output result:

Dw @ dw> set serveroutput ondw @ dw> declare 2 v_var varchar2 (32767 byte); 3 v_char varchar2 (32767 char); 4 begin 5 v_var: = lpad ('A ', 32767, 'A'); 6 dbms_output.put_line (length (v_var); 7 v_char: = lpad ('medium ', 32767,' medium '); 8 dbms_output.put_line (lengthb (v_var); 9 v_var: = lpad ('medium ', 32768,' medium '); 10 end; 11/3276732767 declare * row 1st error: ORA-06502: PL/SQL: Number or value error: string buffer is too small ORA-06512: In line 9dw @ dw> dw @ dw> declare 2 v_var varchar2 (32768); 3 begin 4 null; 5 end; 6/v_var varchar2 (32768); * 2nd rows error: ORA-06550: 2nd rows, 18th columns: PLS-00215: String Length limited in the range (1... 32767)