niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » Oracle
UTL_FILE
Post new topic   Reply to topic Page 1 of 1 [6 Posts] View previous topic :: View next topic
Author Message
Zephyr
*nix forums beginner


Joined: 27 Jun 2005
Posts: 10

PostPosted: Tue Feb 15, 2005 12:35 pm    Post subject: UTL_FILE Reply with quote
Hi,

I want to write from a procedure into a file on the server using UTL_FILE
and do not have a parameter
UTL_FILE_DIR= in my init.ora specifiing a path. The server version is
9.2.0.4 and as far as I know,
I do not need UTL_FILE_DIR with this version. I'am using 'create
directory...' instead.

This is my code for writing in a file on a windows machine

p_FileDir varchar2 (50) := 'C:\';
--p_FileDir varchar2 (50) := '\\elseserver\results';
p_FileName varchar2 (20) := 'user.txt';

v_FileHandle := UTL_FILE.FOPEN (p_FileDir, p_FileName, 'w');
UTL_FILE.PUT_LINE (v_FileHandle, v_text);

I tried several volumes, folders, notations and UNC notation, but always get

ORA-29280: invalid directory path

Has anyone a hint, or do I need the UTL_FILE_DIR parameter set on the
oracle server?


Thanx
Patrick
Back to top
Norman Dunbar
*nix forums beginner


Joined: 09 Feb 2005
Posts: 24

PostPosted: Tue Feb 15, 2005 12:35 pm    Post subject: Re: UTL_FILE Reply with quote
Zephyr wrote:
Quote:
Hi,

I want to write from a procedure into a file on the server using UTL_FILE
and do not have a parameter
UTL_FILE_DIR= in my init.ora specifiing a path. The server version is
9.2.0.4 and as far as I know,
I do not need UTL_FILE_DIR with this version. I'am using 'create
directory...' instead.

This is my code for writing in a file on a windows machine

p_FileDir varchar2 (50) := 'C:\';
--p_FileDir varchar2 (50) := '\\elseserver\results';
p_FileName varchar2 (20) := 'user.txt';

v_FileHandle := UTL_FILE.FOPEN (p_FileDir, p_FileName, 'w');
UTL_FILE.PUT_LINE (v_FileHandle, v_text);

I tried several volumes, folders, notations and UNC notation, but always get

ORA-29280: invalid directory path

Has anyone a hint, or do I need the UTL_FILE_DIR parameter set on the
oracle server?


Thanx
Patrick



Hi Patrick,

here's a couple of (blatent) hints on setting up UTL_FILE. Enjoy.

http://www.jlcomp.demon.co.uk/faq/utl_file.html
http://www.jlcomp.demon.co.uk/faq/utlfiledebug.html

I suspect the first one above will probably explain your problem(s).

Cheers,
Norm.
Back to top
Rauf Sarwar
*nix forums Guru


Joined: 03 May 2005
Posts: 353

PostPosted: Tue Feb 15, 2005 1:35 pm    Post subject: Re: UTL_FILE Reply with quote
Zephyr wrote:
Quote:
Hi,

I want to write from a procedure into a file on the server using
UTL_FILE
and do not have a parameter
UTL_FILE_DIR= in my init.ora specifiing a path. The server version is
9.2.0.4 and as far as I know,
I do not need UTL_FILE_DIR with this version. I'am using 'create
directory...' instead.

This is my code for writing in a file on a windows machine

p_FileDir varchar2 (50) := 'C:\';

<snip>

I'll stop here. You mentioned that you are correctly using DIRECTORY
object in 9i instead of utl_file_dir then what are you doing specifying
this parameter? You should specify the DIRECTORY name in the first
parameter of fopen and also make sure the user has read/write
priviliges on the directory object e.g.

SQL> create or replace directory FOO as 'c:\temp';

Directory created.

SQL> grant read,write on directory foo to foo_user;

Grant succeeded.

