Forum Stats

  • 3,767,860 Users
  • 2,252,726 Discussions
  • 7,874,367 Comments

Discussions

read&write server files from plsql code

Gor_Mahia
Gor_Mahia Member Posts: 1,108 Bronze Badge

All,

i am having the below anonymous block to READ and WRITE to a file located on the server location mapped to the directory object as below,

DIRECTORY_NAME : DIRECTORY_PATH

MY_DIR  :   \\QRD_ATT\Consumer_Projects\commonfiles\portal


grants exists as below,  

GRANTEE TABLE_SCHEMA TABLE_NAME PRIVILEGE GRANTABLE

=========================================================

APTS SYS MY_DIR EXECUTE YES

APTS SYS MY_DIR READ YES

APTS SYS MY_DIR WRITE YES


I am running this code from APTS shema 

 DECLARE

  F UTL_FILE.FILE_TYPE;

  CURSOR C1 IS select EMPNO, ENAME, SAL from emp where rownum <= 10 ;

  C1_R C1%ROWTYPE;

BEGIN

  F := UTL_FILE.FOPEN('MY_DIR','txt1.txt','w',32767);

  FOR C1_R IN C1

  LOOP

    UTL_FILE.PUT(F,C1_R.EMPNO);

    UTL_FILE.PUT(F,','||C1_R.ENAME);

    UTL_FILE.PUT(F,','||C1_R.SAL);

    UTL_FILE.NEW_LINE(F);

  END LOOP;

  UTL_FILE.FCLOSE(F);

END;


but iam getting the below error yet I've full read & write access to the location ?

Error report -

ORA-29283: invalid file operation

ORA-06512: at "SYS.UTL_FILE", line 536

ORA-29283: invalid file operation

ORA-06512: at line 6

29283. 00000 - "invalid file operation%s"

*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.


Any help why iam getting this error?


thanks.

Tagged:
«1

Answers

  • Warren Tolentino
    Warren Tolentino Member Posts: 5,526 Silver Trophy

    verify if the username name you are using has the read and write privilege to the directory

    SELECT * FROM all_tab_privs WHERE table_name = 'your_directory';
    

    also is the directory is on the same database server where your oracle database is installed at?

  • User_H3J7U
    User_H3J7U Member Posts: 630 Silver Trophy

    See the owner of oracle service. Check an access to path under OS.

  • Gor_Mahia
    Gor_Mahia Member Posts: 1,108 Bronze Badge

    Warren,

    oh that (also is the directory is on the same database server ) seems to be the problem.

    Oracle is running on Unix side but the directory is mapped to windows server directory.

    i had the impression the file can still be accessed since the Oracle directory object with read&write permission points to it. but i need to write to the server file on the window so other external window-based application can read its data.

    any suggestion on this ?

  • Gor_Mahia
    Gor_Mahia Member Posts: 1,108 Bronze Badge

    User_H3J7U

    access to the path isnt an issue ive read&write.

  • Mike Kutz
    Mike Kutz Member Posts: 5,789 Silver Crown
    edited Oct 23, 2021 9:31AM

    Oracle is running on Unix


    The directory  \\QRD_ATT\something isn't a valid Unix directory.

    I've always mounted them first. Make sure that the owner of Oracle can write to that directory at the OS level.

    I think you have to have the remote file system mounted before you can have Oracle write to it.

  • Gor_Mahia
    Gor_Mahia Member Posts: 1,108 Bronze Badge
    edited Oct 23, 2021 4:43PM

    Mike

    Probably i was not clear but the oracle db is installed on unix and the server location below is on windows server side

     \\QRD_ATT\Consumer_Projects\commonfiles\portal

    So how can I setup to have oracle write files to the windows location?

  • Mike Kutz
    Mike Kutz Member Posts: 5,789 Silver Crown

    You need to mount the remote file system so that the oracle user (the account running Oracle DB) can access it as a local file system like /mnt/smb/QRD_ATT/folder/subdir.

  • Gor_Mahia
    Gor_Mahia Member Posts: 1,108 Bronze Badge

    Mike,

    i dont know whether we are communicating but the path you provided is unix (/mnt/smb/QRD_ATT/folder/subdir.) side not windows.

    My external server location is hosted on windows server not unix sever.

    In short -

    i. Oracle is running on unix server,

    ii. File location (file directory) is on Windows server

    so oracle db in #i needs to write to a file located in #ii above.

    thanks.

  • Mike Kutz
    Mike Kutz Member Posts: 5,789 Silver Crown

    Oracle UTL_File writes only on the host server of the Oracle database software, which is Unix.

    This location you want to write to MUST be on the local host server. Period.

    To be able to write to another server, you must make the remote location local to the host server by telling the host server to mount the remote file system.

  • Gor_Mahia
    Gor_Mahia Member Posts: 1,108 Bronze Badge

    Mike,

    thats ( To be able to write to another server, you must make the remote location local to the host server by telling the host server to mount the remote file system.)

    sounds like exactly what i need. i deal with dbas who pretend they know nothing so ive to send them request and they do as instructed meaning ive to figure out what to tell them.

    iam researching on this (mounting steps) so i can inform them what do to.