Home » SQL & PL/SQL » SQL & PL/SQL » SQLPlus spool output
SQLPlus spool output [message #437857] Wed, 06 January 2010 05:04 Go to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Hi,

Usually when i run a query in sqlplus to retrieve data from a table that has more than 5 columns the output is usually unreadable. The only way of avoiding this problem is by either selecting fewer columns to view or spooling the output to file.

I think the spool option works well but the problem with it is i dont see the output unless i issue the spool off command. Is there some clever trick to make sure that the output is shown on the file as soon as i press enter?

As i "tail" the file on a separate window, it would be usefull if i can see the output straight away as soon as i press "Enter".

Thanks
Re: SQLPlus spool output [message #437862 is a reply to message #437857] Wed, 06 January 2010 05:14 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> set line 1000
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 sriram's   CLERK           7902 17-DEC-80        800         -1         20
      7499 sriram's   SALESMAN        7698 20-FEB-81       1600        300         30
      7521 sriram's   SALESMAN        7698 22-FEB-81       1250        500         30
      7566 sriram's   MANAGER         7839 02-APR-81       2975         -1         20
      7654 sriram's   SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 sriram's   MANAGER         7839 01-MAY-81       2850         -1         30
      7782 sriram's   MANAGER         7839 09-JUN-81       2450         -1         10
      7788 sriram's   ANALYST         7566 19-APR-87       3000         -1         20
      7839 sriram's   PRESIDENT            17-NOV-81       5000         -1         10
      7844 sriram's   SALESMAN        7698 08-SEP-81       1500          0         30
      7876 sriram's   CLERK           7788 23-MAY-87       1100         -1         20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 sriram's   CLERK           7698 03-DEC-81        950         -1         30
      7902 sriram's   ANALYST         7566 03-DEC-81       3000         -1         20
      7934 sriram's   CLERK           7782 23-JAN-82       1300         -1         10

14 rows selected.

SQL> set pages 1000
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 sriram's   CLERK           7902 17-DEC-80        800         -1         20
      7499 sriram's   SALESMAN        7698 20-FEB-81       1600        300         30
      7521 sriram's   SALESMAN        7698 22-FEB-81       1250        500         30
      7566 sriram's   MANAGER         7839 02-APR-81       2975         -1         20
      7654 sriram's   SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 sriram's   MANAGER         7839 01-MAY-81       2850         -1         30
      7782 sriram's   MANAGER         7839 09-JUN-81       2450         -1         10
      7788 sriram's   ANALYST         7566 19-APR-87       3000         -1         20
      7839 sriram's   PRESIDENT            17-NOV-81       5000         -1         10
      7844 sriram's   SALESMAN        7698 08-SEP-81       1500          0         30
      7876 sriram's   CLERK           7788 23-MAY-87       1100         -1         20
      7900 sriram's   CLERK           7698 03-DEC-81        950         -1         30
      7902 sriram's   ANALYST         7566 03-DEC-81       3000         -1         20
      7934 sriram's   CLERK           7782 23-JAN-82       1300         -1         10

14 rows selected.

SQL> spool c:\sri\emp_list.txt
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 sriram's   CLERK           7902 17-DEC-80        800         -1         20
      7499 sriram's   SALESMAN        7698 20-FEB-81       1600        300         30
      7521 sriram's   SALESMAN        7698 22-FEB-81       1250        500         30
      7566 sriram's   MANAGER         7839 02-APR-81       2975         -1         20
      7654 sriram's   SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 sriram's   MANAGER         7839 01-MAY-81       2850         -1         30
      7782 sriram's   MANAGER         7839 09-JUN-81       2450         -1         10
      7788 sriram's   ANALYST         7566 19-APR-87       3000         -1         20
      7839 sriram's   PRESIDENT            17-NOV-81       5000         -1         10
      7844 sriram's   SALESMAN        7698 08-SEP-81       1500          0         30
      7876 sriram's   CLERK           7788 23-MAY-87       1100         -1         20
      7900 sriram's   CLERK           7698 03-DEC-81        950         -1         30
      7902 sriram's   ANALYST         7566 03-DEC-81       3000         -1         20
      7934 sriram's   CLERK           7782 23-JAN-82       1300         -1         10

14 rows selected.

SQL> spool off
SQL>





What`s hurting you here ?

sriram Smile
Re: SQLPlus spool output [message #437878 is a reply to message #437857] Wed, 06 January 2010 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You can format your columns so it fits your window, see:
SQL*PlusĀ® User's Guide and Reference
Chapter 6 Formatting SQL*Plus Reports

2/ In Windows there a exclusive access to the file, so you can see only when it is closed
On Unix, file is flushed per "page", so you can tail -f the output file but can only see when each "page" is over or file is closed.

Regards
Michel
Re: SQLPlus spool output [message #437879 is a reply to message #437878] Wed, 06 January 2010 05:53 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Yes I miss read the post

sriram Smile
Previous Topic: Doubt (merged)
Next Topic: Need help for query
Goto Forum:
  


Current Time: Tue Sep 27 16:06:48 CDT 2016

Total time taken to generate the page: 0.05870 seconds