Re: ORACLE EXTRACTS (The answer)

From: Bob Treumann <btreuman_at_elmwood.com>
Date: 1995/05/04
Message-ID: <3obhma$v18_at_elmwood.elmwood.com>#1/1

Joe Gard (76546.2161_at_CompuServe.COM) wrote:

: Is there a way to extract data from an ORACLE table to a flat file.  The file
: must be readable (ASCII) not what you get from an EXPORT.  I have not found
: any ORACLE provided utilities to do this.  Are there 3rd parties that do this ?



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

--
  Solution Engineering Inc. (St Paul, Minnesota)    email:  sei_at_elmwood.com
  Bob Treumann                      ORACLE Business Alliance Program Member

Received on Thu May 04 1995 - 00:00:00 CEST