Xref: alice comp.databases.oracle.server:28680
Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!btnet-peer!btnet!news-feed1.eu.concert.net!news-hub.siol.net!news.siol.net!not-for-mail
From: jmodic@src.si (Jurij Modic)
Newsgroups: comp.databases.oracle.server
Subject: Re: Who to use package utl_file ?
Organization: SRC d.o.o, Ljubljana, Slovenia
Message-ID: <35f6ff0d.17590950@news.siol.net>
References: <35F67EBC.D827258E@ce.philips.nl>
X-Newsreader: Forte Free Agent 1.11/32.235
Lines: 57
Date: Wed, 09 Sep 1998 22:20:22 GMT
NNTP-Posting-Date: Thu, 10 Sep 1998 00:20:22 MET DST

On Wed, 09 Sep 1998 15:12:28 +0200, "E. van Diermen"
<diermene@ce.philips.nl> wrote:

>Hay ye'all
>
>I have the following problem/question:
>- How can I create a (e.g. text) file in pl/sql (stored procedure) ?
>(e.g. c:\temp\temp.txt)???
>
>It is easy to create this in a library with the text_io package, but
>this package is of course not usable in a stored procedure...so, use the
>utl_file. But this package gives errors when I want to use it:
>
>SQL> declare
>  2    ft utl_file.file_type;
>  3  begin
>  4    ft := utl_file.fopen('C:\temp\','temp.txt','w');
>  5  end;
>  6  /
>declare
>*
>ERROR at line 1:
>ORA-06510: PL/SQL: unhandled user-defined exception
>ORA-06512: at "SYS.UTL_FILE", line 82
>ORA-06512: at "SYS.UTL_FILE", line 120
>ORA-06512: at line 4

I belive your unhandeled exception is an exception defined in UTL_FILE
package: EXCEPTION UTL_FILE.INVALID_PATH. You can verify this by
including this exception handler into your code.

If you set "UTL_FILE_DIR = *" (read about the security issues
regarding this setting!) in your init<SID>.ora, you'll get your file
"c:\temp\temp.txt" on the server, if such directory exists.

If you set "UTL_FILE_DIR = C:\temp", then you must not use the last
trailing backslash when specifying the path in your call to
UTL_FILE.FOPEN, e.g.

        ft := utl_file.fopen('C:\temp','temp.txt','w');

Remember also that specified path must mach the path in UTL_FILE_DIR
parameter *exactly*. It is case sensitive, although you are on Windows
environment, which is generaly case insensitive.

>Can anyone help me with this? Am I using the utl_file package
>improperly??
>
>Erwin

HTH,
--
Jurij Modic <jmodic@src.si>
Certified Oracle7 DBA (OCP)
================================================
The above opinions are mine and do not represent
any official standpoints of my employer