SQL> connect foo_user/foo_user
Connected.
SQL> declare
2 fHandle_ utl_file.file_type;
3 begin
4 fHandle_ := utl_file.fopen('FOO', 'foo_test.txt', 'w');
5 utl_file.put_line(fHandle_, 'Foo write test');
6 utl_file.fclose(fHandle_);
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> host notepad c:\temp\foo_test.txt

Regards
/Rauf
Back to top
Zephyr
*nix forums beginner


Joined: 27 Jun 2005
Posts: 10

PostPosted: Tue Feb 15, 2005 2:03 pm    Post subject: Re: UTL_FILE Reply with quote
Thanx,

this solves my problem, but I can't write in UNC notation pathes
even if the oracle user has write access to the UNC share.

I always get

ORA-29283: invalid file operation

Is it possible to write in UNC pathes?

Thanx
Patrick
> 4 fHandle_ := utl_file.fopen('FOO', 'foo_test.txt', 'w');
Back to top
Rauf Sarwar
*nix forums Guru


Joined: 03 May 2005
Posts: 353

PostPosted: Tue Feb 15, 2005 2:20 pm    Post subject: Re: UTL_FILE Reply with quote
Zephyr wrote:
Quote:
Thanx,

this solves my problem, but I can't write in UNC notation pathes
even if the oracle user has write access to the UNC share.

I always get

ORA-29283: invalid file operation

Is it possible to write in UNC pathes?

Thanx
Patrick
4 fHandle_ := utl_file.fopen('FOO', 'foo_test.txt', 'w');

I don't think Oracle will accept UNC \\ names or mapped drives (not
confirmed). You may have to use local fixed drive. If you use Java's
File IO, then it can write to UNC or mapped drives.

Regards
/Rauf
Back to top
Andy Hassall
*nix forums Guru


Joined: 20 Feb 2005
Posts: 874

PostPosted: Tue Feb 15, 2005 9:01 pm    Post subject: Re: UTL_FILE Reply with quote
On Tue, 15 Feb 2005 16:03:58 +0100, "Zephyr" <p.neumann@ids-scheer.de> wrote:

Quote:
this solves my problem, but I can't write in UNC notation pathes
even if the oracle user has write access to the UNC share.

I always get

ORA-29283: invalid file operation

Is it possible to write in UNC pathes?

Yep.

$ sqlplus test/test

SQL*Plus: Release 9.2.0.5.0 - Production on Tue Feb 15 21:56:25 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create or replace directory FOO as '//server/andyh/';

Directory created.

SQL> declare
2 fHandle_ utl_file.file_type;
3 begin
4 fHandle_ := utl_file.fopen('FOO', 'foo_test.txt', 'w');
5 utl_file.put_line(fHandle_, 'Foo write test');
6 utl_file.fclose(fHandle_);
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> host cat //server/andyh/foo_test.txt
Foo write test

Running sqlplus under cygwin on Windows, hence the Unix-ish cat working on a
UNC path - but obviously Oracle isn't running under Cygwin so it doesn't affect
the proof that Oracle can access UNC paths.

To get it to work, changed the service to log on as my user which has access
to that share, instead of LocalSystem.

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [6 Posts] View previous topic :: View next topic
The time now is Thu Jan 08, 2009 3:32 am | All times are GMT
navigation Forum index » Databases » Oracle
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts utl_file pawan_test Tools 1 Tue Jun 20, 2006 12:08 am
No new posts utl_file pawan_test Oracle 6 Tue Jun 20, 2006 12:05 am
No new posts utl_file pawan_test Server 5 Tue Jun 20, 2006 12:02 am
No new posts Unable to read a Unicode File using UTL_FILE carla_rj@imagelink.com.br Oracle 3 Thu May 25, 2006 1:47 pm
No new posts UTL_FILE.put_line() and HEXTORAW() spambox1@mindspring.com Oracle 1 Fri Apr 21, 2006 7:03 pm

Debt Consolidation | McDonalds | Links of Movies | Credit Cards UK | Bankruptcy
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.2003s ][ Queries: 16 (0.1156s) ][ GZIP on - Debug on ]