PL/SQL 和 SQL 中 VARCHAR2 的最大大小是多少？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25237463/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the max size of VARCHAR2 in PL/SQL and SQL?
提问by Ajay Gupta
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 readthat
我在 Oracle 10g 上。在要求中，我需要增加 pl/sql VARCHAR2 变量的大小。它已经是 4000 大小。我读过那个
in PL/SQL, VARCHAR2 can be up to 32767 bytes. For SQL the limit is 4000 bytes
在 PL/SQL 中，VARCHAR2 最多可以有 32767 个字节。对于 SQL，限制为 4000 字节
Can I increase the size of this variable without worrying about the SQL limit?
我可以增加这个变量的大小而不用担心 SQL 限制吗？
回答by Andre Kirpitch
See the official documentation (http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330)
参见官方文档（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.
具有最大长度大小字节或字符的可变长度字符串。最大大小为 4000 个字节或字符，最小为 1 个字节或 1 个字符。您必须为 VARCHAR2 指定大小。BYTE 表示该列将具有字节长度语义；CHAR 表示该列将具有字符语义。
But in Oracle Databast 12c maybe 32767 (http://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020)
但在 Oracle Databast 12c 中可能是 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
具有最大长度大小字节或字符的可变长度字符串。您必须为 VARCHAR2 指定大小。最小大小为 1 个字节或 1 个字符。最大大小为：如果 MAX_STRING_SIZE = EXTENDED，则为 32767 字节或字符 如果 MAX_STRING_SIZE = STANDARD，则为 4000 字节或字符
回答by neshkeev
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:
如果您使用 UTF-8 编码，则一个字符可以占用不同数量的字节 (2 - 4)。对于 PL/SQL，varchar2 限制是 32767 字节，而不是字符。看看我如何增加 4000 个字符大小的 PL/SQL varchar2 变量：
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.
作为解决方案，您可以尝试将此变量的值拆分为几个部分 ( SUBSTR) 并分别存储。
回答by Ashwath Padmashali
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:
根据 Andre Kirpitch 共享的官方文档链接，Oracle 10g 为 varchar2 提供了 4000 字节或字符的最大大小。如果您使用的是更高版本的 oracle（例如 Oracle 12c），则 varchar2 的最大大小可达 32767 个字节或字符。要利用 oracle 12 的扩展数据类型特性，您需要以升级模式启动 oracle。在命令提示符下执行以下步骤：
1) Login as sysdba (sqlplus / as sysdba)
1) Login as sysdba (sqlplus / as sysdba)
2) SHUTDOWN IMMEDIATE;
2) SHUTDOWN IMMEDIATE;
3) STARTUP UPGRADE;
3) STARTUP UPGRADE;
4) ALTER SYSTEM SET max_string_size=extended;
4) ALTER SYSTEM SET max_string_size=extended;
5) Oracle\product\12.1.0.2\rdbms\admin\utl32k.sql
5) Oracle\product\12.1.0.2\rdbms\admin\utl32k.sql
6) SHUTDOWN IMMEDIATE;
6) SHUTDOWN IMMEDIATE;
7) STARTUP;
7) STARTUP;
回答by hol
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.
不确定“我可以在不担心 SQL 限制的情况下增加此变量的大小吗？”的意思。只要您不尝试将超过 4000 个 VARCHAR2 插入到 VARCHAR2 SQL 列中，就没有什么可担心的。
Here is the exact reference (this is 11g but true also for 10g)
这是确切的参考（这是 11g，但对于 10g 也是如此）
http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/datatypes.htm
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
PL/SQL 中的 VARCHAR2 最大大小：32,767 字节 SQL 中的最大大小 4,000 字节
