suppress display
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: suppress display

  1. #1
    Join Date
    Mar 2004
    Posts
    10

    suppress display

    Hello, Can somebody please tell how to suppress the query result in sql*plus? I need something to work like unix re-direction operator. i.e I want to issue a select statement and send the results (rows) to a file. They should NOT be displayed on screen. I am using spool to do so, but need help with suppressing the results.

    I used the following
    set verify off
    set heading off
    set linesize 6000
    set feedback off
    set termout off
    set term off
    spool out1.txt

    I used sqlplus -s option. It suppressed the screen display but not the query result. The records displayed as usual. Is there anything else that I should add to the above? Thanks again.

  2. #2
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    TERMOUT is the answer.

    From the doc:
    SET TERM[OUT] {ON|OFF}
    Controls the display of output generated by commands executed from a script. OFF suppresses the display so that you can spool output from a script without seeing the output on the screen. ON displays the output. TERMOUT OFF does not affect output from commands you enter interactively.

  3. #3
    Join Date
    Mar 2004
    Posts
    10
    I used this sequence of commands :

    set verify off;
    set term off; //also with set termout off tried both seperately
    set linesize 6000;
    set feedback off;
    set pagesize 0;
    set echo off;
    set sqlprompt '';
    set trimspool on;
    set termout off;
    spool out_C1.txt

    It STILL displays the rows ... I don't want sql*plus scroll 1/2 million records. Basically, I want to spool the records to a text file ... Is this the right way?

    Thanks

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    What's the problem?

    Code:
    [marist89@jake marist89]$ more noout.sql
    set termout off
    set echo off
    spool foo.out
    select * from t1;
    spool off
    quit
    [marist89@jake marist89]$ sqlplus -s a/a@lx920.us @noout.sql
    [marist89@jake marist89]$  more foo.out
    
            F1 F2
    ---------- --------------------
             1 A
             2 B
    
    Elapsed: 00:00:00.01
    [marist89@jake marist89]$
    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
    Jan 2001
    Posts
    3,131
    TERMOUT needs to be in the script to work.

    Here is a script called yo_momma.sql ....


    set termout off;
    spool d:\your_momma.txt;
    select * from emp;
    spool off;


    SQL> @yo_momma.sql
    SQL>

    It creates a .txt file with all the rows in it, test it and see.

    MH
    I remember when this place was cool.

  6. #6
    Join Date
    Mar 2004
    Posts
    10
    Thanks for giving "your_momma.txt"

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Actually I am wrong.
    SET TERMOUT will suppress the display on a called script.

    SQL> @D:\yo_momma

    So make sure you turn TERMOUT back on at the end of your script.

    MH
    I remember when this place was cool.

  8. #8
    Join Date
    Mar 2004
    Posts
    10
    Thanks for giving "your_momma.txt"

  9. #9
    Join Date
    Mar 2004
    Posts
    10
    Sorry, that was a browser problem ... I tried Marist's script and it worked well. Mr.Hanky your script seems to be the same. Thanks for the tip. BUT, please note that if I type the commands on plain sql*plus window and spool, it still displays output. I don't know why TERMOUT is not working here.

    Thanks both.

  10. #10
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    Once again, from the Doc:

    "TERMOUT OFF does not affect output from commands you enter interactively"

    You are right...it WILL not work, because that is how Oracle has designed it !

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