0

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.

1 Answer 1

3

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
2
  • I tried your code. First I created the directory from the command prompt. Then in SQLPlus from the SYS account I created the directory and then granted priviledges to the user without getting any errors. Then When I execute your code, I get an error displaying "invalid directory". Thank you for the direct link of the reference book. I was able to get a pdf version of it.
    – Edu
    Apr 12, 2013 at 0:24
  • @Edu it was pseudo code. Here's a complete example. Apr 12, 2013 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.