Why am I getting this '#########' as output in the fields of a column?-CodePudding

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:

enter image description here

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.

enter image description here

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?

CodePudding user response：

Your 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.