SQLPlus dump to flat file

Example of sqlplus script to dump tables to flat file

/****************************************************************************************************
* Date       Author          Description                                                            *
* ====       ======          ===========                                                            *
* 20180321   Simon Vollett   This script dumps the following tables to a                            *
*                            pipe delimited unix format flat file for purposes of migrating the     *
*                            state of MDM tables from one MDM installation to another:              *
*                              1)  MDM<ENV>.MDMADMIN.mpi_audattr                                    *
*                              2)  MDM<ENV>.MDMADMIN.mpi_audhead                                    *
*                              3)  MDM<ENV>.MDMADMIN.mpi_audxmem                                    *
*                              4)  MDM<ENV>.MDMADMIN.mpi_entlink_pid                                *
*                              5)  MDM<ENV>.MDMADMIN.mpi_entnote_pid                                *
*                              6)  MDM<ENV>.MDMADMIN.mpi_entrule_pid                                *
*                              7)  MDM<ENV>.MDMADMIN.mpi_entxeia_pid                                *
*                              8)  MDM<ENV>.MDMADMIN.mpi_entxtsk_pid                                *
*                              9)  MDM<ENV>.MDMADMIN.mpi_idsccont                                   *
*                              10) MDM<ENV>.MDMADMIN.mpi_idspaddr                                   *
*                              11) MDM<ENV>.MDMADMIN.mpi_idspahpra                                  *
*                              12) MDM<ENV>.MDMADMIN.mpi_idspflg                                    *
*                              13) MDM<ENV>.MDMADMIN.mpi_idspname                                   *
*                              14) MDM<ENV>.MDMADMIN.mpi_idspnum                                    *
*                              15) MDM<ENV>.MDMADMIN.mpi_idsporg                                    *
*                              16) MDM<ENV>.MDMADMIN.mpi_idspphone                                  *
*                              17) MDM<ENV>.MDMADMIN.mpi_idspreg                                    *
*                              18) MDM<ENV>.MDMADMIN.mpi_idsptrans                                  *
*                              19) MDM<ENV>.MDMADMIN.mpi_idsptype                                   *
*                              20) MDM<ENV>.MDMADMIN.mpi_memattr                                    *
*                              21) MDM<ENV>.MDMADMIN.mpi_membktd                                    *
*                              22) MDM<ENV>.MDMADMIN.mpi_memcmpd                                    *
*                              23) MDM<ENV>.MDMADMIN.mpi_memhead                                    *
****************************************************************************************************/
--Setup the params to format extracts correctly
set colsep |
set headsep off
set pagesize 0
set trimspool on
set linesize 32767
set numwidth 20
set arraysize 5000
set feedback off
set termout off
alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
alter session set CURRENT_SCHEMA=MDMADMIN;

--==> mpi_audattr.unl <==
spool mpi_audattr.unl
select
  AUDRECNO  ||'|'||
  AUDSEQNO  ||'|'||
  ATTRRECNO ||'|'||
  ASAIDXNO  ||'|'||
  ATTRVAL   ||'|'
from
  mpi_audattr;
spool off


--==> mpi_audhead.unl <==
spool mpi_audhead.unl
select
  AUDRECNO     ||'|'||
  AUDSEQNO     ||'|'||
  USRRECNO     ||'|'||
  IXNRECNO     ||'|'||
  AUDCTIME     ||'|'||
  EVTCTIME     ||'|'||
  EVTTYPENO    ||'|'||
  EVTINITIATOR ||'|'||
  EVTLOCATION  ||'|'
from
  mpi_audhead;
spool off


--==> mpi_audxmem.unl <==
spool mpi_audxmem.unl
select
  AUDRECNO ||'|'||
  MEMRECNO ||'|'
from
  mpi_audxmem;
spool off


--==> mpi_entlink_pid.unl <==
spool mpi_entlink_pid.unl
select
  MEMRECNO    ||'|'||
  SRCRECNO    ||'|'||
  CURENTRECNO ||'|'||
  LINKTYPE    ||'|'
from
  mpi_entlink_pid;
spool off


--==> mpi_entnote_pid.unl <==
spool mpi_entnote_pid.unl
select
  MEMRECNO  ||'|'||
  CAUDRECNO ||'|'||
  MAUDRECNO ||'|'||
  CUSRRECNO ||'|'||
  MUSRRECNO ||'|'||
  THENOTE   ||'|'
from
  mpi_entnote_pid;
spool off


--==> mpi_entrule_pid.unl <==
spool mpi_entrule_pid.unl
select
  MEMRECNO  ||'|'||
  CAUDRECNO ||'|'||
  MAUDRECNO ||'|'||
  MEMRECNO2 ||'|'||
  RULETYPE  ||'|'
from
  mpi_entrule_pid;
spool off


