We help IT Professionals succeed at work.

Getting data out of Oracle into a .CSV file

hydev
hydev asked
on
5,938 Views
Last Modified: 2013-12-07
Hi,
I have a View in Oracle that I want to export to a .CSV file.  The view returns about 300,000 lines that I need to import into another program.    How do it do it?  I thought it might easy like in MS SQL Server but all I can see is a way to upload the data into Oracle.  

There must be an easy way.  I don't need to worry about formatting I just want field1,field2,field3...

Any help greatly appreciated.

Mike
Comment
Watch Question

start your sql server session

sqlplus> connect 'username/password'
sqlplus> set echo off
sqlplus> set feed off
sqlplus> set head off
sqlplus>set line 1000 -- whatever you think wouldd be the size of your rows
sqlplus>c:\temp\spool_file.csv
sqlplus>select col1 ||' '||col2||' '|| col3 from table1;
sqlplus> spool off
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
I think Keyurkumar means: "Start a SQL*Plus session" then enter the commands he suggested at the "SQL>" prompt.

I suggest that you also add this one:
set trimspool on

And change the "select..." line slightly to include a comma between each column like this:
select col1 ||', '||col2||', '|| col3 from table1;

No, Oracle does not have tools and utilities that are as easy to use as SQL Server does.  But remember: Oracle is available for a big range of operating systems (not just one!) and a well-tuned Oracle database can perform faster than SQL Server can.
Yes I mean to execute on SQL* prompt. Thanks for correct me on , instead of space
If you have a tool like TOAD then it would be easy to run query in TOAD Editor and save it as you want. Everything automatically. You dont have to worry about any formatting matter.
Create a directory and use the UTL_FILE utility, see the example below:


CREATE OR REPLACE DIRECTORY CDIR AS 'C:\';

Directory created.


SELECT * FROM TAB1;

      COL1       COL2
---------- ----------
       100        200
       200        200


DECLARE
V_FILE UTL_FILE.FILE_TYPE;
V_DIR  VARCHAR2 (30) := 'CDIR';
V_COLS VARCHAR2(1000) := 'COL1,COL2';
BEGIN
V_FILE := UTL_FILE.FOPEN(V_DIR,'EXCEL_FILE.CSV','W');
UTL_FILE.PUT_LINE(V_FILE,V_COLS);
FOR I IN (SELECT COL1, COL2 FROM TAB1) LOOP
UTL_FILE.PUT_LINE(V_FILE,I.COL1||','||I.COL2);
END LOOP;
UTL_FILE.FCLOSE(V_FILE);
END;
/

PL/SQL procedure successfully completed.


INSIDE C:\EXCEL_FILE.CSV :

COL1      COL2
100      200
200      200
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
Yes, a logical directory and utl_file are a good option *IF* you have DBA privileges and access to the server, since utl_file can only write to directories on the server (or to directories mapped or mounted by the server).
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
@markgeer: I totally agree

Commented:
in order to spool you need to set the serveroutput option like this.

sqlplus> set serveroutput on size 1000000

since you mentioned 300,000 records it depends on the size of each record also. for instance if you've like 100 bytes for each record its eaily going to exceed the max limit of the output. even if you use tools like toad or something like that, they've a limit of 65,000 records maximum.

what is the other program where you'r trying to import these data? if its another db, just export/import commands would do the job easily or an odbc connection.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
The "spool" command and setting "serveroutput on" are two *VERY* different things!  

"Spool" just tells SQL*Plus to write everything to a file that it normally shows on the screen, whether that is SQL commands or queries that are typed in to SQL*Plus, or the feedback or results from the database when a command or qurey is executed.  It will continue to write (append) to the file until you do a "spool off" or close the SQL*Plus session.  Unless you also do "set echo off" you will see the results on the screen while the "spool" file is being written.  If you use "spool" to create a large file, you may want to "set echo off" since it will be faster that way.

"Set serveroutput on" is for PL\SQL only in a SQL*Plus window.  It does *NOT* create a file though unless you also use the "spool" command.  With "serveroutput on" you will see the results of any dbms_output.put_line commands in a PL\SQL block or procedure displayed on the screen *AFTER* the PL\SQL block or procedure finishes.

Commented:
mark, hey sorry. i rushed a bit. thanks for pointing it out. (i always run the procedures and always spool the dbms outputs ) i think i lost the touch with oracle because i'm not actively working with it for last 1 year or so.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.