Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

UTL_FILE PROBLEM ;;; SOMEONE CAN HELP ME

orawissJul 11 2006 — edited Jul 12 2006
Hi everybody,
Someone can Help me , I use the PL Function above to return the contains of one HTML File safeguarded on the disc , and when I test the fuction and I pass the parameter v_file_dir = c:\ and v_file_name = a.htm ; I have the utl_file.invalid_path exception;; And I don't know Why this exception???

CREATE OR REPLACE FUNCTION PARSING_FILE (v_file_dir IN VARCHAR2,
v_file_name IN VARCHAR2 )
RETURN CLOB
IS
vf_filehandle utl_file.file_type;
v_new_line VARCHAR2(32767);
v_rec_count NUMBER := 0;
e_insert_failed exception;
BIG_BUFFER CLOB;
v_err_msg VARCHAR2(1000);

BEGIN

vf_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'r', 4000);

--loop through flat file and load each record
LOOP
BEGIN
BEGIN
utl_file.get_line(vf_filehandle, v_new_line);
IF v_new_line IS NOT NULL THEN
DBMS_LOB.WRITEAPPEND( BIG_BUFFER, LENGTH(v_new_line), v_new_line );
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END;
END LOOP;
RETURN BIG_BUFFER;
EXCEPTION
WHEN utl_file.invalid_path THEN
utl_file.fclose(vf_filehandle);
v_err_msg := 'Invalid FILE Path';
dbms_output.put_line(v_err_msg);
WHEN utl_file.invalid_mode THEN
utl_file.fclose(vf_filehandle);
v_err_msg := 'Invalid MODE';
dbms_output.put_line(v_err_msg);
WHEN utl_file.invalid_filehandle THEN
utl_file.fclose(vf_filehandle);
v_err_msg := 'Invalid FILE Handle';
dbms_output.put_line(v_err_msg);
WHEN utl_file.invalid_operation THEN
utl_file.fclose(vf_filehandle);
v_err_msg := 'Invalid Operation';
dbms_output.put_line(v_err_msg);
WHEN utl_file.read_error THEN
utl_file.fclose(vf_filehandle);
v_err_msg := 'Error Reading FILE';
dbms_output.put_line(v_err_msg);
WHEN utl_file.write_error THEN
utl_file.fclose(vf_filehandle);
v_err_msg := 'Error Writing TO FILE';
dbms_output.put_line(v_err_msg);
WHEN utl_file.internal_error THEN
utl_file.fclose(vf_filehandle);
v_err_msg := 'Internal Error';
dbms_output.put_line(v_err_msg);
WHEN OTHERS THEN
utl_file.fclose(vf_filehandle);
v_err_msg := 'OTHERS: ' ||SQLCODE|| ' Error Message: '||SQLERRM;
dbms_output.put_line(v_err_msg);
END;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 9 2006
Added on Jul 11 2006
36 comments
2,359 views