Re: Extracting Data From Oracle

From: Scott Overby <scott.overby_at_sdsdata.com>
Date: 1997/06/17
Message-ID: <5o63gg$6d9$1@news2.alpha.net>#1/1

In article <33A585EF.DAF19651_at_link.com>, cdevries_at_link.com says...
>
>Maybee this is a RTFM but I sure can't find it. We have recently
>migrated an application from a Sybase database to an Oracle database for
>the maintenance tool. Due to several requirements for the data we still
>have the need to extract the data from Oracle and reimport it into
>Sybase on a nightly basis. I have yet to be able to figure out how to
>get data out of Oracle in a CSV type format for reimport.
>
> Is this an actual Oracle decision? You can import whatever you want
>but once it is in it is ours??
>
> If there is no Oracle way to do this does anyone know of any 3rd party
>tools??
>

I picked this PL/SQL script up awhile ago and rem'd out SQL loader portions:

rem Utility oraUnloadToCtl.sql modified from public domain utility rem by Parris Geiser for Bellcore.
rem
rem Creates a SQL*Loader data file containing data description and data. rem
rem SMO changed script to delete out the header info needed for sqlldr rem by commenting out appropriate selects. rem
rem Script did not work as is...missing some semi colons.

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 .txt)

undefine owner
undefine table
undefine outfile
define owner = &1
define table = &2
define outfile = &3

Prompt 'Copying data into &outfile..txt where the original owner ', Prompt 'is &owner and the table name is &table.' prompt
PROMPT Loading command: SQLLOAD un/pw &outfile..txt

SET TERM OFF
spool unload.tmp

COLUMN CR PRINT FOLD_AFTER 1
COLUMN LINELTH FORMAT 9999
COLUMN LRECL FORMAT 0999 rem Sets maximum 'record width' depending on size of colums

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..txt' FROM DUAL;

rem Following commented out to delete sqlldr stuff rem

rem SELECT 'prompt OPTIONS (BINDSIZE=1024000)' CR,
rem        'prompt LOAD DATA' CR,
rem        'prompt INFILE *' CR,
rem        'prompt INTO TABLE &table' CR,
rem        'prompt REPLACE' CR,
rem        'prompt FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"'' '
rem CR,
rem        'prompt ( ' cr

rem from dual;
rem
rem REM Get first without comma:
rem select 'prompt '|| column_name || decode(data_type,'VARCHAR2','char'||data_l ength,'')
rem FROM ALL_TAB_COLUMNS
rem WHERE OWNER = UPPER('&owner')
rem AND TABLE_NAME = UPPER('&table')
rem AND COLUMN_ID = 1
rem ;
rem
rem REM Get rest with leading comma:
rem select 'prompt ' || ' ,'||column_name || ' ' || decode(data_type,'VARCHAR2', 'char'||'('||data_length||')','')
rem FROM ALL_TAB_COLUMNS
rem WHERE OWNER = UPPER('&owner')
rem AND TABLE_NAME = UPPER('&table')
rem AND COLUMN_ID > 1
rem order by column_id;
rem
rem SELECT 'prompt ) ' cr ,

rem        'prompt BeginData ' cr
rem        from dual;

rem

rem Sets up first column(doesn't need leading comma)

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; rem Sets up rest of columns and places quotes around CHAR and VCHAR data

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
HOST rm unload.tmp
SET LINESIZE 80
SET FEED 6
SET HEA ON
SET VER ON
SET PAGES 14
SET ECHO ON
SET TERM ON exit

-- 
==========================================================================
Scott Overby                    E-mail  scott.overby_at_sdsdata.com
Sr. Systems Analyst
Strategic Data Systems          FAX     (414) 459-9123
615 Penn Ave.                   
Sheboygan, WI 53082
===========================================================================

Received on Tue Jun 17 1997 - 00:00:00 CDT