You are here:

Oracle/exporting the database tables to csv

Advertisement


Question
i want to export database table data to csv file.i use a pl/sql procedure with utl_file package.i created a logical directory in oracle but still when i run the script i could not create a csv file.i failed to map to the OS directory to the logical directory .what should i do to map the logical  directory to OS directory.
will i be able access the file through the OS .

the

Answer
Hi Faizal,

I hope you had a great weekend.

Please note when you create a package and execute it, it may write the output to the database server and not to your desktop. You need to test in a non-production environment before executing in production!


1. To create a file, we need to create a directory and have the read write permission. In this example, I am creating a "/tmp" folder.

CREATE DIRECTORY test_dir AS 'c:\<your_folder>/tmp'; -- for Windows based
CREATE DIRECTORY test_dir AS '/user/tmp';          -- for Unix based


Note that CREATE DIRECTORY privileges are granted to SYS and SYSTEM user by default. You or your DBA will need to grant those privileges to the account you are using.


2. Grant access privileges to the <test_dir> to your SQL user:


GRANT READ, WRITE on test_dir to <my_sql_user>;

3. In the following example/psuedocode, I am reading the sample table SCOTT.EMP (this can be installed in Oracle in a sample database). The output file found in test_dir is called EMP_DEPT.CSV


DECLARE
   F UTL_FILE.FILE_TYPE;
   CURSOR C1 IS SELECT EMPNO, ENAME, SAL, E.DEPTNO, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO ORDER BY EMPNO;
   C1_R C1%ROWTYPE;
BEGIN
   F := UTL_FILE.FOPEN('test_dir','EMP_DEPT.CSV','w',32767);
   FOR C1_R IN C1
   LOOP
       UTL_FILE.PUT(F,C1_R.EMPNO);
       UTL_FILE.PUT(F,','||C1_R.ENAME);
       UTL_FILE.PUT(F,','||C1_R.SAL);
       UTL_FILE.PUT(F,','||C1_R.DEPTNO);
       UTL_FILE.PUT(F,','||C1_R.DNAME);
       UTL_FILE.NEW_LINE(F);
   END LOOP;
   UTL_FILE.FCLOSE(F);
END;
/

4. Enclosed is another sample code for your reference

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  /


Source:
1. Oracle UTL_FILE http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm#BABGGEDF
2. Nimishgarg Blog http://nimishgarg.blogspot.ca/2011/09/create-csv-file-using-plsql.html
3. PSOUG Reference http://psoug.org/snippet/UTL_FILE_719.htm
4. StackExchange http://dba.stackexchange.com/questions/39715/oracles-utl-file


I hope this helps

Peter

Oracle

All Answers


Answers by Expert:


Ask Experts

Volunteer


Peter Choi

Expertise

I am a senior Oracle DBA, PeopleSoft HCM specialist and Project Manager with 15+ years experience. I have been working with PeopleSoft (HRMS/HCM 5, 7, 7.x and 8.9, 9.x), Oracle RDBMS (7.3 - 11gR2) on various Unix and MS-Windows platforms, and some Oracle Application Server (9i - 11gR1). I also have experience with the configuration and administration of PeopleSoft's Internet Architecture (PIA) and the Oracle 11g Fusion Middleware and Oracle Business Intelligence (OBI).

©2016 About.com. All rights reserved.