error trigger

[raf]

Hi,
I have these tables:
desc LEAD
BL_ID VARCHAR2(32)
DATE_COST_ANAL_END DATE
DATE_COST_ANAL_START DATE
DATE_COSTS_LAST_CALCD DATE
DATE_END DATE
DATE_MOVE DATE
DATE_START DATE
FLOORS VARCHAR2(16)
HPATTERN VARCHAR2(16)
HPATTERN_ACAD VARCHAR2(35)
IMAGE_DOC1 VARCHAR2(64)
IMAGE_DOC2 VARCHAR2(64)
IMAGE_DOC3 VARCHAR2(64)
LANDLORD_TENANT NOT NULL VARCHAR2(8)
LD_CONTACT VARCHAR2(32)
LD_NAME VARCHAR2(32)
LEASE_SUBLEASE NOT NULL VARCHAR2(8)
TN_NAME VARCHAR2(32)
TN_CONTACT VARCHAR2(32)
SPACE_USE VARCHAR2(16)
SIGNED NOT NULL NUMBER(38)
QTY_SUITE_OCCUPANCY NOT NULL NUMBER(38)
QTY_OCCUPANCY NOT NULL NUMBER(38)
OWNED NOT NULL NUMBER(38)
OPTION2 VARCHAR2(16)
OPTION1 VARCHAR2(16)
LS_PARENT_ID VARCHAR2(16)
LEASE_TYPE VARCHAR2(10)
LS_ID NOT NULL VARCHAR2(32)
USE1 NOT NULL VARCHAR2(32)
NAME VARCHAR2(60)
CODE_BL_PADRE VARCHAR2(32)
ADDRESS1 VARCHAR2(50)
ZIP VARCHAR2(10)
COUNTY_ID VARCHAR2(16)
CITY_ID VARCHAR2(48)
CTRY_ID VARCHAR2(16)
DATA_CESSAZ DATE
DATA_AGGIOR DATE
DATA_AGGIOR_AFM DATE
CODE_CT_UBIC VARCHAR2(4)
CODE_CT_COMP VARCHAR2(4)
FLAG_CANC VARCHAR2(2)
CT_SERVCO VARCHAR2(3)
NOTE VARCHAR2(200)
TEL_CENTR VARCHAR2(30)
FAX_CENTR VARCHAR2(30)
NOP VARCHAR2(4)
STATE_ID VARCHAR2(32)
REGN_ID VARCHAR2(16)
REGN_ID2 VARCHAR2(16)
FLAG_RSG CHAR(1)
DATA_FINE_UTILIZZO DATE
COD_IMM_PRECEDENTE VARCHAR2(20)
COD_IMM_SUCCESSIVO VARCHAR2(20)
PROPRIETA CHAR(1)
PROPRIETARIO VARCHAR2(64)
desc TEAR_PM
TIPO_OGG VARCHAR2(10)
COD_IMM NOT NULL VARCHAR2(33)
PT_SERVCO VARCHAR2(2)
CT_SERVCO VARCHAR2(3)
DENOM_AZ VARCHAR2(60)
NOTE VARCHAR2(200)
COD_IMM_SUP VARCHAR2(33)
NOP VARCHAR2(4)
INDIRIZZO VARCHAR2(50)
CAP VARCHAR2(10)
CODE_PROV VARCHAR2(16)
COMUNE VARCHAR2(48)
COUNTRY VARCHAR2(16)
TEL_CENTR VARCHAR2(30)
FAX_CENTR VARCHAR2(30)
DATA_CESSAZ VARCHAR2(8)
DATA_AGGIOR_AFM VARCHAR2(8)
CODE_CT_UBIC VARCHAR2(4)
CODE_CT_COMP VARCHAR2(4)
DATA_FINE_UTILIZZO VARCHAR2(32)
COD_IMM_PRECEDENTE VARCHAR2(20)
COD_IMM_SUCCESSIVO VARCHAR2(20)
PROPRIETA CHAR(1)
desc TEAR_IS
SITO VARCHAR2(64)
TIPO_OGG VARCHAR2(10)
COD_IMM NOT NULL VARCHAR2(39)
PT_SERVCO VARCHAR2(2)
CT_SERVCO VARCHAR2(3)
DENOM_AZ VARCHAR2(50)
NOTE VARCHAR2(200)
COD_IMM_SUP VARCHAR2(32)
NOP CHAR(3)
INDIRIZZO VARCHAR2(50)
CAP VARCHAR2(10)
CODE_PROV VARCHAR2(16)
COMUNE VARCHAR2(48)
COUNTRY VARCHAR2(16)
TEL_CENTR VARCHAR2(30)
FAX_CENTR VARCHAR2(30)
DATA_CESSAZ VARCHAR2(8)
DATA_AGGIOR_AFM VARCHAR2(8)
CODE_CT_UBIC VARCHAR2(4)
CODE_CT_COMP VARCHAR2(4)
DATA_FINE_UTILIZZO DATE
COD_IMM_PRECEDENTE VARCHAR2(20)
COD_IMM_SUCCESSIVO VARCHAR2(20)
PROPRIETA VARCHAR2(1)
I created this trigger:
CREATE OR REPLACE TRIGGER AFTER_UPD
AFTER UPDATE OF DATA_FINE_UTILIZZO, COD_IMM_SUCCESSIVO
ON LEAD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
tmpVar := 0;
BEGIN
SELECT COUNT(*) INTO TMPVAR FROM TEAR_PM WHERE COD_IMM=substr(:OLD.LS_ID,1,2)||'-'||REPLACE(LTRIM(REPLACE(SUBSTR(:OLD.LS_ID,3),'0',' ')),' ','0');
IF TMPVAR>0 THEN
DELETE TEAR_PM WHERE COD_IMM=substr(:OLD.LS_ID,1,2)||'-'||REPLACE(LTRIM(REPLACE(SUBSTR(:OLD.LS_ID,3),'0',' ')),' ','0');
END IF;
INSERT INTO TEAR_PM (TIPO_OGG, COD_IMM,DENOM_AZ,COD_IMM_SUP, NOP, INDIRIZZO, CAP,CODE_PROV,COMUNE,COUNTRY,TEL_CENTR,FAX_CENTR,DATA_CESSAZ,DATA_AGGIOR_AFM,CODE_CT_UBIC, CODE_CT_COMP,DATA_FINE_UTILIZZO,COD_IMM_PRECEDENTE,COD_IMM_SUCCESSIVO,
PROPRIETA)
VALUES (ld.use1,substr(:OLD.LS_ID,1,2)||'-'||REPLACE(LTRIM(REPLACE(SUBSTR(:OLD.LS_ID,3),'0',' ')),' ','0'), :OLD.NAME,
:OLD.CODE_BL_PADRE, :OLD.NOP,:OLD.ADDRESS1, :OLD.ZIP, :OLD.COUNTY_ID, :OLD.CITY_ID, :OLD.CTRY_ID,
:OLD.TEL_CENTR,:OLD.FAX_CENTR, TO_DATE(:OLD.DATA_CESSAZ), TO_DATE(:OLD.DATA_AGGIOR_AFM), :OLD.CODE_CT_UBIC,
:OLD.CODE_CT_COMP, TO_DATE(:OLD.DATA_FINE_UTILIZZO,'DDMMYYYY'), :OLD.COD_IMM_PRECEDENTE, :OLD.COD_IMM_SUCCESSIVO,:OLD.PROPRIETA);
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END;
BEGIN
tmpVar := 0;
SELECT COUNT(*) INTO TMPVAR FROM TEAR_IS WHERE SUBSTR(COD_IMM,8)=:OLD.LS_ID;
IF TMPVAR>0 THEN
DELETE TEAR_IS WHERE SUBSTR(COD_IMM,8)=:OLD.LS_ID;
END IF;

