DBMS_OUTPUT.PUT_LINE: Deciding how many tabs to put according to output length

Question

I have a procedure where a CURSOR c1 is declared. Then I want to output columns in each role c1 fetches.

FOR rec IN c1
    LOOP
      DBMS_OUTPUT.PUT_LINE (rec.branchno || CHR(9) || rec.street || CHR(9)
        || rec.city || CHR(9) || rec.postcode);
    END LOOP;

I'm now using CHR(9) to output exactly one tab. However, sometimes rec.city has more than 8 characters and thus a tab will push the following postcode right by one tab, as in the second line of output shown below .

B003    163 Main St.    Glasgow G11 9QX         
B007    16 Argyll St.   Aberdeen    AB2 3SU 
B005    22 Deer Rd.     London  SW1 4EH

Is there a way to make DBMS_OUTPUT adjust the tabs automatically? For example,

IF STRLEN(rec.city) < 8
    PUT ( CHR(9) || CHR(9) )
ELSE
    PUT ( CHR(9) )
END IF;

If no, what's the industry convention of generating such formatted output?

Thank you for your help.

Nice , i never thought this. But I dont think it is possible, only table row based results can be formatted ,that too, the client does that job. (SQL*Plus) — Maheswaran Ravisankar, Feb 20 '14 at 22:41

score 2 · Accepted Answer · answered Feb 20 '14 at 22:41

2

Using DBMS_OUTPUT to produce formatted reports is probably the wrong approach.

SQL*Plus is pretty good at producing fixed-width text reports. It's not obvious whether the stored procedure is adding some value (in which case you could have it return a sys_refcursor to SQL*Plus) or whether it would just make more sense to put the SQL statement in a SQL*Plus script along with appropriate column format commands to control the output of the data. Any number of other tools (SQL Developer, for example) support enough SQL*Plus commands to be able to generate a simple fixed width report as well.

answered Feb 20 '14 at 22:41

Justin Cave

227,342
24
367
384

Thank you for your answer. That's also what I was thinking about, simply relying on SQL Plus `column format`. The reason I'm asking this question is that I've just started learning `PROCEDURE` and thought *maybe* there's some magic with `DBMS_OUTPUT` methods. – goldfrapp04 Feb 20 '14 at 22:49
2

@goldfrapp04 - Could you? Sure. Something like `put( rpad( chr(9), ceil(length(rec.city)/8), chr(9)))` would work. It's just the wrong tool for the job. – Justin Cave Feb 20 '14 at 22:54

Sunanda Das · Answer 2 · 2016-07-22T16:12:12.437

2

I had the same problem when i used a CURSOR within a PROCEDURE.I used a similar code as following to solve my problem.Hope,this might help you.

 DBMS_OUTPUT.PUT_LINE(RPAD(rec.branchno,10)||RPAD(rec.street,20)||RPAD(rec.city,20)||RPAD(rec.city,20));

RPAD function actually pads the right-side of an expression to a specified length.The padding can be used using specified character but it is optional. If the expression to be padded has longer length than the specified length then only the portion that fits the specified length will be shown.

RPAD( expression, padded_length, padded_character(optional) );

RPAD('school',10);

OUTPUT:school

RPAD('school',6);

OUTPUT:school

RPAD('school',2);

OUTPUT:sc

RPAD('school',10,'1');

OUTPUT:school1111

edited Jul 22 '16 at 16:12

answered Jul 22 '16 at 15:26

Sunanda Das

19
3

1

You could improve this answer by adding a sentence to say what `RPAD` is and how it solves the problem. – Peter Hilton Jul 22 '16 at 15:36
thanks a lot for your valuable suggestion @PeterHilton – Sunanda Das Jul 22 '16 at 16:08

score 1 · Answer 3 · answered Feb 20 '14 at 23:01

1

This will give you what you want:

SELECT RPAD (BRANCHNO, 5) c1, 
       RPAD (STREET, 15) c2, 
       RPAD (CITY, 10) c3, 
       RPAD (POSTCODE, 10) c4
 FROM RECORD;

http://sqlfiddle.com/#!4/96e4a/8/3

answered Feb 20 '14 at 23:01

haschibaschi

2,734
23
31

score 0 · Answer 4 · edited Aug 24 '17 at 21:07

v_padlength number; 
v_sku_desc varchar2(30);
v_pad varchar2(1) := ' ';


if length(v_sku_desc) <= 11 then 
     v_padlength   := 35-length(v_sku_desc); 
 else
     v_padlength   := 30-length(v_sku_desc); 
 end if;                                             
 DBMS_OUTPUT.PUT_LINE (v_sku_desc||lpad(v_pad,v_padlength,' ')||chr(9)||'aligned');

set the <= 11 to needed length and adjust the 35 for the shorter fields

DBMS_OUTPUT.PUT_LINE: Deciding how many tabs to put according to output length

4 Answers4