Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2010
    Posts
    9

    Unanswered: Cannot set headsep

    hai I am new to this forum ,
    i built a csv file using spool command

    i am trying to remove '--' line that seperating my header and data when i am using headsep off its not removing the header.What i need is i dont want any seperation between header and data .here is the code im using to spool

    -------------------------

    SET linesize 2000
    SET PAGESIZE 30000
    SET PAGESIZE 2000
    SET NEWPAGE 0
    SET EMBEDDED OFF
    SET UNDERLINE OFF
    SET SPACE 2000
    SET LINESIZE 1000
    SET NUMWIDTH 50
    SET HEADSEP OFF
    SET ECHO OFF
    SET FEEDBACK OFF
    SET HEADING ON
    SET TERMOUT OFF
    SET TRIMSPOOL ON
    SET TRIMOUT ON
    SET COLSEP ,
    whenever sqlerror exit 8


    SPOOL C:\RESULTS.CSV;
    SELECT * FROM VENDOR;
    SPOOL OFF;
    EXIT;



    Thanks in advance

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You got it wrong. (SET System Variable Summary)

    HEADSEP is used to define character that will break heading (a column title):
    Code:
    SQL> set headsep '$'
    SQL> column ename heading 'employee$name'
    SQL> select ename from emp where rownum < 3;
    
    employee
    name
    ----------
    SMITH
    ALLEN
    
    SQL>
    If you want to modify a heading underline character, you'd use UNDERLINE SET parameter:
    Code:
    SQL> set underline '*'
    SQL> select ename from emp where rownum < 3;
    
    employee
    name
    **********
    SMITH
    ALLEN
    
    SQL>
    Too bad for you that it can't be set to whitespace.

    Therefore, you'll need to trick SQL*Plus: remove headings and create them by yourself:
    Code:
    SQL> set heading off
    SQL> column rn noprint
    SQL>
    SQL> select 1 rn, 'employee name' ename, 'job name' job from dual
      2  union
      3  select 2 rn, ename, job from emp where rownum < 3
      4  order by 1;
    
    employee name job name
    ALLEN         SALESMAN
    SMITH         CLERK
    
    SQL>

  3. #3
    Join Date
    Dec 2010
    Posts
    9
    But i had another problem the select statement gives output of a dynamic table so i dont know the columns prior to execution so i cannot use concatination..



    Is there a way that i spool the heading only and then the data only and later we concatinate it...if yes please tell me how to spool only header



    thanks for ur reply..

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there a way that i spool the heading only and then the data only and later we concatinate it.
    use a different client; which offers the control you desire
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Try this, Spool the headings:
    Code:
    spool c:\results_1.csv;
    ....
    spool off;


    Then spool the detail:
    Code:
    spool c:\results_2.csv;
    ....
    spool off;


    Then concatenate the files:
    Code:
    copy c:\results_1.csv+c:\results_2.csv c:\results.csv

  6. #6
    Join Date
    Dec 2010
    Posts
    9

    thanks for u both

    but how to generate only header without data??

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select column_name from user_tab_columns where table_name = '<tablename_variable>';
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Dec 2010
    Posts
    9

    I understand what u said

    But my problem is the table i m selecting is a dynamic table that generated from dynamic sql code so i dont know the names of ny columns .

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by nareshb, answering to Anacedent's message
    I understand what u said
    I don't think you do.

  10. #10
    Join Date
    Dec 2010
    Posts
    9

    I generated the column by query but could not avoid the seperartion line

    ldc_vendor, DT20101909 DT20101908 DT20101907 DT 20101906
    -----------, ------------ ,------------- ,------------ ,----------

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Sql> set pagesize 0
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Dec 2010
    Posts
    9

    This time im missing entire header

    i just want the line to be removed(whichs seperates the data and header)

    i tried set headsep off
    set underline off
    nothing helped me


    Thank you guys

  13. #13
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Post your existing code.

  14. #14
    Join Date
    Dec 2010
    Posts
    9

    My code

    SET linesize 2000
    SET PAGESIZE 30000
    SET PAGESIZE 0
    SET NEWPAGE 0
    SET EMBEDDED OFF
    SET UNDERLINE NONE
    SET SPACE 0
    SET LINESIZE 1000
    SET NUMWIDTH 50
    set Headsep OFF
    SET ECHO OFF
    SET FEEDBACK OFF
    SET HEADING ON
    SET TERMOUT OFF
    SET TRIMSPOOL ON
    SET TRIMOUT ON
    SET COLSEP ,
    whenever sqlerror exit 8

    spool C:\Results.csv;

    select * from vendor v ;

    spool off;
    exit;

  15. #15
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What is your Oracle version, anyway? SET UNDERLINE OFF appears to be working just fine for me:
    Code:
    C:\>sqlplus scott/tiger
    
    SQL*Plus: Release 10.2.0.1.0 - Production on Pet Pro 3 21:48:30 2010
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    
    SQL> set underline off
    SQL> select * from dept;
    
        DEPTNO DNAME          LOC
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL>
    By the way, what's the purpose of (re)setting
    Code:
    SET linesize 2000
    SET LINESIZE 1000
    and
    Code:
    SET PAGESIZE 30000
    SET PAGESIZE 0

Tags for this Thread

Posting Permissions

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