DBMS Output table columns 1

[CassidyHunt]

This is probably a really simple question but I haven't been able to find an answer by googling or going through the posts here.

Is there a way to make columns in the dbms_output.put_line so that you can line up text?

an example would be this:

id            description                   Cost
LHP001        This is a part number         $5.00
LHP002        This is another part number   $6.00

Currently I get this

id            description                   Cost
LHP001        This is a part number    $5.00
LHP002        This is another part number   $6.00

Thanks

Cassidy

 

[SantaMufasa]

Good question, Cassidy. Here is sample code that I use to get PL/SQL to produce columns similar to a SQL*Plus table query:
Section 1 -- Output from dbms_output.put_line:

                                                                                              Reserved
Tablespace                      Table                            Current      Est.      Est.     Space   Chained
Name            Owner           Name                              Blocks    Blocks   Savings      Used      Rows
--------------- --------------- ------------------------------ --------- --------- --------- --------- ---------
IPLAN           IPLNDICT_UA     TFAX_AX_TEST_CASE_RESULT             719       309       410         0         0
IPLAN           IPLNDICT_AT     TFAX_AX_ACTION_RESULT               2245      1774       471         0         0
IPLAN           IPLNDICT_AT     TFAX_AX_FILE_RESULT                  777       260       517         0       121
****************************************************************************************************************

...and here is are code excerpts that achieved the above formatting:

create or replace procedure reorg
	(MinFragPct	number
	,MinTabSize	number
	,MinFragSize	number)
is
	blksize		number;
	type ints is varray(10) of integer;
	x ints := ints(15,15,30,9,9,9,9,9); -- chr. widths of each of 8 output columns
	heading_needed	boolean := true;
	procedure prt (x varchar2) is
		begin
			dbms_output.put_line(x);
		end;
	procedure head_ck is
		begin
		    if heading_needed then
			heading_needed := false;
			prt(rpad(' ',x(1)) -- Heading line 1
				||' '||rpad(' ',x(2))
				||' '||rpad(' ',x(3))
				||' '||lpad(' ',x(4))
				||' '||lpad(' ',x(5))
				||' '||lpad(' ',x(6))
				||' '||lpad('Reserved',x(7))
				||' '||lpad(' ',x(8))
			   );
			prt(rpad('Tablespace',x(1)) -- Heading line 3
				||' '||rpad(' ',x(2))
				||' '||rpad('Table',x(3))
				||' '||lpad('Current',x(4))
				||' '||lpad('Est.',x(5))
				||' '||lpad('Est.',x(6))
				||' '||lpad('Space',x(7))
				||' '||lpad('Chained',x(8))
			   );
			prt(rpad('Name',x(1)) -- Heading Line 3, Column 1
				||' '||rpad('Owner',x(2))  -- Column 2
				||' '||rpad('Name',x(3))   -- Column 3
				||' '||lpad('Blocks',x(4)) -- Column 4
				||' '||lpad('Blocks',x(5)) -- Column 5
				||' '||lpad('Savings',x(6))-- Column 6
				||' '||lpad('Used',x(7))   -- Column 7
				||' '||lpad('Rows',x(8))   -- Column 8
			   );
			prt(rpad('-',x(1),'-') -- Ruling line Below heading
				||' '||rpad('-',x(2),'-')
				||' '||rpad('-',x(3),'-')
				||' '||rpad('-',x(4),'-')
				||' '||rpad('-',x(5),'-')
				||' '||rpad('-',x(6),'-')
				||' '||rpad('-',x(7),'-')
				||' '||rpad('-',x(8),'-')
			   );
		    end if;
		end;
begin
	select value into blksize
		from v$parameter
		where name = 'db_block_size';
	for r in (select * from swiss_cheese_table
		   where ((est_savings/current_blocks) * 100 > minfragpct
                           and (current_blocks*blksize) > (MinTabSize*(1024*1024))
		      or (est_savings*blksize) > (MinFragSize*(1024*1024)))
		   order by est_savings) loop
		head_ck;
			prt(rpad(r.tablespace_name,x(1))
				||' '||rpad(r.owner,x(2))
				||' '||rpad(r.table_name,x(3))
				||' '||lpad(r.current_blocks,x(4))
				||' '||lpad(r.est_blocks,x(5))
				||' '||lpad(r.est_savings,x(6))
				||' '||lpad(r.reserved_space_used,x(7))
				||' '||lpad(r.chained_rows,x(8))
			   );
		-- swiss_cheese.reorg(r.owner,r.table_name,r.current_blocks);
	end loop;
end;
/

Notice that I use "rpad" to left justify + pad on the right; "lpad" to right justify + pad on the left.

Also, *** very important *** execute the SQL*Plus command, "set serveroutput on format wrap", to cause dbms_output.put_line to output to the screen and output leading white space on the line.

Let us know, Cassidy, if this gives you the hints you need to format your columns.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

DBMS_OUTPUT is good for simple debugging, not for reporting. I suppose that your next question would be about buffer overflow errors

Regards, Dima

http://seminihin.narod.ru

 

[CassidyHunt]

Wow that is a lot more complex than I thought it would be. But it works well. Thanks.

I wondered why it was hard to find something on google about this.

I already learned the lesson the hard way about buffer size. Really I only have one more little question. Is there a preferred method for using code in a declare test type way and moving it to production in a procedure or function?

i.e.

declare

i integer;

begin

dbms_output.put_line tochar(i);

end;

I know that it probably is not correct code above but if I wanted to move that to a procedure do I just change the declare to the create or replace procedure myproc or is there another method more preferred to use?

Thanks
Cassidy

 

[SantaMufasa]

Cassidy,

I'm not certain what you are trying to ask/resolve in your question, above. Regardless of your question, since it is a completely different topic from your original posting, and since we don't charge extra if you start a new thread , may I suggest your creating a new thread for this question...it will be less confusing for Tek-Tipsters who may excavate this thread in the future.

Also, when you post a clarification of what you are trying to do, your corrected code would be:

set serveroutput on format wrap
declare
    i integer := 12345;
begin
    dbms_output.put_line(i);
end;
/

12345

PL/SQL procedure successfully completed.

Looking forward to your new thread.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.