Re: URGENT HELP re: UTL

From: Jurij Modic <jmodic_at_src.si>
Date: Wed, 08 Sep 1999 19:15:34 GMT
Message-ID: <37d8aeca.6303211@news.siol.net>

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.

>Thanks very much,
>
>Bob

HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)

The above opinions are mine and do not represent any official standpoints of my employer Received on Wed Sep 08 1999 - 14:15:34 CDT

Re: URGENT HELP re: UTL_FILE needed