1 2 Previous Next 18 Replies Latest reply: Jan 28, 2009 8:41 AM by John Spencer RSS

    Spool without query

    Ash_
      Hi all,

      Could you please let me know the command so that when I spool output in sql it doesn't display the query and only the output.

      Thanks,
      Ash
        • 1. Re: Spool without query
          riedelme
          I assume you're working with SQL*PLUS.

          There are several SQL*PLUS commands you can use to avoid listing queries - you can look them up in the on-line docs for more information

          SET ECHO OFF - turns off display of SQL
          SET HEADING OFF - if desired, turns off the automatic headings
          SET LINESIZE 999 - sets # of characters displayed before breaking to the next line
          SET PAGESIZE 999 - sets # of lines before new page (form feed, headings)
          • 2. Re: Spool without query
            Boneist
            set echo off
            • 3. Re: Spool without query
              427828
              Try this

              set termout off
              set arraysize 5
              set echo off
              set verify off
              set heading off
              • 4. Re: Spool without query
                Centinul
                If you are using SQL*Plus try the following:
                SET FEEDBACK OFF
                Here is my test (test.sql):
                SET FEEDBACK OFF;
                SPOOL test.txt
                SELECT * from dual;
                SPOOL OFF;
                test:
                SQL> @V:\test.sql
                
                D
                -
                X
                SQL> host type test.txt
                
                D
                -
                X
                
                SQL>
                HTH!
                • 5. Re: Spool without query
                  Sven W.
                  It might also depend from how you start sql*plus. If it is from a command shell (e.g. batch script) you should turn on the silent option.
                  sqlplus -s user/password@database @myScript.sql
                  • 6. Re: Spool without query
                    isotope
                    Centinul wrote:
                    If you are using SQL*Plus try the following:
                    SET FEEDBACK OFF
                    It's not the "feedback off" state that's omitting the query in your log file. The "feedback off" turns off the display of the feedback messages like say, "<n> rows selected" or "Table created" etc.
                    I think "ECHO" was set to off in your sql*plus session due to which the query itself wasn't spooled to the log file.
                    test@ORA10G>
                    test@ORA10G>
                    test@ORA10G> -- what is "ECHO" set to ?
                    test@ORA10G> show echo
                    echo ON
                    test@ORA10G>
                    test@ORA10G> -- display the contents of c:\test.sql
                    test@ORA10G> host type c:\test.sql
                    SET FEEDBACK OFF;
                    SPOOL test.txt
                    SELECT * from dual;
                    SPOOL OFF;
                    
                    test@ORA10G>
                    test@ORA10G> -- so ECHO is ON... run the script now
                    test@ORA10G> @c:\test.sql
                    test@ORA10G> SET FEEDBACK OFF;
                    test@ORA10G> SPOOL test.txt
                    test@ORA10G> SELECT * from dual;
                    
                    D
                    -
                    X
                    test@ORA10G> SPOOL OFF;
                    test@ORA10G>
                    test@ORA10G> -- check the contents of c:\test.txt
                    test@ORA10G> host type c:\test.txt
                    test@ORA10G> SELECT * from dual;
                    
                    D
                    -
                    X
                    test@ORA10G> SPOOL OFF;
                    
                    test@ORA10G>
                    test@ORA10G> -- the query itself was spooled...
                    test@ORA10G>
                    test@ORA10G> -- now, set ECHO to OFF
                    test@ORA10G> set echo off
                    test@ORA10G>
                    test@ORA10G> show echo
                    echo OFF
                    test@ORA10G>
                    test@ORA10G> -- run the script c:\test.sql
                    test@ORA10G> @c:\test.sql
                    
                    D
                    -
                    X
                    test@ORA10G>
                    test@ORA10G> -- now check the contents of c:\test.txt
                    test@ORA10G> host type c:\test.txt
                    
                    D
                    -
                    X
                    
                    test@ORA10G>
                    test@ORA10G> -- the query was not spooled because ECHO was OFF
                    test@ORA10G>
                    test@ORA10G>
                    isotope
                    • 7. Re: Spool without query
                      Ash_
                      Thanks to all for the wonderful help.
                      One doubt though- set echo off doesn't seem to be working when I simply use it at command prompt and then spooling a file.
                      But when I save this and the query in a sql file and run the sql file from the command prompt then it works fine. Is there any reason for that?
                      • 8. Re: Spool without query
                        Ash_
                        Any further insight would be much appreciated. Thanks.
                        • 9. Re: Spool without query
                          Centinul
                          isotope --

                          Thanks for the catch, I went through and rechecked my login.sql file and it had ECHO OFF specified.

                          Thanks again!
                          • 10. Re: Spool without query
                            isotope
                            Ash_ wrote:
                            ...
                            One doubt though- set echo off doesn't seem to be working when I simply use it at command prompt and then spooling a file.
                            But when I save this and the query in a sql file and run the sql file from the command prompt then it works fine. Is there any reason for that?
                            Yes, that is the documented behavior of SET ECHO sqlplus command:

                            http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12040.htm#SQPUG075

                            <snip>
                            SET ECHO {ON | OFF}
                            Controls whether or not to echo commands in a script that is executed with @, @@ or START+.
                            ...
                            ECHO does not affect the display of commands you enter interactively+ or redirect to SQL*Plus from the operating system.
                            </snip>

                            HTH,
                            isotope
                            • 11. Re: Spool without query
                              Ash_
                              Hmm.. Got it.

                              So is there any way around to not to display the sql when we run it form prompt?
                              • 12. Re: Spool without query
                                isotope
                                Ash_ wrote:
                                ...

                                So is there any way around to not to display the sql when we run it form prompt?
                                Not as far as I know.

                                If you think about it, it doesn't really make any sense.
                                If you cannot see your sql statement at the interactive sqlplus prompt, you'll never know what went wrong with the syntax (if at all), besides finding it extremely difficult to see a history of past SQL statements you executed.

                                Agreed, suppressing of passwords while logging on to, say, Unix/Linux or Oracle etc. makes sense due to security reasons (aka "someone-watching-over-your-shoulder"). But suppressing SQL statements in an interactive session? That's stretching things a bit too far.

                                just my $0.02
                                isotope
                                • 13. Re: Spool without query
                                  John Spencer
                                  About the closest you can get is to type your sql statment without a terminator, then set spool on and run the query the set spool off. Like this:
                                  SQL> set pages 0 lines 120 pause off feedback off
                                  SQL> SELECT rownum, TO_CHAR(TO_DATE(rownum, 'J'), 'Jsp')
                                    2  FROM all_objects
                                    3  WHERE rownum <= 10
                                    4
                                  SQL> spool test.txt
                                  SQL> /
                                           1 One
                                           2 Two
                                           3 Three
                                           4 Four
                                           5 Five
                                           6 Six
                                           7 Seven
                                           8 Eight
                                           9 Nine
                                          10 Ten
                                  SQL> spool off;
                                  SQL> !cat test.txt
                                  SQL> /
                                           1 One
                                           2 Two
                                           3 Three
                                           4 Four
                                           5 Five
                                           6 Six
                                           7 Seven
                                           8 Eight
                                           9 Nine
                                          10 Ten
                                  SQL> spool off;
                                  Not perfect, but less to clean up in the output file. However, it only works for single statements.

                                  John
                                  • 14. Re: Spool without query
                                    Ash_
                                    Isotope,

                                    Yeah you are right mate! But just by stretching things a bit too far - we tend to learn things we never forget. ;)

                                    your $0.02 - whenever you want! ;)

                                    thanks, Ash.
                                    1 2 Previous Next