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.
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
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>;
After doing it, you can create the Oracle directory directly with your normal user.
SQL> CREATE DIRECTORY MY_DIR AS '/tmp';
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;
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;
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;
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;
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