Re: S: unloader for Oracle

From: Antero.Elovaara <antero.elovaara_at_ivo.fi>
Date: 20 Mar 1995 08:02:07 GMT
Message-ID: <3kjcpv$226_at_idefix.eunet.fi>

> Hi,
>
> I'm looking for an unloader-utility (SQL*Plus or Pro*C) which is
> able to unload tables from Oracle. I know that there were some
> postings concerning 'unload of a table' in former times, but at
> that time it was not interesting for me. Perhaps somebody outside
> has one or more of these tools that were published some months ago.
>
> Thanks in advance
> Helmut
> --
> Helmut Kuhn Tel: +49 89 636-2887
> Siemens-Nixdorf Informationssysteme AG Fax: +49 89 636-48332
> SNI MR PD 432 email: helmut.kuhn_at_mch.sni.de
> Otto-Hahn-Ring 6
> 81739 Muenchen

I Found some old nut hopefully not obsolete material from Oracle user week 1991. This is part of SQL*Dump originally designed by John C. Flack. I have done some minor modifications to make this one run under Unix and Oracle 6.0.36. The enclosed script needs two parameters, table owner and table name.

Reference:
SQL*Dump: A Generalized Data Dump Utility Paper Reference Number 57/IOUW 1991
John C. Flack
Synectics for Management Decisions

REM Modified Antero Elovaara 30.11.93
REM
SET ECHO OFF
SET FEED OFF
SET HEA OFF
SET VER OFF
SET PAGES 0
SET SPACE 0
SET RECSEP OFF
SET LINESIZE 80
SELECT 'Copying data into &2..doc where the original owner ',

       'is &1 and the table name is &2.'   FROM DUAL;
SET TERM OFF
SPOOL TMPSQL.sql
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 ACCESSIBLE_COLUMNS
 WHERE OWNER      = UPPER('&1')

   AND TABLE_NAME = UPPER('&2')
/
SELECT 'SPOOL &2..doc' FROM DUAL
/
SELECT 'SELECT 'öödecode(data_type,'CHAR',

                  '''"''öö'ööcolumn_nameöö'öö''"''',
                  column_name)
  FROM ACCESSIBLE_COLUMNS
 WHERE OWNER      = UPPER('&1')

   AND TABLE_NAME = UPPER('&2')
   AND COLUMN_ID = 1
/
SELECT 'öö'',''öö'öödecode(data_type,'CHAR',

                  '''"''öö'ööcolumn_nameöö'öö''"''',
                  column_name)
  FROM ACCESSIBLE_COLUMNS
 WHERE OWNER      = UPPER('&1')

   AND TABLE_NAME = UPPER('&2')
   AND COLUMN_ID > 1
ORDER BY COLUMN_ID
/
SELECT 'FROM &1..&2;' FROM DUAL
/
SELECT 'SPOOL OFF' FROM DUAL
/
SPOOL OFF
START TMPSQL.sql
SET LINESIZE 80
SET FEED 6
SET HEA ON
SET VER ON
SET PAGES 14
SET ECHO ON
SET TERM ON
exit Received on Mon Mar 20 1995 - 09:02:07 CET