CODEWITHSUNDEEP
oracle-databaseplsql
Vineet
Vineet

Reputation: 5359

Error in opening a file residing on server through utl_file.open!

I am trying to open a file residing in data base server , i used SELECT * FROM V$PARAMETER WHERE NAME = 'utl_file_dir' to find out directory path.

When i am executing this code i am getting this error. error coming "ORA-29283: invalid file operation"

declare
    v_file_handler utl_file.file_type;
    p_dir varchar2(100):='/d04/data/edi/inbound';
    v_no  number:=1;
    v_file varchar2(30):='ut_file.txt';
    begin
    if utl_file.is_open(v_file_handler) then 
    dbms_output.put_line('Already opened');
    else
    v_file_handler:= utl_file.fopen(p_dir,v_file,'r');
    utl_file.putf(v_file_handler,'program %s\n',sysdate);
    dbms_output.put_line('not opened');
    end if;
    exception
    when others then
    dbms_output.put_line(sqlerrm);
    end;

Upvotes: 0

Views: 1039

Answers (2)

Gary Myers
Gary Myers

Reputation: 35401

utl_file_dir has been deprecated in favour of CREATE DIRECTORY

Upvotes: 1

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

v_file_handler:= utl_file.fopen(p_dir,v_file,'r');
utl_file.putf(v_file_handler,'program %s\n',sysdate);

You're opening a file for reading and attempting to write to it. That's bound to throw the error.

From Oracle documentation:

UTL_FILE.FOPEN (
   location     IN VARCHAR2,
   filename     IN VARCHAR2,
   open_mode    IN VARCHAR2,
   max_linesize IN BINARY_INTEGER) 

open_mode Specifies how the file is opened. 
Modes include:  r -- read text

Also,

ORA-29283: invalid file operation

Cause: An attempt was made to read from a file or directory that does not exist, or file or directory access was denied by the operating system.

Action: Verify file and directory access privileges on the file system, and if reading, verify that the file exists.

Upvotes: 1

Related Questions