Home » SQL & PL/SQL » SQL & PL/SQL » Invalid File Operation (oracle 10g, windows 7)
Invalid File Operation [message #590230] Tue, 16 July 2013 02:10 Go to next message
gowthamsanj
Messages: 18
Registered: June 2013
Location: tirupur
Junior Member
CREATE OR REPLACE PROCEDURE SAL_STATUS(DIR IN VARCHAR2,FILENAME IN VARCHAR2)
IS
FILE UTL_FILE.FILE_TYPE;
CURSOR EMPC IS SELECT ENAME,SAL,DEPTNO FROM SCOTT.EMP ORDER BY DEPTNO;
NEWDEPTNO SCOTT.EMP.DEPTNO%TYPE;
OLDDEPTNO SCOTT.EMP.DEPTNO%TYPE:=0;
BEGIN
FILE:=UTL_FILE.FOPEN(DIR,FILENAME,'W');
UTL_FILE.PUT_LINE(FILE,'REPORT: GENERATED ON '||SYSDATE);
UTL_FILE.NEW_LINE(FILE);
FOR EMP_REC IN EMPC
LOOP
IF EMP_REC.DEPTNO<>OLDDEPTNO THEN
UTL_FILE.PUT_LINE(FILE,'DEPARTMENT: '||EMP_REC.DEPTNO);
UTL_FILE.NEW_LINE(FILE);
END IF;
UTL_FILE.PUT_LINE(FILE,' EMPLOYEE: '||EMP_REC.ENAME||' EARNS : '||EMP_REC.SAL);
OLDDEPTNO:=EMP_REC.DEPTNO;
UTL_FILE.NEW_LINE(FILE);
END LOOP;
UTL_FILE.PUT_LINE(FILE,'END OF REPORT');
UTL_FILE.FCLOSE(FILE);
EXCEPTION
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20001,'INVALID FILE.');
WHEN UTL_FILE.WRITE_ERROR THEN
RAISE_APPLICATION_ERROR(-20002,'UNABLE TO WRITE TO FILE.');
END SAL_STATUS;

Procedure Created

when i execute

BEGIN
SAL_STATUS('MYDIR','ERECORD.TXT');
END;

BEGIN
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at "SYS.SAL_STATUS", line 8
ORA-06512: at line 2

how can i rectify this
Re: Invalid File Operation [message #590231 is a reply to message #590230] Tue, 16 July 2013 02:18 Go to previous messageGo to next message
Littlefoot
Messages: 19605
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Do you have privileges to access files in that directory? You understand that it is a directory located on a database server, not your own (local) computer?

Besides, think twice before creating your procedures in SYS schema. This is most probably a very bad decision.
Re: Invalid File Operation [message #590232 is a reply to message #590231] Tue, 16 July 2013 02:46 Go to previous messageGo to next message
gowthamsanj
Messages: 18
Registered: June 2013
Location: tirupur
Junior Member
ya the database server is my local computer, i installed for practising purpose, and i issue " grant read,write on directory mydir to public "

grant succeeded

Re: Invalid File Operation [message #590233 is a reply to message #590232] Tue, 16 July 2013 03:08 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
gowthamsanj wrote on Tue, 16 July 2013 09:46
ya the database server is my local computer, i installed for practising purpose, and i issue " grant read,write on directory mydir to public "


You must also give privileges at operating system level, the oracle OS user must actually be able to access that directory on the system.

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_file.htm#autoId15
Quote:

. . .
The privileges needed to access files in a directory object are operating system specific. UTL_FILE directory object privileges give you read and write access to all files within the specified directory.
. . .


Regards,
Dariyoosh
Re: Invalid File Operation [message #590294 is a reply to message #590233] Tue, 16 July 2013 15:53 Go to previous message
mikomi
Messages: 33
Registered: July 2013
Member
What output do you get if you run the following:

SELECT * FROM USER_TAB_PRIVS WHERE TABLE_NAME = '<directoryobjectname>'
SELECT * FROM ALL_DIRECTORIES WHERE DIRECTORY_NAME = '<directoryobjectname>'




Previous Topic: Updating view using instead of trigger
Next Topic: XML not saving in a particular directory
Goto Forum:
  


Current Time: Wed Sep 17 02:48:02 CDT 2014

Total time taken to generate the page: 0.11931 seconds