Re: Dumping oracle table to ascii delimited file?

From: Tommy Wareing <p0070621_at_oxford-brookes.ac.uk>
Date: 12 Jan 1994 03:31:45 -0600
Message-ID: <CJIFLC.K3H_at_uk.ac.brookes>

Patrick S Hart (phart_at_magnus.acs.ohio-state.edu) wrote:

> Can anyone tell me how to dump an oracle table into an ascii delimeted
> file?  I would likl e to do this to assist in altering tables with
> data in them.  Or perhaps there is a better way?  You would think it
> would be simple.

This was posted a while ago, and does the trick fairly completely:

---

REM *************************************************************

REM EXPV6A.SQL
REM - EXPORTS ORACLE V6 TABLE DATA TO SQL SCRIPT

REM *************************************************************

REM
REM CREATE EXPORT/IMPORT SCRIPT
REM AUTHOR: JOE HARLEMAN

REM         APPLIED DATABASE DESIGN, INC.
REM         8640 JERNIGAN RD. #45
REM         PENSACOLA, FL 32514
REM         904-479-3416

PROMPT
PROMPT EXPV6A.SQL CREATE EXPORT/IMPORT SCRIPT PROMPT
PROMPT Note: use " to surround char variables in the where clause

PROMPT         the where clause is mandatory (use "X" = "X" if all desired)
PROMPT         the word WHERE will be automatically added

PROMPT

ACCEPT TABLE_OWNER	CHAR PROMPT 'TABLE OWNER ...(OPS$ACCTG) .......: '
ACCEPT TABLE_NAME	CHAR PROMPT 'TABLE NAME ....(CUSTOMER).........: '
ACCEPT WHERE_CLAUSE	CHAR PROMPT 'WHERE CLAUSE ..(CUST_ID = "ABC")..: '
ACCEPT EXPORT_FILE	CHAR PROMPT 'EXPORT TO FILE (CUSTOMER.SQL).....: '

SET TERMOUT  OFF
SET ECHO     OFF

SET PAGESIZE 0
SET HEADING OFF
SET LINESIZE 80
SET FEEDBACK OFF
SET VERIFY OFF
SET EMBEDDED ON
SET RECSEP OFF
SET SPACE 0 COLUMN X1 NEWLINE
COLUMN X2 NEWLINE
COLUMN X3 NEWLINE
COLUMN X4 NEWLINE
COLUMN X5 NEWLINE
COLUMN X6 NEWLINE
COLUMN X7 NEWLINE
COLUMN X8 NEWLINE
COLUMN X9 NEWLINE
COLUMN X10 NEWLINE
COLUMN X11 NEWLINE
COLUMN X12 NEWLINE
COLUMN X13 NEWLINE
COLUMN X14 NEWLINE
COLUMN X15 NEWLINE
COLUMN X16 NEWLINE
COLUMN X17 NEWLINE
COLUMN X18 NEWLINE
COLUMN X19 NEWLINE
COLUMN X20 NEWLINE
COLUMN X21 NEWLINE SPOOL EXPV6B.sql

SELECT
'REM*********************************************************' X1,
'REM EXPV6B.SQL' X2,
'REM CREATED BY EXPV6A.SQL' X3,
'REM*********************************************************' X4,
'REM' X5,
'COL X1 NEWLINE' X6,
'COL X2 NEWLINE' X7,
'COL X3 NEWLINE' X8,
'COL X4 NEWLINE' X9,
'COL X5 NEWLINE' X10,
'COL X6 NEWLINE' X11,
'COL X7 NEWLINE' X12,
'COL X8 NEWLINE' X13,
'COL X9 NEWLINE' X14,
'COL X10 NEWLINE' X15,
' ' X16,
'SELECT '||''''||'REM '||'&&EXPORT_FILE'||''''||' X1,' X17,

           ''''||'REM '||''''||' X2'				X18,


'FROM DUAL'							X19,

'/'								X20,

'SELECT '||''''||'INSERT INTO '||OWNER||'.'||TABLE_NAME||' ('||''''||',' X21
FROM ALL_TABLES
WHERE TABLE_NAME = UPPER('&&TABLE_NAME') AND OWNER = UPPER('&&TABLE_OWNER')
/

