Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE: invalid file operation Error in oraclebased on unix
UTL_FILE: invalid file operation Error in oraclebased on unix [message #439518] Mon, 18 January 2010 02:10 Go to next message
dkarthick_be
Messages: 4
Registered: January 2010
Location: Bangalore-India
Junior Member

Dear All,

when i execute the following procedures in Oracle 10g based on windows , its executing well.

CREATE DIRECTORY test_dir AS 'c:\';
-- CREATE DIRECTORY test_dir AS '/tmp';
GRANT READ, WRITE TO TEST_DIR

DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('TEST_DIR', 'test_file.txt', 'W');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file.');
END;
/

But i am getting the following error when i execute the oracle 10g based on Unix

ORA-29283: invalid file operation

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

ORA-29283: invalid file operation

Pls help how to resolve the same..


Thanks
Karthik
Re: UTL_FILE in oracle [message #439519 is a reply to message #439518] Mon, 18 January 2010 02:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The database is running on that linux machine, right? (utl_file can only write to the server).
If so, check if the user running the Oracle proceesses has the privilage to create (or alter) the file you try to write.
Re: UTL_FILE in oracle [message #439523 is a reply to message #439519] Mon, 18 January 2010 02:28 Go to previous messageGo to next message
dkarthick_be
Messages: 4
Registered: January 2010
Location: Bangalore-India
Junior Member

Dear Frank,

In Server only we generate the files.
Can you pls tell how to check if the user running the Oracle proceesses has the privilage to create (or alter) the file i try to write ?

Thanks and Regardds,
Karthik
Re: UTL_FILE in oracle [message #439526 is a reply to message #439523] Mon, 18 January 2010 02:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
log on as that user and try to perform those actions from the command line.
Re: UTL_FILE in oracle [message #439528 is a reply to message #439526] Mon, 18 January 2010 02:43 Go to previous messageGo to next message
dkarthick_be
Messages: 4
Registered: January 2010
Location: Bangalore-India
Junior Member

Dear Frank,

After creating directory in Database, we gave read and write permission to that directory in Database level.

is that enough or should i check in OS level?.


Thanks and Regards,
Karthik.

Re: UTL_FILE in oracle [message #439531 is a reply to message #439528] Mon, 18 January 2010 02:52 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
dkarthick_be wrote on Mon, 18 January 2010 14:13


is that enough or should i check in OS level?.

Please do the same thing at OS level also.

Read it completly

sriram Smile

[Updated on: Mon, 18 January 2010 02:56]

Report message to a moderator

Re: UTL_FILE in oracle [message #439663 is a reply to message #439526] Tue, 19 January 2010 00:50 Go to previous messageGo to next message
dkarthick_be
Messages: 4
Registered: January 2010
Location: Bangalore-India
Junior Member

Dear All ,

When i execute the following procedures in oracle based on unix , some times it generate the files and it doesnot work some time and throws the
error:


DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('RIPLEY_DIR', 'test_file.txt', 'W');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file.');
END;

Error Message:


ORA-29283: invalid file operation

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

ORA-29283: invalid file operation



i checked the space in the file generating directory , there is enough space.and the directory has got all the permissions.



Pls help me to resolve the same.

Thanks and Regards,
Karthik.



Re: UTL_FILE in oracle [message #439671 is a reply to message #439663] Tue, 19 January 2010 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ The Oracle directory must reference a valid and accessible server directory

2/ If you cannot point the problem knowing your environment how could we do only knowing what you posted (that is nothing on the environment)?

Copy and paste the WHOLE SQL*Plus session from directory creation to the error.
Post "ls -l" for the OS directory.
Post "ps -eaf" (or equivalent) for listener and Oracle processes)
Post OS groups for the user(s) that started the previous process.
Post user and groups for user that executes the SQL*Plus session.

Regards
Michel
Re: UTL_FILE in oracle [message #439690 is a reply to message #439671] Tue, 19 January 2010 01:50 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
or, even better, ask your DBA, since it looks like you are not able to determine the cause of this problem. I don't mean that in any degrading way, but it looks like you are having difficulties grasping certain concepts.
Best option then is to ask the people that actually are responsible for this stuff.
Previous Topic: utl_file_dir parameter in oracle10G
Next Topic: sql query
Goto Forum:
  


Current Time: Sun Dec 11 06:28:38 CST 2016

Total time taken to generate the page: 0.11781 seconds