Adding virtual column: ORA-12899: value too large for column

Advertisement
I'm using Oracle 11g, Win7 OS, SQL Developer
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
(col1 VARCHAR2(100));
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]*_'))
FROM test_reg_exp;Thanks.
Advertisement

Replay

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

Java.sql.BatchUpdateException: ORA-12899[ value too large for column.......

Hi All, I am using SOA 11g(11.1.1.3). I am trying to insert data in to a table coming from a file. I have encountered the fallowing error. Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding ex

Oracle : ORA-12899: value too large for column

Hi Experts, I am loading multibyte data from fixed width flat file to Oracle database(which is a utf8 characterset) via Informatica. I have set utf8 as characterset in both source and target definitions. Source flat file data : Münchener(this flat f

Fdpstp failed due to ora-12899 value too large for column

Hi All, User facing this problem while running the concurrent program. the program is complted but with rhis error. fdpstp failed due to ora-12899 value too large for column Can any one tell me the excat solution for this? RDBMS : 10.2.0.3.0 Oracle A

Error on reverse on XML: value too large for column

Hi All, I am trying to reverse engineer while creating the data model on XML technology. My JDBC URL on data server reads this: jdbc:snps:xml?d=../demo/abc/CustomerPartyEBO.xsd&s=MYEBO I get an error while doing the reverse. java.sql.SQLException: OR

Sqlldr ORA-01401: inserted value too large for column

export nls_characterset WE8ISO8859P1 import nls_characterset AL32UTF8 Here is how my control file looks like LOAD DATA INFILE 'db_service.dat' INTO TABLE DB_SERVICE FIELDS TERMINATED BY ',' TRAILING NULLCOLS APPS_ID CHAR NULLIF (APPS_ID=BLANKS) , LCR

'Value too large for column' error in msql

In my 9ilite database I have a table with a LONG column In the documentation it says that a LONG column can hold upto 2 GB data However when I try an insert more than 4097 characters into the column I get the error message on insert 'Value too large

Value too large for "BPM_AUDIT_QUERY"."AUDIT_LOG"(real: 2077, max: 2000)

Hello everybody: I really need some help. I've modeled and implemented a BPMN process. I'm trying to test it but I got the following error. ORA-12899: value too large for column "DEV1_SOAINFRA"."BPM_AUDIT_QUERY"."AUDIT_LOG" (

Ora-14036 error partition bound too large for column

Hi All, I have a partitioned table on balance_period .. format YYYYMM . I am trying to split the PMAX partition but get the errors :- > ALTER TABLE ACT_MD_FOREIGNEXCHANGE SPLIT PARTITION ACT_MD_FOREIGNEX_PMAX AT (TO_DATE ('20081201', 'YYYYMMDD)) 2 IN

Mkisofs: Value too large for defined data type too large

Hi: Does anyone meet the problem when use mkisofs command? <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Oracle error message code 1401 op 4, ORA-01401: inserted value too large fo

Hi All I got this error when I try to run convert tables on our application server. Anyone know why? I am using Oracle 9i Here is the error message: Checking Table WORK_INSTRUCTION Table not in database Conversion Method - Create Table Creating Table

Ncomp value too large

Hi, I am trying to use ncomp, currently on a Hello world class. The class itself works fine, but ncomp (as well as statusnc) gives the error below on standard output. It also says 'error: Can't read: exact_class_list.txt' in the ncomp log file. Can a

OS10.3.1 a disaster! Among other things, the dsplay is too large for screen

I do not like OS 10.3 ! After my Z10 was updated to 10.3.1.1779 about a month ago, I access it because the password entry keyboard displayed for about 1/10 sec leaving a black screen. After a device reset the device the keyboard remained visible but

F-28 : the difference is too large for clearing

Hi, i'm posting an incoming payment with Tcode F-28 but when i save i have this message : "the difference is too large for clearing" i have already done the following configurations in SPRO : 1- Define Tolerance Groups for Employees : for permet

FRM-40831 : Truncation occured: value too long for filed MAST_EMP_NAME

Hi I'm using Forms 5 and getting following error. FRM-40831 : Truncation occured: value too long for filed MAST_EMP_NAME. When i checked in the column Mast_emp_name, all the values are with in the limit. Table contains some 9 lacs record. Please help

Backup too large for volume

I have 2 macbook pro's (120GB & 160GB) backing up to a 500GB TM. both were backing up just fine, however in the past month the 160GB macbook pro keeps getting this message..... "backup too large for volume?" and subsequently the backup fails

Time Machine backs up one MacBook, but "backup is too large" for second

We have two MacBooks running OS 10.5.8 and both are connected wirelessly to Time Capsule. My daughter's laptop backs up fine. But my wife's laptop fails. It backed up one time when I first installed it, but never since. The error message I get is tha

With Adobe Flash, the image is looking for a "zoom" setting and does not find it. The result is that the imbedded image is too large for the space allowed. Is there a zoom setting for Firefox?

When running FB game apps, the image loaded is too large for the FB space. The program looks for a "zoom" setting and does not find it. This was from the error screen. I have not been able to locate where the image zoom is located (found the fon

Time Machine backup too large for backup disk

My Mac hard drive is 750GB (25GB free). My LaCie external dedicated backup hard drive is 1TB (342GB free). I am now getting the error "Time Machine couldn't complete the backup" "This backup is too large for the backup disk" I thought

IDOC staus: 51, The difference is too large for clearing

All, IDOC staus: 51, the difference is too large for clearing.  Can somebody throw light on this ? -Rajani SateeshHi, Thanks for your response. External transaction type 165(+), Posting rule 0001, interpretation algorthim 020