UTL_FILE, problems w/ read append on digital unix

Hi,                               Kiel, 30-DEC-1999

I have read about similar problems in this newsgroup but found no solution yet.

I am using the UTL_FILE package.
For a while it worked without problems using FOPEN with all three access modes (r,w,a).

But now I discovered, that sometimes errors ('Invalid Operation') occur. Only with the modes r (read) and a (append) (write works fine). I think, this is because the database sometimes doesn't find the file which is to be opened.

I made some funny (?) observations:
- After restarting the database everything works fine for a
  while. But then after a number of trials, I get the   exception 'invalid operation' all the time.
- If I start SQL*Plus in one shell, I can execute the
  commands many times without getting an error. In another   shell, I receive the error after first time execution.   If I close SQL*Plus in the first shell and restart SQL*Plus,   I will receive the error too.
- But somtimes I can open 2 or 3 SQL*Plus sessions (at the same
  time) without errors, the error comes up in the 4th (or so ...).

It seems to be a bug. Hotline doesn't help. Do you have any hints? Or a workaround? (Code and sample output follow).

I need to use the append-mode!

Many thanks and a happy New Year!

   friedel

Additional facts:

• Digital UNIX 4.x on DEC Alpha
• Oracle 7 RDBMS 7.3.2.3 (very old, I know)
  1. output (w/o error):

SQL> execute fileaccess
start
fopen: write
put_line
close
fopen: read
result: open
close
fopen: append
put_line
flush
close
close all

2. output (w/ error):

SQL> execute fileaccess
start
fopen: write
put_line
close
Invalid Operation

• procedure ----------

rem fileaccess.sql
rem

spool fileaccess.log

SET SERVEROUTPUT ON SIZE 1000000 CREATE OR REPLACE PROCEDURE fileaccess   IS

  ft_file	UTL_FILE.FILE_TYPE;
  spath		VARCHAR2(255) := '/xxxxx/app/oracle/product/7.3.2/WEBACCESS'; 
  sfile     VARCHAR2(255) := 'test.txt'; 
  

BEGIN
 dbms_output.put_line('start');    

 /* write - works always*/
 ft_file := UTL_FILE.FOPEN(spath,sfile,'W');  dbms_output.put_line('fopen: write');

 UTL_FILE.put_Line(ft_file, 'write: ' || TO_CHAR(sysdate,'DD.MM.YYYY HH24:MI:SSSSS'));
 dbms_output.put_line('put_line');

 UTL_FILE.FCLOSE(ft_file);
 dbms_output.put_line('close');  

 /* check existence */
 ft_file := UTL_FILE.FOPEN(spath,sfile,'R');  dbms_output.put_line('fopen: read');

 IF (UTL_FILE.IS_OPEN(ft_file)) THEN

    dbms_output.put_line('result: open');  

    UTL_FILE.FCLOSE(ft_file);
    dbms_output.put_line('close');
 ELSE
    dbms_output.put_line('result: not open');  END IF;  /* append */
 ft_file := UTL_FILE.FOPEN(spath,sfile,'A');  dbms_output.put_line('fopen: append');  

 UTL_FILE.put_Line(ft_file,'append: ' || TO_CHAR(sysdate,'DD.MM.YYYY HH24:MI:SSSSS'));
 dbms_output.put_line('put_line');

 UTL_FILE.FFLUSH(ft_file);
 dbms_output.put_line('flush');

 UTL_FILE.FCLOSE(ft_file);
 dbms_output.put_line('close');

 UTL_FILE.FCLOSE_ALL;
 dbms_output.put_line('close all');

     EXCEPTION
      WHEN UTL_FILE.INVALID_OPERATION THEN
        dbms_output.put_line('Invalid Operation');
        UTL_FILE.FCLOSE_ALL;

      WHEN UTL_FILE.INVALID_MODE THEN
        dbms_output.put_line('Invalid Mode');
        UTL_FILE.FCLOSE_ALL;
	
      WHEN UTL_FILE.INVALID_PATH THEN
        dbms_output.put_line('Invalid Path');
        UTL_FILE.FCLOSE_ALL;

      WHEN UTL_FILE.INVALID_FILEHANDLE THEN
        dbms_output.put_line('Invalid Filehandle');
        UTL_FILE.FCLOSE_ALL;

      WHEN UTL_FILE.WRITE_ERROR THEN
        dbms_output.put_line('Write Error');
        UTL_FILE.FCLOSE_ALL;

      WHEN OTHERS THEN
        dbms_output.put_line('unrecognized error');
        UTL_FILE.FCLOSE_ALL;
 

END;
/
show errors
spool off

--

    Friedel Hosenfeld       http://www.pz-oekosys.uni-kiel.de/~friedel/

Ecosystem Research Centre      private:        http://www.hosenfeld.de/

Schauenburgerstr.112  D-24118 Kiel       friedel_at_pz-oekosys.uni-kiel.de
                       PGP key available!