I am using ORACLE SQL* PLUS XE 11g. I made a Vehicles table with:
CREATE TABLE Vehicles ( vehicle_id NUMBER PRIMARY KEY, v_type VARCHAR2(50) NOT NULL, v_price DECIMAL(10,2) NOT NULL );
And added data with:
INSERT ALL INTO Vehicles VALUES (1, 'Car', 'Limousine', 67113480) INTO Vehicles VALUES (2, 'Car', 'Toyota Camry', 19000000)
Note: Original code consists of many columns
Now to print data in this table on terminal, I used
SELECT * FROM Vehicles;
This printed the table in a unordered format. I hope you know what I mean.
Something like this:
To solve this issue, I used
COLUMN vehicle_id FORMAT A5 (To shrink the size of column while displaying). This specific command caused the output to turn out like this for the column I formatted.
I tried to fix this issue by again using this query
COLUMN vehicle_id FORMAT A50(To expand the size of column while displaying) but nothing changes and I still have the same output. Do anyone knows how do I solve this?
vehicle_id column is numeric, so you should use a numeric column model like
99999 rather than a string model like
A5. But the column will still be displayed using the width of the column heading, so you can change that too, e.g.:
COLUMN vehicle_id FORMAT 99999 HEADING V_ID
However, that will still be six characters wide, not five, because it allows an extra digit for a possible minus sign. You may know the value won’t ever be negative, but SQL*Plus doesn’t.
The behaviour you are seeing is explained in the documentation:
If a number format model does not contain the MI, S or PR format elements, negative return values automatically contain a leading negative sign and positive values automatically contain a leading space.
SQL*Plus formats NUMBER data right-justified. A NUMBER column’s width equals the width of the heading or the width of the FORMAT plus one space for the sign, whichever is greater.
If a value cannot fit in the column, SQL*Plus displays pound signs (#) instead of the number.