UTL File problem

Posted on 16th Feb 2014 07:03 pm by admin

Hi
I have a file in certain path with the following permissions (The file is a dummy file without
any contents)--Zero byte file.

Permissions -rw-r--r--
I need to check if the file exist or not irrespective of file permissions,Is it possible.
If the file exist call a pl/sql and delete the file

else

Simply print a message.

I have written the following code but

I am getting the following error.. when file exist with above permisison, it will not call
the pl/sql
====================================
After opening utl file File name is :xx.txt
File exists xx.txt at /gpsescs1/custom/gl/inbound
Invalid operation because File does not exist-29283 ORA-29283: invalid file operation
====================================

If the file is present with rwxrwxrwx it completes normal by calliing the pl/sql

================
if the file is not present in the directory
it shows the following message

Invalid operation because File does not exist-29283 ORA-29283: invalid file operation
ORA-06512: at
"SYS.UTL_FILE", line 488
ORA-29283: invalid file operation

========================
declare

v_data_file UTL_FILE.FILE_TYPE ;
v_location VARCHAR2(250):='/gpsescs1/custom/gl/inbound';
v_file_name VARCHAR2(250):='xx.txt';
v_exists BOOLEAN ;
v_f_len NUMBER ;
v_bsize NUMBER ;

BEGIN
v_data_file := UTL_FILE.FOPEN(v_location,v_file_name,'r');
dbms_output.put_line('After opening utl file File name is :'||v_file_name);
UTL_FILE.fgetattr(v_location, v_file_name, v_exists, v_f_len, v_bsize);
--if file exist then delete the existing file
IF v_exists THEN
dbms_output.put_line('File exists '||v_file_name ||' '||'at '||v_location);
--remove the existing file from the inbound directory if errored out
UTL_FILE.fremove (v_location,
v_file_name
);
--call to plsql
dbms_output.put_line('Deleted the file '||v_file_name ||' '||'at '||v_location);
ELSE
dbms_output.put_line('file does not exist '||v_file_name ||' '||'at '||v_location);
END IF;
UTL_FILE.FCLOSE(v_data_file);
--close the file
EXCEPTION
WHEN utl_file.invalid_mode THEN
dbms_output.put_line('invalid mode'||SQLCODE||' '||SQLERRM);
WHEN utl_file.invalid_path THEN
dbms_output.put_line('invalid path'||SQLCODE||' '||SQLERRM);
WHEN utl_file.invalid_filehandle THEN
dbms_output.put_line('invalid File handle'||SQLCODE||' '||SQLERRM);
WHEN utl_file.invalid_operation THEN
dbms_output.put_line('Invalid operation because File does not exist'||SQLCODE||' '||SQLERRM);
WHEN utl_file.write_error THEN
dbms_output.put_line('invalid write error'||SQLCODE||' '||SQLERRM);
WHEN utl_file.internal_error THEN
dbms_output.put_line('invalid internale error'||SQLCODE||' '||SQLERRM);
WHEN utl_file.file_open THEN
dbms_output.put_line('invalid file open'||SQLCODE||' '||SQLERRM);
WHEN utl_file.invalid_filename THEN
dbms_output.put_line('invalid File name'||SQLCODE||' '||SQLERRM);

WHEN utl_file.access_denied THEN
dbms_output.put_line('invalid access denied' ||SQLCODE||' '||SQLERRM);
WHEN OTHERS THEN
dbms_output.put_line('Exception Occured'||SQLCODE||' '||SQLERRM);

end;

Your Answer:

Login to answer

Other forums

Problem with DB connection
Hello there! I'm new to this forum and I'm new to PHP coding also. I wrote something that doesn't ma

need help in dynamic select menu in php
hi i have created a dynamic select menu using php. i have a problem in that which is when the user s

Business Health Check
Hello

I need to create an online business health check for a client it will be multiple choi

SAP Logon Failed
I tried to login to SAP through MMC.

When i click start and give password.

it

Help With editting and deleting form
Hallo !!

So look at this image :

http://img194.imageshack.us/img194/8272/snapshot5f.pn

help with email script...
hey

I need help with my mail script

when the form on http://www.mcgdesignstudio.com/c

The page should be expire when cilck back button
hi,
i'm new to php world.
i create user registration page.
when i submit it,data goes to my

Functions Not Loading Into Div
I had some help doing some of this but what I'm trying to do is get my functions to retrieve its val

Feed Maker
Hi all.
First of all I must say I am not a php developer so I am afraid I don't know much about i

how to populate a drop down box
Hi buddies!

PROCEDURE DOWNLOAD_REPORT_FORM (report_name varchar) is begin DOWNLO