Maximum length supported by Oracle varchar2

Source: Internet
Author: User

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. /
  13. -- If the number exceeds 32768, an error is returned.
  14. Declare
  15. V_var varchar2 (32768 );
  16. Begin
  17. Null;
  18. End;
  19. /
 
  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. /
  13. -- If the number exceeds 32768, an error is returned.
  14. Declare
  15. V_var varchar2 (32768 );
  16. Begin
  17. Null;
  18. End;
  19. /

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.