read&write server files from plsql code

Gor_Mahia

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.

Warren Tolentino

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

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

Gor_Mahia

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

User_H3J7U

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

Mike Kutz

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

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

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

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

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

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.