在虚拟列表达式中连接数字会抛出ORA-12899:该值对于列而言太大 [英] Concatenating numbers in virtual column expression throws ORA-12899: value too large for column
问题描述
昨天我给这个问题 时，我建议使用虚拟列而不是手动更新计算值.
While I gave this answer to a question yesterday, I suggested to use a VIRTUAL COLUMN for computed values instead of manually updating it.
我自己做了一个测试，发现了虚拟列表达式在连接两个 NUMBER 类型列时所占用的数据大小的问题.不过，串联两个字符没有问题.
I did a test myself, and figured out an issue with the data size that the virtual column expression takes while concatenating two NUMBER type columns. Though, no issue while concatenating two characters.
数据库版本:
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>
因此，串联两个字符类型列没有问题.
So, no issues with concatenating two character type columns.
测试案例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)
不允许? ！让我们增加大小-
Not allowed? Huh! Let's increase the size -
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个字节时，为什么 VIRTUAL COLUMN 占用了这么多的大小，但是却占用了 81个字节.
So what happened now? Table got created, but why VIRTUAL COLUMN occupies that much size when the expected data size is just 3 bytes, however it takes 81 bytes.
检查长度值是正确的，但是数据大小要大得多.例如，我希望长度为3，因此我将列的大小声明为10个字节.但是虚拟列表达式产生的值的大小要大得多.
Checking the length, value is correct, however, the data size is much larger. For example, I expect the length to be 3, so I declare the size of the column as 10 bytes. But the virtual column expression yields the value with a size much more than that.
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表达式.所以，下面的作品-
UDPATE Thanks to Alex Poole. I did not think about the implicit conversion, so I did not care to CAST the expression explicitly. So, the below works -
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>
推荐答案
您的电话号码不受限制.对于单个数字(正)，您 知道合并的长度只能是三，但是虚拟列必须足够大以容纳任何数字-因此看起来隐式字段最多可以容纳40个数字格式模型(38个有效数字，小数点分隔符和符号； @collspar的词汇化).
Your numbers are not constrained. With single digit (positive) numbers you know the concatendated length can only be three, but the virtual column has to be large enough for any number - so it looks like it's allowing up to 40 digits for the implicit format model (38 significant digits, the decimal separator, and the sign; @collspar's lexicalisation).
话虽如此，约束number列不会反映在虚拟列的长度中-使两个列NUMBER(1,0)仍然保留需要81个字符的串联.取生成值的子字符串也不起作用，在这种情况下，获取ORA-12899: value too large for column "TEXT" (actual: 10, maximum: 40).为每个to_char()调用提供格式模型，例如FM999)，可以，但是限制下划线两侧的值，而不是直接限制整个长度.
Having said that, constraining the number column wouldn't be reflected in the virtual column length - making both columns NUMBER(1,0) still leaves the concatenation requiring 81 characters. Taking the substring of the generated value won't work either, in this case getting ORA-12899: value too large for column "TEXT" (actual: 10, maximum: 40). Supplying a format model for each to_char() call, e.g. of FM999), would work but restricts the values either side of the underscore rather than the overall length directly.
如果要限制列大小，可以将其强制转换为相同的数据类型和大小，这更加明确:
If you want to restrict the column size, you can cast it to the same data type and size, which is more explicit:
text VARCHAR2(10) generated always as
(cast(to_char(id)||'_'||to_char(num) as VARCHAR2(10))) VIRTUAL
这篇关于在虚拟列表达式中连接数字会抛出ORA-12899:该值对于列而言太大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
