Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    33

    Unhappy Unanswered: Spooling file size exceeds

    Hi All,
    I wonder if someone could help me with this situation.

    I am spooling the output of a select statement in a file, with '|' delimited fields.I am retrieving millions of records.

    I find the output file created with the last record written half way.
    I could not figure out, if my sql really finished capturing all the records, or due to file size limit errors, it stopped half way where it was writing.

    I dont see any error message in the output file.
    If there was any error on the SQLPLUS, i would assume, it would have been captured in my output file.

    I would appreciate , if anyone can help me find why the records got written halfway and ended.

    I have the foll defined in my sql file.


    SET TERMOUT OFF;
    SET ECHO OFF;
    SPOOL cust.txt;
    SELECT COL1'|'COL2'|'
    FROM ....
    SPOOL OFF;

    I find the output file created with a record wrtten half way.
    If there was any error on the SQLPLUS, i would assume, it would have been captured in my output file.

    The filesize was 2,147,483,647

    I have attached last 2 records from the file.

    84731|73104|1|18533|0|1020404977872020|WT|2003-05-20 00:00:00|2003-06-20 00:00:00|659|kWh||R|2003-11-22 15:00:27
    84473|31904|1|18538|0|102049797872020|WTU|200


    Thanks for your help in advance.
    Saratha

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    what happens when you try to print only that customer to a spool file??
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2003
    Posts
    33
    Originally posted by The_Duck
    what happens when you try to print only that customer to a spool file??
    It prints fine for a customer. to avoid filesize limitations, i am running the same file for a different range of date,
    Foe eg.

    1st file - will hold data from DEC,2002 to Aug 2003
    2nd file - will hold data from Sep1st,2003 to current date.

    The second file finishes fine with 2 million records.

    Saratha

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    good idea.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2003
    Posts
    33
    but still the first file ends up with record half way written,
    is thisreally a file size issue , oor i am dealing with some other issue.

    do I still have to split the first file.

    Thanks for the help
    saratha

  6. #6
    Join Date
    Nov 2003
    Posts
    33
    Originally posted by The_Duck
    what happens when you try to print only that customer to a spool file??
    Do you think, i should take the "set echo or set feedback" off of the sql.
    Will it then , send the error message then to the output file

    I really cannot take term out off, as it will start listing everything on the screen, there are millions of records i am trying to spool.

    Any suggestions would help
    Saratha

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    can i see your script?

    Have you tried adding ablank line at the end of the spool?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Nov 2003
    Posts
    33
    Originally posted by The_Duck
    can i see your script?

    Have you tried adding ablank line at the end of the spool?
    Here is the script , how do you add a blank line at the end of the spool?

    Thanks for your help
    Saratha

    -- This script Generates and spools Customer Usage information from datawarehouse -- Initial extract
    -- set serveroutput on;
    set tab off;
    set heading off;
    set feedback off;
    set pagesize 0;
    set verify off;
    set termout off;
    set linesize 300;

    spool /f1/conv/peace/rep_energy/energy/dedw_out/dedw_usage_extract_init1.txt;

    select rtrim('PREMNUM|DEBTORNUM|SERVICENUM|METERNUM|REGIS TERNUM|ESIID|DISTRIBUTOR|START_DATE|END_DATE|USAGE |USAGE_UNIT|READCODE|DATA_SOURCE|TIMESTAMP')
    from dual;

    select
    -- /*+ INDEX(EB_READING EB_RDG_IDX1) */
    /*+ INDEX(PM_PREMISE PM_PM_IDX1) */
    RTRIM(pm_service.premnum) ||'|'||
    RTRIM(a.DEBTORNUM) ||'|'||
    RTRIM(pm_service.servicenum)||'|'||
    RTRIM(eb_reading.meternum)||'|'||
    RTRIM(EB_READING.registernum)||'|'||
    -- RTRIM(get_esi_part_code(a.premnum))||'|'||
    RTRIM(A2.ext_sdp_code)||'|'|| -- ESI_ID
    RTRIM(A2.PARTICIPANT_CODE)||'|'||
    RTRIM(to_char(eb_reading.prevdate,'yyyy-mm-dd hh24:mi:ss'))||'|'||
    RTRIM(to_char(eb_reading.readdate,'yyyy-mm-dd hh24:mi:ss'))||'|'||
    RTRIM(abs(eb_reading.reading))||'|'||
    RTRIM(B_UNITS.UNIT_ABBREV)||'|'||
    -- RTRIM(get_unit_abbrv(pm_service.premnum))||'|'||
    RTRIM(eb_reading.readcode)||'|'||
    RTRIM(decode(eb_reading.reversed,'Y','C','N','R')) ||'|'||
    RTRIM(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')) || '###'
    from PM_SERVICE,
    PM_E_CONSPREM a,
    PM_SDP_ROLE A2,
    pm_svc_deliv_pt B2,
    eb_reading,
    eb_invoice,
    pm_premise,
    pm_division,
    EB_METERMODEL,
    B_UNITS
    where
    PM_SERVICE.PREMNUM = a.Premnum
    and a.premnum = pm_premise.premnum
    and pm_service.premnum = pm_premise.premnum
    and pm_premise.division_code = pm_division.division_code
    and eb_reading.premnum = pm_service.premnum
    and eb_reading.servicenum = pm_service.servicenum
    and eb_reading.e_invnum = eb_invoice.e_invnum
    and eb_invoice.debtornum = a.debtornum
    and pm_premise.premnum = B2.ref_no1
    and pm_service.premnum = B2.ref_no1
    and B2.SDP_CODE = A2.sdp_code
    and A2.participant_role = 'TDSP'
    and pm_division.company_code in ('WTUR','CPLR','DEBS')
    and (eb_reading.readcode != 'M' or eb_reading.readcode is null)
    and ((a.finalreaddate >= '23-DEC-2002' and a.status_cons in ('P','D'))
    OR (a.finalreaddate is NULL and a.status_cons in ('F','C'))
    OR (a.supplyreqdate >= '23-DEC-2002' and a.status_cons in ('D')))
    and a.supplyreqdate = (select max(supplyreqdate) from pm_e_consprem b
    where a.premnum = b.premnum
    AND A.DEBTORNUM = B.DEBTORNUM
    group by b.premnum,b.debtornum)
    and a.consnum = (select max(consnum) from pm_e_consprem b
    where a.premnum = b.premnum
    AND A.DEBTORNUM = B.DEBTORNUM
    group by b.premnum,b.debtornum)
    -- and A2.effective_to is null
    AND EB_METERMODEL.METERMOD = EB_READING.METERMOD
    AND EB_METERMODEL.UNIT_CODE = B_UNITS.UNIT_CODE
    and eb_reading.readdate >= '23-DEC-2002'
    and eb_reading.readdate <= '30-AUG-2003'
    UNION
    select
    /*+ INDEX(PM_PREMISE PM_PM_IDX1) */
    -- /*+ INDEX(EB_READING EB_RDG_IDX1) */
    RTRIM(pm_register.premnum) ||'|'||
    RTRIM(a.DEBTORNUM) ||'|'||
    RTRIM(pm_register.servicenum) ||'|'||
    RTRIM(eb_reading.meternum) ||'|'||
    RTRIM(pm_register.registernum) ||'|'||
    -- RTRIM(get_esi_part_code(a.premnum)) ||'|'||
    RTRIM(A2.ext_sdp_code) ||'|'|| -- ESI_ID
    RTRIM(A2.PARTICIPANT_CODE) ||'|'||
    RTRIM(to_char(eb_reading.prevdate,'yyyy-mm-dd hh24:mi:ss')) ||'|'||
    RTRIM(to_char(eb_reading.readdate,'yyyy-mm-dd hh24:mi:ss')) ||'|'||
    RTRIM(abs(eb_reading.reading)) ||'|'||
    RTRIM(b_units.unit_abbrev) ||'|'||
    RTRIM(eb_reading.readcode) ||'|'||
    RTRIM(decode(eb_reading.reversed,'Y','C','N','R')) ||'|'||
    RTRIM(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')) ||'###'
    from PM_REGISTER,
    PM_E_CONSPREM a,
    PM_SDP_ROLE A2,
    pm_svc_deliv_pt B2,
    eb_reading,
    eb_invoice,
    pm_premise,
    pm_division,
    B_UNITS
    where
    PM_register.PREMNUM = a.Premnum
    and a.premnum = pm_premise.premnum
    and pm_register.premnum = pm_premise.premnum
    and pm_premise.division_code = pm_division.division_code
    and pm_register.unit_code = b_units.unit_code
    and eb_reading.registernum = pm_register.registernum
    and eb_reading.premnum = pm_register.premnum
    and eb_reading.servicenum = pm_register.servicenum
    and eb_reading.e_invnum = eb_invoice.e_invnum
    and eb_invoice.debtornum = a.debtornum
    and pm_premise.premnum = B2.ref_no1
    and pm_register.premnum = B2.ref_no1
    and B2.SDP_CODE = A2.sdp_code
    and A2.participant_role = 'TDSP'
    and pm_division.company_code in ('WTUR','CPLR','DEBS')
    and (eb_reading.readcode != 'M' or eb_reading.readcode is null)
    and ((a.finalreaddate >= '23-DEC-2002' and a.status_cons in ('P','D'))
    OR (a.finalreaddate is NULL and a.status_cons in ('F','C'))
    OR (a.supplyreqdate >= '23-DEC-2002' and a.status_cons in ('D')))
    and a.supplyreqdate = (select max(supplyreqdate) from pm_e_consprem b
    where a.premnum = b.premnum
    AND A.DEBTORNUM = B.DEBTORNUM
    group by b.premnum,b.debtornum)
    and a.consnum = (select max(consnum) from pm_e_consprem b
    where a.premnum = b.premnum
    AND A.DEBTORNUM = B.DEBTORNUM
    group by b.premnum,b.debtornum)
    -- and A2.effective_to is null
    and eb_reading.readdate >= '23-DEC-2002'
    and eb_reading.readdate <= '30-AUG-2003';
    spool off;

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    set your linesize to something larger like 500 or 1000

    next, before your spool off command add 4 carriage returns


    run the script for a small dataset and see if the last line changes any.

    If you run the script for 10 customers, does the last line always break or is this only an occurance when you use a large dataset?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Sep 2003
    Location
    NE Florida w/ view of co-workers
    Posts
    32
    Just a thought...

    You mentioned that the filesize was 2,147,483,647; that's 2 gig. Could you be running into an o/s filesize limitation of 2 gig?

    What was the size of the spool file from the second date range? Was it less than 2 gig?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •