Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: ORA-06502: PL/SQL: numeric or value error: character to number..........

    HI,
    I run this procedure:
    CREATE OR REPLACE Procedure TEST
    IS
    err_msg number;
    err_num number;
    errore number;
    conta number;
    cursor mycur is
    select
    cod_imm,
    dv.bu_id bu_id,
    nome_sap,
    cod_dest_uso,
    codifica servizio,
    sum(area) area
    from update_x_cod, dv , codifica_servizi
    WHERE cod_dest_uso is not null and servizio is not null
    and update_x_cod.LEGAL_ENTITY=dv.dv_id
    and servizio=codifica_servizi.codice
    group by cod_imm, dv.bu_id, nome_sap, cod_dest_uso, codifica;


    begin

    UPDATE report2
    SET FLAG=1, AREA_OLD=AREA;
    commit;
    dbms_output.put_line ('fase1 update completato');
    for CUR in mycur
    loop
    select count(*) into conta from report2 a
    where a.COD_IMM=CUR.cod_imm
    and a.LEGAL_ENTITY=CUR.bu_id
    and a.NOME_SAP=CUR.nome_sap
    and a.COD_DEST_USO=CUR.COD_DEST_USO
    and a.SERVIZIO=CUR.servizio;
    if conta > 0 then
    UPDATE report2 a
    SET COD_IMM=CUR.cod_imm,
    LEGAL_ENTITY=CUR.bu_id,
    NOME_SAP=CUR.nome_sap,
    COD_DEST_USO=CUR.COD_DEST_USO,
    SERVIZIO=CUR.servizio,
    AREA=CUR.area,
    area_diff= cur.area-area_old,
    flag=0
    where
    a.COD_IMM=CUR.cod_imm
    and a.LEGAL_ENTITY=CUR.bu_id
    and a.NOME_SAP=CUR.nome_sap
    and a.COD_DEST_USO=CUR.COD_DEST_USO
    and a.SERVIZIO=CUR.servizio;

    COMMIT;

    else
    insert into report2 (COD_IMM, LEGAL_ENTITY, NOME_SAP, COD_DEST_USO, SERVIZIO, AREA,AREA_DIFF,AREA_OLD,FLAG)
    VALUES (CUR.cod_imm,CUR.bu_id,CUR.nome_sap,CUR.COD_DEST_U SO,CUR.servizio,CUR.area,CUR.area,0,0);
    commit;
    dbms_output.put_line ('fase2 insert report_sap completato');
    end if;

    end loop;

    EXCEPTION
    WHEN OTHERS THEN
    err_msg:= SUBSTR(SQLERRM, 1, 100);
    err_num:= SQLCODE;
    INSERT INTO ERROR_CODE (proc_name, err_code, err_msg, err_date)
    VALUES ('TEST', err_num, err_msg, sysdate);

    END;

    but I get this error:
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error

    What I wrong?
    Thanks
    Raf

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    one of your insert statements is attempting to insert the wrong datatype.

    IE: you are trying to insert a character into a numeric field


    Use DBMS_output to print out your data before each insert statement in order to debug. OR, recreate test tables to insert into with ALL character fields. Then check the data that was inserted for the culprit(s)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

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

    Re: ORA-06502: PL/SQL: numeric or value error: character to number..........

    This:

    err_msg number;

    should be:

    err_msg varchar2(100);


    However, your code has a very common bug in its exception handling. You are catching all exceptions and writing them to a file, but you do not re-raise the exception (or a different exception) to abort the process. Your procedure can terminate due to an exception after processing half the data, and then finishes apparently successfully. The caller could then commit the changes that had been made up to the point of failure.
    You should at least do this:

    EXCEPTION
    WHEN OTHERS THEN
    err_msg:= SUBSTR(SQLERRM, 1, 100);
    err_num:= SQLCODE;
    INSERT INTO ERROR_CODE (proc_name, err_code, err_msg, err_date)
    VALUES ('TEST', err_num, err_msg, sysdate);
    RAISE;
    END;

  4. #4
    Join Date
    Jul 2002
    Posts
    227
    Originally posted by The_Duck
    one of your insert statements is attempting to insert the wrong datatype.

    IE: you are trying to insert a character into a numeric field


    Use DBMS_output to print out your data before each insert statement in order to debug. OR, recreate test tables to insert into with ALL character fields. Then check the data that was inserted for the culprit(s)
    des report2

    COD_IMM VARCHAR2(32)
    LEGAL_ENTITY VARCHAR2(64)
    NOME_SAP VARCHAR2(16)
    COD_DEST_USO VARCHAR2(32)
    SERVIZIO VARCHAR2(64)
    AREA NUMBER
    AREA_OLD NUMBER
    AREA_DIFF NUMBER
    FLAG NUMBER(38)

    Raf

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    describe these tables where you are getting your data:
    update_x_cod, dv , codifica_servizi

    it would help if you used prefixes on your column-names (or aliases or something) in your selects and cursors. This way we already know what tables they belong to.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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