Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Unanswered: trigger problem ora-06502

    I have the following message when i try to insert on INFOTEL:
    ORA-06502: PL/SQL: numeric or value error

    The problem seems to be in this line:
    tel_liste := tel_liste || ',' || new_poste;

    :new.indicatif is VARCHAR2(6)
    :new_poste is VARCHAR2(4)

    thank you for your help


    CREATE OR REPLACE TRIGGER T_INFOTEL
    AFTER DELETE OR INSERT OR UPDATE
    OF INDICATIF,POSTE,LOCALISATION,CODE_ANNUAIRE
    ON INFOTEL
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE existMatricule NUMBER;
    tel_liste VARCHAR2(25);
    local_liste VARCHAR2(65);
    new_poste VARCHAR2(10);
    old_poste VARCHAR2(10);
    del_poste VARCHAR2(5);
    del_local VARCHAR2(13);
    existTel NUMBER;
    existBureau NUMBER;
    nb_elts_tel_liste NUMBER;
    nb_elts_local_liste NUMBER;
    position_old_poste NUMBER;
    position_old_localisation NUMBER;
    -- TABLE_MUTANTE EXCEPTION;
    -- PRAGMA EXCEPTION_INIT(TABLE_MUTANTE, -4091);

    BEGIN
    IF INSERTING THEN
    new_poste := (:new.indicatif||:new.poste);
    SELECT count(matricule) into existMatricule from DEVTEL.DIRXML_INFO_USER WHERE matricule = '0'||:new.matricule;
    IF existMatricule = 0 THEN
    IF :new.code_annuaire != 2 and :new.code_annuaire != 5 THEN
    INSERT INTO DEVTEL.DIRXML_INFO_USER(matricule,telephone,bureau ) VALUES ('0'||:new.matricule,:new.indicatif||:new.poste,:n ew.localisation);
    ELSE
    -- traitement liste rouge
    INSERT INTO DEVTEL.DIRXML_INFO_USER(matricule,telephone,bureau ) VALUES ('0'||:new.matricule,'',:new.localisation);
    END IF;
    ELSIF existMatricule = 1 THEN
    SELECT telephone into tel_liste from DEVTEL.DIRXML_INFO_USER WHERE matricule = '0'||:new.matricule;
    SELECT bureau into local_liste from DEVTEL.DIRXML_INFO_USER WHERE matricule = '0'||:new.matricule;

    existTEL := INSTR(tel_liste,new_poste);


    -- 1er cas: liste rouge
    IF :new.code_annuaire = 2 or :new.code_annuaire = 5 THEN
    tel_liste := tel_liste;
    -- 2eme cas: tel_liste est vide
    ELSIF tel_liste is NULL THEN
    tel_liste := new_poste;
    -- 3eme cas: new.poste est NULL
    ELSIF existTEL = '' THEN
    tel_liste := tel_liste;
    -- 4eme cas: new.poste n'existe pas dans tel_liste
    ELSIF existTEL = 0 THEN
    tel_liste := tel_liste || ',' || new_poste;
    END IF;
    existBureau := INSTR(local_liste,:new.localisation);
    -- 1er cas: local_liste est vide
    IF local_liste is NULL THEN
    local_liste := :new.localisation;
    -- 2 eme cas new.localisation est NULL
    ELSIF existBureau = '' THEN
    local_liste := local_liste;
    -- 3 eme cas: new.localisation n'existe pas dans local_liste
    ELSIF existBureau = 0 THEN
    local_liste := local_liste || ',' || :new.localisation;
    END IF;

    UPDATE DEVTEL.DIRXML_INFO_USER SET telephone = tel_liste, bureau = local_liste WHERE matricule = '0'||:new.matricule;
    END IF;

    END IF;
    END IF;
    -- EXCEPTION
    -- WHEN TABLE_MUTANTE THEN
    -- DBMS_OUTPUT.PUT_LINE('Fausse alerte');
    END;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: trigger problem ora-06502

    First, you have posted in the wrong Forum - Oracle-specific question go in the Oracle Forum. I will move it.

    The error indicates that you are trying to assign a value that is too big to fit in the variable.

    If it is indeed that line of code, then it indicates that the length of
    (tel_liste || ',' || new_poste) exceeds 25 characters and so cannot be assigned to tel_liste. You could confirm this by adding a DBMS_OUTPUT.PUT_LINE just before the assignment.

    If you don't mind truncating it you could change the line to:

    tel_liste := SUBSTR(tel_liste || ',' || new_poste,1,25);

  3. #3
    Join Date
    Jan 2004
    Posts
    4
    Thank you for your help


    I resolved the problem before

    you are right
    the length of
    (tel_liste || ',' || new_poste) exceeds 25 characters!!

    thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •