Re: Oracle UTL_FILE

From: Michel Cadot <micadot_at_netcourrier.com>
Date: 2000/05/10
Message-ID: <8fc172$1ulu$1_at_news4.isdnet.net>#1/1

Here's a simple example on how to use UTL_FILE package:

declare

   file utl_file.file_type;
begin

   /* Type "set serveroutput on" to see the lines displayed by dbms_output */

   dbms_output.enable;
   dbms_output.put('>>> Begin');
   dbms_output.new_line;

   file := utl_file.fopen ('C:\','flatfile','w');    /* Directory must be in the list given by the parameter(s)

      UTL_FILE_DIR in the INIT.ORA file.
      This parameter can be "*" for all the directories       */
   if ( utl_file.is_open(file) ) then
      dbms_output.put_line('>>> File opened');
      utl_file.put_line (file, 'something writen in the file');
      dbms_output.put_line('>>> Line writen');
      utl_file.fflush (file);
      dbms_output.put_line('>>> Buffer flushed');
      utl_file.fclose (file);
      dbms_output.put_line('>>> File closed');
   else
      dbms_output.put_line('>>> File not open');

   end if;
   dbms_output.put_line('>>> End');
exception

   when utl_file.invalid_path then

      dbms_output.put_line('>>> Invalid path');    when utl_file.invalid_filehandle then

      dbms_output.put_line('>>> Invalid file handle');    when utl_file.invalid_mode then

      dbms_output.put_line('>>> Invalid mode');    when utl_file.invalid_operation then

      dbms_output.put_line('>>> Invalid operation');    when utl_file.write_error then

      dbms_output.put_line('>>> Write error');
   when utl_file.read_error then
      dbms_output.put_line('>>> Read error');
   when utl_file.internal_error then
      dbms_output.put_line('>>> Internal error');
   when others then
      dbms_output.put_line('>>> Other error');

end;
/

--
Have a nice day
Michel


<parthur_at_my-deja.com> a écrit dans le message : 8fbube$6pc$1_at_nnrp1.deja.com...


> I am currently using DBMS_OUTPUT, but need to switch to UTL_FILE.


> However it is not clear the necessary syntax. The following is what I

> get back.

> SQL> CREATE OR REPLACE PROCEDURE redi.TestFileIO

>   2  AS

>   3  BEGIN

>   4  UTL_FILE.FOPEN('/log/file1.log','w');

>   5  UTL_FILE.FCLOSE_ALL;

>   6  END;

>   7  /

> Warning: Procedure created with compilation errors.

> SQL> show errors

> Errors for PROCEDURE REDI.TESTFILEIO:

> LINE/COL ERROR

> -----------------------------------------------------------------

> 4/1      PLS-00306: wrong number or types of arguments in call to

> 'FOPEN'

> 4/1      PL/SQL: Statement ignored

>
>


> Sent via Deja.com http://www.deja.com/


> Before you buy.

Received on Wed May 10 2000 - 00:00:00 CEST