extracting data into text file
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: extracting data into text file

  1. #1
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250

    Exclamation

    I have written a shell script to login to sqlplus, run sql and spool into a text file.
    Here is a part of the script:
    sqlplus $login < set feed off;
    set verify off;
    set head off;
    set echo off;
    set termout off;
    #set page 0;
    spool $pth/GLACCT.D$dt;
    select rownum||'|'||acct||'|'||acct_desc||'|'||entity||'|'||org_cd||'|'||prod_cd||'|'||state||'|'||invest_y ear||'|'||
    amt_class||'|'||curr_cd||'|'||amt from bar_stg_ss_acct_balance
    where aud_hdr_id in (select aud_hdr_id from bar_aud_hdr where to_char(load_dt,'mm/dd/yyyy')=to_char(sysdate,'mm/dd/yyyy'))
    /
    spool off;

    Now the problem is the text file also contains the sql and the command spool off besides the data . How do I eliminate that from getting spooled??

    hanx
    Kavita
    KN

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    If you don't want your sql echoed to the screen, you have to run it from a script file.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    Can you give me an example please?? I didn't follow the method

    Thanx
    Kavita
    KN

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    example - run this as a script and from sqlplus
    Code:
    spool tabs2.out
    set termout off
    select table_name from user_tables;
    spool off
    set termout on
    As a script, your output will look like:
    Code:
    ABC
    WAITSTAT_BEFORE    
    XYZ123                                                                          
    XyZ                                                                             
    xyz
    and from sqlplus, your output will look like:
    Code:
    SQL> set termout off
    SQL> select table_name from user_tables;
    ABC                                                                             
    WAITSTAT_BEFORE    
    XYZ123                                                                          
    XyZ                                                                             
    xyz                                                                             
    SQL> spool off
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    I am running it from a shell script so your example doesn't work it works otherwise when you login to sqlplus manually, Well finally it worked when I added -s option while invoking sqlplus , here's the script which worked

    sqlplus -s < set feed off;
    set head off;
    set echo off;
    spool /ex.dat;
    select * from user_tables;
    spool off;
    !

    Thanks
    KN

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Knarayan
    I am running it from a shell script so your example doesn't work
    That was the point. If you put your commands in a script file and then:
    sqlplus / @scriptname.sql
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250

    Talking

    Got you Jeff.

    Thanx
    KN

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