This discussion is archived
2 Replies Latest reply: Feb 1, 2013 5:41 AM by 964844 RSS

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

964844 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi Frank,

    Thank you for the solution.

    It is OK like this.


    Edrin

Legend

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