spool file without blank line
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: spool file without blank line

  1. #1
    Join Date
    Feb 2004
    Location
    NYC
    Posts
    39

    spool file without blank line

    Hi,

    I want to spool a query result without heading, title and the blank line in a text file, but no matter what I tried,use "space 0, newspace 0", there is alwasy a blank line between the content and the top border in the spool file, can anyone have good idea to get rid of it?
    Thank you!

    What I wrote -
    "set heading off;
    set space 0;
    spool result.txt;
    select id, name from emp;
    spool off;
    set space 1;
    set heading on;
    "

    -Betty

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    set pagesize 0
    set trimspool on

  3. #3
    Join Date
    Feb 2004
    Location
    NYC
    Posts
    39

    Great !

    Great !
    Thank you! It works!

  4. #4
    Join Date
    Sep 2012
    Posts
    1

    Spool the file without blank line before the header and return record count in EOF

    I am using below code:

    SET FEEDBACK OFF
    SET NEWPAGE NONE
    set trimspool on
    SET ECHO OFF
    SET HEADING OFF
    set verify off
    set feed off
    SET LINESIZE 140

    COLUMN file_name NOPRINT new_value l_file_name
    SELECT 'CONCUR' || to_char(sysdate + 7,'yyyymmdd') file_name from dual;

    spool D:\HoneywellInterfaces\Canada_Payroll\&l_file_name..txt

    COLUMN current_date NOPRINT NEW_VALUE report_date
    SELECT TO_CHAR(SYSDATE + 7,'YYYYMMDD') current_date FROM DUAL;

    COLUMN current_date1 NOPRINT NEW_VALUE report_date1
    SELECT TO_CHAR(0, 'FM00000000') current_date1 FROM DUAL;


    REPH LEFT '1' report_date 'XXXN'
    REPF LEFT '9'report_date1

    I am getting 2 blank space adove the '120120922XXXN' header line.I dont want 2 blank space at the strating of the file.And I want to return in footer '900000000' total record count.
    EG:My output has 8 line excluding header footer.So i want to return like ''900000008'

    Please help me in these 2 issues.

    My Output is:



    120120922XXXN
    2C04799 0000000 EXP 000000 0000004500 Expense Reimbursement-No Tax
    2C07399 0000000 EXP 000000 0000026707 Expense Reimbursement-No Tax
    2C00777 0000000 EXP 000000 0000004264 Expense Reimbursement-No Tax
    2C02491 0000000 EXP 000000 0000051888 Expense Reimbursement-No Tax
    2C01600 0000000 EXP 000000 0000024440 Expense Reimbursement-No Tax
    2C24912 0000000 EXP 000000 0000032571 Expense Reimbursement-No Tax
    2C02449 0000000 EXP 000000 0000099980 Expense Reimbursement-No Tax
    2C28054 0000000 EXP 000000 0000024544 Expense Reimbursement-No Tax
    900000000

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