Oracle's UTL_FILE

Question

I am learning Oracle pl/sql by myself. I need to create a procedure that exports a table to a csv file. For that I am thinking of UTL_FILE.Is there any good book or a site to get me started? I did already looked and find some samples to look to be good like this one:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:88212348059

but I find few explanations, also Oracle docs are not very user friendly.

I come from javascript and don't have much experience with databases. From the samples I see on the internet, for me PL/SQL scripts look like C language.

Answer 1

I agree the Oracle docs can be a bit bland, however they are in general very complete. Once you learn how to locate the relevant piece of information you're looking for, they are often the best resource you can find online.

In your case I would suggest you take a look at the PL/SQL Packages and Types Reference book, where you will find the complete documentation of all standard packages.

The UTL_FILE chapter, contains a collection of examples, in particular how to set up your directories to enable access.

Once you have setup your directory object, you can create a file in PL/SQL with something like the following :

SQL> CREATE DIRECTORY tmp_dir AS '/tmp/';

Directory created

SQL> DECLARE
  2     l_file utl_file.file_type;
  3  BEGIN
  4     l_file := utl_file.fopen('tmp_dir', 'my_file.csv', 'W');
  5     FOR cc IN (SELECT * FROM all_objects WHERE ROWNUM <= 5) LOOP
  6        utl_file.put_line(l_file, cc.object_name||','||cc.object_type);
  7     END LOOP;
  8     utl_file.fclose(l_file);
  9  END;
 10  /

PL/SQL procedure successfully completed

SQL> DECLARE
  2     l_file utl_file.file_type;
  3     l_line VARCHAR2(100);
  4  BEGIN
  5     l_file := utl_file.fopen('tmp_dir', 'my_file.csv', 'R');
  6     LOOP
  7        utl_file.get_line(l_file, l_line);
  8        dbms_output.put_line(l_line);
  9     END LOOP;
 10  EXCEPTION
 11     WHEN no_data_found THEN -- EOF
 12        utl_file.fclose(l_file);
 13  END;
 14  /

C_OBJ#,CLUSTER
I_OBJ#,INDEX
TAB$,TABLE
CLU$,TABLE
C_TS#,CLUSTER