oracle – 虚拟列表达式中的连接数会引发ORA-12899:值对于列太大

前端之家收集整理的这篇文章主要介绍了oracle – 虚拟列表达式中的连接数会引发ORA-12899:值对于列太大前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
虽然我昨天给了这个 answer一个问题,但我建议使用VIRTUAL COLUMN来计算值而不是手动更新它.

我自己做了一个测试,并找出了在连接两个NUMBER类型列时虚拟列表达式所占用的数据大小的问题.虽然在连接两个字符时没有问题.

数据库版本:

@H_502_5@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:连接字符串

@H_502_5@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:连接数字

@H_502_5@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)

不允许?咦!让我们增加尺寸 –

@H_502_5@sql> CREATE TABLE t( 2 ID 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个字节.

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

@H_502_5@sql> CREATE TABLE t( 2 ID 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,4 text VARCHAR2(81) generated always as (length(to_char(id)||'_'||to_char(num))) VIRTUAL 5 ); Table created. sql> sql> INSERT INTO t(ID,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表达式.那么,以下作品 –

@H_502_5@sql> DROP TABLE t PURGE; Table dropped. sql> sql> CREATE TABLE t( 2 ID 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,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的词汇化).

话虽如此,约束数列不会反映在虚拟列长度中 – 使得NUMBER(1,0)列仍然留下需要81个字符的串联.获取生成won’t work either的子字符串,在这种情况下获取ORA-12899:值对于“TEXT”列来说太大(实际:10,最大值:40).为每个to_char()调用提供格式模型,例如FM999),可以工作,但直接限制下划线两侧的值而不是整体长度.

如果要限制列大小,可以将其强制转换为相同的数据类型和大小,这更明确:

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

猜你在找的Oracle相关文章