Oracle-L: RE: How to extract data from existing table to csv file.

From: Jack Silvey <jack_silvey_at_yahoo.com>
Date: Mon, 17 Jun 2002 18:28:18 -0800
Message-ID: <F001.0047FE12.20020617182818@fatcity.com>

Mujeeb,

Try this one. A little old, but might give you some ideas. It will export based on the ASCII character code you give it. hth. jack

REM

  REM
  REM Custom-delimited Export Creator
  REM Usage: @tabexp <owner> <table name> <ASCII Character Code>
  REM EX: @tabexp SCOTT EMP 44
  REM
  REM Hull Crush Depth is 1000 characters.   REM LONG columns will cause errors, so they are not exported.
  REM Delimiter characters in the text will be converted to a ~.
  REM

  set echo off
  set feedback off
  set pages 0
  set lines 100
  set verify off
  prompt Creating command file for table: &1 . &2 ...   set termout off
  spool cmd.sql
  REM
  REM start the statement...
  REM
  select 'Select rtrim(' from dual;
  REM
  REM We're converting everything to CHAR   REM Watch for extra spaces at the end of CHAR columns
  REM Convert any embedded delimiter characters to tildes (~)
  REM
  select decode(data_type ,'NUMBER', 'to_char('
,'DATE', 'to_char('
,'CHAR', 'rtrim(translate('
,'VARCHAR2', 'rtrim(translate('

  ,NULL
  ) ||
  column_name ||
  decode(data_type ,'NUMBER', ') || chr(&3) || '

             ,'DATE', ') || chr(&3) || '
             ,'CHAR'  , ',chr(&3),chr(126) )) ||
chr(&3) ||'
             ,'VARCHAR2', ',chr(&3),chr(126) )) ||
chr(&3) ||'
             ,' || chr(&3) || '
         )

  from all_tab_columns
  where table_name = upper('&2')
    and owner = upper('&1')
    and data_type not in ('LONG','LONG RAW');   REM
  REM Remember that we concatenated an extra "||" on the end
  REM of the last column. Can't leave it hanging, so put
  REM another delimiter on the end to fix the statement's syntax.
  REM
  select 'chr(&3))' from dual;
  REM
  REM Now, tack on the predicate...
  REM
  select 'from &1' || '.' || '&2;' from dual;   spool off
  REM Done with extract statement. Wanna see it?   set termout on
  get cmd.sql
  prompt
  prompt Hit any key to start export, or <ctrl>-c to cancel
  accept keypress
  prompt

  prompt Now exporting table: owner             = &1
  prompt                      table name        = &2 ,
  prompt                      delimited by

ASCII(&3)
  set termout off
  set lines 1000
  REM
  REM Let's export some Data!!
  REM
  spool &2
  @cmd.sql
  spool off
  host rm cmd.sql
  set termout on
  prompt "Export Completed."
  REM
  REM End of file.

> 
> Hi All,
> 
> Could anyone tell me how to get data from existing
> table as a csv file using
> sql*plus.  Appreciate any suggestions.
> Thanks
> Mujeeb Chowdhry
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Mujeeb Chowdhry
>   INET: mujeeb.chowdhry_at_nara.gov
> 
> 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).
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Suhen Pather
>   INET: Suhen.Pather_at_strandbags.com.au
> 
> 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).


__________________________________________________

Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  INET: jack_silvey_at_yahoo.com

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 Mon Jun 17 2002 - 21:28:18 CDT