[Oracle Misc] Varchar2 in SQL and PL/SQL, etc

Varchar2的大小限制在SQL和PL/SQL中是不一样的，这点经常被忘记，因此会遇到一些问题。

Oracle SQL中Varchar2类型只支持最大4000bytes，而pl/sql则可以支持最大32767bytes的大小！在创建Oracle Table时，如果一个列存储的数据大于4000bytes时候，最好换成CLOB类型。

下面进行一些测试看看，

SQL> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

先看看简单的SQL语句，看看最多能返回多长字符串，

SQL> select length(rpad('A', 3999, '*')) from dual;
LENGTH(RPAD('A',3999,'*'))
--------------------------
3999

SQL> select length(rpad('A', 4000, '*')) from dual;
LENGTH(RPAD('A',4000,'*'))
--------------------------
4000

SQL> select length(rpad('A', 4001, '*')) from dual;
LENGTH(RPAD('A',4001,'*'))
--------------------------
4000

SQL> select length(rpad('A', 40001, '*')) from dual;
LENGTH(RPAD('A',40001,'*'))
---------------------------
4000

SQL>

可以看到，当对RPAD的参数设置超过4000时，最后返回的结果也只是4000.

SQL> select rpad('A', 4000, '*') || 'T' from dual;
select rpad('A', 4000, '*') || 'T' from dual
                                        *
ERROR at line 1:
ORA-01489: result of string concatenation is too long

当选出的字符串超过4000的时候，会报错。

这就就提示我们，当通过调用一个存储过程来得到结果的时候需要注意这个存储过程返回的结果的长度有多少，来测试下，

SQL> CREATE OR REPLACE FUNCTION test_varchar2_max_length RETURN varchar2 AS
  2  BEGIN
  3    return RPAD('A', 4001, '*');
  4  END;
  5  /

Function created.

SQL> select test_varchar2_max_length from dual;
select test_varchar2_max_length from dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "FRANK.TEST_VARCHAR2_MAX_LENGTH", line 3


SQL>

函数test_varchar2_max_length返回了一个长度为4001的字符串（远小于PL/SQL支持的32767的大小），但是SELECT语句却报错了。

解决这个问题的一个方法是改变函数的返回值类型，将varchar2 改成clob，虽然从PL/SQL角度来说完全没有必要！

SQL> CREATE OR REPLACE FUNCTION test_varchar2_max_length RETURN CLOB AS
2  BEGIN
3    return RPAD('A', 4001, '*');
4  END;
5  /
Function created.

SQL> select test_varchar2_max_length from dual;

TEST_VARCHAR2_MAX_LENGTH
--------------------------------------------------------------------------------
A*******************************************************************************

SQL> set long 5000
SQL> /

TEST_VARCHAR2_MAX_LENGTH
--------------------------------------------------------------------------------
A*******************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************

TEST_VARCHAR2_MAX_LENGTH
--------------------------------------------------------------------------------
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************

TEST_VARCHAR2_MAX_LENGTH
--------------------------------------------------------------------------------
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************

TEST_VARCHAR2_MAX_LENGTH
--------------------------------------------------------------------------------
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************

TEST_VARCHAR2_MAX_LENGTH
--------------------------------------------------------------------------------
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
*


SQL>

----其他------------------

(1)如何把CLOB转成VARCHAR2

dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte );

SQL> select DBMS_LOB.SUBSTR(test_varchar2_max_length, 1, 1) from dual;

DBMS_LOB.SUBSTR(TEST_VARCHAR2_MAX_LENGTH,1,1)
-----------------------------------------------------------------------------
A

(2) set serveroutput on  &  dbms_output.enable

SET SERVEROUTPUT ON是SQL*PLUS命令，其实跟调用DBMS_OUTPUT.ENABLE是一样的

关于DBMS_OUTPUT的用法，参见这里.

(3) DBMS_OUTPUT enhancement in oracle 10.2

注意包含两部分，

length limit (255bytes –> 32767 bytes)

buffer limit

SQL> set serveroutput on size 1000
SP2-0547: size option 1000 out of range (2000 through 1000000)

SQL> set serveroutput on size 1000000000000
SP2-0547: size option 3567587328 out of range (2000 through 1000000)

The unconstrained set serveroutput on setting is now equivalent to set serveroutput on size unlimited. Both of these sqlplus commands execute "DBMS_OUTPUT.ENABLE(NULL)" which can be seen in the trace file if running with SQL trace on.

SQL> set serveroutput on size unlimited

SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

SQL> set serveroutput on

SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

-----End----