How To Tune A File Using Oracle UTL_FILE.PUT_LINE
  Home
 

This Online Tech Support tutorial page is based on examples. Package Oracle UTL_FILE allows reading and writing text files from inside the Oracle database and the files will be handled depending on the operation system. For example a text file created by Oracle package UTL_FILE in Unix or Linux system can have strange characters inside when you are opening the file in the Windows file editors. Still we are thinking most file editors should have compatible with all operation systems and different characters will be handled correctly.

The most basic syntax to write into a file using an anonymous PL/SQL block is following:

DECLARE  
  <your_file_variable> UTL_FILE.FILE_TYPE; 
BEGIN 
  <your_file_variable> := UTL_FILE.FOPEN('ORACLE_DIRECTORY','<your_file_name>','W',32760); 
  UTL_FILE.PUT_LINE(<your_file_variable>,<your_text>); 
  UTL_FILE.FCLOSE(<your_file_variable>); 
END;

The next example is the most basic way to write into a file a text “online tech support computer help”. To make this example to work in your Oracle database you need to create an Oracle directory as the following example:

SQL> CREATE DIRECTORY MY_DIR AS '/tmp'; 
SQL> GRANT READ ON DIRECTORY MY_DIR TO my_user; 
SQL> GRANT WRITE ON DIRECTORY MY_DIR TO my_user;

The new directory is using the Linux operation system and it is pointing to directory /tmp. If you are using Windows server then you should declare it is as C:/tmp and make sure the directory exists and has read-write permissions to everybody. The Oracle directory name is MY_DIR and make sure you will give reading and writing permissions to your user using Oracle grant command.

Important To Know:

When you tried to create directory as your normal user in the following way and you got this error:

Error starting at line 1 in command:
CREATE DIRECTORY my_dir AS '/tmp'
Error at Command Line:1 Column:0
Error report:
SQL Error: ORA-01031: insufficient privileges

online tech support computer help computer technician computer problems computer oracle utl_file.fopen oracle utl_file.fopen in oracle utl_file oracle in UTL_FILE oracle create directory oracle create directory in oracle directory oracle directory ORA-01031 oracle directory in oracle sql oracle sql database sql oracle retirement planning retirement

To fix this you need to give more permission to you user using the SYSTEM user. You would need GRANT CREATE ANY DIRECTORY.

GRANT CREATE ANY DIRECTORY TO <username>;

online tech support computer help computer technician computer problems computer oracle utl_file.fopen oracle utl_file.fopen in oracle utl_file oracle in UTL_FILE oracle create directory oracle create directory in oracle directory oracle directory ORA-01031 oracle directory in oracle sql oracle sql database sql oracle retirement planning retirement

After doing it, you can create the Oracle directory directly with your normal user.

SQL> CREATE DIRECTORY MY_DIR AS '/tmp';

online tech support computer help computer technician computer problems computer oracle utl_file.fopen oracle utl_file.fopen in oracle utl_file oracle in UTL_FILE oracle create directory oracle create directory in oracle directory oracle directory ORA-01031 oracle directory in oracle sql oracle sql database sql oracle retirement planning retirement

Now we can come back to the writing into file example.

DECLARE  
  w_file UTL_FILE.FILE_TYPE;
BEGIN
  -- open file in writing mode and make it empty
  w_file := UTL_FILE.FOPEN('MY_DIR','onlinetechsupport.txt','W',32760);
  -- write text 'online tech support computer help' into file
  UTL_FILE.PUT_LINE(w_file,'online tech support computer help');
  -- close file
  UTL_FILE.FCLOSE(w_file); 
END;

online tech support computer help computer technician computer problems computer oracle utl_file.fopen oracle utl_file.fopen in oracle utl_file oracle in UTL_FILE oracle create directory oracle create directory in oracle directory oracle directory ORA-01031 oracle directory in oracle sql oracle sql database sql oracle retirement planning retirement

In the example above we would like to remind you to close ALWAYS the file even when an error rises. Use the following example to make sure your file is always closed when the procedure is done.

DECLARE  
  w_file UTL_FILE.FILE_TYPE; 

  -- procedure to close the file
  PROCEDURE close_file
  IS
  BEGIN
    UTL_FILE.FCLOSE(w_file);
  EXCEPTION 
    WHEN OTHERS THEN -- when the file never opened do not raise an error
         null;
  END close_file;
BEGIN
  -- open file in writing mode and make it empty
  w_file := UTL_FILE.FOPEN('MY_DIR','onlinetechsupport.txt','W',32760);
  -- write text 'online tech support computer help' into file
  UTL_FILE.PUT_LINE(w_file,'online tech support computer help');
  -- close file
  close_file;
EXCEPTION 
    WHEN OTHERS THEN
         close_file; -- on error close the file
         raise; -- raise the error
END;

online tech support computer help computer technician computer problems computer oracle utl_file.fopen oracle utl_file.fopen in oracle utl_file oracle in UTL_FILE oracle create directory oracle create directory in oracle directory oracle directory ORA-01031 oracle directory in oracle sql oracle sql database sql oracle retirement planning retirement

The following package UTL_FILE in Oracle example shows how to write into file without making it empty every time. The difference is in file opening mode that will be this time “A” as appending. The next example will add only lines to the file and you will see every time additional line with text “online tech support computer problems” in it after executing the following code.

