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?
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.
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;
