Re: Export data in ASCII format.

From: Yassir Khogaly <yassir_at_khogaly.freeserve.co.uk>
Date: Sat, 31 Oct 1998 20:28:45 -0000
Message-ID: <71fruh$ine$1@newsreader1.core.theplanet.net>

This may help ..

SET ECHO off

REM ------------------------------------------------------------------------

--
REM REQUIREMENTS:
REM SELECT on the given table(s)

REM ------------------------------------------------------------------------

--
REM PURPOSE:
REM Generates a sql*plus script to unload a table to a file and a REM SQL*Loader script to reload the same data. Intent is to create REM a faster alternative to export/import.

REM ------------------------------------------------------------------------

---

set tab off
set heading off heading off feedback off echo off verify off space 1 pagesize
0 linesize 120
accept owner prompt 'What schema owns the table to be unloaded? '
accept table_name prompt 'What table is to be unloaded? ' accept default_precision prompt 'What TOTAL number of digits should be reserved for numbers without defined precision? ' accept default_scale prompt 'What number of DECIMAL digits should be reserved for numbers without defined scale? '

---

• Generate the unload script

  ---

  spool unload_fixed2.sql select 'SET HEADING OFF FEEDBACK OFF ECHO OFF VERIFY OFF SPACE 0 PAGESIZE 0 TERMOUT OFF' from dual
  /
  

--Calculate the sum of all output field lengths and set the output record size
select 'SET LINESIZE '

       || (sum(decode(data_type,
                      'CHAR',data_length,
                      'VARCHAR',data_length,
    'VARCHAR2',data_length,
                      'DATE',14,
   'NUMBER',decode(data_precision,
                                   '',&default_precision+2,

greatest(data_precision-data_scale,1)+decode(data_scale,0,0,1)+data_scale)+1 ,

                      'FLOAT',&default_precision+2,
           data_length)))

  from dba_tab_columns
 where owner=upper('&&owner')
   and table_name=upper('&&table_name')
/

• Generate an appropriate SQL*Plus COLUMN command to control formatting of each output field select 'COLUMN ' || rpad('"'||column_name||'"',32) || ' FORMAT ' || rpad(decode(data_type, 'CHAR','A'||data_length, 'VARCHAR2','A'||data_length, 'VARCHAR','A'||data_length, 'DATE','A14', 'NUMBER',decode(data_precision, '', rpad('0',&default_precision-&default_scale,'9')||'.'||rpad('9',&default_scal e,' 9'),

rpad('0',greatest(data_precision-data_scale,1),'9') || decode(data_scale,0,'','.')

                       ||

decode(data_scale,0,'',rpad('9',data_scale,'9'))),

'FLOAT',rpad('0',&default_precision-&default_scale,'9')||'.'||rpad('9',&defa ult
_scale,'9'),

                 'ERROR'),40)
       || ' HEADING ''X'''

  from dba_tab_columns
 where owner=upper('&&owner')
   and table_name=upper('&&table_name')  order by column_id
/

• Generate the actual SELECT statement to unload table data select 'SPOOL /tmp/&&owner..&&table_name..DAT' from dual
  /
  column var1 noprint column var2 noprint select 'a' var1, 0 var2, 'SELECT ' from dual union select 'b', column_id, decode(column_id, 1, ' ', ' , ') || decode(data_type,'DATE','to_char('||'"'||column_name||'"'||',''YYYYMMDDHH24M ISS '') '||'"'||column_name||'"' , '"'||column_name||'"') from dba_tab_columns where owner=upper('&&owner') and table_name=upper('&&table_name') union select 'c', 0, 'FROM &&owner..&&table_name' from dual union select 'd', 0, ';' from dual order by 1,2
  /
  select 'SPOOL OFF' from dual
  /
  select 'SET TERMOUT ON' from dual
  /
  

spool off

---

-
--  Generate the SQL*Loader control file
----------------------------------------------------------------------------
-

set lines 120 pages 0
spool &&owner..&&table_name..CTL
select 'a' var1, 0 var2, 'OPTIONS(DIRECT=TRUE)'   from dual
union
select 'b', 0, 'LOAD DATA'
  from dual
union
select 'c', 0, 'INFILE ''/tmp/&&owner..&&table_name..DAT'''   from dual
union
select 'd', 0, 'BADFILE &&owner..&&table_name..BAD'   from dual
union
select 'e', 0, 'DISCARDFILE &&owner..&&table_name..DSC'   from dual
union
select 'f', 0, 'DISCARDMAX 999'
  from dual
union
select 'm', 0, 'INTO TABLE &&owner..&&table_name'   from dual
union
select 'n', column_id,
rpad(decode(column_id,1,'(',',')||'"'||column_name||'"',31)

                       || decode(data_type,
 'CHAR','CHAR('||data_length||')',
                                 'VARCHAR','CHAR('||data_length||')',
                             'VARCHAR2','CHAR('||data_length||')',
                    'DATE','DATE(14) "YYYYMMDDHH24MISS"',

 'NUMBER','DECIMAL
EXTERNAL('||decode(data_precision,

'',&default_precision+2,

greatest(data_precision-data_scale,1)+decode(data_scale,0,0,1)+data_scale+1)

              ||')',
                                 'FLOAT','DECIMAL

EXTERNAL('||to_char(&default_precision+2)||')',   'ERROR--'||data_type)

                       || ' NULLIF ("' ||column_name||'" = BLANKS)'

  from dba_tab_columns
 where owner = upper('&&owner')
   and table_name = upper('&&table_name') union
select 'z', 0, ')'
  from dual
 order by 1, 2
/

spool off

---

-
--  Cleanup
----------------------------------------------------------------------------
-

clear column
clear break
clear compute
undef owner
undef table_name
undef default_precision
undef default_scale

kelvinw_at_asymetrix.com wrote in message <3630a9f3.252478194_at_news>...

>
>Hi everybody,
>
>I have a bunch of tables needed to be exported in ASCII format with
>comma delimited and loaded up to a spreadsheet. I have tried the
>exp73.exe utility. It seems to me that it can only export table data
>in a Oracle format.   Does anyone  have an idea how to export table
>data in ASCII format.
>
>Thanks,
>
>__kelvin

begin 666 Yassir Khogaly.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..DMH;V=A;'D[66%S<VER#0I& M3CI987-S:7(@2VAO9V%L>0T*5$E43$4Z4V5N:6]R($]R86-L92!$0D$-"E1% M3#M(3TU%.U9/24-%.BLT-"@P*3$X,2 T-C T,#0R#0I414P[0T5,3#M63TE# M13HK-#0T,3$Y,#8W-S8-"D%$4CM(3TU%.CL[.SM+96YT.SM%;F=L86YD#0I, M04)%3#M(3TU%.T5.0T]$24Y'/5%53U1%1"U04DE.5$%"3$4Z2V5N=#TP1#TP M045N9VQA;F0-"E523#IH='1P.B\O=W=W+FMH;V=A;'DN9G)E97-E<G9E+F-O M+G5K#0I54DPZ:'1T<#HO+W=W=RYA965U+F]R9RYU:PT*14U!24P[4%)%1CM) M3E1%4DY%5#IY87-S:7) :VAO9V%L>2YF<F5E<V5R=F4N8V\N=6L-"E)%5CHQ <.3DX,3 S,50R,#(X-#1:#0I%3D0Z5D-!4D0-"@`` `
end Received on Sat Oct 31 1998 - 14:28:45 CST