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: Valentin Minzatu <valentinminzatu_at_yahoo.com>
Date: Wed, 06 Jun 2007 16:19:59 -0000
Message-ID: <1181146799.831196.320370@z28g2000prd.googlegroups.com>


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? Received on Wed Jun 06 2007 - 11:19:59 CDT

Original text of this message

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