Oracle SQL Plus not all table columns displayed

Question

I've created this table :

CREATE TABLE PRODUCT (
P_CODE                        CHAR(8),
P_DESCRIPT                    CHAR(200),
P_INDATE                      CHAR(20),
P_ONHAND                      INTEGER,
P_MIN                         INTEGER,
P_PRICE                       NUMBER,
P_DISCOUNT                    NUMBER,
V_CODE                        CHAR(5)
);

And I've inserted the following data in the table:

INSERT INTO PRODUCT VALUES ('11QER/31','Power painter, 15 psi, 3-nozzle','12/2/96',8,5,109.99,0.00,25595);
INSERT INTO PRODUCT VALUES ('13-Q2/P2','7.25-in. pwr. Saw blade','11/12/96',32,15,14.99,0.05,21344);
INSERT INTO PRODUCT VALUES ('14-Q1/L3','9.00-in. pwr. Saw blade','11/12/96',18,12,17.49,0.00,21344);
INSERT INTO PRODUCT VALUES ('156-QQ2','Hrd. Cloth, 1/4-in., 2x50','8/14/96',15,8,39.95,0.00,23119);
INSERT INTO PRODUCT VALUES ('1558-QW1','Hrd. Cloth, 1/2-in., 3x50','8/14/96',23,5,43.99,0.00,23119);
INSERT INTO PRODUCT VALUES ('2232/QTY','B&D jigsaw, 12-in, blade','10/29/96',8,5,109.92,0.05,24288);
INSERT INTO PRODUCT VALUES ('2232/QWE','B&D jigsaw, 8-in, blade','9/23/96',6,5,99.87,0.05,24288);
INSERT INTO PRODUCT VALUES ('2238/QPD','B&D cordless drill, 1/2-in.','10/19/96',12,5,38.95,0.05,25595);
INSERT INTO PRODUCT VALUES ('23109-HB','Claw hammer','11/19/96',23,10,9.95,0.10,21225);
INSERT INTO PRODUCT VALUES ('23114-AA','Sledge hammer, 12 lb.','12/1/96',8,5,14.40,0.05,null);
INSERT INTO PRODUCT VALUES ('54778-2T','Rat-tail file, 1/8-in. fine','6/14/96',43,20,4.99,0.00,'21344');
INSERT INTO PRODUCT VALUES ('89-WRE-Q','Hicut chain saw, 16 in.','7/6/96',11,5,256.99,0.05,'24288');
INSERT INTO PRODUCT VALUES ('PVC23DAT','PVC pipe, 3.5-in., 8-ft','12/19/96',188,75,5.87,0.00,null);
INSERT INTO PRODUCT VALUES ('SM-18277','1.25-in. metal screw, 25','11/28/96',172,75,6.99,0.00,'21225');
INSERT INTO PRODUCT VALUES ('SW-23116','2.5-in. wd. screw, 50','9/23/96',237,100,8.45,0.00,'21231');
INSERT INTO PRODUCT VALUES ('WR3/TT3','Steel matting, 4''x8''x1/6", .5" mesh','11/16/96',18,5,119.95,0.10,'25595');
INSERT INTO PRODUCT VALUES ('11QER/31','Power painter, 15 psi, 3-nozzle','12/2/96',8,5,109.99,0.00,25595);
INSERT INTO PRODUCT VALUES ('13-Q2/P2','7.25-in. pwr. Saw blade','11/12/96',32,15,14.99,0.05,21344);
INSERT INTO PRODUCT VALUES ('14-Q1/L3','9.00-in. pwr. Saw blade','11/12/96',18,12,17.49,0.00,21344);
INSERT INTO PRODUCT VALUES ('156-QQ2','Hrd. Cloth, 1/4-in., 2x50','8/14/96',15,8,39.95,0.00,23119);
INSERT INTO PRODUCT VALUES ('1558-QW1','Hrd. Cloth, 1/2-in., 3x50','8/14/96',23,5,43.99,0.00,23119);
INSERT INTO PRODUCT VALUES ('2232/QTY','B&D jigsaw, 12-in, blade','10/29/96',8,5,109.92,0.05,24288);
INSERT INTO PRODUCT VALUES ('2232/QWE','B&D jigsaw, 8-in, blade','9/23/96',6,5,99.87,0.05,24288);
INSERT INTO PRODUCT VALUES ('2238/QPD','B&D cordless drill, 1/2-in.','10/19/96',12,5,38.95,0.05,25595);
INSERT INTO PRODUCT VALUES ('23109-HB','Claw hammer','11/19/96',23,10,9.95,0.10,21225);
INSERT INTO PRODUCT VALUES ('23114-AA','Sledge hammer, 12 lb.','12/1/96',8,5,14.40,0.05,null);
INSERT INTO PRODUCT VALUES ('54778-2T','Rat-tail file, 1/8-in. fine','6/14/96',43,20,4.99,0.00,'21344');
INSERT INTO PRODUCT VALUES ('89-WRE-Q','Hicut chain saw, 16 in.','7/6/96',11,5,256.99,0.05,'24288');
INSERT INTO PRODUCT VALUES ('PVC23DAT','PVC pipe, 3.5-in., 8-ft','12/19/96',188,75,5.87,0.00,null);
INSERT INTO PRODUCT VALUES ('SM-18277','1.25-in. metal screw, 25','11/28/96',172,75,6.99,0.00,'21225');
INSERT INTO PRODUCT VALUES ('SW-23116','2.5-in. wd. screw, 50','9/23/96',237,100,8.45,0.00,'21231');
INSERT INTO PRODUCT VALUES ('WR3/TT3','Steel matting, 4''x8''x1/6", .5" mesh','11/16/96',18,5,119.95,0.10,'25595');

now when I do the following:

SELECT * FROM PRODUCT;

only the first 4 columns is returned!

I tried setting the following:

SET WRAP ON
SET TERMOUT OFF
SET VERIFY OFF
SET TRIMSPOOL OFF
SET LINESIZE 2000
SET LONG 200000
SET PAGES 0

And now only two columns appear.

Please help.

score 0 · Answer 1 · 2017-05-21T14:32:13.037

If the column should be CHAR(200), then you should leave it alone - don't change it to CHAR(50) only because you can't display it properly.

Instead, you are probably OK by showing only the first 50 characters when you query the table. You could do that in the query itself, like this:

select p_code, substr(p_descript, 1, 50) as p_descript, p_indate, ...

Alternatively, you can tell SQL Plus to display the column in 50-character width, with a SQL Plus command - you were trying to do that, you just didn't find the right command. It is the column command; specifically in this case:

column p_descript format a50

(note this is NOT followed by semicolon ; since it is not a SQL command) This will wrap the values that are actually longer than 50 characters. If instead you want them to be truncated, issue an additional SQL Plus command:

set wrap off

Oracle SQL Plus not all table columns displayed

1 Answers1