SQLPLUS Formatting

Julesie

Hi Guys,

I have what seems like a simple problem that has been driving me crazy for hours.

In a nutshell, is it possible to choose the alignment of the data outputted by SQLPLUS?

I know it defaults to right-justify for numeric columns and left-justify for text. I am looking for a way to override this and centre the data of some of columns.

The justify command appears to only affect the column header and not the data itself.

Any help very much appreciated.

Cheers,

Jules

Seth Brundle

isn't it something like
column column_name justify left
or
column column_name justify right
or
column column_name justify center

Its a year or two now since I played with Oracle!

Edit: just re-reading your post - was this the command you used which resulted in just the heading changing?

Julesie

Yup afraid so...

I have looked it up in a couple of reference manuals and it seems the Justify command is only meant to affect the header.

Seth Brundle

Why do you want to align it in SQL PLus anyway as its not really a reporting tool?

Julesie

I know...believe me i'd love to be using something more suitable. However the powers that be won't shell out for any such luxury so I am stuck trying to make the most of the available resources.

To be honest the alignment isn't absolutely critical it was more that it is bugging me that i can't find a way to override the default formatting.

I still think there must be a way.

Tom Dunne

Off the top of my head, I don't know.

But have a look here, you may find something :SQL*Plus quick reference

Seth Brundle

Get it to output an HTML report!
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a90842/ch8.htm

ste_wood

Julesie wrote: »

Hi Guys,

I have what seems like a simple problem that has been driving me crazy for hours.

In a nutshell, is it possible to choose the alignment of the data outputted by SQLPLUS?

I know it defaults to right-justify for numeric columns and left-justify for text. I am looking for a way to override this and centre the data of some of columns.

The justify command appears to only affect the column header and not the data itself.

Any help very much appreciated.

Cheers,

Jules

I know this post is very old but to provide a useful reply to all the googlers out there this is how you do this. I'm also aware that a to_number inside a to_char is bonkers but it demonstrates that the to_char is effecting an actual number not a char string made up of numbers. With rpad you need to be careful that the value you specify is big enough for the field so use the to_char method if the numbers are very large and vary alot.

SQL> select rpad(to_number(123456789),5,' ') AS TEST1 from dual;
TEST1

---

12345
SQL> select to_char(to_number(123456789)) AS TEST2 from dual;
TEST2

---

123456789

ste_wood

And another way..

SQL> select ltrim(to_number(123456789)) from dual;
LTRIM(TO_

---

123456789