Re: printing a varchar field as wide as necessary

Richard Walker wrote:
>
> 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

The problem is that in the expression "substr(lastname,1,:maxlast)" :maxlast is a bind variable which, as far as the Oracle kernel "knows",may be varied from one execution to the next. The length ascribed to the expression is therefore the maximum possible. In SQL*Plus, however, you can use a SQL*Plus substitution string such as "substr(lastname,1,&maxlast)", which is substituted before the statement is parsed and, therefore, behaves like a literal. The problem is now to get the correct value into the substitution variable.

The usual way to do this is to define a SQL*Plus column as: column xxx new_value mxlast;

Then, using :maxlast as given in the PL/SQL block you defined above,

   select :maxlast xxx from dual;

and, finally:

 select substr(lastname,1,&mxlast),

        substr(firstname,1,25),
 from my_table;

Complicated? Yes!
Difficult? Not really.

Hope this helps.

Chrysalis. Received on Thu Apr 03 1997 - 00:00:00 CST