虚拟列表达式中的串联数字引发ORA-12899:值对于列太大

Question

当我昨天回答一个问题时，我建议对计算值使用虚拟列，而不是手动更新它。

我自己做了一个测试，发现了连接两个NUMBER类型列时虚拟列表达式所占用的数据大小问题。不过，连接两个字符时没有问题。

DB版本：

SQL> select banner from v$version where rownum = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
SQL>

测试用例1：连接字符串

SQL> CREATE TABLE t(
  2  ID varchar2(2),
  3  num varchar2(2),
  4  text VARCHAR2(10) generated always as (id||'_'||num) VIRTUAL
  5  );
Table created.
SQL>
SQL> INSERT INTO t(ID, num) VALUES ('a', 'e');
1 row created.
SQL> INSERT INTO t(ID, num) VALUES ('b', 'f');
1 row created.
SQL> INSERT INTO t(ID, num) VALUES ('c', 'g');
1 row created.
SQL>
SQL> SELECT * FROM T;
ID NU TEXT
-- -- ----------
a  e  a_e
b  f  b_f
c  g  c_g
SQL>

因此，连接两个字符类型的列没有问题。

测试用例2：连接数字

SQL> CREATE TABLE t(
  2  ID NUMBER,
  3  num NUMBER,
  4  text VARCHAR2(10) generated always as (to_char(id)||'_'||to_char(num)) VIRTUAL
  5  );
text VARCHAR2(10) generated always as (to_char(id)||'_'||to_char(num)) VIRTUAL
*
ERROR at line 4:
ORA-12899: value too large for column "TEXT" (actual: 10, maximum: 81)

不允许？啊！让我们增加尺寸-

SQL> CREATE TABLE t(
  2  ID NUMBER,
  3  num NUMBER,
  4  text VARCHAR2(81) generated always as (to_char(id)||'_'||to_char(num)) VIRTUAL
  5  );
Table created.
SQL>
SQL> INSERT INTO t(ID, num) VALUES (1, 4);
1 row created.
SQL> INSERT INTO t(ID, num) VALUES (2, 5);
1 row created.
SQL> INSERT INTO t(ID, num) VALUES (3, 6);
1 row created.
SQL>
SQL> SELECT * FROM T;
        ID        NUM
---------- ----------
TEXT
--------------------------------------------------------------------------------
         1          4
1_4
         2          5
2_5
         3          6
3_6

SQL> set linesize 200
SQL> SELECT * FROM T;
        ID        NUM TEXT
---------- ---------- ----------------------------------------------------------------------------------------------------
         1          4 1_4
         2          5 2_5
         3          6 3_6
SQL>

那么现在发生了什么？表已创建，但为什么虚拟列在预期数据大小仅为3字节的情况下会占用这么多大小，但它需要81字节

检查长度，值是正确的，但是数据大小要大得多。例如，我希望长度为3，所以我将列的大小声明为10个字节。但虚拟列表达式生成的值的大小远不止于此。

SQL> CREATE TABLE t(
  2  ID NUMBER,
  3  num NUMBER,
  4  text VARCHAR2(10) generated always as (length(to_char(id)||'_'||to_char(num))) VIRTUAL
  5  );
text VARCHAR2(10) generated always as (length(to_char(id)||'_'||to_char(num))) VIRTUAL
*
ERROR at line 4:
ORA-12899: value too large for column "TEXT" (actual: 10, maximum: 40)

SQL>
SQL> CREATE TABLE t(
  2  ID NUMBER,
  3  num NUMBER,
  4  text VARCHAR2(81) generated always as (length(to_char(id)||'_'||to_char(num))) VIRTUAL
  5  );
Table created.
SQL>
SQL> INSERT INTO t(ID, num) VALUES (1, 4);
1 row created.
SQL> INSERT INTO t(ID, num) VALUES (2, 5);
1 row created.
SQL> INSERT INTO t(ID, num) VALUES (3, 6);
1 row created.
SQL>
SQL> SELECT * FROM T;
        ID        NUM TEXT
---------- ---------- ----------------------------------------------------------------------------------------------------
         1          4 3
         2          5 3
         3          6 3
SQL> clear columns
columns cleared
SQL> SELECT * FROM T;
        ID        NUM TEXT
---------- ---------- ---------------------------------------------------------------------------------
         1          4 3
         2          5 3
         3          6 3

任何见解都非常受欢迎。

UDPATE感谢Alex Poole。我没有考虑隐式转换，所以我不想显式地CAST表达式。所以，下面的工作-

SQL> DROP TABLE t PURGE;
Table dropped.
SQL>
SQL> CREATE TABLE t(
  2  ID NUMBER,
  3  num NUMBER,
  4  text VARCHAR2(10) generated always as (cast(to_char(id)||'_'||to_char(num) as varchar2(3))) VIRTUAL
  5  );
Table created.
SQL>
SQL> INSERT INTO t(ID, num) VALUES (1, 4);
1 row created.
SQL> INSERT INTO t(ID, num) VALUES (2, 5);
1 row created.
SQL> INSERT INTO t(ID, num) VALUES (3, 6);
1 row created.
SQL>
SQL> SELECT * FROM T;
        ID        NUM TEXT
---------- ---------- ----------
         1          4 1_4
         2          5 2_5
         3          6 3_6
SQL>

Answer 1

您的数字不受限制。对于个位数（正），您知道连接长度只能是三，但虚拟列必须足够大才能容纳任何数字，因此它看起来允许隐式格式模型最多40位数字（38位有效数字、十进制分隔符和符号；@collspar的词汇化）。

话虽如此，约束数字列不会反映在虚拟列长度中——使两列都为NUMBER(1,0)仍然需要81个字符的串联。获取生成的值的子字符串也不起作用，在本例中获取ORA-12899: value too large for column "TEXT" (actual: 10, maximum: 40)。为每个to_char()调用（例如FM999）提供一个格式模型是可行的，但会限制下划线两侧的值，而不是直接限制总长度。

如果您想限制列大小，可以将其强制转换为相同的数据类型和大小，这更明确：

text VARCHAR2(10) generated always as 
    (cast(to_char(id)||'_'||to_char(num) as VARCHAR2(10))) VIRTUAL