CODEWITHSUNDEEP
oracle-databaseoracle11gterminalsqlplus
PWL
PWL

Reputation: 466

SQLPlus varchar2 outputs whitespaces

When I query my table like below, the output of the column "NAME" is way too long. I recently changed the data type to VARCHAR2(150) instead of VARCHAR(150) to not save whitespaces. However, the output seems to include (some?) whitespaces anyway. Can anyone clearify what's going on here? Am I looking at whitespaces here, or is the problem only my terminal/console, or can SQLPLUS itself has something to do with it? Using Windows' terminal. 

SQL> SELECT * FROM SYS.O1_Orders;

        ID
----------
NAME
--------------------------------------------------------------------------------

  QUANTITY      PRICE
---------- ----------
         1
Cakes
        25        200

Upvotes: 2

Views: 2163

Answers (3)

Jan
Jan

Reputation: 2293

You can use SET NUM[WIDTH] {10 | n}
Sets the default width for displaying numbers.

Or use SET LONG {80 | n}
Sets maximum width (in bytes) for displaying BLOB, BFILE, CLOB, LONG, NCLOB and XMLType values.

Question remains if there is a SET option for displaying a fixed width far all VARCHAR2 fields. As for as I know and as noted above you can use COLUMN column_name FORMAT model to only format a specific(!) column of a specific table.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191570

varchar2 doesn't have blank padding when stored, but neither does varchar, and that would make no difference to how SQL*Plus displays it anyway.

SQL*Plus displays the column with the maximum width that it could contain, which it obtains from the query metadata before retrieving the actual result set. Otherwise it would have to fetch all the values, keep them somewhere, find the length of the longest value, and only then start to produce the output. And the output would vary with different executions, which probably wouldn't be useful for a report.

You have a few options. If you know the value will never be longer than a certain length (in which case why is the maximum size of the column greater than that?) then you could cast it to that, or tell SQL*Plus the column width to use with the column command:

column name format a20

The is much more on formatting in the documentation. But longer values will be wrapped if they do occur (or data will be lost if you cast/truncate in the query).

You can also put all the output on one line, which you haven't explicitly asked about but would make things look neater:

set linesize 200

... or some value wide enough for all your columns. And you can combine both settings, of course. The single line may still wrap within your terminal window, so you can make that wider if needed.

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231861

This is purely a SQL*Plus display issue. There is no difference (currently) between the VARCHAR and VARCHAR2 data type-- neither will space-pad the data you save. Only CHAR columns would do that.

In SQL*Plus, you can control how wide the name column is displayed

SQL> column name format a30;

for example, will tell SQL*Plus to use 30 columns to display the data in the name column. You can adjust that up or down depending on the actual length of your data, how much data you want to fit in a single row, etc. You'll have to either put this in a script along with the SQL statement or you'll need to replicate it in every session before you issue the statement or you'll need to add it to the login/ glogin.sql file.

SQL*Plus is a lovely tool if you are trying to generate fixed-width text reports. If you're just interactively running queries, however, it's not the most user-friendly solution-- adjusting the width of your text columns, dealing with output that is longer than your line, etc. gets old very quickly. If you're doing interactive development, a tool like SQL Developer with a proper GUI is much friendlier.

Upvotes: 3

Related Questions