SQL Plus Spool - I can't get rid of a trailing blank line in the output file

Hi, I need to create a text file that is fixed format of 131 characters. I am not able to find a way to remove the 1 trailing blank line at the end of the output file. This is what I have now. I have tried several other set commands in various combinations, but still get the trailing blank line.

set heading off
set feedback off
set verify off
set trimspool off
set term off
set colsep ''
set space 0
set pagesize 0
set linesize 131
spool D:\egis\MapidUpdate\C2MapidUpdate.txt

select LPAD(' ',24) as FILLER1, RPAD(LK.MAP_PAGE,4) as MAPIDFROMSERVICE, LPAD(' ',35) as FILLER2,
LPAD(C.SIT_ID,9,'0') as SITEID, LPAD(C.IS_SEQ_NUM,9,'0') as INSTSERV, LPAD(' ',50) as FILLER3
from GIS_C_lOCATION@ORAC2Rdv.world C
join ELECTRIC.SERVICE_TB S on C.SIT_ID_ISS_SEQ_NUM = S.CUSTOMER_ACCOUNT_NO
join electric.electric_service_point_tb e on s.electric_service_point_id = e.id
join ops$enom.SERVICE_POINT_MAP_PAGE lk on lk.elec_service_point_id = s.electric_service_point_id,
electric_land.state_tb st
where SDO_RELATE(e.location , st.extent, 'mask=anyinteract querytype=WINDOW' ) = 'TRUE'
and trim(LK.MAP_PAGE) != trim(c.psnh_map_num)
spool off;
/
quit;


The output file is attached.

Any help would be greatfully appreciated.

Try this, if it doesn't work, then you might want to wrap the columns with '|'||column_name||'|' column_name,
because its possible that the query is returning the blank line.

Code:

---

set term off
set heading off
set feedback off
set verify off
set trimspool on
set tab off
set space 0
set pagesize 0
set linesize 250
set term on

---

Thank you for the reply. I tried this but got the same result. I had already tried wrapping the columns and determined that the query does not return a blank line. On the set commands you posted, I noticed that you first set term off then later set term on. Which did you intend?

Thanks!

Doing set term off stops the output from going to the screen. This is usually done to keep the formatting commands from going to the screen. You should also make sure to do set echo off. Possibly set timing off. Otherwise I am not sure why you get the blank line. You can also try using an anonymous pl/sql block with dbms_output.put_line.

The dbms_output.put_line does not generate a blank line for the query. I also tried the spool script using this for the sql:
select 'xxxxxxxxxx' from dual; I also added set echo off and set timing off. But the result still has a blank line at the end.

Hi Donna11,

When you use comma's (,) between the columns, sql*plus will output fixed length columns and therefore "add" spaces even if you trim the db column.
If you want to output the values without spaces, you need to concatenate the columns with ||','|| as stated above.
:rolleyes:
PS: If you use LPAD/RPAD you yourself are adding blanks. Also, the last blank line may have been added by your editor program.
:p