Re: export to ascii file

From: Ken Shirey <shireyk_at_flash.net>
Date: 1997/04/02
Message-ID: <01bc3f90$deaafe80$37ddc2d0@ickshirey>#1/1

Raul Sanchez <rsanchez_at_lingocd.com> wrote in article <333DC0E2.1FAB_at_lingocd.com>...
> Hi,
>
> I would like to know how or where I might find information on how
> to export a table to an ascii file in ORACLE (for example, in SYBASE
> I would use the following:
>
> bcp databaseName..tableName out dataFile -c .....
>
> In order words, what is the equivalent to this statement above
> in ORACLE. Thanx for you kind help in advance.
>
> --Raul
>

Try this:

contents of tabexp.sql:
(cut below & name "tabexp.sql")

---

REM

REM	Tab-Separated Export Creator
REM	for Oracle7 / SQL*Plus
REM	
REM	Created 11/15/96
REM	By Ken Shirey
REM        Oracle Database Administrator
REM	   Commerical Data Systems
REM        email: shireyk_at_flash.net
REM        
REM     This program may be freely distributed, provided
REM     it retains this header.  Use at your own risk.

REM
set echo off
set feedback off
set pages 0
set lines 1000
set verify off
prompt "Creating command file for table: &1 ..." set termout off
spool cmd.sql
select 'Select rtrim(' from dual;

select decode(datatype, number,'to_char(',
                        date  ,'to_char(',
                        char  ,'rtrim('  ,
                        NULL ) || 
       column_name ||
       decode(datatype, number,') ',
                        date  ,')', NULL ) || ' || chr(9) || '
from all_tab_columns
      where table_name = upper('&1');

SQL> select 'chr(9))' from dual;
select ' from &1;' from dual;
spool off
set termout on
prompt "Now exporting table: &1 ..."
set termout off
spool &1.txt
@cmd.sql
host rm cmd.sql
set termout on
prompt "Export Completed."

---

SQL> @tabexp EMP
SQL> Creating command file for table: EMP ...
SQL> Now exporting table: EMP ...
SQL> Export Completed.
SQL>

SQL> desc emp  

      empno		number(5)	NOT NULL
      ename             varchar2(50)    NULL
      mgrid		number(5)       NULL
      hiredate		date		NULL
      wasted_space	char(1000)      NOT NULL

The results:
% more cmd.sql

  Select rtrim(

  to_char(EMPNO) || chr(9) ||
  ename || chr(9) ||
  mgrid || chr(9) ||

  to_char(hiredate) || chr(9) ||
  rtrim(wasted_space) || chr(9) ||
  chr(9))
  from EMP; Received on Wed Apr 02 1997 - 00:00:00 CST