Getting data out of Oracle into a .CSV file

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

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

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).

@markgeer: I totally agree

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.

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.

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.