SELECT ''''||COLUMN_NAME||','||''''||',' CN FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&&TABLE_NAME') AND OWNER = UPPER('&&TABLE_OWNER')
AND COLUMN_ID !=

       (SELECT MAX(COLUMN_ID)
        FROM   ALL_TAB_COLUMNS
        WHERE  TABLE_NAME = UPPER('&&TABLE_NAME')
        AND    OWNER = UPPER('&&TABLE_OWNER') )

ORDER BY COLUMN_ID
/

SELECT ''''||COLUMN_NAME||''''||',' CN
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&&TABLE_NAME') AND OWNER = UPPER('&&TABLE_OWNER')
AND COLUMN_ID =

       (SELECT MAX(COLUMN_ID)
        FROM   ALL_TAB_COLUMNS
        WHERE  TABLE_NAME = UPPER('&&TABLE_NAME')
        AND    OWNER = UPPER('&&TABLE_OWNER') )

ORDER BY COLUMN_ID
/

SELECT ''''||' ) VALUES ('||''''||',' X1 FROM ALL_TABLES
WHERE TABLE_NAME = UPPER('&&TABLE_NAME') AND OWNER = UPPER('&&TABLE_OWNER')
/

SELECT DECODE(DATA_TYPE,'DATE',''''||''''||''''||'''',

                        'CHAR',''''||''''||''''||'''',
                         NULL)||
       DECODE(DATA_TYPE,'DATE','||',
                        'CHAR','||',
                        'NVL(TO_CHAR(')||
       DECODE(DATA_TYPE, 'LONG', 'NULL', COLUMN_NAME)||
       DECODE(DATA_TYPE,'DATE','||',
                        'CHAR','||',
                       '),'||''''||'NULL'||''''||')')||
       DECODE(DATA_TYPE,'DATE',''''||''''||''''||'''',
                        'CHAR',''''||''''||''''||'''',
                         NULL)||
       '||'||''''||','||''''||
       ',' CN

FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&&TABLE_NAME') AND OWNER = UPPER('&&TABLE_OWNER')
AND COLUMN_ID !=

       (SELECT MAX(COLUMN_ID)
        FROM   ALL_TAB_COLUMNS
        WHERE  TABLE_NAME = UPPER('&&TABLE_NAME')
        AND    OWNER = UPPER('&&TABLE_OWNER') )

ORDER BY COLUMN_ID
/

SELECT DECODE(DATA_TYPE,'DATE',''''||''''||''''||'''',

                        'CHAR',''''||''''||''''||'''',
                         NULL)||
       DECODE(DATA_TYPE,'DATE','||',
                        'CHAR','||',
                        'NVL(TO_CHAR(')||
       DECODE(DATA_TYPE,'LONG', 'NULL', COLUMN_NAME)||
       DECODE(DATA_TYPE,'DATE','||',
                        'CHAR','||',
                       '),'||''''||'NULL'||''''||')')||
       DECODE(DATA_TYPE,'DATE',''''||''''||''''||'''',
                        'CHAR',''''||''''||''''||'''',
                         NULL)||
       ' ' CN

FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&&TABLE_NAME') AND OWNER = UPPER('&&TABLE_OWNER')
AND COLUMN_ID =

       (SELECT MAX(COLUMN_ID)
        FROM   ALL_TAB_COLUMNS
        WHERE  TABLE_NAME = UPPER('&&TABLE_NAME')
        AND    OWNER = UPPER('&&TABLE_OWNER') )

/

SELECT '||'||

         ''''||
         ');'||
         ''''				X1,
       'FROM '||OWNER||'.'||TABLE_NAME	X2,
       'WHERE '||TRANSLATE('&&WHERE_CLAUSE','"','''')	X3,
       '/'				X4

FROM ALL_TABLES
WHERE TABLE_NAME = UPPER('&&TABLE_NAME') AND OWNER = UPPER('&&TABLE_OWNER')
/

SPOOL OFF SPOOL &&EXPORT_FILE START EXPV6B SPOOL OFF HOST rm -f EXPV6B.sql

EXIT

---

--
  _________________________   _______________________________
 /  Tommy Wareing          \ / 'Happy, happy. Joy, joy!'     \
|  p0070621_at_brookes.ac.uk   X      said Ren & Stimpy          |
 \  0865-483389            / \ (Supplied by Simone, not me!) /
  ~~~~~~~~~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Received on Wed Jan 12 1994 - 10:31:45 CET