goldfrapp04 goldfrapp04 - 4 months ago 9
SQL Question

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

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

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.

Comments