Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Location
    Dublin, Ireland
    Posts
    16

    Unanswered: Spool produces blank first line

    My spool is producing a blank first line.

    Here's the code:

    Code:
    set pagesize 10000   
    set verify off
    set linesize 1000
    set trimspool on
    set feedback off
    set termout  off
    set colsep ','
    -- Separate each column by a comma character (CSV output)
    set underline off
    set heading on
    set headsep ','
    set echo off
    set term off
    
    SPOOL C:\Kevin\Projects\TF\Imports\ImportA301Record.csv;
    SELECT keyindicator, stud_no, nin, date_brth, place_brth, date_death,
          gender, surname, firstname, middlename, title, noemail, inactive, novalidadd, add_import_id, maidenname
    FROM reintray.import_record WHERE import_label = 'ImportA301';
    spool off;
    Can anyone tell me how to make the heading the first line?

    Kevin

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Change the following:
    Code:
    set pagesize 0
    --...etc...
    -- remove the next stmnt:
    --set headsep ','
    --...etc...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Oct 2004
    Location
    Dublin, Ireland
    Posts
    16
    This eliminates the blank line alright but I need the column headings (separated by commas)

  4. #4
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    Can you select the column names from dual with one query, then run your query afterwards with LKBrwn_DBA's change?

    Sorry, not sure if there's a way to meet all your demands, but this would be a workaround.
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Attached is a script that will generate what you want.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Ooops, how do yo do attachments? Seems it did not upload -- let me try again.
    (Seems it did not like the .sql extension.
    Attached Files Attached Files
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Oct 2004
    Location
    Dublin, Ireland
    Posts
    16
    Thanks for the suggestions.

    In the end I kept it simple and went for

    Code:
    set newpage 0
    The only side effect is that I get an unwanted ' ' character before by first column heading. I can live with this.

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You can still do this:
    PHP Code:
    SPOOL C:\Kevin\Projects\TF\Imports\ImportA301Record.csv;  
    PROMPT keyindicatorstud_nonindate_brthplace_brthdate_deathgendersurnamefirstnamemiddlenametitlenoemailinactivenovalidaddadd_import_idmaidenname
    SELECT keyindicator
    , ...
      
    FROM reintray.import_record WHERE import_label 'ImportA301';
    spool off
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    Nov 2007
    Posts
    1

    Cool This is the Oracle solution for creating a flat file.

    Better late than never...space 0 removed space character

    SET NEWPAGE 0
    SET SPACE 0
    SET LINESIZE 80
    SET PAGESIZE 0
    SET ECHO OFF
    SET FEEDBACK OFF
    SET VERIFY OFF
    SET HEADING OFF
    SET MARKUP HTML OFF SPOOL OFF

    found on
    http://download.oracle.com/docs/cd/B...a90842/ch7.htm

Posting Permissions

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