Adding virtual column: ORA-12899: value too large for column
I'm trying to add virtual column to my test table, but getting ORA-12899: value too large for column error. Below are the details.
Can someone help me in this?
CREATE TABLE test_reg_exp
INSERT INTO test_reg_exp (col1) VALUES ('ABCD_EFGH');
INSERT INTO test_reg_exp (col1) VALUES ('ABCDE_ABC');
INSERT INTO test_reg_exp (col1) VALUES ('WXYZ_ABCD');
INSERT INTO test_reg_exp (col1) VALUES ('ABCDE_PQRS');
INSERT INTO test_reg_exp (col1) VALUES ('ABCD_WXYZ');
ALTER TABLE test_reg_exp
ADD (col2 VARCHAR2(100) GENERATED ALWAYS AS (REGEXP_REPLACE (col1, '^ABCD[A-Z]*_')));
SQL Error: ORA-12899: value too large for column "COL2" (actual: 100, maximum: 400)
12899. 00000 - "value too large for column %s (actual: %s, maximum: %s)"
*Cause: An attempt was made to insert or update a column with a value
which is too wide for the width of the destination column.
The name of the column is given, along with the actual width
of the value, and the maximum allowed width of the column.
Note that widths are reported in characters if character length
semantics are in effect for the column, otherwise widths are
reported in bytes.
*Action: Examine the SQL statement for correctness. Check source
and destination column data types.
Either make the destination column wider, or use a subset
of the source column (i.e. use substring).When I try to select, I'm getting correct results:
SELECT col1, (REGEXP_REPLACE (col1, '^ABCD[A-Z]*_'))
Yes RP, it working if you give col2 size >=400.
@Northwest - Could you please test the same w/o having a regex clause in col2?
I doubt on the usage of a REGEX in this dynamic col case.
Refer this (might help) -- http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php
Below snippet from above link.... see if this helps...
Notes and restrictions on virtual columns include:
Indexes defined against virtual columns are equivalent to function-based indexes.
Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.
Tables containing virtual columns can still be eligible for result caching.
Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled:
Constraint on the virtual column must be disabled and re-enabled.
Indexes on the virtual column must be rebuilt.
Materialized views that access the virtual column must be fully refreshed.
The result cache must be flushed if cached queries have accessed the virtual column.
Table statistics must be regathered.
Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
The expression used in the virtual column definition has the following restrictions:
It cannot refer to another virtual column by name.
It can only refer to columns defined in the same table.
If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.
Edited by: ranit B on Oct 16, 2012 11:48 PM
Edited by: ranit B on Oct 16, 2012 11:54 PM