printing tabs in reports from a stored procedure?

[misapena]

Hi all,

I'm trying to print tabs between my dbms_output.put(field) to format the data nicely. Any clues?

Thanks,
Lisa

 

[sem]

sql*plus has quite rich formating capabilities throug COLUMN, BREAK and other commands. What is your specific task?

Regards, Dima

http://seminihin.narod.ru

 

[misapena]

I would like to have the values of my fields separated by tabs (or a specified number of white spaces) in a report. My code currently looks something like this:

for loop
dbms_output.put(field1)
dbms_output.put(field2)
if condition
dbms_output.put(field3)
else
dbms_output.put(error)
end if
dbms_output.put(field4)
end loop

So my report looks something like:

field1field2field3field4

I would like somthing like:

field1 field2 field3 field4

As a side question I was reading something about using chr9 as the ascii tab equivalent... is that the same in PL/SQL?

Thanks,
Lisa

 

[sem]

You may add chr(9):

for loop
dbms_output.put(field1);
dbms_output.put(chr(9));
dbms_output.put(field2);
dbms_output.put(chr(9));
if condition
dbms_output.put(field3);
else
dbms_output.put(error);
end if
dbms_output.put(chr(9));
dbms_output.put(field4);

end loop

Regards, Dima

http://seminihin.narod.ru

 

[misapena]

Is there something that I can add to the first line to make the two lines equivalent?

dbms_output.put(field1);

column field1 A20;

thanks,
-lisa

 

[sem]

They have NOTHING similar. The first puts some value to buffer, the second is unknown for me. Probably lacks of FORMAT key? Even if so it just declares column format.

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Lisa,

If you are trying to produce "tab-delimited" output from "dbms_output", then appending chr(9) between output fields works fine. If you want a "columnar" report from "dbms_output", then there is a slightly different method:

1) Decide how "wide" you want each output field.
2) Use the width specifications in LPAD and RPAD functions:

begin
	for r in (select * from s_emp) loop
		dbms_output.put_line
			( rpad(r.last_name||', '||r.first_name,22)
			||rpad(r.title,22)
			||lpad(to_char(r.salary,'999,990.99'),10));
	end loop;
end;
/
Garcia, Carmen        President               11,110.0
Ngao, LaDoris         VP, Operations           1,450.0
Nagayama, Midori      VP, Sales                1,400.0
Quick-To-See, Mark    VP, Finance              1,450.0
Ropeburn, Audry       VP, Administration       1,550.0
Urguhart, Molly       Warehouse Manager        1,200.0
Menchu, Roberta       Warehouse Manager        1,250.0
Biri, Ben             Warehouse Manager        1,100.0
Catchpole, Antoinette Warehouse Manager        1,300.0
Havel, Marta          Warehouse Manager        1,307.0
Magee, Colin          Sales Representative     1,400.0
Giljum, Henry         Sales Representative     1,490.0
Sedeghi, Yasmin       Sales Representative     1,515.0
Nguyen, Mai           Sales Representative     1,525.0
Dumas, Andre          Sales Representative     1,450.0
Maduro, Elena         Stock Clerk              1,400.0
Smith, George         Stock Clerk                940.0
Nozaki, Akira         Stock Clerk              1,200.0
Patel, Vikram         Stock Clerk                795.0
Newman, Chad          Stock Clerk                750.0
Markarian, Alexander  Stock Clerk                850.0
Chang, Eddie          Stock Clerk                800.0
Patel, Radha          Stock Clerk                795.0
Dancs, Bela           Stock Clerk                860.0
Schwartz, Sylvie      Stock Clerk              1,100.0

Remember, DBMS_OUTPUT.PUT_LINE (stupidly) left trims leading blanks from output lines, so if you want some leading "white space", then you need to use something like a CHR(9) to be the leading character of your output line.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:25 (12Dec03) GMT, 11:25 (12Dec03) Mountain Time)