printing a varchar field as wide as necessary

Hi, I have just started using Oracle (gulp), and I have the following scenario:

My database has a number of varchar columns. I want to print out a report, where these columns occupy only as much space as necessary. So I want to compute the maximum length over all rows, and use that to set the column width. Here's what I do:

Suppose there are fields lastname, firstname which are varchars. I define a variable maxlast, which we will use to store the maximum length of the lastname field:

variable maxlast number

begin
select max(length(lastname))
  into :maxlast
  from my_table;
end;
/

And then try this:

select
  substr(lastname,1,:maxlast),
  substr(firstname,1,25),
from my_table;

This doesn't work!!! Each lastname is padded out to the maximum length (40 in my case).

If you try a constant value, e.g.

select
  substr(lastname,1,3),
  substr(firstname,1,25),
from my_table;

you get the expected result: the lastnames are truncated to a field width of 3.

Now try this:

begin
  :maxlast := 3;
end;
/

If you run the first version again, you see what happens: the substr is being done - the lastname field is truncated to 3 - but, having been truncated, it is then padded out to the maximum width (40).

Why does it work with a literal 3, but not with my variable? And what can I do to fix it? (You can't use a column format statement, because the format can't be a variable!)

PS I'm using Oracle 7.3.

Thanks for any help,

Richard.

-- 
Richard Walker                            richard_at_cs.anu.edu.au
Department of Computer Science            Aust:  (06) 279 8194
The Australian National University        Intl: +61 6 279 8194
Canberra, ACT 0200, Australia             Fax:  +61 6 249 0010