INSERT INTO TEAR_IS (TIPO_OGG, COD_IMM, COD_IMM_SUP, NOP,
TEL_CENTR, FAX_CENTR, DATA_CESSAZ, DATA_AGGIOR_AFM,
CODE_CT_UBIC, CODE_CT_COMP, DATA_FINE_UTILIZZO, COD_IMM_PRECEDENTE,
COD_IMM_SUCCESSIVO, PROPRIETA )
VALUES
(ld.use1, :OLD.LS_ID,:OLD.CODE_BL_PADRE, 'EMS',
:OLD.TEL_CENTR, :OLD.FAX_CENTR, TO_DATE(:OLD.DATA_CESSAZ), TO_DATE(:OLD.DATA_AGGIOR_AFM),
:OLD.CODE_CT_UBIC, :OLD.CODE_CT_COMP, TO_DATE(:OLD.DATA_FINE_UTILIZZO, 'DDMMYYYY'), :OLD.COD_IMM_PRECEDENTE,
:OLD.COD_IMM_SUCCESSIVO, :OLD.PROPRIETA );
END;
END AFTER_UPD;
but when I update LEAD table (update LEAD
set data_fine_utilizzo=data_fine_utilizzo
where ls_id='00001234')

I get this error:
ORA-01401: inserted value too large for column
ORA-06512: at "AFTER_UPD", line 76
ORA-04088: error during execution of trigger 'AFTER_UPD'
What i wrong??
Thanks
Raf

[AOrehek]

As Oracle says : ORA-01401: inserted value too large for column

You try to insert into TIPO_OGG "VARCHAR2(10)" data from column
USE1 "VARCHAR2(32)"