Or check if the file is not blank or the no. of characters exceeding 2000 (As you have set v_check_stmt to VARCHAR2(2000)).
Main Topics
Browse All TopicsHi, Experts,
I have a PROCEDURE to open a flat file. The following is code;
v_check_FHandle UTL_FILE.FILE_TYPE;
v_input_dir varchar2(100) :='DEPWORK';
v_input_file varchar2(100);
v_check_stmt VARCHAR2(2000);
v_check_cnt number:=0;
v_check_flag boolean := TRUE;
--------------------------
-- Checking the Existance of the file
--------------------------
begin
BEGIN
v_input_file := 'test_data.out';
v_check_FHandle := utl_file.fopen(v_input_dir
utl_file.get_line(v_check_
v_check_cnt := v_check_cnt + 1;
utl_file.fclose(v_check_Fh
IF v_check_cnt > 0 then
v_check_flag := FALSE;
END IF;
EXCEPTION
When NO_DATA_FOUND then
-- the file exists, but has zero bytes
utl_file.fclose(v_check_Fh
v_check_flag := TRUE;
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandl
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.charsetmismatch THEN
RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR
But Later I/O Inconsistent');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_maxlinesi
RAISE_APPLICATION_ERROR(-2
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN utl_file.invalid_offset THEN
RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
BEGIN
-- Open the input file for reading
if v_check_flag then
v_check_FHandle:=UTL_FILE.
end if;
DBMS_OUTPUT.PUT_LINE('File
end;
end;
When I run this in sqlplus, I got a error:
PLS-00483: exception 'INVALID_OPERATION' may appear in at most one exception handler in this block
Please help. Thanks!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
create directory mydir as '/home/';
declare
v_check_FHandle UTL_FILE.FILE_TYPE;
v_input_dir varchar2(100) :='MYDIR';
v_input_file varchar2(100);
v_check_stmt VARCHAR2(2000);
v_check_cnt number:=0;
v_check_flag boolean := TRUE;
--------------------------
-- Checking the Existance of the file
--------------------------
begin
BEGIN
v_input_file := 'h.txt';
v_check_FHandle := utl_file.fopen(v_input_dir
utl_file.get_line(v_check_
v_check_cnt := v_check_cnt + 1;
utl_file.fclose(v_check_Fh
IF v_check_cnt > 0 then
v_check_flag := FALSE;
END IF;
EXCEPTION
When NO_DATA_FOUND then
-- the file exists, but has zero bytes
utl_file.fclose(v_check_Fh
v_check_flag := TRUE;
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandl
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.charsetmismatch THEN
RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR
But Later I/O Inconsistent');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_maxlinesi
RAISE_APPLICATION_ERROR(-2
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN utl_file.invalid_offset THEN
RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
BEGIN
-- Open the input file for reading
if v_check_flag then
v_check_FHandle:=UTL_FILE.
end if;
DBMS_OUTPUT.PUT_LINE('File
end;
end;
/
This code works for me..can you paste your full code. the code you have pasted seems to be ok only but it is partial
.looks like you have exception handler more than once in your full set of code..paste the code from where you are calling this code...
Business Accounts
Answer for Membership
by: sujit_kumarPosted on 2007-07-12 at 07:44:42ID: 19472247
Check if the file exists in the folder specified and Oracle user has READ access to that file.