Hi Guys, I am trying to get an invalid_path exception for the below code. But, the error gets displayed as
ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation ORA-06512: at "XYZ.UTLFILE_READ", line 14 ORA-06512: at line 2
But, I want to make it by_pass the default exception and make it user defined like I have tried below.
RAISE_APPLICATION_ERROR(-20103,'The PATH which was specified was invalid');
[ or ] like,
dbms_output.put_line('The path is invalid')
CREATE OR REPLACE PROCEDURE utlfile_read
( directory_in IN VARCHAR2, filename_in IN VARCHAR2 )
IS
v_1 VARCHAR2(200);
f_1 UTL_FILE.FILE_TYPE; /* File Handle */
v_id NUMBER;
v_type VARCHAR2(20);
v_number VARCHAR2(12);
no_data EXCEPTION;
PRAGMA EXCEPTION_INIT(no_data,100);
BEGIN
f_1 := UTL_FILE.FOPEN(directory_in,filename_in,'R');
UTL_FILE.GET_LINE(f_1,v_1);
v_id := INITCAP(SUBSTR(v_1,1,5));
v_type := UPPER(SUBSTR(v_1,7,1));
v_number := UPPER(SUBSTR(v_1,9,12));
INSERT INTO utl_file_insertion VALUES(v_number,v_type);
COMMIT;
UTL_FILE.FCLOSE(f_1);
DBMS_OUTPUT.PUT_LINE('Data Inside The File Are As Follows '||' Number '||v_number||' Type '||v_bltype);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN
RAISE_APPLICATION_ERROR(-20103,'The PATH which was specified was invalid');
WHEN no_data
THEN
DBMS_OUTPUT.PUT_LINE('There is NO DATA inside the specified file');
-- DBMS_OUTPUT.PUT_LINE('The file path '||directory_in||' invalid ');
/*
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('There is no data inside the file '||filename_in||' Error Code '||SQLCODE||' Error Message '||SQLERRM);
*/
END;
Please help.