blocks|key|2732423|text|请参阅官方文档(http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2732424|具有最大长度、大小、字节或字符的|2732425|可变长度字符串。最大为4000字节或字符，最小为1字节或1个字符。您必须指定VARCHAR2的大小。BYTE表示该列将具有字节长度语义；CHAR表示该列将具有字符语义。|blockquote|2732426|2732427|2732428|但在Oracle数据库12c中可能是32767+(http://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020)|2732429|2732430|可变长度字符串。您必须指定VARCHAR2的大小。最小大小为1个字节或1个字符。最大大小为:+32767字节或字符(如果MAX_STRING_SIZE+=扩展的4000字节)或字符(如果MAX_STRING_SIZE+=标准|2732431|2732432|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330|1|http://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020^0|8|28|0|0|0|0|0|0|P|20|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|6|7|14|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|15|8|@]|9|@]|C|$]]|$1|I|3|-4|5|6|7|16|8|@]|9|@]|C|$]]|$1|J|3|-4|5|6|7|17|8|@]|9|@]|C|$]]|$1|K|3|L|5|6|7|18|8|@]|9|@$A|19|B|1A|1|1B]]|C|$]]|$1|M|3|E|5|6|7|1C|8|@]|9|@]|C|$]]|$1|N|3|O|5|H|7|1D|8|@]|9|@]|C|$]]|$1|P|3|-4|5|6|7|1E|8|@]|9|@]|C|$]]|$1|Q|3|-4|5|6|7|1F|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]|Y|$5|T|U|V|C|$W|Z]]]]

See the official documentation (<a href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330" rel="noreferrer">http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330</a>)

<blockquote>
 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.
</blockquote>

But in Oracle Databast 12c maybe 32767 (<a href="http://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020" rel="noreferrer">http://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020</a>)

<blockquote>
 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
</blockquote>

blocks|key|2732458|text|不知道你说的“我可以增加这个变量的大小而不用担心SQL限制吗？”是什么意思？只要您不尝试将超过4000个VARCHAR2插入到VARCHAR2+SQL列中，就没有什么可担心的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2732459|这是确切的参考(这是11g，但对于10g也是正确的)|2732460|http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/datatypes.htm|offset|length|2732461|2732462|+VARCHAR2在PL/SQL中的最大大小:+32,767字节SQL中的最大大小4,000字节|blockquote|2732463|2732464|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|1V|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@$F|W|G|X|1|Y]]|A|$]]|$1|H|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|10|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|11|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|E]]]]

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)

<a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/datatypes.htm" rel="nofollow">http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/datatypes.htm</a>

<blockquote>
 VARCHAR2 Maximum Size in PL/SQL: 32,767 bytes Maximum Size in SQL
 4,000 bytes
</blockquote>

blocks|key|2732486|text|如果使用UTF-8编码，那么一个字符可以占用不同数量的字节(2+-+4)。对于PL/SQL，varchar2限制为32767个字节，而不是字符。请参见我如何增加4000个字符大小的PL/SQL+varchar2变量：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2732487|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+%7C%7C+rpad('B',+10000);
++7++++dbms_output.put_line(length(l_var));
++8*+end;
SQL>+/
4000
14000

PL/SQL+procedure+successfully+completed.|code-block|syntax|javascript|2732488|但是您不能将这些变量的值插入到您的表中：|2732489|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+%7C%7C+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|2732490|作为一种解决方案，您可以尝试将该变量的值拆分为几个部分(SUBSTR)，并将它们分开存储。|offset|length|2732491|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.htm^0|0|0|0|0|S|6|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

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:

<pre><code>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.
</code></pre>

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

<pre><code>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
</code></pre>

As a solution, you can try to split this variable's value into several parts (<a href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.htm" rel="nofollow noreferrer">SUBSTR</a>) and store them separately.

blocks|key|149987|text|根据Andre+gives共享的官方文档链接，Oracle10g为varchar2提供了4000字节或字符的最大大小。如果您使用的是更高版本的oracle+(例如Oracle12c)，则varchar2的最大大小可达32767个字节或字符。要利用oracle+12的扩展数据类型特性，您需要在升级模式下启动oracle。在命令提示符下执行以下步骤：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|149988|1)+Login+as+sysdba+(sqlplus+/+as+sysdba)|offset|length|style|CODE|149989|2)+SHUTDOWN+IMMEDIATE;|149990|3)+STARTUP+UPGRADE;|149991|4)+ALTER+SYSTEM+SET+max_string_size=extended;|149992|5)+Oracle\product\12.1.0.2\rdbms\admin\utl32k.sql|149993|6)+SHUTDOWN+IMMEDIATE;|149994|7)+STARTUP;|149995|entityMap^0|0|3|11|0|3|J|0|3|G|0|3|16|0|3|1A|0|3|J|0|3|8|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@$D|X|E|Y|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|Z|8|@$D|10|E|11|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|12|8|@$D|13|E|14|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|15|8|@$D|16|E|17|F|G]]|9|@]|A|$]]|$1|N|3|O|5|6|7|18|8|@$D|19|E|1A|F|G]]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1B|8|@$D|1C|E|1D|F|G]]|9|@]|A|$]]|$1|R|3|S|5|6|7|1E|8|@$D|1F|E|1G|F|G]]|9|@]|A|$]]|$1|T|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]]|U|$]]

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) <code>Login as sysdba (sqlplus / as sysdba)</code>

2) <code>SHUTDOWN IMMEDIATE;</code>

3) <code>STARTUP UPGRADE;</code>

4) <code>ALTER SYSTEM SET max_string_size=extended;</code>

5) <code>Oracle\product\12.1.0.2\rdbms\admin\utl32k.sql</code>

6) <code>SHUTDOWN IMMEDIATE;</code>

7) <code>STARTUP;</code>

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 <a href="https://stackoverflow.com/a/414949/1529709">have read</a> that 

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

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

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

Oracle数据库

数据库

我在使用Oracle 10g。在一个需求中，我需要增加pl/sql VARCHAR2变量的大小。它已经是4000大小了。I  that在PL/SQL中，VARCHAR2最多可达32767个字节。对于SQL，限制为4000字节我可以在不担心SQL限制的情况下增加这个变量的大小吗？

问PL/SQL和SQL中VARCHAR2的最大大小是多少？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PL/SQL和SQL中VARCHAR2的最大大小是多少？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PL/SQL和SQL中VARCHAR2的最大大小是多少？
EN