0

I'm working with Oracle database using the HR sample schema. And this is the code that I wrote:

select d.department_id, location_id, l.street_address, l.state_province, l.city, c.country_name
 from departments d
natural join locations l natural join countries c

and the problem is that the result came out kind of bizarre.

here is the result:

how can I fix the display?

William Robertson
  • 15,273
  • 4
  • 38
  • 44
Saif Romdhane
  • 5
  • 4
  • 1
    Those columns combined are wider than your terminal window, so each row is spread over three lines. Can you make your window wider? If not (and you Know that not all of the columns can fit in a single line) what do YOU want to do about it? Truncate the columns? Have one columns per line on your screen? Etc, etc? – MatBailie Nov 07 '21 at 10:53
  • A modern alternative to sqlplus is sqlcl - no need to format the columns to get good screen sizing based on size of screen and your actual query results. Oracle.com/sqlcl – thatjeffsmith Nov 07 '21 at 11:12

1 Answers1

3

Adjust columns' format as e.g.

SQL> col street_address format a20
SQL> col state_province format a15
SQL> col city format a15
SQL> col country_name format a15

and then run the select statement.

Also, you might want to set line size to e.g.

SQL> set linesize 100

Example that shows what @MatBailie commented:

SQL> col dname format a5
SQL> select * From dept;

    DEPTNO DNAME LOC
---------- ----- -------------
        10 ACCOU NEW YORK
           NTING
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Risks the op not realising that longer values will therefor be truncate. Unless that's stated somewhere, such as in a comment ;) – MatBailie Nov 07 '21 at 10:58
  • 1
    No, @Mat, they won't be truncated. Oracle will move the rest of characters into a new line anyway (so it'll just look *ugly*, but no information will be lost). See the example. – Littlefoot Nov 07 '21 at 11:00