OraFAQ Forum: SQL & PL/SQL » UTL_FILE: invalid file operation Error in oraclebased on unix

Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE: invalid file operation Error in oraclebased on unix

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UTL_FILE: invalid file operation Error in oraclebased on unix [message #439518]

Mon, 18 January 2010 02:10

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

Report message to a moderator

Re: UTL_FILE in oracle [message #439519 is a reply to message #439518]

Mon, 18 January 2010 02:15

Frank
Messages: 7901
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.

Report message to a moderator

Re: UTL_FILE in oracle [message #439523 is a reply to message #439519]

Mon, 18 January 2010 02:28

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

Report message to a moderator

Re: UTL_FILE in oracle [message #439526 is a reply to message #439523]

Mon, 18 January 2010 02:34

Frank
Messages: 7901
Registered: March 2000

Senior Member

log on as that user and try to perform those actions from the command line.

Report message to a moderator

Re: UTL_FILE in oracle [message #439528 is a reply to message #439526]

Mon, 18 January 2010 02:43

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.

Report message to a moderator

Re: UTL_FILE in oracle [message #439531 is a reply to message #439528]

Mon, 18 January 2010 02:52

ramoradba
Messages: 2457
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

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.

Report message to a moderator

Re: UTL_FILE in oracle [message #439671 is a reply to message #439663]

Tue, 19 January 2010 00:58

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: UTL_FILE in oracle [message #439690 is a reply to message #439671]

Tue, 19 January 2010 01:50

Frank
Messages: 7901
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.

Report message to a moderator

Previous Topic:	utl_file_dir parameter in oracle10G
Next Topic:	sql query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 09 20:35:10 CST 2024