Home » SQL & PL/SQL » SQL & PL/SQL » utl_file fopen problem
utl_file fopen problem [message #193850] Tue, 19 September 2006 12:30 Go to next message
devp2
Messages: 6
Registered: September 2006
Junior Member
what is the mistake in the below coding:
anyone please suggest me.

declare
f utl_file.file_type;
begin
f := utl_file.fopen('c:\temp', 'test.txt','R');
utl_file.put_line(f, 'line one: some text');
utl_file.put_line(f, 'line two: more text');
utl_file.fclose(f);
end;

i am getting "Invalid directory path"
Re: utl_file fopen problem [message #193852 is a reply to message #193850] Tue, 19 September 2006 12:35 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Make sure that utl_file_dir is set to a list of explicit directories or *.

select value from v$parameter
 where name='utl_file_dir';
Re: utl_file fopen problem [message #193857 is a reply to message #193852] Tue, 19 September 2006 13:28 Go to previous messageGo to next message
devp2
Messages: 6
Registered: September 2006
Junior Member
Hi andrew again,

i created temp directory in the windows.is it ok...
or i have to create in oracle.
let me know.
and i heard that i can write files to serverside but not on client. what's this. i can't understand. can u explain me clarly.
thanka in advance.
regarding connection [message #193864 is a reply to message #193850] Tue, 19 September 2006 13:54 Go to previous messageGo to next message
devp2
Messages: 6
Registered: September 2006
Junior Member
how to connect to system in oracle?
connect system/manager. it's saying invalid username/password.
please help me.
Re: regarding connection [message #193869 is a reply to message #193864] Tue, 19 September 2006 14:35 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
The Oracle server can only write to a location which is visible to it on the host where it runs.
Re: utl_file fopen problem [message #194154 is a reply to message #193850] Thu, 21 September 2006 00:39 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
invalid directory path because

f := utl_file.fopen('c:\temp', 'test.txt','R');

you should use a DIRECTORY object,

create or replace directory my_dir as 'c:\temp\';

declare
v_dir varchar2(10) := 'MY_DIR';
.....
begin
f := utl_file.fopen(v_dir, 'test.txt','R');


HTH
Re: utl_file fopen problem [message #452586 is a reply to message #194154] Thu, 22 April 2010 21:36 Go to previous messageGo to next message
wandroide
Messages: 4
Registered: April 2010
Junior Member
i´m having the same problem but i has already done what is said before and i already have the same problem. I´m trying to use directory. When i did the select thing it returnes null, so i use alter... but nothing changes.
Re: utl_file fopen problem [message #452587 is a reply to message #452586] Thu, 22 April 2010 22:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
ALWAYS
Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: utl_file fopen problem [message #452615 is a reply to message #452586] Fri, 23 April 2010 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.

Before Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
Re: utl_file fopen problem [message #452719 is a reply to message #452615] Fri, 23 April 2010 10:30 Go to previous messageGo to next message
wandroide
Messages: 4
Registered: April 2010
Junior Member
Sorry for everything.

My problem is the following:

The result for SELECT * from v$version; was:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
"CORE 10.2.0.1.0 Production"
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

And i´m using oracle sql developer.

I´m trying to execute this code:

#################################################################
declare
f utl_file.file_type;
V_dir varchar2(10);
begin
dbms_output.put_line('Iniciei programa.');
V_dir := 'dir';
f := utl_file.fopen(V_dir, '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);
dbms_output.put_line('Cheguei no fim.');
end;

And I´m getting as result:


Erro ao iniciar na linha 3 no comando
-- Apaguei o código que estava aqui.
Relatório de erro:
ORA-29283: operação de arquivo inválida
ORA-06512: em "SYS.UTL_FILE", line 475
ORA-29283: operação de arquivo inválida
ORA-06512: em line 7
29283. 00000 - "invalid file operation"
*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.
#################################################################

The problem is that i has already done this code logged as sys:

grant execute on utl_file to public;
create or replace directory dir as 'c:/' ;
grant read,write on directory dir to public;

Something estrange is that when i put this:

alter system set utl_file_dir = 'c:\' scope=spfile;
select value from v$parameter where name='utl_file_dir';

the select returns null but in the file is utl_file_dir = 'c:\'

Please help me.
Re: utl_file fopen problem [message #452721 is a reply to message #452719] Fri, 23 April 2010 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the select returns null but in the file is utl_file_dir = 'c:\'

No the alter system will take effect after the next restart (with the pfile).
Also the directory must be on the databaae server not on the client one.

Regards
Michel
Re: utl_file fopen problem [message #452731 is a reply to message #452721] Fri, 23 April 2010 11:41 Go to previous messageGo to next message
wandroide
Messages: 4
Registered: April 2010
Junior Member
Michel Cadot wrote on Fri, 23 April 2010 10:41

No the alter system will take effect after the next restart (with the pfile).
Also the directory must be on the databaae server not on the client one.



But how can i know that my directory is in the client or in the server? I´m using only one computer.

Also when you say restart you refers to restart the computer or the program? If nothing of them, how i restart it?

Looking better at the error mensage i noticed that the problem can be with the O.S., so how can i see in windows if the directory is liberate for use?

[Updated on: Fri, 23 April 2010 21:50]

Report message to a moderator

Re: utl_file fopen problem [message #452774 is a reply to message #452731] Sat, 24 April 2010 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But how can i know that my directory is in the client or in the server? I´m using only one computer.

In this case, it is local this is what I mean, directory should be local to database server.

Quote:
Also when you say restart you refers to restart the computer or the program? If nothing of them, how i restart it?

Restart the instance (database).

Regards
Michel
Re: utl_file fopen problem [message #452795 is a reply to message #452774] Sat, 24 April 2010 11:57 Go to previous message
wandroide
Messages: 4
Registered: April 2010
Junior Member
Holy Crap(sorry i had to write this)! Only in pl sql. Do you know why everything was going wrong????????????????
Because it create a directory in UPERCASE!!!!!!!!!!!!!!!!!!!
That is, when I put there f := utl_file.fopen('dir', 'something.txt', 'w'); He gives error because don´t exist 'dir',the directory name that it created was 'DIR', WHAT A HELL, waste 3 days of my life to discover that the error was that stupid. But ok, it could be worse, i could waste more days or worse: don´t find the answer.

Sorry for the trouble.
Previous Topic: Oracle sort by date (merged by CM)
Next Topic: Hierarchical Query
Goto Forum:
  


Current Time: Sat Dec 10 03:00:51 CST 2016

Total time taken to generate the page: 0.10901 seconds