Re: Are these "features" or bugs?

In article <dceh4sk4bjckgkmo6rtjp96vcfg6o14viq_at_4ax.com>,

        Walter Dnes <waltdnes_at_waltdnes.org> writes:
> 1) The PL/SQL output procedure DBMS_OUTPUT.PUT_LINE() appears to
> do an implicit LTRIM() on output. E.g. the string ' XX' is output
> as 'XX'. This really mucks up attempts to output reports in neat
> columns. I've resorted to putting leading dots at the beginning
> of the line to force proper alignment. Is there such an animal as
> a "non-breaking-space" ?
> BTW, lines consisting entirely of spaces don't get printed at all.
> Not even a line feed!

This is a bug in sqlplus, actually. But you can work around it. Just insert whatever you would like to give as output into a table and later on, select everything from that table. You could use a table like this:

CREATE TABLE output_table
( session_id NUMBER
, rownum NUMBER
, text VARCHAR2(100))
/

Then inside a PL/SQL block, instead of

DBMS_OUTPUT.PUT_LINE(variableA);

you use

v_rownum := v_rownum + 1;
INSERT INTO output_table
VALUES
(userenv('sessionid'), v_rownum, variableA);

You'd have to have a NUMBER variable v_rownum which is known throughout the whole PL/SQL block to keep track of the number of lines you have already inserted.

Then after the PL/SQL block executes, you do:

set heading off
set feedback off
set pagesize 0
set linesize 100
set trimspool off
(probably some other things you need to set off, too)

SELECT text
FROM output_table
WHERE session_id = userenv('sessionid') ORDER BY rownum
/

DELETE output_table
WHERE session_id = userenv('sessionid') /

The session_id column ensures that several people can use this table at the same time. Every connection to the database gets a unique session id.

> 2) TO_CHAR(<number>, '99') and any variant thereof appears to
> return a leading space in addition to the columns specified. Again,
> not knowing this creates mucho problems when trying to line up output
> in columns.

Use TO_CHAR(<number>, 'FM99')

Remco
--
rd1936: 1:15am up 3 days, 5:54, 8 users, load average: 1.34, 1.52, 1.80 Received on Tue Jan 11 2000 - 18:33:28 CST