Re: utl_file Package

From: Ralf Bender <Ralf.Bender_at_wolnet.de>
Date: Mon, 17 Aug 1998 22:02:24 +0200
Message-ID: <35d889c0.0@dns.wolnet.de>

Bill Buchan schrieb in Nachricht <6qn8dm$oqv$1_at_phys-ma.sol.co.uk>...
>I'd very much appreciate any help in getting the following code running
>using the utl_file package:
>
>DECLARE
> file_aaa utl_file.file_type;
> file_text varchar2(80);
>BEGIN
> file_aaa := utl_file.fopen('C:\Temp','ORACLE_testfile','w');
> file_text := 'Gibberish';
> utl_file.put_line(file_aaa,file_text);
> utl_file.fclose(file_aaa);
>END;
>/
>
>
>I have set the parameter
>
>utl_file_dir = C:\Temp
>
>in my init_.ora file and have bounced the database. I am running the code
>on the server-side but it merely returns exceptions - I am using Oracle8 on
>WinNT4.
>
>Any help would be much appreciated,
>
>Thanks,
>
>- Bill.
>

Hello Bill,
here is my example, which runs well :

**
** UTL_FILE_DIR = e:\orawin95\softmati
*/

CREATE OR REPLACE procedure do_update as

iScan number;
iTempax number;

cursor curTempax is
 select count(*) from Tempax;

cursor curScan is
 select count(*) from ll0230notinfibu;

file_handle UTL_FILE.FILE_TYPE;
log_file_handle UTL_FILE.FILE_TYPE;

begin
 open curScan;
 open curTempax ;
 fetch curScan into iScan;
 fetch curTempax into iTempax;
 close curScan;
 close curTempax ;
 if iScan = iTempax then
  log_file_handle := UTL_FILE.FOPEN('e:\orawin95\softmati', 'do_upd.log', 'a');
  UTL_FILE.PUTF(log_file_handle, to_char(sysdate,'DD-Mon-YYYY HH24:MM:SS') || ' \n');
  UTL_FILE.PUTF (log_file_handle, 'iScan = ' || iScan || '\niTempax = ' || iTempax || ' \n');
  UTL_FILE.PUT(log_file_handle,

'*******************************************');

  UTL_FILE.FCLOSE(log_file_handle);
  mailax2fibu;
 else
  file_handle := UTL_FILE.FOPEN('e:\orawin95\softmati', 'do_upd.bad', 'w');   UTL_FILE.PUTF(file_handle, to_char(sysdate,'DD-Mon-YYYY HH24:MM:SS') || ' \n');
  UTL_FILE.PUTF (file_handle, 'iScan = ' || iScan || '\niTempax = ' || iTempax || ' \n');

  UTL_FILE.PUT(file_handle, '*******************************************');

  UTL_FILE.FCLOSE(file_handle);

  log_file_handle := UTL_FILE.FOPEN('e:\orawin95\softmati', 'do_upd.log', 'a');
  UTL_FILE.PUTF(log_file_handle, to_char(sysdate,'DD-Mon-YYYY HH24:MM:SS') || ' \n');
  UTL_FILE.PUTF (log_file_handle, 'iScan = ' || iScan || '\niTempax = ' || iTempax || ' \n');
  UTL_FILE.PUT(log_file_handle,

'*******************************************');

  UTL_FILE.FCLOSE(log_file_handle);

  end if;

EXCEPTION
 WHEN NO_DATA_FOUND THEN

         DBMS_OUTPUT.PUT_LINE('no_data_found');
         UTL_FILE.FCLOSE_ALL ();
    WHEN UTL_FILE.INVALID_PATH THEN
         DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
         UTL_FILE.FCLOSE_ALL ();
    WHEN UTL_FILE.READ_ERROR THEN
         DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
         UTL_FILE.FCLOSE_ALL ();
    WHEN UTL_FILE.WRITE_ERROR THEN
         DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
         UTL_FILE.FCLOSE_ALL ();
    WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('other stuff');
         UTL_FILE.FCLOSE_ALL ();

end;
/
Hope it help.

by ralf Received on Mon Aug 17 1998 - 15:02:24 CDT