SQL Plus Spool - I can't get rid of a trailing blank line in the output file
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

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

  1. #1
    Join Date
    Feb 2014
    Posts
    3

    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.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    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
    this space intentionally left blank

  3. #3
    Join Date
    Feb 2014
    Posts
    3
    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!

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    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.
    this space intentionally left blank

  5. #5
    Join Date
    Feb 2014
    Posts
    3
    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.

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    Cool

    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.

    PS: If you use LPAD/RPAD you yourself are adding blanks. Also, the last blank line may have been added by your editor program.
    Last edited by LKBrwn_DBA; 02-20-2014 at 10:29 AM.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

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



Click Here to Expand Forum to Full Width