using dbms_output 1

[jmannix]

I use dbms_output.put_line and dbms_output.put to spool output. However it seems that these funtions do not like beginning spaces, otherwise I can not use dbms_output.put_line(' This is a line of text to be centered" I have been able to insert a tab char before any spaces and get it to work. But this isn't exactly portable since a user can define how many characters a tab represents. Does any one have any suggestions on how to proceed text with spaces? Secondly any suggestions on formatting text such as bold or underline?

Thanks in advance!

 

[sem]

DBMS_OUTPUT has nothing to do with it. SQL*PLUS cuts them off. If you need a good advice - don't use this package for reporting at all. Read about sql*plus commands like PRINT, COLUMN, COLSEP, TTITLE, BTITLE, REPHEADER, REPFOOTER etc.
You can not change font attributes because sql*plus was desined for generating plain text (now the simpliest HTML also) output.

Regards, Dima

http://seminihin.narod.ru

 

[jmannix]

Thanks Dima,

I am familar with the SQL*Plus reporting but we seem to keep reverting to PS/SQL because of the complexity of the work that we do.

Thanks,

Joe

 

[sem]

Assign what you need to bind variable and print it. IMO dbms_output is too restictive (e.g. size of line) and has been primarilly designed for debugging only.

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Mannix,

Here is a function (Center_dbms) that should work for you. (I say "should" because it uses CHR(160), a non-printing character on most Oracle character sets.) If there are any problems using it, let me know:

create or replace function center_dbms (text in varchar, linesize in number) return varchar is
	strsize	number;
	half		number;
begin
	strsize	:= length(text);
	half		:= trunc((linesize-strsize)/2);
	return rpad(chr(160),half,chr(160))||
		text||lpad(' ',half+mod(linesize-strsize,2));
end;
/
set serveroutput on
begin
	dbms_output.put_line(center_dbms('This is a',100));
	dbms_output.put_line(center_dbms('sample',100));
	dbms_output.put_line(center_dbms('of centered',100));
	dbms_output.put_line(center_dbms('text',100));
	dbms_output.put_line(center_dbms('using',100));
	dbms_output.put_line(center_dbms('DBMS_OUTPUT.PUT_LINE',100));
end;
/

                                             This is a
                                               sample
                                            of centered
                                                text
                                               using
                                        DBMS_OUTPUT.PUT_LINE
[code]

If you exceed 2000 characters of output using dbms_output.put_line, I presume you are aware of how to increase the dbms_output buffer up to 1M:

dbms_output.enable(1000000)

An alternate (preferred) method for outputting flat text is to use PL/SQL's utl_file functionality that lets you READ, WRITE, and APPEND from/to a flat text file.

For both dbms_output.put_line and utl_file, you can embed control characters that can [b]BOLD[/b] and [i]italicize[/i] your output depending upon your target display/word processing environment.

Let us know if this was any help for you.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 17:20 (18Dec03) GMT, 10:20 (18Dec03) Mountain Time)

 

[jmannix]

SantaMufasa/Dave,

Thanks, I thought I had tried all the non-printing characters, but obviously not. Yes, I am aware of the buffer limit. I will also check out the utl_file function, I know it exists but since coming to the group I have picked up the use of dbms_output from the others.

Thanks and have a great holiday,

Joe (Mannix)

 

[jmannix]

Thanks to all who have previously posted suggestions/solutions. I have been using the non-printing character chr(160) as posted by Mufasa above. I have since learned that if you use format wrap with the SET serveroutput on then you can start your string with spaces.

SET serveroutput on format wrap

DBMS_OUTPUT.PUT_LINE(' This string is preceded by 5 spaces');

jmannix

 

[SantaMufasa]

Joe,

Please accept this Purple Star for providing my "Learn-something-new-every-day" item.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:18 (23Jun04) UTC (aka "GMT" and "Zulu"), 14:18 (23Jun04) Mountain Time)