| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: URGENT HELP re: UTL_FILE needed
Jurij Modic wrote:
> On Wed, 08 Sep 1999 04:15:54 GMT, Bob <meyer_b_at_my-deja.com> wrote:
>
> >(NT 4.0 SP3, Oracle 7.3.3.0.0)
> >
> >I absolutely cannot get UTL_FILE.FOPEN to work. I get an invalid
> >directory message every time. I've got UTL_FILE_DIR set in initxxx.ora.
> > I've made sure case matches between init.ora and my script. I've tried
> >it with and without a '\' in initxxx.ora and in my script. I've tried
> >UTL_FILE_DIR=*. I've tried the root directory and subdirectories. And
> >I've created an NT user named oracle and created the subdirctory I'm
> >trying to write to using that user.
> >
> >I'm running the script on the server (but its an anonymous PL/SQL
> >script, not a compiled one--would that make a difference?) I can't
> >think of anything else to try.
> >
> >I've noticed a couple of other people seemed to have the same problem.
> >Has anyone found a solution?
>
> Here is a sample code that works without a problem. I've just tested
> this on my Personal Oracle on Win95, but I've done this many times on
> NT Oracle server.
>
> The entry in my init.ora for this sample was (case sensitive!):
> utl_file_dir = c:\temp
>
> DECLARE
> myfile UTL_FILE.FILE_TYPE;
> BEGIN
> myfile := UTL_FILE.FOPEN('c:\temp','blah.txt', 'w');
> UTL_FILE.PUT_LINE(myfile, 'Hello world');
> UTL_FILE.FCLOSE(myfile);
> EXCEPTION
> WHEN UTL_FILE.INVALID_PATH THEN
> DBMS_OUTPUT.PUT_LINE('invalid_path');
> WHEN UTL_FILE.INVALID_MODE THEN
> DBMS_OUTPUT.PUT_LINE('invalid_mode');
> WHEN UTL_FILE.INVALID_FILEHANDLE THEN
> DBMS_OUTPUT.PUT_LINE('invalid_filehandle');
> WHEN UTL_FILE.INVALID_OPERATION THEN
> DBMS_OUTPUT.PUT_LINE('invalid_operation');
> WHEN UTL_FILE.READ_ERROR THEN
> DBMS_OUTPUT.PUT_LINE('read_error');
> WHEN UTL_FILE.WRITE_ERROR THEN
> DBMS_OUTPUT.PUT_LINE('write_error');
> WHEN UTL_FILE.INTERNAL_ERROR THEN
> DBMS_OUTPUT.PUT_LINE('internal_error');
> END;
>
> But I've noticed some peculiar behavior when tried to write to the
> root directory. Here is what I've found:
>
> INT.ORA UTL_FILE.FOPEN RESULT
> path
> ------- -------------- -------------------------
> c: 'c:' File written on the SQL*Plus's
> startup directory (!!!???)
> c:\ 'c:\' INVALID_PATH exception
> c:\ 'c:' File written on the SQL*Plus's
> startup directory (!!!???)
> c:\ 'c:' File written on the SQL*Plus's
> startup directory (!!!???)
> * 'c:\' File written on the root directory
> * 'c:' File written on the SQL*Plus's
> startup directory (!!!???)
> * 'c:\temp' File written on the c:\temp directory
>
> What surprised me was that client settings (directory from which
> SQL*Plus was started) can have any effect on the UTL_FILE, which
> should be totally client independable. Also, the only way I've found
> to write to the root is by setting the UTL_FILE_DIR parameter to "*".
>
> Note again that at the moment I can't test if the behavior is the same
> under WinNT - this are the results from Win95.
Try either of these:
and then use your code as written OR
2. Change your initxxx.ora setting to:
uti_file_dir = *
and then change your fopen call to:
myfile := UTL_FILE.FOPEN('c:\temp\','blah.txt', 'w');
It seems to be a 7.3.2 and 7.3.3 thing. The got it right in 7.3.4
 |
 |