CODEWITHSUNDEEP
sqlstringoracle-databasesqlplus
elbarna
elbarna

Reputation: 727

A simple question about Oracle database format column

On Oracle, to set the output more nice using sqlplus we did this

column author format a15
column editor format a15
column title format a15...

The question is simple: set column size for 3 column is easy, but if we had 7 or more column? Is possible to set a default column size for all columns?

Upvotes: 0

Views: 257

Answers (2)

Littlefoot
Littlefoot

Reputation: 142705

Well, it is possible - for numbers. You have 3 options available:

  • COLUMN FORMAT takes precedence over
  • SET NUMFORMAT which takes precedence over
  • SET NUMWIDTH

For example:

SQL> set numwidth 2
SQL> select sal from emp where rownum <= 2;

SAL
---
 ##
 ##

SQL> set numformat $9990d0
SQL> select sal from emp where rownum <= 2;

     SAL
--------
 $1000,0
 $1600,0

SQL> col sal format $999g990d00
SQL> select sal from emp where rownum <= 2;

         SAL
------------
   $1.000,00
   $1.600,00

SQL>

No such luck for other datatypes, though.

Upvotes: 1

GMB
GMB

Reputation: 222432

No. By default, the display length of a string column is the size of the column in the database. So if a column is defined, for example, as varchar2(100), then SQL*Plus reserves 100 characters in the resultset. Specific rules apply for datatypes such as dates, CLOB or else, but this is probably not what you are asking for here.

If you want to change that, it needs to be done on a column-per-column basis.

From the documentation:

The default width of datatype columns is the width of the column in the database.

You can change the displayed width of a datatype or DATE, by using the COLUMN command with a format model consisting of the letter A (for alphanumeric) followed by a number representing the width of the column in characters.

Within the COLUMN command, identify the column you want to format and the model you want to use.

Upvotes: 1

Related Questions