Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sql*Plus formatting question

Re: sql*Plus formatting question

From: EdStevens <quetico_man_at_yahoo.com>
Date: Wed, 06 Jun 2007 09:38:23 -0700
Message-ID: <1181147903.889683.267350@a26g2000pre.googlegroups.com>


On Jun 6, 11:19 am, Valentin Minzatu <valentinminz..._at_yahoo.com> wrote:
> On Jun 6, 9:02 am, EdStevens <quetico_..._at_yahoo.com> wrote:
>
>
>
> > On Jun 5, 1:57 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > On Jun 5, 11:43 am, EdStevens <quetico_..._at_yahoo.com> wrote:
>
> > > > On Jun 5, 10:31 am, Valentin Minzatu <valentinminz..._at_yahoo.com>
> > > > wrote:
>
> > > > > On Jun 5, 11:27 am, EdStevens <quetico_..._at_yahoo.com> wrote:
>
> > > > > > Platform: Oracle EE 10.2 on HPUX
>
> > > > > > Given this snippet of code:
>
> > > > > > set pages 999
> > > > > > set lines 512
> > > > > > set trimspool on
> > > > > > --
> > > > > > spo plus.log
> > > > > > --
> > > > > > select name "Name",
> > > > > > owe||to_number(grade) "Grade",
> > > > > > from ...
>
> > > > > > where owe is varchar2(1)
> > > > > > grade is varchar2(3)
>
> > > > > > and grade is actually a 2-digit number, all of which are in the range
> > > > > > '01' to '09'
>
> > > > > > When I run the query, the length of the 'Grade' column comes out to 41
> > > > > > characters, left justified. I've tried bracketing the results ('['||
> > > > > > owe||to_number(grade)||']') to see if I'm getting a bunch of leading
> > > > > > or trailing spaces, but that has shown that the data returned is
> > > > > > exactly the expected two characters. Have tried enclosing the
> > > > > > to_number inside a to_char (since I'm concatenating the results to a
> > > > > > varchar), but to no effect. If I don't concatenate owe and grade
> > > > > > (selecting them as two separate columns) there is no problem.
>
> > > > > Have you tried: col "Grade" format a3?
>
> > > > Which, of course, works -- but begs the question of why it is needed
> > > > in this instance. ;-0- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > You cannot, of course, know both the question AND the answer lest the
> > > universe morph into something unknown and uncontrollable ... :)
>
> > > David Fitzjarrell
>
> > I think it has already. About the time I think I've got a grip on a
> > small piece of knowledge, something comes along that doesn't fit.
> > Much like Fyneman's description of how scientific theory is constantly
> > refined due to observation of new phenomenon.
>
> > So I experimented a bit more.
>
> > I found that if I
>
> > SELECT owe || grade "Grade"
> > FROM ....
>
> > (with no COLUMN format defintions)
>
> > the resulting "Grade" column is the expected 4 characters .. the sum
> > of the length of the two varchar2 columns. However, if I
>
> > SELECT owe || to_number(grade) "Grade"
> > FROM ...
>
> > The resulting column (header) is 41 characters.
>
> > And if I
>
> > SELECT '[' || owe || to_number(grade) || ']' "Grade"
> > FROM ...
>
> > The resulting column (header) is still 41 characters wide, even though
> > the returned data, counting the square bracket literals, is only 4
> > characters wide.
>
> > Am I missing the obvious, or is there some subtle effect going on here?- Hide quoted text -
>
> > - Show quoted text -
>
> Wild guess: when you convert to_number Oracle expects a 38 digits long
> number?

Ah ... and so allocates the column that wide because it has no way of knowing (yet) that the actual data returned will not require that much space. Makes sense. Received on Wed Jun 06 2007 - 11:38:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US