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

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Hybrid View

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):

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.