Re: Export/Import

From: Thomas Olszewicki <ThomasO_at_cpas.com>
Date: Thu, 25 Nov 1999 17:51:59 GMT
Message-ID: <3He%3.586$75.43028@news1.rdc2.on.home.com>

Bojan,
For exporting use SQLPlus scripts like this:

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 SPOOL c:\mwWorks\TO\libmfo\MFORDER.DAT;

SET LINESIZE 19
SELECT 'HMFORDER '||TO_CHAR( sysDate , 'YYYYMMDD' ) FROM DUAL /
SET LINESIZE 75

SELECT   'D'                                              ||
                    SUBSTR( NVL( recMW.RKEY        , '          '  ) , 1 ,
  )    ||
                      RPAD( NVL( recMW.CLNT        , '          '  )     ,
10 )    ||
            RPAD(SUBSTR( NVL( recMW.MKEY        , '          '  ) , 1 ,
  ),9)    ||
                      RPAD( NVL( getMwPlanType( recMW.PLAN_TYPE )  , ' '  )
, 1 )  ||
                      RPAD( NVL( getMWSource( recMW.SOURCE  ) , ' '  ) , 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 CPASDBA.MF_ORDER recMW
WHERE recMW.rec_status='REJ' AND recMW.ORDER_DATE='09-MAY-97' /
SET LINESIZE 11
SELECT 'T+'||LTRIM( TO_CHAR( count(*) , '099999999' ) ) FROM CPASDBA.MF_ORDER
WHERE rec_status='REJ' AND ORDER_DATE='09-MAY-97' /
SPOOL OFF; this is a script used to create real output file of SDF type. I hope you can see the picture.

For imports you can use Oracle Loader.

There are many products similar to MS DTS for Oracle, but you have to buy them.

HTH
Thomas Olszewicki
ThomasO_at_cpas.com
www.cpas.com

Bojan <avrambo_at_fin.gov.on.ca> wrote in message news:81jj0k$mdm$1_at_news.gov.on.ca...
> I want to export data from Oracle to a text file. Since Oracle doesn't
have
> export/import utility, which is sad (it has, but only from/to Oracle -
dump
> files), I am using SQLPlus to spool query output to a file.
> Is there a way to exclude query text at the begining and prompt at the end
> of the output file? I just want the data, no headers etc.
> I set all parameters to OFF, but it didn't help.
> Also, what 3rd party software would you recommend for export/import,
> something similar to SQL Server's DTS, with all formats (text, dBASE,
> Access, etc etc)?

>

> Best regards.

>
> Received on Thu Nov 25 1999 - 11:51:59 CST