--==> mpi_entxeia_pid.unl <==
spool mpi_entxeia_pid.unl
select
  MEMRECNO     ||'|'||
  SRCRECNO     ||'|'||
  CAUDRECNO    ||'|'||
  MAUDRECNO    ||'|'||
  RECCTIME     ||'|'||
  RECMTIME     ||'|'||
  EIATYPENO    ||'|'||
  EIASTATNO    ||'|'||
  EIASTATNO2   ||'|'||
  TSKTYPENO    ||'|'||
  TSKSTATNO    ||'|'||
  USRRECNO     ||'|'||
  MAXSCORE     ||'|'||
  SUPENTRECNO  ||'|'||
  XTSKCARN     ||'|'||
  PREVENTRECNO ||'|'
from
  mpi_entxeia_pid;
spool off


--==> mpi_entxtsk_pid.unl <==
spool mpi_entxtsk_pid.unl
select
  MEMRECNO          ||'|'||
  SRCRECNO          ||'|'||
  CAUDRECNO         ||'|'||
  MAUDRECNO         ||'|'||
  RECCTIME          ||'|'||
  RECMTIME          ||'|'||
  TSKRECNO          ||'|'||
  TSKTYPENO         ||'|'||
  TSKSTATNO         ||'|'||
  OWNERTYPE         ||'|'||
  OWNERRECNO        ||'|'||
  PREVOWNERTYPE     ||'|'||
  PREVOWNERRECNO    ||'|'||
  MAXSCORE          ||'|'||
  SETRECNO          ||'|'||
  SUPENTRECNO       ||'|'||
  RELATEDMEMBERS    ||'|'
from
  mpi_entxtsk_pid;
spool off

--==> mpi_idsccont.unl <==
spool mpi_idsccont.unl
select
  MEMRECNO     ||'|'||
  MEMSEQNO     ||'|'||
  CAUDRECNO    ||'|'||
  MAUDRECNO    ||'|'||
  RECSTAT      ||'|'||
  ATTRRECNO    ||'|'||
  ASAIDXNO     ||'|'||
  EMAILADDRESS ||'|'
from
  mpi_idsccont;
spool off


--==> mpi_idspaddr.unl <==
spool mpi_idspaddr.unl
select
  MEMRECNO              ||'|'||
  MEMSEQNO              ||'|'||
  CAUDRECNO             ||'|'||
  MAUDRECNO             ||'|'||
  RECSTAT               ||'|'||
  ATTRRECNO             ||'|'||
  ASAIDXNO              ||'|'||
  ADDRESSLINE1          ||'|'||
  ADDRESSLINE2          ||'|'||
  ADDRESSLINE3          ||'|'||
  ADDRESSLINE4          ||'|'||
  SUBURB                ||'|'||
  STATE                 ||'|'||
  POSTCODE              ||'|'||
  LOTTYPE               ||'|'||
  LOTNUMBER             ||'|'||
  HOUSENUMBER1          ||'|'||
  HOUSENUMBER1SUFFIX    ||'|'||
  HOUSENUMBER2          ||'|'||
  HOUSENUMBER2SUFFIX    ||'|'||
  STREETNAME            ||'|'||
  STREETTYPE            ||'|'||
  STREETSUFFIX          ||'|'||
  POSTALDELTYPE         ||'|'||
  POSTALDELNUMBER       ||'|'||
  POSTALDELNUMBERSUFFIX ||'|'||
  FLOORTYPE             ||'|'||
  FLOORNUMBER           ||'|'||
  UNITTYPE              ||'|'||
  UNITNUMBER            ||'|'||
  MULTIUNITTYPE         ||'|'||
  MULTIUNITNUMBER       ||'|'||
  BUILDINGNAME1         ||'|'||
  BUILDINGNAME2         ||'|'||
  ADDITIONALADDRESS     ||'|'||
  DPID                  ||'|'
from
  mpi_idspaddr;
spool off


--==> mpi_idspahpra.unl <==
spool mpi_idspahpra.unl
select
  MEMRECNO    ||'|'||
  MEMSEQNO    ||'|'||
  CAUDRECNO   ||'|'||
  MAUDRECNO   ||'|'||
  RECSTAT     ||'|'||
  ATTRRECNO   ||'|'||
  ASAIDXNO    ||'|'||
  AHPRANUMBER ||'|'
from
  mpi_idspahpra;
spool off


--==> mpi_idspflg.unl <==
spool mpi_idspflg.unl
select
  MEMRECNO           ||'|'||
  MEMSEQNO           ||'|'||
  CAUDRECNO          ||'|'||
  MAUDRECNO          ||'|'||
  RECSTAT            ||'|'||
  ATTRRECNO          ||'|'||
  ASAIDXNO           ||'|'||
  LEFTPRACTICEFLAG   ||'|'||
  PRACTICECLOSEDFLAG ||'|'
from
  mpi_idspflg;
spool off


