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

User · Answer

As per official documentation link shared by Andre Kirpitch, Oracle 10g gives a maximum size of 4000 bytes or characters for varchar2. If you are using a higher version of oracle (for example Oracle 12c), you can get a maximum size upto 32767 bytes or characters for varchar2. To utilize the extended datatype feature of oracle 12, you need to start oracle in upgrade mode. Follow the below steps in command prompt:

1) Login as sysdba (sqlplus / as sysdba)

2) SHUTDOWN IMMEDIATE;

3) STARTUP UPGRADE;

4) ALTER SYSTEM SET max_string_size=extended;

5) Oracle\product\12.1.0.2\rdbms\admin\utl32k.sql

6) SHUTDOWN IMMEDIATE;

7) STARTUP;

User · Answer

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

User · Answer

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 gt  set serveroutput on SQL gt  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 gt    4000 14000  PL SQL procedure successfully completed    But you can t insert into your table the value of such variable   SQL gt  ed Wrote file afiedt buf    1  create table ttt     2    col1 varchar2 2000 char    3    SQL gt     Table created   SQL gt  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 gt    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

User · Answer

Not sure what you meant with  Can I increase the size of this variable without worrying about the SQL limit    As long you do not try to insert a more than 4000 VARCHAR2 into a VARCHAR2 SQL column there is nothing to worry about   Here is the exact reference  this is 11g but true also for 10g   http   docs oracle com cd E11882 01 appdev 112 e17126 datatypes htm     VARCHAR2 Maximum Size in PL SQL  32 767 bytes Maximum Size in SQL   4 000 bytes

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

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to varchar2