2 Replies Latest reply: Feb 1, 2013 7:41 AM by 964844 RSS

    Spool query results in a file and number of rows in a different file

    964844
      Hi,

      I would like to export only the result of my query to a file. I am able to do this with sql plus, but I want to output the number of rows selected in a different 'log' file.

      For example:

      -------------------------------------
      select 'TEST' from DUAL;

      TEST

      +1 row selected.+

      -------------------------------------

      So 'TEST' should be displayed in the results file and '1 row selected' in a different log file.

      Is there any way to achieve this from sql*Plus.

      Thank you for your time

      Edrin,
        • 1. Re: Spool query results in a file and number of rows in a different file
          Frank Kulash
          Hi, Edrin,

          Welcome to the forum!

          Here's one way:
          COLUMN     row_cnt_col  NOPRINT     NEW_VALUE row_cnt
          
          --  You may or may not want the following settings.
          --  The 2-file output works with or without them.
          SET     FEEDBACK     OFF
          SET     VERIFY          OFF
          
          
          SPOOL     dept.txt
          
          SELECT     deptno, dname
          ,     COUNT (*) OVER ()     AS row_cnt_col
          FROM     scott.dept
          ;
          
          SPOOL     OFF
          
          
          SPOOL     dept.log
          
          SELECT     &row_cnt     AS total_rows_found
          FROM     dual;
          
          SPOOL     OFF
          The first SPOOL file, dept.txt, looks like this:
          `   DEPTNO DNAME
          ---------- --------------
                  10 ACCOUNTING
                  20 RESEARCH
                  30 SALES
                  40 OPERATIONS
          The second SPOOL file, dept.log, looks like this:
          TOTAL_ROWS_FOUND
          ----------------
                         4
          Using your example, only the first query has to change:
          SELECT     'TEST'               AS output
          ,     COUNT (*) OVER ()     AS row_cnt_col
          FROM     dual
          ;
          The second query, and all the set-up stuff is exactly the same.

          Edited by: Frank Kulash on Sep 27, 2012 9:38 AM
          • 2. Re: Spool query results in a file and number of rows in a different file
            964844
            Hi Frank,

            Thank you for the solution.

            It is OK like this.


            Edrin