--==> mpi_idspname.unl <==
spool mpi_idspname.unl
select
  MEMRECNO  ||'|'||
  MEMSEQNO  ||'|'||
  CAUDRECNO ||'|'||
  MAUDRECNO ||'|'||
  RECSTAT   ||'|'||
  ATTRRECNO ||'|'||
  ASAIDXNO  ||'|'||
  ONMTITLE  ||'|'||
  ONMLAST   ||'|'||
  ONMFIRST  ||'|'||
  ONMMIDDLE ||'|'||
  ONMSUFFIX ||'|'
from
  mpi_idspname;
spool off


--==> mpi_idspnum.unl <==
spool mpi_idspnum.unl
select
  MEMRECNO       ||'|'||
  MEMSEQNO       ||'|'||
  CAUDRECNO      ||'|'||
  MAUDRECNO      ||'|'||
  RECSTAT        ||'|'||
  ATTRRECNO      ||'|'||
  ASAIDXNO       ||'|'||
  PROVIDERNUMBER ||'|'||
  PROVIDERSTEM   ||'|'||
  ABN            ||'|'
from
  mpi_idspnum;
spool off


--==> mpi_idsporg.unl <==
spool mpi_idsporg.unl
select
  MEMRECNO     ||'|'||
  MEMSEQNO     ||'|'||
  CAUDRECNO    ||'|'||
  MAUDRECNO    ||'|'||
  RECSTAT      ||'|'||
  ATTRRECNO    ||'|'||
  ASAIDXNO     ||'|'||
  ORGNAME      ||'|'||
  ORGLEGALNAME ||'|'
from
  mpi_idsporg;
spool off


--==> mpi_idspphone.unl <==
spool mpi_idspphone.unl
select
  MEMRECNO  ||'|'||
  MEMSEQNO  ||'|'||
  CAUDRECNO ||'|'||
  MAUDRECNO ||'|'||
  RECSTAT   ||'|'||
  ATTRRECNO ||'|'||
  ASAIDXNO  ||'|'||
  PHICC     ||'|'||
  PHAREA    ||'|'||
  PHNUMBER  ||'|'||
  PHEXTN    ||'|'||
  PHCMNT    ||'|'
from
  mpi_idspphone;
spool off


--==> mpi_idspreg.unl <==
spool mpi_idspreg.unl
select
  MEMRECNO              ||'|'||
  MEMSEQNO              ||'|'||
  CAUDRECNO             ||'|'||
  MAUDRECNO             ||'|'||
  RECSTAT               ||'|'||
  ATTRRECNO             ||'|'||
  ASAIDXNO              ||'|'||
  REGISTRATIONSTARTDATE ||'|'||
  REGISTRATIONENDDATE   ||'|'
from
  mpi_idspreg;
spool off


--==> mpi_idsptrans.unl <==
spool mpi_idsptrans.unl
select
  MEMRECNO   ||'|'||
  MEMSEQNO   ||'|'||
  CAUDRECNO  ||'|'||
  MAUDRECNO  ||'|'||
  RECSTAT    ||'|'||
  ATTRRECNO  ||'|'||
  ASAIDXNO   ||'|'||
  TRANSACTED ||'|'
from
  mpi_idsptrans;
spool off


--==> mpi_idsptype.unl <==
spool mpi_idsptype.unl
select
  MEMRECNO         ||'|'||
  MEMSEQNO         ||'|'||
  CAUDRECNO        ||'|'||
  MAUDRECNO        ||'|'||
  RECSTAT          ||'|'||
  ATTRRECNO        ||'|'||
  ASAIDXNO         ||'|'||
  PROVIDERTYPECODE ||'|'||
  PROVIDERTYPEDESC ||'|'
from
  mpi_idsptype;
spool off


--==> mpi_memattr.unl <==
spool mpi_memattr.unl
select
  MEMRECNO  ||'|'||
  MEMSEQNO  ||'|'||
  CAUDRECNO ||'|'||
  MAUDRECNO ||'|'||
  RECSTAT   ||'|'||
  ATTRRECNO ||'|'||
  ASAIDXNO  ||'|'||
  ATTRVAL   ||'|'
from
  mpi_memattr;
spool off


--==> mpi_membktd.unl <==
spool mpi_membktd.unl
select
  MEMRECNO ||'|'|| 
  SRCRECNO ||'|'||
  BKTHASH  ||'|'
from
  mpi_membktd;
spool off


--==> mpi_memcmpd.unl <==
spool mpi_memcmpd.unl
select
  MEMRECNO ||'|'||
  SRCRECNO ||'|'||
  CMPSEQNO ||'|'||
  CMPVAL   ||'|'
from
  mpi_memcmpd;
spool off


--==> mpi_memhead.unl <==
spool mpi_memhead.unl
select
  MEMRECNO  ||'|'||
  MEMSEQNO  ||'|'||
  CAUDRECNO ||'|'||
  MAUDRECNO ||'|'||
  MEMSTAT   ||'|'||
  MEMVERNO  ||'|'||
  SRCRECNO  ||'|'||
  MEMIDNUM  ||'|'
from
  mpi_memhead;
spool off