Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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
share|improve this answer
    
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 '13 at 0:24
    
@Edu it was pseudo code. Here's a complete example. –  Vincent Malgrat Apr 12 '13 at 7:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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