Re: Need help exporting tables to flat files

From: Thomas Olszewicki <ThomasO_at_cpas.com>
Date: Thu, 24 Feb 2000 21:33:19 GMT
Message-ID: <zsht4.44170$632.1827937@news1.rdc2.on.home.com>

Ken,
This is an example of one of my scripts to create flat file:

SET PAUSE OFF
SET PAGESIZE 0
SET ECHO OFF
SET TERMOUT OFF
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET RECSEP OFF
SET SPACE 0

--
-- Redirect output to file.
--

SPOOL <Filename>;

--
-- create data rows. (SDF format)
-- All columns are converted to strings for better formating
-- for comma delimited add ',' strings
-- Example: SELECT 'D'||',',
--                 SUBSTR( NVL( recMW.RKEY, '          ') , 1 , 4  )||',',
--          -- Note ||',' in end of every column.
--
-- set line size correctly to prevent trailing blanks.
SET LINESIZE 74
SELECT SUBSTR( NVL( recMW.RKEY, '          ') , 1 , 4  ),
       RPAD(NVL( recMW.CLNT, '          ') , 10 ),
       RPAD(SUBSTR( NVL( recMW.MKEY, '          ') , 1 , 9  ),9),
       RPAD( NVL(  recMW.MWPLAN, ' ') , 1 ),
       RPAD( NVL( recMW.MWSRCE, ' '  ) , 1 ),
       RPAD( NVL( recMW.AMT_TYPE,' ') , 1 ),
       LTRIM( TO_CHAR( NVL( recMW.AMOUNT,0),'S099999999.9999' )),
       RPAD( NVL( recMW.FUND_MNGR,' '),3),
       RPAD( NVL( recMW.FUND_NUM,' '),5),
       LTRIM( TO_CHAR( NVL( recMW.COM_PERCNT,0),'S09.999')),
       RPAD( NVL( recMW.FUND_OPT, ' '),1),
       RPAD( NVL( recMW.TRADE_TYPE,' '),2),
       RPAD( DECODE(recMW.ORDER_DATE,NULL,'
,TO_CHAR( recMW.ORDER_DATE,'YYYYMMDD')),8),
       RPAD( NVL( recMW.DIV_OPTION,' '),1),
       RPAD( NVL( recMW.SEC_CODE,' '),6)

FROM CPASINT.MFO_B2 recMW
WHERE....
/

--
-- Close output to file.
--

SPOOL OFF; This may give you an idea how to format your output. For 50+ tables it may be a handfull solution..., but it is fast.

HTH
Thomas

"Ken Taylor" <taylorkh_at_mind$pring.com> wrote in message news:38b5966c.293217875_at_news.mindspring.com...
> Greetings,

>

> I have a need to export a significant number (50+) of large tables
> (500k - 3M rows) to space padded flat files for processing in a PC/LAN
> based conversion program. The source is Oracle 7.x on HPUX. I have
> accomplished this with Datacom on MVS with little problem using
> Dataquery and some JCL. On the Oracle side I am not finding a
> convenient method.

>

> The flat files must be space padded (e.g. a VARCHAR3(500) containing
> 100 bytes must be padded with 400 spaces for a total width of 500 in
> the ASCII file.) This is a limitation of the comversion environment.
> Connecting with SQL*Net and ODBC is slower than dirt. Spooling the
> data from within SQL*Plus has been recommended however that presents
> the problem of stripping header lines from the resulting file.

>

> The process needs to be reasonably speedy and repeatable as we will be
> making a number of practice runs.

>

> Any suggestions?

>

> Thanks,

>

> Ken

>
>

> Ken Taylor
> taylorkh_at_mind$pring.com
> change the $ to s to reply
> unless you are a spammer then...
Received on Thu Feb 24 2000 - 15:33:19 CST