Home » SQL & PL/SQL » SQL & PL/SQL » Concatenating numbers in virtual column expression throws ORA-12899: value too large for column (Oracle database, 12.1.0.1)
Concatenating numbers in virtual column expression throws ORA-12899: value too large for column [message #633362] Tue, 17 February 2015 01:51 Go to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
While replying to a question in another forum, I suggested using VIRTUAL column for computed values instead of manually updating. When I made a small test, I figured out an issue with the data size that the virtual column expression takes while concatenating two NUMBERS. Though, no issue while concatenating two characters.

DB version :

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

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL>


Test case 1 : Concatenating characters

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.

Test case 2 : concatenating numbers.

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)


Increasing 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>



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


Any insight is more than welcome.

Regards,
Lalit

Report message to a moderator

Re: Concatenating numbers in virtual column expression throws ORA-12899: value too large for column [message #633364 is a reply to message #633362] Tue, 17 February 2015 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I see no problem with Oracle checking the maximum possible size of the expression result (which is the size of the expression data type) and it seems the implicit conversion from NUMBER to VARCHAR2 implies a VARCHAR2(40).

If you declare your VARCHAR2 with length 40 in the first table I bet you'll have the same error.

Report message to a moderator

Re: Concatenating numbers in virtual column expression throws ORA-12899: value too large for column [message #633366 is a reply to message #633364] Tue, 17 February 2015 02:29 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Tue, 17 February 2015 13:33
If you declare your VARCHAR2 with length 40 in the first table I bet you'll have the same error.
You are correct.

Quote:
it seems the implicit conversion from NUMBER to VARCHAR2 implies a VARCHAR2(40).


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>

Report message to a moderator

Previous Topic: Identity column inserts duplicates with Insert All statement, unique constraint violation
Next Topic: how to grant database link to user
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 14:59:06 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.