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.

Answer 1

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.