What is the max size of VARCHAR2 in PL/SQL and SQL?

Question

I am on Oracle 10g. In a requirement I need to increase the size of a pl/sql VARCHAR2 variable. It is already at 4000 size. I have read that

in PL/SQL, VARCHAR2 can be up to 32767 bytes. For SQL the limit is 4000 bytes

Can I increase the size of this variable without worrying about the SQL limit?

Answer 1

See the official documentation (http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330)

Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2. BYTE indicates that the column will have byte length semantics; CHAR indicates that the column will have character semantics.

But in Oracle Databast 12c maybe 32767 (http://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020)

Variable-length character string having maximum length size bytes or characters. You must specify size for VARCHAR2. Minimum size is 1 byte or 1 character. Maximum size is: 32767 bytes or characters if MAX_STRING_SIZE = EXTENDED 4000 bytes or characters if MAX_STRING_SIZE = STANDARD

Answer 2

If you use UTF-8 encoding then one character can takes a various number of bytes (2 - 4). For PL/SQL the varchar2 limit is 32767 bytes, not characters. See how I increase a PL/SQL varchar2 variable of the 4000 character size:

SQL> set serveroutput on SQL> l   1  declare   2    l_var varchar2(30000);   3  begin   4    l_var := rpad('A', 4000);   5    dbms_output.put_line(length(l_var));   6    l_var := l_var || rpad('B', 10000);   7    dbms_output.put_line(length(l_var));   8* end; SQL> / 4000 14000  PL/SQL procedure successfully completed. 

But you can't insert into your table the value of such variable:

SQL> ed Wrote file afiedt.buf    1  create table ttt (   2    col1 varchar2(2000 char)   3* ) SQL> /  Table created.  SQL> ed Wrote file afiedt.buf    1  declare   2    l_var varchar2(30000);   3  begin   4      l_var := rpad('A', 4000);   5      dbms_output.put_line(length(l_var));   6      l_var := l_var || rpad('B', 10000);   7      dbms_output.put_line(length(l_var));   8      insert into ttt values (l_var);   9* end; SQL> / 4000 14000 declare * ERROR at line 1: ORA-01461: can bind a LONG value only for insert into a LONG column ORA-06512: at line 8 

As a solution, you can try to split this variable's value into several parts (SUBSTR) and store them separately.