Wednesday, April 7, 2010

UTL_FILE GET_LINE ORA-29284 File Read Error

While reading a file using UTL_FILE .GET_LINE the procedure kept failing after reading a few line with a ORA-29284 error.
The file and directory had all full permissions [777].
The reason for the error was found to be due to the OCFS [oracle cluster file system] filesystem. Due to the mismatch in the version of the linux kernel and Oracle version.The physical path of 'VECTOR_DP500_ARCHIVE' was mapped to a shared folder on the oracle server.

The error was resolved after changing the physical path to non shared directory

The sql script.
declare

ptempFileName VARCHAR2(30) :='dpps_temp_file.dpps';
tempFileHandle UTL_FILE.FILE_TYPE;
lv_message VARCHAR2(500) := NULL;
lv_log_message VARCHAR2(500) := NULL;
line number:=0;
BEGIN
tempFileHandle := UTL_FILE.FOPEN('VECTOR_DP500_ARCHIVE',ptempFileName, 'R');
LOOP
lv_message:=NULL;
UTL_FILE.get_line(tempFileHandle,lv_message);
line:=line+1;
dbms_output.put_line(line||'='||lv_message||'\n');
END LOOP;
UTL_FILE.FCLOSE(tempFileHandle);
exception
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('no data found');
UTL_FILE.fclose_all();
WHEN OTHERS THEN
UTL_FILE.FCLOSE(tempFileHandle);
lv_log_message:='ERROR: COPY_DPPS_TEMP_FILE--'sqlerrm;
dbms_output.put_line(lv_log_message);
RAISE;
END ;
/

No comments:

Post a Comment