Java – ora-01733 (virtual columns are not allowed here) after database update
One of our databases has been updated from Oracle 9i to 11g Since then, one of our programs has repeatedly encountered errors when trying to update columns through the view We also updated the jdbc driver to 11g
scenery:
CREATE OR REPLACE VIEW UIV AS SELECT (SELECT FIR_KO_KREIS FROM FIRMA WHERE FIR_KONZERN_NR = TRCO_KONZERN_NR AND FIR_FIRMA_NR = TRCO_FIRMA_NR) UIVNFIRMA,UIVAAENNKZ UIVAAENKZ,UVVOL,UVCLS,UVID1,UVID2,UVID3,UVID4,UVID5,UVID6,TO_NUMBER(TRIM(UVGJ)) UVGJ,UVEINH,TO_NUMBER(TRIM(UVW00)) UVW00,TO_NUMBER(TRIM(UVW01)) UVW01,TO_NUMBER(TRIM(UVW02)) UVW02,TO_NUMBER(TRIM(UVW03)) UVW03,TO_NUMBER(TRIM(UVW04)) UVW04,TO_NUMBER(TRIM(UVW05)) UVW05,TO_NUMBER(TRIM(UVW06)) UVW06,TO_NUMBER(TRIM(UVW07)) UVW07,TO_NUMBER(TRIM(UVW08)) UVW08,TO_NUMBER(TRIM(UVW09)) UVW09,TO_NUMBER(TRIM(UVW10)) UVW10,TO_NUMBER(TRIM(UVW11)) UVW11,TO_NUMBER(TRIM(UVW12)) UVW12,TO_NUMBER(TRIM(UVW13)) UVW13,NULL UVK01,NULL UVK02,NULL UVK03,NULL UVK04,NULL UVK05,NULL UVK06,NULL UVK07,NULL UVK08,NULL UVK09,NULL UVK10,NULL UVK11,NULL UVK12,NULL UVK13 FROM CO_BUCH;
Our program selects all records with uivaaenkz of 1 This column should be updated in the Java resultset and set to 0
In resultset updateRow(); We get an exception with Oracle code 01733 Does anyone know we can solve this problem? It works well with Oracle 9i
Edit: first, we write all the selected entries to the file Then we roll back the result set (extracted data) and update the row:
extractedData.beforeFirst();
while (extractedData.next()) {
extractedData.updateString("UIVAAENKZ","0");
extractedData.updateRow();
}
Note that the column names in the view are slightly different from those in the original table uivaaenkz uivaaennkz But as I said, it's not a problem
Original table:
create table CO_BUCH ( uvvol VARCHAR2(4),uvcls VARCHAR2(4),uvid1 VARCHAR2(15),uvid2 VARCHAR2(15),uvid3 VARCHAR2(15),uvid4 VARCHAR2(15),uvid5 VARCHAR2(15),uvid6 VARCHAR2(15),fill001 VARCHAR2(1),uvgj VARCHAR2(3),uveinh VARCHAR2(4),fill002 VARCHAR2(1),uvw00 VARCHAR2(16),fill003 VARCHAR2(1),uvw01 VARCHAR2(16),fill004 VARCHAR2(1),uvw02 VARCHAR2(16),fill005 VARCHAR2(1),uvw03 VARCHAR2(16),fill006 VARCHAR2(1),uvw04 VARCHAR2(16),fill007 VARCHAR2(1),uvw05 VARCHAR2(16),fill008 VARCHAR2(1),uvw06 VARCHAR2(16),fill009 VARCHAR2(1),uvw07 VARCHAR2(16),fill010 VARCHAR2(1),uvw08 VARCHAR2(16),fill011 VARCHAR2(1),uvw09 VARCHAR2(16),fill012 VARCHAR2(1),uvw10 VARCHAR2(16),fill013 VARCHAR2(1),uvw11 VARCHAR2(16),fill014 VARCHAR2(1),uvw12 VARCHAR2(16),fill015 VARCHAR2(1),uvw13 VARCHAR2(16),fill016 VARCHAR2(13),trco_konzern_nr NUMBER,trco_firma_nr NUMBER,trco_betrieb_nr NUMBER,trco_tras_beleg_nr NUMBER,trco_senden_datum DATE,trco_ausgabe VARCHAR2(1000),uivaaennkz VARCHAR2(1),uivaersben VARCHAR2(10),uivdersdat DATE,uivaaenben VARCHAR2(10),uivdaendat DATE,uivndsasta NUMBER(2),uivnfirma NUMBER )
Edit 2:
SELECT COLUMN_ Out of name, data_ TYPE,DATA_ DEFAULT,HIDDEN_ COLUMN,VIRTUAL_ Column from user_ TAB_ COLS WHERE TABLE_ NAME =’CO_ BUCH’ORDER BY COLUMN_ ID;
UVVOL;VARCHAR2;<null>;NO;NO UVCLS;VARCHAR2;<null>;NO;NO UVID1;VARCHAR2;<null>;NO;NO UVID2;VARCHAR2;<null>;NO;NO UVID3;VARCHAR2;<null>;NO;NO UVID4;VARCHAR2;<null>;NO;NO UVID5;VARCHAR2;<null>;NO;NO UVID6;VARCHAR2;<null>;NO;NO FILL001;VARCHAR2;<null>;NO;NO UVGJ;VARCHAR2;<null>;NO;NO UVEINH;VARCHAR2;<null>;NO;NO FILL002;VARCHAR2;<null>;NO;NO UVW00;VARCHAR2;<null>;NO;NO FILL003;VARCHAR2;<null>;NO;NO UVW01;VARCHAR2;<null>;NO;NO FILL004;VARCHAR2;<null>;NO;NO UVW02;VARCHAR2;<null>;NO;NO FILL005;VARCHAR2;<null>;NO;NO UVW03;VARCHAR2;<null>;NO;NO FILL006;VARCHAR2;<null>;NO;NO UVW04;VARCHAR2;<null>;NO;NO FILL007;VARCHAR2;<null>;NO;NO UVW05;VARCHAR2;<null>;NO;NO FILL008;VARCHAR2;<null>;NO;NO UVW06;VARCHAR2;<null>;NO;NO FILL009;VARCHAR2;<null>;NO;NO UVW07;VARCHAR2;<null>;NO;NO FILL010;VARCHAR2;<null>;NO;NO UVW08;VARCHAR2;<null>;NO;NO FILL011;VARCHAR2;<null>;NO;NO UVW09;VARCHAR2;<null>;NO;NO FILL012;VARCHAR2;<null>;NO;NO UVW10;VARCHAR2;<null>;NO;NO FILL013;VARCHAR2;<null>;NO;NO UVW11;VARCHAR2;<null>;NO;NO FILL014;VARCHAR2;<null>;NO;NO UVW12;VARCHAR2;<null>;NO;NO FILL015;VARCHAR2;<null>;NO;NO UVW13;VARCHAR2;<null>;NO;NO FILL016;VARCHAR2;<null>;NO;NO TRCO_KONZERN_NR;NUMBER;<null>;NO;NO TRCO_FIRMA_NR;NUMBER;<null>;NO;NO TRCO_BETRIEB_NR;NUMBER;<null>;NO;NO TRCO_TRAS_BELEG_NR;NUMBER;<null>;NO;NO TRCO_SENDEN_DATUM;DATE;<null>;NO;NO TRCO_AUSGABE;VARCHAR2;<null>;NO;NO UIVAAENNKZ;VARCHAR2;<null>;NO;NO UIVAERSBEN;VARCHAR2;<null>;NO;NO UIVDERSDAT;DATE;<null>;NO;NO UIVAAENBEN;VARCHAR2;<null>;NO;NO UIVDAENDAT;DATE;<null>;NO;NO UIVNDSASTA;NUMBER;<null>;NO;NO UIVNFIRMA;NUMBER;<null>;NO;NO
Solution
We finally found a solution Although it may not be beautiful, it is effective and simple
The problem is that the following columns in the view are selected from different tables:
(SELECT FIR_KO_KREIS FROM FIRMA WHERE FIR_KONZERN_NR = TRCO_KONZERN_NR AND FIR_FIRMA_NR = TRCO_FIRMA_NR) UIVNFIRMA
When we do not select this column during export, everything is normal Unfortunately, we need this column, so we have to propose a different solution
Additional choices around the entire selected data eliminate the problem:
CREATE OR REPLACE VIEW UIV AS SELECT * FROM ( SELECT (SELECT FIR_KO_KREIS FROM FIRMA WHERE FIR_KONZERN_NR = TRCO_KONZERN_NR AND FIR_FIRMA_NR = TRCO_FIRMA_NR) UIVNFIRMA,. . . FROM CO_BUCH);