Concatenar números na expressão da coluna virtual lança ORA-12899: valor muito grande para a coluna
Enquanto eu dei issoresponda ontem a uma pergunta, sugeri usar umCOLUNA VIRTUAL para valores calculados em vez de atualizá-lo manualmente.
Eu mesmo fiz um teste e descobri um problema com o tamanho dos dados que a expressão da coluna virtual leva enquantoconcatenar doisNÚMERO digite colunas. No entanto, não há problema ao concatenar dois caracteres.
Versão do banco de dados:
SQL> select banner from v$version where rownum = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
SQL>
Caso de teste 1: Concatenando cadeias
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>
Portanto, não há problemas em concatenar duas colunas de tipo de caractere.
Caso de teste 2: concatenando números
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)
Não permitido? Hã! Vamos aumentar o tamanho -
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>
Então o que aconteceu agora? A tabela foi criada, mas por queCOLUNA VIRTUAL ocupa esse tamanho quando oo tamanho esperado dos dados é de apenas 3 bytes, no entanto, é preciso81 bytes.
Verificando ocomprimento, o valor está correto, no entanto, o tamanho dos dados é muito maior. Por exemplo, espero que o comprimento seja 3, por isso declaro o tamanho da coluna como 10 bytes. Mas a expressão da coluna virtual gera o valor com um tamanho muito mais que isso.
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
Qualquer insight é mais que bem-vindo.
UDPATE Graças a Alex Poole. Como não pensei na conversão implícita, não me importei em CAST a expressão explicitamente. Então, o abaixo funciona -
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>