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

Solution 1:

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>selectempno,ename,job,mgr,hiredate,sal,comm,deptno2fromemp;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO------------------------------------------------------------------------------7369 smithCLERK7902 17-DEC-80800207499 ALLENSALESMAN7698 20-FEB-811600        300307521 WARDSALESMAN7698 22-FEB-811250        500307566 JONESMANAGER7839 02-APR-812975207654 MARTINSALESMAN7698 28-SEP-811250       1400307698 BLAKEMANAGER7839 01-MAY-812850307782 CLARKMANAGER7839 09-JUN-812450107788 SCOTTANALYST7566 19-APR-873000207839 KINGPRESIDENT17-NOV-815000107844 TURNERSALESMAN7698 08-SEP-811500          0307876 ADAMSCLERK7788 23-MAY-87111020EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO------------------------------------------------------------------------------7900 SM0CLERK7698 03-DEC-81950307902 FORDANALYST7566 03-DEC-813000207934 MILLERCLERK7782 23-JAN-821300101234 FOO15rowsselected.

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>columnempnoformat99999;SQL>columnenameformata10;SQL>columnjobformata10;SQL>columnmgrformat99999;SQL>/EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-----------------------------------------------------------------------7369 smithCLERK7902 17-DEC-80800207499 ALLENSALESMAN7698 20-FEB-811600        300307521 WARDSALESMAN7698 22-FEB-811250        500307566 JONESMANAGER7839 02-APR-812975                    207654 MARTINSALESMAN7698 28-SEP-811250       1400         307698 BLAKEMANAGER7839 01-MAY-812850                    307782 CLARKMANAGER7839 09-JUN-812450                    107788 SCOTTANALYST7566 19-APR-873000                    207839 KINGPRESIDENT17-NOV-815000                    107844 TURNERSALESMAN7698 08-SEP-811500          0307876 ADAMSCLERK7788 23-MAY-871110                    20EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-----------------------------------------------------------------------7900 SM0CLERK7698 03-DEC-81950307902 FORDANALYST7566 03-DEC-813000                    207934 MILLERCLERK7782 23-JAN-821300                    101234 FOO15rowsselected.

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.