Re: Need a SQL*UNLoader
From: Bob Treumann <btreuman_at_elmwood.com>
Date: 13 Dec 1994 04:26:56 GMT
Message-ID: <3cj7qg$acn_at_elmwood.elmwood.com>
Date: 13 Dec 1994 04:26:56 GMT
Message-ID: <3cj7qg$acn_at_elmwood.elmwood.com>
Steven Spruell (sspruell_at_ssf3.jsc.nasa.gov) wrote: : Has anyone got any info on products or techniques to generate a delimited : export file from Oracle tables? I know I can use sqlplus or Reports and
Here it is!
No Warranties...
-- Bob Treumann Solution Engineering Inc. email: sei_at_elmwood.com ORACLE Business Partner (IOCA program). ORACLE CASE Specialist. ============================================================================ rem Utility Unload.sql modified from public domain utility rem cpydlmtd.sql contained in IOUG 1992 utilities diskette. rem rem Modifications 1993 Solution Engineering Inc. rem sei_at_elmwood.com 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')Received on Tue Dec 13 1994 - 05:26:56 CET
/
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