Spool File issues

[akutty]

Hi All,

I run the following spool commands from pl/SQL. The problem I face is all the records doesnt get spooled into the text file, always some records are left out from the end, table typically contains approximately 1351000 records, every month this records count varies slightly, after running spool command, i notice approximately last 130-150 records missing in the spooled text file. please help

set echo off;
set head off;
set feedback off;
set linesize 1000;
set pagesize 0;
set sqlprompt '';
set trimspool on;
set verify off;

spool d:/us_extract.txt

select '"' || nvl(replace(EXT_BUSINESS,'"'),'') || '","' || nvl(replace(EXT_CUST_BUS_ENTITY,'"'),'')||'","'
||nvl(replace(EXT_CUSTID,'"'),'') || nvl(replace(EXT_PLTF,'"'),'') || nvl(replace(EXT_CUST_COUNTRY_CD,'"'),'') || nvl(replace(EXT_DEALID,'"'),'') || '","' || nvl(replace(EXT_CUST_NAME,'"'),'')||'","'
||nvl(replace(EXT_CUST_ADD1,'"'),'') || '","' ||nvl( replace(EXT_CUST_ADD2,'"'),'')||'","'
||nvl(replace(EXT_CUST_ADD3,'"'),'') || '","' ||nvl( replace(EXT_CUST_ADD4,'"'),'')||'","'
||nvl(replace(EXT_CUST_ADD5,'"'),'') || '","' ||nvl( replace(EXT_CUST_CITY,'"'),'')||'","'
||nvl(replace(EXT_CUST_STATE_CODE,'"'),'') || '","' ||nvl( replace(EXT_CUST_POST_CODE,'"'),'')||'",'
||nvl(replace(EXT_CUST_ADDR_TYPE,'"'),'') || ',"' || nvl(replace(EXT_CUST_COUNTRY_CD,'"'),'')||'","'
||nvl(replace(TRIM(LPAD(EXT_CUST_IND_CD,2)),'"'),'') || '","' ||nvl( replace(EXT_BUS_CR_SC_NAME,'"'),'')||'",'
||nvl(replace(EXT_BUS_CR_SC,'"'),'') || ',"' ||nvl( replace(EXT_BUS_CR_RAT,'"'),'')||'","'
||nvl(replace(EXT_DEALID,'"'),'') || '","' ||nvl( replace(EXT_DEALNAME,'"'),'')||'","'
||nvl(replace(EXT_CURR_CD,'"'),'') ||nvl(replace(EXT_TRANSID,'"'),'') || '","' ||nvl( replace(EXT_CUS_TRANSROLE,'"'),'')||'","'
||nvl(replace(EXT_BUS_PRODUCT_NAME1,'"'),'') || '","' ||nvl(to_char(ext_matu_date,'MM/DD/YYYY'),'')||'","'
||nvl(replace(EXT_TRANS_CONT_CD,'"'),'') || '",' ||nvl( replace(EXT_EXPOSURE_AMT,'"'),'')||',"'
||nvl(replace(EXT_CURR_CD,'"'),'') || '","' ||nvl(to_char(EXT_PERIOD_END_DT,'MM/DD/YYYY'),'')||'",'
||nvl(replace(EXT_3059_PAST_DUEAMT,'"'),'') || ',' || nvl(replace(EXT_6089_PASS_DUEAMT,'"'),'')||','
||nvl(replace(EXT_90_PAST_DUEAMT,'"'),'') || ',"' ||nvl( replace(EXT_NEG_WATCH,'"'),'')||'","'
||nvl(replace(EXT_EXP_AMT_nonearn,'"'),'') || '","' ||nvl( replace(EXT_BUS_COLL_NAME1,'"'),'')||'"'
from T_STAM_PROTNEXTRACT_JUN04;

 

[KenCunningham]

Is it filling up your disk?

 

[hoinz]

do you use 'spool off' at the end?

 

[akutty]

Hi All,

1) No I have sufficient disk space left, spool file comes to around 400 MB

2) I am not using 'spool off'

Regards
Anil

 

[hoinz]

Not sure, but I think you should.
I once noticed on a Unix system, that the output was written to the spoolfile in chunks of a few KB. Part of the last chunk was kept in some kind of buffer until one issued a 'spool off'.
Not sure if this applies to Win; but why not try it?
hope this helps

 

[SantaMufasa]

Kutty,

Hoinz is correct: The "spool off" command writes an "end-of-file" marker to your output file. Without it, your results on any and all operating systems will be incomplete. I cannot imagine any good reason to avoid using "spool off". Please add it to your code and advise us of your findings/success.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 16:24 (08Feb05) UTC (aka "GMT" and "Zulu"),
@ 09:24 (08Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[akutty]

Thank you all for your help, I tried and it seems to work

Regards
Anil