CODEWITHSUNDEEP
oracle-databaseplsqlcsvexporttext-files
Rich
Rich

Reputation: 3762

Oracle how to export query to a text/csv file

I was wondering how to go about exporting a query from PL/SQL to an text file or csv file. The query I have in mind exports a huge amount of data (about 1 gig). So I'd also like the data split across multiple files;

out1.csv out2.csv out3.csv

I'd like to be able to decide how many files to split it across.

Anyone have any idea how to do this?

Upvotes: 9

Views: 65799

Answers (2)

Wai
Wai

Reputation: 54

Try this

For creating MYDIR

create or replace directory MYDIR as 'F:/DATA/';

Grant all permission to MYDIR via SYS user execute this procedure

 CREATE OR REPLACE PROCEDURE export_to_csv(refcur out sys_refcursor) IS
      v_file   UTL_FILE.file_type;
      v_string VARCHAR2(4000);
      CURSOR c_emp IS
        SELECT ROLE_ID, ROLE_DESC FROM role_mst;
    BEGIN
      open refcur for
        SELECT ROLE_ID, ROLE_DESC FROM role_mst;
      v_file := UTL_FILE.fopen('MYDIR', 'empdata.csv', 'w', 1000);       
      -- if you do not want heading then remove below two lines
      v_string := 'Emp Code, Emp Name';
      UTL_FILE.put_line(v_file, v_string);
      FOR cur IN c_emp LOOP
        v_string := cur.ROLE_ID || ',' || cur.ROLE_DESC;
        UTL_FILE.put_line(v_file, v_string);
      END LOOP;
      UTL_FILE.fclose(v_file);
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line(sqlerrm);
        IF UTL_FILE.is_open(v_file) THEN
          UTL_FILE.fclose(v_file);
        END IF;
    END;

Upvotes: 0

Matthew Watson
Matthew Watson

Reputation: 14233

Use UTL_FILE.

A well known ( probably the most complete discussion on this topic ) discussion on this can be found at Ask Tom, Here , note that many of the examples there date back to oracle 8, so there may be better ways to do it in your version of Oracle.

Upvotes: 8

Related Questions