DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: utl_file ORA-06502: PL/SQL: numeric or value error: character to number conversion

  1. #1
    Join Date
    Aug 2004
    Posts
    1

    utl_file ORA-06502: PL/SQL: numeric or value error: character to number conversion

    Hello, all -- I did a search, but didn't come up with anything on this (could have missed it, but anyway...). The background here is a little complex, and I don't know where the problem is, so bear with me.

    I have three utl_file scripts. They've all been in production and working fine. We recently converted to 9i. I had to change two proc's that populate the tables for two of the utl_file scripts, and now I am getting this error whenever there is actual data in those two tables: ORA-06502: PL/SQL: numeric or value error: character to number conversion error. The other utl_file script still works fine.

    The line number on the error is the RAISE statement in the exception handler code at the end of each of these utl_file scripts. The utl_file scripts themselves have not changed at all, nor have the table layouts in the proc's feeding the data into the utl_file scripts. Here is one of the scripts (it's short):

    CREATE OR REPLACE PROCEDURE to_iac_giving_current_fix_file AS
    v_filepath VARCHAR2(100) := '/Data/FTP'; -- IMPORTANT: No trailing slash
    v_filename VARCHAR2(50) := 'to_iac_giving_current_fix_';
    v_filesuffix VARCHAR2(4) := '.csv';
    v_datestamp VARCHAR2(8) := TO_CHAR(SYSDATE,'MMDDYYYY');
    v_chars VARCHAR2(3) := '","';
    v_filehandle UTL_FILE.FILE_TYPE;
    v_string VARCHAR2(1023); -- Max length of a string (Oracle limitation)
    CURSOR c_giving_current IS
    SELECT *
    FROM blah.to_iac_giving_current
    ORDER BY igc_id_number;
    BEGIN
    v_filename := v_filename || v_datestamp || v_filesuffix;
    -- Open file for writing ('w' overwrites, 'a' appends)
    v_filehandle := UTL_FILE.FOPEN(v_filepath, v_filename, 'w');
    FOR a IN c_giving_current LOOP
    -- Clear variable
    v_string := '';
    v_string := '"' || a.igc_id_number || v_chars
    || NVL(a.igc_iac_payment_id,' ') || v_chars
    BLAH, blah
    || NVL(a.igc_claim_amount,' ') || v_chars
    || NVL(a.igc_handler_code,' ') ||'"';
    UTL_FILE.PUT_LINE(v_filehandle, v_string);
    END LOOP;
    -- Must close the file
    UTL_FILE.FCLOSE(v_filehandle);
    EXCEPTION
    WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(v_filehandle) THEN
    UTL_FILE.FCLOSE(v_filehandle);
    END IF;
    RAISE;
    END;

    Perhaps this is a result of some change in the utl_file utility in 9i?

    Anyway, I'm pretty stumped! Any suggestions? Thanks!

    Donna

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    No change in UTL_FILE , but in the behaviour of NVL. You get the error because the datatypes in some of the NLV functions (NVL(a.igc_claim_amount,' ')) do not match. Use TO_CHAR to convert the numbers to character.

    SQL> declare
    2 dummy varchar2(1);
    3 begin
    4 dummy:=nvl(0,' ');
    5 end;
    6 /
    declare
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 4

    SQL> declare
    2 dummy varchar2(1);
    3 begin
    4 dummy:=nvl(to_char(0),' ');
    5 end;
    6 /

    PL/SQL procedure successfully completed.

Posting Permissions

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


Click Here to Expand Forum to Full Width