DECLARE  
  w_file UTL_FILE.FILE_TYPE; 

  -- procedure to close the file
  PROCEDURE close_file
  IS
  BEGIN
    UTL_FILE.FCLOSE(w_file);
  EXCEPTION 
    WHEN OTHERS THEN -- when the file never opened do not raise an error
         null;
  END close_file;
BEGIN
  -- open file in writing mode and make it empty
  w_file := UTL_FILE.FOPEN('MY_DIR','onlinetechsupport.txt','A',32760);
  -- write text 'online tech support computer problems' into file
  UTL_FILE.PUT_LINE(w_file,'online tech support computer help');
  -- close file
  close_file;
EXCEPTION 
    WHEN OTHERS THEN
         close_file; -- on error close the file
         raise; -- raise the error
END;

online tech support computer help computer technician computer problems computer oracle utl_file.fopen oracle utl_file.fopen in oracle utl_file oracle in UTL_FILE oracle create directory oracle create directory in oracle directory oracle directory ORA-01031 oracle directory in oracle sql oracle sql database sql oracle retirement planning retirement

Computer problems: writing to file is slow and takes too much time.
We say: your code goes slower almost every time when you are using Oracle UTL_FILE procedures and functions, because they are mostly doing something with I/O or in other-words package UTL_FILE Oracle is using a hard-drive (I/O) and this is the slowest part of any computer. To make your writing into file procedure faster you need to use the Oracle UTL_FILE package as less as possible.

The example below is tuned version of writing into file in Oracle. Basically the procedure collecting all text in a text variable and writes it into file when it is full. We will write 5000 lines into the file and to make sure all text got into file the last line gets write into file without checking if the variable is full or not. To make sure the lines will keep their format the code is using character CHR(10) as the ending of line symbol.

DECLARE  
  w_file      UTL_FILE.FILE_TYPE;
  w_next_line VARCHAR2(1);
  w_text      VARCHAR2(32700);

  -- procedure to close the file
  PROCEDURE close_file
  IS
  BEGIN
    UTL_FILE.FCLOSE(w_file);
  EXCEPTION 
    WHEN OTHERS THEN -- when the file never opened do not raise an error
         null;
  END;

  -- Write after the variable gets full
  PROCEDURE write_to_file(p_text VARCHAR2)
  IS   
  BEGIN
    w_text := w_text||w_next_line||p_text;
  EXCEPTION 
    WHEN VALUE_ERROR THEN -- when the variable is full then write into file
      UTL_FILE.PUT_LINE(w_file,w_text);
      w_text := p_text;
  END;
BEGIN
  -- open file in writing mode and make it empty
  w_file := UTL_FILE.FOPEN('MY_DIR','onlinetechsupport.txt','W',32760);

  -- write 5000 lines into the file
  FOR i IN 1..5000 
  LOOP
    write_to_file('online tech support computer help computer technician computer problems');
    -- do not add an empty line in the beginning of file 
    w_next_line := CHR(10);
  END LOOP;

  -- write the last text to complete file
  UTL_FILE.PUT_LINE(w_file,w_text);
  -- close file
  close_file;
EXCEPTION 
    WHEN OTHERS THEN
         close_file; -- on error close the file
         raise; -- raise the error
END;

online tech support computer help computer technician computer problems computer oracle utl_file.fopen oracle utl_file.fopen in oracle utl_file oracle in UTL_FILE oracle create directory oracle create directory in oracle directory oracle directory ORA-01031 oracle directory in oracle sql oracle sql database sql oracle retirement planning retirement

Important about this code to do less IF-s or any checking to save writing time as it still takes some time, but not as much as writing any possible line or word one by one. Using this style of code the execution reduces file writing from 20 minutes to 4 minutes. That is less than 5 times. P.S. Using Oracle clob or blob types were slower than this method.

The last PL/SQL anonymous block is working in the same principle as the last one with only one exception it is using a select query. The columns will be separated using coma.

DECLARE 
  w_file      UTL_FILE.FILE_TYPE;
  w_next_line VARCHAR2(1);
  w_text      VARCHAR2(32700);
  -- procedure to close the file
  PROCEDURE close_file IS
  BEGIN
    UTL_FILE.FCLOSE(w_file);
  EXCEPTION
    WHEN OTHERS THEN -- when the file never opened do not raise an error
         null;
  END;
  -- Write after the variable gets full
  PROCEDURE write_to_file(p_text VARCHAR2) IS  
  BEGIN
    w_text := w_text||w_next_line||p_text;
  EXCEPTION
    WHEN VALUE_ERROR THEN -- when the variable is full then write into file
     UTL_FILE.PUT_LINE(w_file,w_text);
     w_text := p_text;
  END;
BEGIN
  -- open file in writing mode and make it empty
  w_file := UTL_FILE.FOPEN('MY_DIR','onlinetechsupport.txt','W',32760);

  FOR rec IN (SELECT * FROM customers t )
  LOOP
    write_to_file(rec.ID ||',"'|| 
                  rec.FORENAME||'","'||
                  rec.SURNAME||'"');

    -- do not add an empty line in the beginning of file
    w_next_line := CHR(10);
  END LOOP;
  -- write the last text to complete file
  UTL_FILE.PUT_LINE(w_file,w_text);
  -- close file
  close_file;
EXCEPTION
    WHEN OTHERS THEN
         close_file; -- on error close the file
         raise; -- raise the error
END;



See Also:
Home Oracle Select

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*