Spool output of large table with million records
Hi,
I am trying to simply take output of one table records in text file using query. I am getting error "ORA 01841 full year must be between 4713 and 9999 and not be 0" after extraction of very few records from the table.
Below is the query I am presently using:
SET HEADING OFF
SET VERIFY OFF
SET SERVEROUTPUT OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET LINESIZE 32000
SET PAGESIZE 0
SET COLSEP "|"
Column MANDT format A5
Column BUKRS format A5
Column BELNR format A10
Column GJAHR format A5
Column BLART format A2
Column BLDAT format A11
Column BUDAT format A11
Column MONAT format A5
Column CPUDT format A10
Column CPUTM format A8
Column AEDAT format A10
Column UPDDT format A10
Column WWERT format A10
Column USNAM format A12
Column TCODE format A20
Column BVORG format A16
Column XBLNR format A16
Column DBBLG format A10
Column STBLG format A10
Column STJAH format A5
Column BKTXT format A25
Column WAERS format A5
Column KURSF format 999999999.99999
Column KZWRS format A5
Column KZKRS format 999999999.99999
Column BSTAT format A5
Column XNETB format A5
Column FRATH format 9999999999999.99
Column XRUEB format A5
Column GLVOR format A5
Column GRPID format A12
Column DOKID format A40
Column ARCID format A10
Column IBLAR format A5
Column AWTYP format A5
Column AWKEY format A20
Column FIKRS format A5
Column HWAER format A5
Column HWAE2 format A5
Column HWAE3 format A5
Column KURS2 format 999999999.99999
Column KURS3 format 999999999.99999
Column BASW2 format A5
Column BASW3 format A5
Column UMRD2 format A5
Column UMRD3 format A5
Column XSTOV format A5
Column STODT format A10
Column XMWST format A5
Column CURT2 format A5
Column CURT3 format A5
Column KUTY2 format A5
Column KUTY3 format A5
Column XSNET format A5
Column AUSBK format A5
Column XUSVR format A5
Column DUEFL format A5
Column AWSYS format A10
Column TXKRS format 999999999.99999
Column LOTKZ format A10
Column XWVOF format A5
Column STGRD format A5
Column PPNAM format A12
Column BRNCH format A5
Column NUMPG format A5
Column ADISC format A5
Column XREF1_HD format A20
Column XREF2_HD format A20
Column XREVERSAL format A10
Column REINDAT format A10
Column PSOTY format A5
Column PSOAK format A10
Column PSOKS format A10
Column PSOSG format A5
Column PSOFN format A30
Column INTFORM format A8
Column INTDATE format A10
Column PSOBT format A10
Column PSOZL format A5
Column PSODT format A10
Column PSOTM format A8
Column FM_UMART format A8
Column CCINS format A4
Column CCNUM format A25
Column SSBLK format A5
Column BATCH format A10
Column SNAME format A12
Column SAMPLED format A8
Column KNUMV format A10
Column XBLNR_ALT format A26
Column ZZREASON_CD format A12
Column ZZREASON_DSCRPT format A50
spool bkpf.txt
select MANDT, BUKRS, BELNR, GJAHR, BLART, decode(BLDAT,'00000000','00.00.0000',to_char(to_date(substr(BLDAT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as BLDAT, decode(BUDAT,'00000000','00.00.0000',to_char(to_date(substr(BUDAT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as BUDAT, MONAT, decode(CPUDT,'00000000','00.00.0000',to_char(to_date(substr(CPUDT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as CPUDT, decode(cputm,'000000','00:00:00',to_char(substr(cputm,1,2)||':'||substr(cputm,3,2)||':'||substr(cputm,5,2))) as CPUTM,
decode(aedat,'00000000','00.00.0000',to_char(to_date(substr(AEDAT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as AEDAT,
decode(UPDDT,'00000000','00.00.0000',to_char(to_date(substr(UPDDT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as UPDDT,
decode(WWERT,'00000000','00.00.0000',to_char(to_date(substr(WWERT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as WWERT,
USNAM, TCODE, BVORG, XBLNR, DBBLG, STBLG, STJAH, BKTXT, WAERS, KURSF, KZWRS, KZKRS, BSTAT, XNETB, FRATH, XRUEB, GLVOR, GRPID, DOKID, ARCID, IBLAR, AWTYP,
AWKEY, FIKRS, HWAER, HWAE2, HWAE3, KURS2, KURS3, BASW2, BASW3, UMRD2, UMRD3, XSTOV, decode(STODT,'00000000','00.00.0000',to_char(to_date(substr(STODT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as STODT, XMWST, CURT2, CURT3, KUTY2,
KUTY3, XSNET, AUSBK, XUSVR, DUEFL, AWSYS, TXKRS, LOTKZ, XWVOF, STGRD, PPNAM, BRNCH, NUMPG, ADISC, XREF1_HD, XREF2_HD, XREVERSAL,
decode(REINDAT,'00000000','00.00.0000',to_char(to_date(substr(REINDAT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as REINDAT, PSOTY, PSOAK, PSOKS, PSOSG, PSOFN, INTFORM, decode(INTDATE,'00000000','00.00.0000',to_char(to_date(substr(INTDATE,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as INTDATE,
decode(PSOBT,'00000000','00.00.0000',to_char(to_date(substr(PSOBT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as PSOBT, PSOZL,
decode(PSODT,'00000000','00.00.0000',to_char(to_date(substr(PSODT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as PSODT, decode(PSOTM,'000000','00:00:00',to_char(substr(PSOTM,1,2)||':'||substr(PSOTM,3,2)||':'||substr(PSOTM,5,2))) as PSOTM, FM_UMART, CCINS,
CCNUM, SSBLK, BATCH, SNAME, SAMPLED, KNUMV, XBLNR_ALT, ZZREASON_CD, ZZREASON_DSCRPT from sapsrp.bkpf where mandt = 200;
spool off
Kindly suggest what I am missing in this, so that I can extract all records from this table.
Thanks,
Manish