5 Replies Latest reply: Feb 1, 2013 3:06 AM by Fran RSS

    spool data only

    473034
      When I spool query output data to a text file the SQL statement is also written to the text file. How do I prevent the SQL statement from being written so that I can get the data only? I executed SET HEADING OFF before the spool.
        • 1. Re: spool data only
          Frank Kulash
          Hi,

          Put all of your code (or at least the part from SPOOL to SPOOL OFF, inclusive) in a script.

          For example, if you create a file called dept.sql, like this:
          SET   HEADING    OFF
          
          SPOOL   dept.txt
          
          SELECT  *
          FROM    scott.dept;
          
          SPOOL   OFF
          and save it on the d:\scott\my_scripts folder, then, at the SQL> prompt, you can enter
          @d:scott\my_scripts\dept
          and SQL*Plus will execute all the code in the script, but the contents of the script will not appear in the SPOOL file.
          • 2. Re: spool data only
            765338
            Hi,

            I placed all the commands in file and then executed, but still i am facing the same issue.
            Below is what i am trying to do:

            spool on;
            set heading off;
            set feedback off;
            spool 'D:\new_file.ctl' create;
            @D:\commands.sql;
            spool off;

            In the commands.sql, i am calling a procedure. The spooled file which is getting created is as:

            @D:\commands.sql;
            Execution started.
            10 records executed
            20 records executed
            30 records executed
            40 records executed
            commited
            Execution ended.
            spool off;


            I don't want the @D:\commands.sql; and spool off; commands to be writen in my output file. What should i do for it??

            Thanks in advance.

            Edited by: user12957512 on Feb 8, 2011 10:27 PM
            • 3. Re: spool data only
              Frank Kulash
              Hi,
              Anjali wrote:
              Hi,

              I placed all the commands in file and then executed, but still i am facing the same issue.
              Below is what i am trying to do:

              spool on;
              set heading off;
              set feedback off;
              spool 'D:\new_file.ctl' create;
              @D:\commands.sql;
              spool off;
              Look at the example I posted: the SPOOL commands were in the script. You're apparantly typing them at a SQL> , which is why they're appearing in the output.
              In the commands.sql, i am calling a procedure. The spooled file which is getting created is as:

              @D:\commands.sql;
              Execution started.
              10 records executed
              20 records executed
              30 records executed
              40 records executed
              commited
              Execution ended.
              spool off;


              I don't want the @D:\commands.sql; and spool off; commands to be writen in my output file. What should i do for it??
              Do you want to suppress messages like "Execution started." and "10 records executed", too? They don't look familiar to me. Are they something that your procedure is printing, using dbms_output? If so, try
              SET   SERVEROUTPUT  OFF
              before running the scrpt (or in the script, but before you start SPOOLing). Remember to turn it ON again afterwards.
              • 4. Re: spool data only
                765338
                Hi,

                I placed everything (the spool commands) in the script and it is working perfectly. Thanks a lot for the help.

                Regards,
                Anjali
                • 5. Re: spool data only
                  Fran
                  Please close this thread marking it as answered. Keep clean this forum.