Home » SQL & PL/SQL » SQL & PL/SQL » utl_file issue?
utl_file issue? [message #426097] Wed, 14 October 2009 01:56 Go to next message
mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai
Senior Member
Hi,
I want create a file at runtime using utl_file.

First i just create the alias for the server directory using the following command

Select * from all_directories;

OWNER  DIRECTORY_NAME  DIRECTORY_PATH  
SYS  AUTO_MAIL  home/oracle9i/backups


and i had give the permission to access the directory using

grand read,write on directory to public


further, i run the following coding but i am getting the error too.

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen('AUTO_MAIL', 'something.txt', 'w');
  utl_file.put_line(f, 'line one: some text');
  utl_file.put_line(f, 'line two: more text');
  utl_file.fclose(f);
end;


error

declare
*

ERROR at line 1: 
ORA-29283: invalid file operation 
ORA-06512: at "SYS.UTL_FILE", line 449 
ORA-29283: invalid file operation 
ORA-06512: at line 4



please help how to solve this

kanish

Report message to a moderator

Re: utl_file issue? [message #426102 is a reply to message #426097] Wed, 14 October 2009 02:03 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Where to start...
Quote:
First i just create the alias for the server directory using the following command

The command that follows that quote would not create an alias for a directory.
Quote:

and i had give the permission to access the directory using
Nope, code that followed that quote is syntactically invalid.
Did you actually create a directory on the Oracle server's OS? Does Oracle have permission to write to that location?
How about you go through the process and show us a cut and paste from SQL*Plus...

Report message to a moderator

Re: utl_file issue? [message #426103 is a reply to message #426097] Wed, 14 October 2009 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Has the Oracle instance/listener owner privileges to access to the directory?

Regards
Michel

Report message to a moderator

Re: utl_file issue? [message #426106 is a reply to message #426097] Wed, 14 October 2009 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
grand read,write on directory to public

This should be:
grant read,write on directory AUTO_MAIL to public;

Regards
Michel

Report message to a moderator

Re: utl_file issue? [message #426108 is a reply to message #426097] Wed, 14 October 2009 02:13 Go to previous messageGo to next message
mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai
Senior Member
Hi

Sorry typograhicall erron on grant command

As you said i have direcoty on the server and already available ('/home/oracle9i/backups') and it is having read and write access at all level (owner,group,others).

but still have same problem

kanish

Report message to a moderator

Re: utl_file issue? [message #426109 is a reply to message #426108] Wed, 14 October 2009 02:28 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
mm_kanish05 wrote on Wed, 14 October 2009 08:56
Select * from all_directories;

OWNER  DIRECTORY_NAME  DIRECTORY_PATH  
SYS  AUTO_MAIL  home/oracle9i/backups

mm_kanish05 wrote on Wed, 14 October 2009 09:13
Hi

Sorry typograhicall erron on grant command

As you said i have direcoty on the server and already available ('/home/oracle9i/backups')

Another typo or really missing full path start (/) in the directory definition?
mm_kanish05 wrote on Wed, 14 October 2009 09:13
and it is having read and write access at all level (owner,group,others).

You may be right or wrong, it is hard to say without knowing how you got it. It would be good to show how you came to this conclusion, e.g. show the result of 
ls -l /home/oracle9i
ls -al /home/oracle9i/backups

Report message to a moderator

Re: utl_file issue? [message #426127 is a reply to message #426097] Wed, 14 October 2009 03:02 Go to previous messageGo to next message
mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai
Senior Member

hi flyboy,

-bash-3.00$ pwd
/home/oracle9i
-bash-3.00$ ls -ls
total 6304
   8 -rw-r--r--   1 oracle9i dba     441 Jun 30 17:03 a.out
   8 drwxrwxrwx   2 oracle9i dba    4096 Oct 14 08:39 backups



-bash-3.00$ pwd
/home/oracle9i/backups
-bash-3.00$ ls -l
total 8800924
-rw-r--r--  1 oracle9i dba  285612166 Oct 11 08:53 1110.tgz
-rw-r--r--  1 oracle9i dba  285608624 Oct 12 08:47 1210.tgz
-rw-r--r--  1 oracle9i dba  285720394 Oct 13 08:45 1310.tgz
-rw-r--r--  1 oracle9i dba  285792120 Oct 14 08:42 1410.tgz
-rw-r--r--  1 oracle9i dba 1964752896 Oct 11 08:48 expdat.dmp.1110.0840
-rw-r--r--  1 oracle9i dba 1964785664 Oct 12 08:43 expdat.dmp.1210.0835
-rw-r--r--  1 oracle9i dba 1965367296 Oct 13 08:41 expdat.dmp.1310.0832
-rw-r--r--  1 oracle9i dba 1965613056 Oct 14 08:38 expdat.dmp.1410.0832
-rw-r--r--  1 oracle9i dba         21 Oct 13 14:56 todaymail.txt
-bash-3.00$

Report message to a moderator

Re: utl_file issue? [message #426135 is a reply to message #426127] Wed, 14 October 2009 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ls -ld /home/oracle9i

And who owns the instance and listener?
Also you didn't answer to the / question about your directory. Is it defined with "home/oracle9i/backups" or "/home/oracle9i/backups"

Regards
Michel

Report message to a moderator

Re: utl_file issue? [message #426145 is a reply to message #426097] Wed, 14 October 2009 03:35 Go to previous messageGo to next message
mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai
Senior Member

Quote:
Is it defined with "home/oracle9i/backups" or "/home/oracle9i/backups


check my previous mail when i check with pwd and i am getting the following output.

/home/oracle9i/backups.

-bash-3.00$ ls -ld /home/oracle9i
drwxrwxrwx  22 oracle9i dba 4096 Oct 14 08:30 /home/oracle9i
-bash-3.00$

Report message to a moderator

Re: utl_file issue? [message #426147 is a reply to message #426145] Wed, 14 October 2009 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
check my previous mail when i check with pwd and i am getting the following output.

I don't see it in any of your post but the one where / is missing.
So answer CLEARLY, does your directory in database contains the first / or not. YES or NO? Prove it using SQL*Plus and copy and paste:
Select * from all_directories;

You didn't to the question I already posted twice:
who owns the instance and listener?
Yes I checked your post you didn't answer adn even if you did repeat it. YOU have the problem and only YOU.

Regards
Michel

Report message to a moderator

Re: utl_file issue? [message #426148 is a reply to message #426145] Wed, 14 October 2009 03:41 Go to previous message
mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai
Senior Member
Thank michel

the problem solved using /home/oracle9i/backups instead of home/oracle9i/backups

thanks.

Report message to a moderator

Previous Topic: Query is extremly slow
Next Topic: Tips (merged 5)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Aug 10 22:31:23 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.