This discussion is archived
5 Replies Latest reply: Feb 1, 2013 1:06 AM by Fran RSS

spool data only

473034 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Please close this thread marking it as answered. Keep clean this forum.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points