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.

Did you know?Explore Trending and Topic pages for more stories like this.
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;
No comments posted yet

Your Answer:

Login to answer
145 Like 8 Dislike
Previous forums Next forums
Other forums

DB2 for z/OS, LUW, iSeries
Hello, In this forum a lot of threads related to other members of the DB2 family than DB2 for z/OS

php mysql script to find record
Hi,

Currently I have a simple table with 4 columns. A, B, C, D

If I want to find all

problems with search form numerical "between" sending by php
I am trying to display the results of a search, on a sql database, on a web page. I set up html &quo

Parse multirow HTML table
Hello all,
I have a site I am working on. Its a sports site and I am trying to add stats to a DB

Selecting an "empty" date formated field
How do you select an "empty" date field? I've tried the few ways I can think.
SQL> select

Echoing ASCII code
I have a php page that splits a string into chars and then echoes the ASCII code for each char. I'm

path to include folder
Hi everyone,

When I'm testing locally, I have to add the name of the site folder as part of t

How to convert this array to string
I am having problems converting this array to a string
using print_r($val[1]); I get:
Code: [

Delete Client 066 Earlywatch
Hi all,

in former times client earlywatch was required for SAPs remote access to SAP inst

BackButton Behaviour in AJAX
I have an ASPX Page AJAX Enabled!The page has a gridView and a DDL for filtering on it. The Gridview

Sign up to write
Sign up now if you have flare of writing..
Login   |   Register
Follow Us
Indyaspeak @ Facebook Indyaspeak @ Twitter Indyaspeak @ Pinterest RSS



Play Free Quiz and Win Cash