Re: How to extract table contents to files?
Date: 5 Jan 1995 06:26:16 GMT
Message-ID: <3eg3e8$83g_at_elmwood.elmwood.com>
Bosco Tsang (btsang_at_ctsc.hkbc.hk) wrote: : How can I extract the content of an oracle 7 table to a text file in the home : directory (unix version)?
Here is your answer... ( But your sig file is way too long, eh? )
rem !# Creates a SQL*Loader data file containing data description and rem !# data. rem !# This version tailored for ORACLE7.
SET ECHO OFF
SET FEED OFF
SET HEA OFF
SET VER OFF
SET PAGES 0
SET SPACE 0
SET RECSEP OFF
SET LINESIZE 80
prompt parameters are
prompt .. 1: user name, prompt .. 2: table name, prompt .. 3: output file name (Without extension .ctl)
undefine owner
undefine table
undefine outfile
define owner = &1
define table = &2
define outfile = &3
Prompt 'Copying data into &outfile..ctl where the original owner ',
Prompt 'is &owner and the table name is &table.'
prompt
PROMPT Loading command: SQLLOAD un/pw &outfile..ctl
SET TERM OFF
spool unload.tmp
COLUMN CR PRINT FOLD_AFTER 1
COLUMN LINELTH FORMAT 9999
COLUMN LRECL FORMAT 0999
SELECT 'SET LINESIZE ',
sum(decode(data_type,'NUMBER', decode(nvl(data_scale,0),0, nvl(data_precision,20)+2, data_precision+3), 'DATE',10,data_length+3)) linelth, ' ' CR FROM ALL_TAB_COLUMNS WHERE OWNER = UPPER('&owner')
AND TABLE_NAME = UPPER('&table')
/
SELECT 'SPOOL &outfile..ctl' FROM DUAL
/
SELECT 'prompt LOAD DATA' CR, 'prompt INFILE *' CR, 'prompt INTO TABLE &table' CR, 'prompt FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"'' ' CR, 'prompt ( ' cr
from dual;
REM Get first without comma:
select 'prompt ', column_name
FROM ALL_TAB_COLUMNS
WHERE OWNER = UPPER('&owner')
AND TABLE_NAME = UPPER('&table')
AND COLUMN_ID = 1
;
REM Get rest with leading comma:
select 'prompt ', ' ,'||column_name
FROM ALL_TAB_COLUMNS
WHERE OWNER = UPPER('&owner')
AND TABLE_NAME = UPPER('&table')
AND COLUMN_ID > 1
order by column_id;
SELECT 'prompt ) ' cr ,
'prompt BeginData ' cr from dual; SELECT 'SELECT '||decode( data_type,'CHAR', '''"''||'||column_name||'||''"''', 'VARCHAR2', '''"''||'||column_name||'||''"''', column_name) FROM ALL_TAB_COLUMNS WHERE OWNER = UPPER('&owner')
AND TABLE_NAME = UPPER('&table')
AND COLUMN_ID = 1
/
SELECT '||'',''||'||decode(data_type,'CHAR',
'''"''||'||column_name||'||''"''', 'VARCHAR2', '''"''||'||column_name||'||''"''', column_name) FROM ALL_TAB_COLUMNS WHERE OWNER = UPPER('&owner')
AND TABLE_NAME = UPPER('&table')
AND COLUMN_ID > 1
ORDER BY COLUMN_ID
/
SELECT 'FROM &owner..&table;' FROM DUAL
/
SELECT 'SPOOL OFF' FROM DUAL
/
/
SPOOL
SPOOL OFF
START unload.tmp
rem HOST DELETE unload.tmp
SET LINESIZE 80
SET FEED 6
SET HEA ON
SET VER ON
SET PAGES 14
SET ECHO ON
SET TERM ON
-- Bob Treumann Solution Engineering Inc. email: sei_at_elmwood.com ORACLE Business Partner (IOCA program). ORACLE CASE Specialist.Received on Thu Jan 05 1995 - 07:26:16 CET