RE: Dump Oracle Tables To ASCII/Comma Delimited File

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Thu, 13 Sep 2001 07:12:44 -0700
Message-ID: <F001.0038D143.20010913061529@fatcity.com>

!! Please do not post Off Topic to this List !!

deepender,

there are not tools provided, but you can use several oracle products to do this.

The first one to look at is Sqlplus. If the tables are not large, you can always do :

select col1||','||col2||','||col3
from table

and spool the result to a file. You would need to 'set heading off' and 'set pagesize 1000' to avoid headers and page breaks.

Another way is to write a PL/SQL block that uses DBMS_OUTPUT to output the file.
It is a variation of the first example.
It would look like this:

set pages 1000
set head off
spool out.lis
declare
cursor c1 is
  select col1||','||col2||','||col3 data_str from table;
begin
for c1_rec in c1 loop

   dbms_output.put_line(data_str);
end loop;
end;
/
spool off

The third way is to use UTL_FILE to write the data. This would produce the data in a directory on the server. This avoids the problems you have with sqlplus with headers and page sizes.

hope these help.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Thursday, September 13, 2001 9:20 AM To: Multiple recipients of list ORACLE-L

Hi everybody,
Can anybody tell me the command/tool in oracle that can dump Oracle Tables To ASCII/Comma Delimited File.
Regds
deepender gupta

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Sep 13 2001 - 09:12:44 CDT