9 Replies Latest reply on Nov 17, 2009 6:49 PM by Frank Kulash

    how to avoid displaying spool syntax

    684135
      Hi,

      I am trying to take output of following sql into a file through spool

      -- Following code is included in sql file which is called from batch file
      spool d:\DATACHECKING\ACTIVE_MEMBER_STATUS.csv
      select * from tablename where rownum < 101 order by last_update_date desc;
      spool off

      The output in the file comes like this

      ---------------------------------------------------
      sql> spool d:\DATACHECKING\ACTIVE_MEMBER_STATUS.csv
      record 1
      record 2
      sql>spool off
      -----------------------------------------------
      I do want to capture only records in the file and not the "spool command", which set command should I use?
        • 1. Re: how to avoid displaying spool syntax
          Florian W.
          Dear user!

          Do it like that:
          set termout off
          set verify off
          set echo off
          set feedback off
          
          spool d:\DATACHECKING\ACTIVE_MEMBER_STATUS.csv
          spool off
          Should be better.

          Yours sincerely

          Florian W.

          Edited by: Florian W. on 30.06.2009 16:11
          • 2. Re: how to avoid displaying spool syntax
            Toni Lazarin
            user10862179 wrote:
            Hi,

            I am trying to take output of following sql into a file through spool

            -- Following code is included in sql file which is called from batch file
            spool d:\DATACHECKING\ACTIVE_MEMBER_STATUS.csv
            select * from tablename where rownum < 101 order by last_update_date desc;
            spool off

            The output in the file comes like this

            ---------------------------------------------------
            sql> spool d:\DATACHECKING\ACTIVE_MEMBER_STATUS.csv
            record 1
            record 2
            sql>spool off
            -----------------------------------------------
            I do want to capture only records in the file and not the "spool command", which set command should I use?
            try with echo
            http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12040.htm#SQPUG075

            e.g. , your batch file would look something like this:
            set echo off
            spool d:\DATACHECKING\ACTIVE_MEMBER_STATUS.csv
            select * from tablename where rownum < 101  order by last_update_date desc;
            spool off
            • 3. Re: how to avoid displaying spool syntax
              684135
              My output is still same

              SQL> select * from (select * from ADDRESS_TYPE order by address_type desc) where rownum < 101;
              P Place of Residence
              O Office
              D Duty Station
              SQL> spool off

              I have used following set commands
              set ECHO off
              set termout off
              set verify off
              set feedback off
              SET HEADING off
              SET LINESIZE 9999
              set PROMPT off

              Pls. let me know. Thanks
              • 4. Re: how to avoid displaying spool syntax
                Toni Lazarin
                http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch7.htm#i1043544
                http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch5.htm#sthref999

                can you paste here content of that batch file and write how you execute it ?
                • 5. Re: how to avoid displaying spool syntax
                  Frank Kulash
                  Hi,

                  It looks like you are entering the query in response to a "SQL>" prompt. I don't think there's any way to keep the query itself from appearing in that case.

                  Put the query and the SPOOL commands into a .sql script.
                  You can put the SET commands that Florian gave you in the srcript, too (before the first SPOOL command, as I did below), or you can run them separately from the SQL> prompt: that part doesn't matter.
                  What is essential is that the last thing you do directly at a SQL> prompt before producing the output is
                  SQL> @my_script.sql
                  where my_script.sql is something like:
                  set termout off
                  set verify off
                  set echo off
                  set feedback off
                   
                  spool d:\DATACHECKING\ACTIVE_MEMBER_STATUS.csv
                  
                  select * from tablename where rownum < 101 order by last_update_date desc;
                  
                  spool off
                  It doesn't matter if you are typing the commands at the SQL> promnpt or if an OS-level batch file is doing it: you must not enter the query itself at the SQL> prompt if you don't want it in the SPOOL file.
                  • 6. Re: how to avoid displaying spool syntax
                    684135
                    Hi ,

                    I think I did exactly the same way.
                    My sql file contains -----

                    set ECHO off
                    set termout off
                    set verify off
                    set feedback off
                    SET HEADING off
                    SET LINESIZE 9999
                    set PROMPT off
                    spool d:\DATACHECKING\ADDRESS_TYPE.csv
                    select * from (select * from ADDRESS_TYPE order by address_type desc) where rownum < 101;
                    spool off
                    --------------------------------

                    Then I am calling this sql file from .bat file with following synatx--->

                    sqlplus user/password@migrnhii < Datascript_oracle_old.sql


                    Pls. check at your end and let me know.
                    Thanks
                    • 7. Re: how to avoid displaying spool syntax
                      Laurent Schneider
                      1)
                      sqlplus -s user/password@migrnhii < Datascript_oracle_old.sql
                      or

                      2)
                      sqlplus -s user/password@migrnhii @Datascript_oracle_old.sql
                      be sure to put a quit at the end of your script in case 2
                      • 8. Re: how to avoid displaying spool syntax
                        716065
                        Where to save the .sql file to run it using @command
                        • 9. Re: how to avoid displaying spool syntax
                          Frank Kulash
                          Hi,
                          user8703472 wrote:
                          Where to save the .sql file to run it using @command
                          Put it anywhere you want, and reference it with the full path name, like this:
                          SQL>  @d:\orders\sql_scripts\my_script.sql
                          The default location (that is, where the system will look if you give only a simple file name, like my_script.sql) can be set. Look up [SQLPATH in the SQL*Plus manual|http://download.oracle.com/docs/cd/B28359_01/server.111/b31189/ch2.htm#sthref63].