utl_file.fopen without 'create directory ... as ...'

Question

Hi, everybody. I am new to PL/SQL and Oracle Databases. I need to read/write file that exists on server so i'm using utl_file.fopen('/home/tmp/','text.txt','R') but Oracle shows error 'invalid directory path'.

Main problem is that i have only user privileges, so i cant use commands like create directory user_dir as '/home/temp/' or view utl_file_dir with just show parameter utl_file_dir; I used this code to view utl_file_dir:

SQL> set serveroutput on;
SQL> Declare
  2      Intval number;
  3      Strval varchar2 (500);
  4      Begin
  5       If (dbms_utility.get_parameter_value('utl_file_dir', intval,strval)=0)
  6      Then dbms_output.put_line('value ='||intval);
  7      Else dbms_output.put_line('value = '||strval);
  8      End if;
  9      End;
 10  /

and output was 'value = 0'.

I google'd much but didnt find any solution of this problem, so i'm asking help here.

To read file i used this code:

    declare
    f utl_file.file_type;
    s varchar2(200);
begin
    f := utl_file.fopen('/home/tmp/','text.txt','R');
    loop
        utl_file.get_line(f,s);
        dbms_output.put_line(s);
    end loop;
exception
    when NO_DATA_FOUND then
        utl_file.fclose(f);
end;

score 2 · Accepted Answer · answered Oct 08 '13 at 20:26

2

If you do not have permission to create the directory object (and assuming that the directory object does not already exist), you'll need to send a request to your DBA (or someone else that has the appropriate privileges) in order to create a directory for you and to grant you access to that directory.

utl_file_dir is an obsolete parameter that is much less flexible than directory objects and requires a reboot of the database to change-- unless you're using Oracle 8.1.x or you are dealing with a legacy process that was written back in the 8.1.x days and hasn't been updated to use directories, you ought to ignore it.

answered Oct 08 '13 at 20:26

Justin Cave

227,342
24
367
384

Thank you for answer, but how can i read file with pl sql? I can use mkdir and cat commands in shell, but not able do create directory with sqlplus. – Alexeev Michael Oct 09 '13 at 14:43
@AlexeevMichael - If you want to read a file from PL/SQL (which, remember, can only access files on the database server's file system), you need to be granted appropriate privileges. Someone (your DBA perhaps) realistically needs to create the directory object and grant you access to it. The fact that your operating system account has privileges to create operating system directories is not directly relevant (though it may be something you can use if you want to request the `CREATE DIRECTORY` privilege from your DBA). – Justin Cave Oct 09 '13 at 14:48

utl_file.fopen without 'create directory ... as ...'

1 Answers1