DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004

    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;
    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);
    -- Must close the file
    IF UTL_FILE.IS_OPEN(v_filehandle) THEN
    END IF;

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

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


  2. #2
    Join Date
    Jul 2000
    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 /
    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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.