Everyone,
We have a set of 8 jobs which runs in parallel on Unix server. Those jobs calls Oracle stored procedure. All those procedures does a set of DB operations (on different tables) and at the end creates files in Unix server. (Each job creates a file in different names. But puts in same folder)
Recently, we are seeing random failures with error message "ORA-06512: at "SYS.UTL_FILE", line 536". Each day one or two job fails, while creating the error report. When the job is rerun there is no issues. We couldn't reproduce the issue in lower environment.
The folder has all access granted. This jobs have been running for more than an year with no issues. Any ideas appreciated.
Based on my analysis:- DB Operations have completed without any issues. There is no file created (not even an empty one). So it failed while encountering fopen.
Sample code
DECLARE
IN_CONT_TYPE varchar2(100) := 'HARDWARE_ATTRIBUTES' ;
in_batch_name ccpm_epslz_control.push_batch_name%TYPE := 'HARDWARE_ATTRIBUTES_20181211062540';
l_file_type utl_file.file_type;
file_record_hold_cur sys_refcursor;
BEGIN
/*DB Operations*/
l_file_type :=utl_file.fopen('ERR_FOLDER',l_file_name,'W');
utl_file.put_line(l_file_type, 'count of input records filtered based on errors:');
utl_file.put_line(l_file_type, '-----------------------------------------------');
utl_file.put_line(l_file_type, l_col_name_print);
OPEN file_record_hold_cur FOR l_select_stmt_bus;
LOOP
FETCH file_record_hold_cur INTO l_putline_stmt_bus;
EXIT
WHEN file_record_hold_cur%notfound;
utl_file.put_line(l_file_type, l_putline_stmt_bus);
END LOOP;
CLOSE file_record_hold_cur;
utl_file.fclose(l_file_type);
EXCEPTION
WHEN OTHERS THEN
Dbms_Output.put_line ( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
END;
ERROR MESSAGE:- ORA-20051: Internal Error in file generationORA-06512: at "MYPACKAGE", line 84 ORA-06512: at "SYS.UTL_FILE", line 536
