0

I have to access a remote file in a different computer using utl_file.

This is what I did:

  1. create a user with Adminstrator+Users+ora_db profile in the remote PC, the same user is created with the same role in my PC.

  2. Run the Oracle Services using this administrator account

  3. Map the remote directory under Windows

  4. use the following command to configure and accesss the file: 

create or replace directory REMOTE_LOG as '\\remote_shared_dir\log';

declare
    f   UTL_FILE.FILE_TYPE;
    line    VARCHAR2(32767) ;

begin

    BEGIN
      f := UTL_FILE.FOPEN('REMOTE_LOG','toto.txt','R',32764);
    EXCEPTION WHEN OTHERS THEN
      dbms_output.put_line('err '||sqlerrm);
    END;

    UTL_FILE.GET_LINE( f,  line );                
    UTL_FILE.FCLOSE( f ) ;                
end;

But it failed with:

[Error] Execution (7: 3): ORA-29282: invalid file ID
ORA-06512: at "SYS.UTL_FILE", line 735
ORA-06512: at line 13

When I remove the exception handler I instead see:

[Error] Execution (7: 3): ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 8

What am I doing wrong?

dbabti
  • 11
  • 6
  • The action for the (new) error is "Verify file and directory access privileges on the file system, and if reading, verify that the file exists". You haven't grant privileges on the directory object, but it looks like the 'DBA' user you're running this as doesn't need them explicitly, and it's an O/S level error. Have you checked you can read the remote file from the local Oracle account, just from Explorer - not from the DB? – Alex Poole Feb 29 '16 at 17:07
  • Yes I can read it and modify it (as i ve already reported it) i can also create a new file in the remote directory using the local oracle account. – dbabti Feb 29 '16 at 17:14
  • So Alex, there is no Windows access issue to this directory and its files. I think it's an Oracle directory/file access issue , what do you think? – dbabti Feb 29 '16 at 17:25
  • 1
    [This article](http://www.jlcomp.demon.co.uk/faq/utl_file.html) is old, but see the "Windows shares and mapped drives section". Your Oracle service may just not be able to see the mapped drive. I don't know if the same workaround applies to your version of Windows, or if it's a good idea; [the documentation](http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5007.htm) just refers to an "operating system directory". I know you can use NFS mounts etc. on Unix; Windows seems less happy with shares. – Alex Poole Feb 29 '16 at 17:31

0 Answers0