My oracle SQL SELECT query returns on 1 column-CodePudding

My SQL SELECT query returns with the information being on 1 column and I'm not sure why it does that. When I do SELECT * it appears fine. Any clues?

SELECT customer_id, name
FROM customers;

SQL QUERY TABLE DESC

CodePudding user response：

Your Client Tool opens new line for it does not know the length of name column. If you take just first 40 chars from name it should give the expected result.

SELECT customer_id, SubStr(name, 1, 20) "NAME"
FROM customers;

CodePudding user response：

It is about a "long" name column datatype; here's a demo.

SQL> desc customers
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                               NOT NULL NUMBER(4)
 NAME                                               VARCHAR2(100)

SQL> select * from customers;

CUSTOMER_ID
-----------
NAME
--------------------------------------------------------------------------------
       7369
SMITH

       7499

CUSTOMER_ID
-----------
NAME
--------------------------------------------------------------------------------
ALLEN

       7521
WARD

CUSTOMER_ID
-----------
NAME
<snip>

What to do?

As you use SQL*Plus, one option is to modify page and line size:

SQL> set pagesize 100
SQL> set linesize 200
SQL> select * from customers;

CUSTOMER_ID NAME
----------- ----------------------------------------------------------------------------------------------------
       7369 SMITH
       7499 ALLEN
       7521 WARD
       7566 JONES
       7654 MARTIN
<snip>

Another option is to format that particular column:

SQL> col name format a20
SQL> select * from customers;

CUSTOMER_ID NAME
----------- --------------------
       7369 SMITH
       7499 ALLEN
       7521 WARD
       7566 JONES
       7654 MARTIN
<snip>

Or, as already suggested, fetch a substring.