CODEWITHSUNDEEP
oracle-databasesqlplus
SunyGirl
SunyGirl

Reputation: 419

Format query results in SQL*Plus

when I want to see one table with its records in oracle, the table is disorganized,I mean is not like my-sql to see each value of attribute exactly under the name of attribute!how can I solve it?

Upvotes: 1

Views: 3582

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

What tool are you using?

If you are using the command-line SQL*Plus, you may need to use formatting commands to specify how wide the display should be, i.e.

Ugly data

SQL> select empno, ename, job, mgr, hiredate, sal, comm, deptno
  2    from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     D
EPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- -----
-----
      7369 smith      CLERK           7902 17-DEC-80        800
   20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
   30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
   30
      7566 JONES      MANAGER         7839 02-APR-81       2975
   20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
   30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
   30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
   10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000
   20
      7839 KING       PRESIDENT            17-NOV-81       5000
   10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
   30
      7876 ADAMS      CLERK           7788 23-MAY-87       1110
   20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     D
EPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- -----
-----
      7900 SM0        CLERK           7698 03-DEC-81        950
   30
      7902 FORD       ANALYST         7566 03-DEC-81       3000
   20
      7934 MILLER     CLERK           7782 23-JAN-82       1300
   10
      1234 FOO

15 rows selected.

But if we specify that EMPNO and MGR should only have room for 5 digits and ENAME and JOB should be displayed in 10 characters, everything fits

SQL> column empno format 99999;
SQL> column ename format a10;
SQL> column job format a10;
SQL> column mgr format 99999;
SQL> /

 EMPNO ENAME      JOB           MGR HIREDATE         SAL       COMM     DEPTNO
------ ---------- ---------- ------ --------- ---------- ---------- ----------
  7369 smith      CLERK        7902 17-DEC-80        800                    20
  7499 ALLEN      SALESMAN     7698 20-FEB-81       1600        300         30
  7521 WARD       SALESMAN     7698 22-FEB-81       1250        500         30
  7566 JONES      MANAGER      7839 02-APR-81       2975                    20
  7654 MARTIN     SALESMAN     7698 28-SEP-81       1250       1400         30
  7698 BLAKE      MANAGER      7839 01-MAY-81       2850                    30
  7782 CLARK      MANAGER      7839 09-JUN-81       2450                    10
  7788 SCOTT      ANALYST      7566 19-APR-87       3000                    20
  7839 KING       PRESIDENT         17-NOV-81       5000                    10
  7844 TURNER     SALESMAN     7698 08-SEP-81       1500          0         30
  7876 ADAMS      CLERK        7788 23-MAY-87       1110                    20

 EMPNO ENAME      JOB           MGR HIREDATE         SAL       COMM     DEPTNO
------ ---------- ---------- ------ --------- ---------- ---------- ----------
  7900 SM0        CLERK        7698 03-DEC-81        950                    30
  7902 FORD       ANALYST      7566 03-DEC-81       3000                    20
  7934 MILLER     CLERK        7782 23-JAN-82       1300                    10
  1234 FOO

15 rows selected.

You can also do things like

SQL> set pagesize 100;
SQL> set linesize 120;

to control how frequently the column headers are displayed (the default is every 10 lines) and how wide each line should be.

Of course, if you're just a developer writing ad-hoc queries, this sort of formatting is a pain. For that sort of thing, you're much better off using something like Oracle's SQL Developer, a free PL/SQL IDE Oracle provides. The GUI automatically displays your results in a table that you can scroll through.

Upvotes: 6

Related Questions