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_string VARCHAR2(1023); -- Max length of a string (Oracle limitation)
CURSOR c_giving_current IS
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
|| NVL(a.igc_claim_amount,' ') || v_chars
|| NVL(a.igc_handler_code,' ') ||'"';
-- Must close the file
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(v_filehandle) THEN
Perhaps this is a result of some change in the utl_file utility in 9i?
Anyway, I'm pretty stumped! Any suggestions? Thanks!
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.
2 dummy varchar2(1);
4 dummy:=nvl(0,' ');
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4