SQL dbms_output.put_line formatting

Question

I would like it to be like when you do a select query from a table(like columns). I added tab characters for the column names but doing so between the names and statuses, offsets some of the demand statuses because the name is too long. When dbms outputs, it does this:

Product Name            Demand Status
------------            -------------
Pro Ski Pole High Demand
Water Bottle High Demand
Intermediate Ski Pole Low Demand
Tire Pump Low Demand
Bicycle Helmet Low Demand
Beginner's Ski Boot Low Demand
Intermediate Ski Boot High Demand
Beginner's Ski Pole Low Demand
Road Bicycle Low Demand
Bicycle Tires Low Demand
Mountain Bicycle Low Demand
Pro Ski Boot Low Demand

Here is the code:

declare
STATUS VARCHAR(25);
cursor product_cursor is
SELECT PRODUCT_NAME, COUNT(*) AS DEMAND_STATUS
FROM PRODUCT FULL JOIN ORDER_DETAILS 
ON PRODUCT.PRODUCT_ID = ORDER_DETAILS.PRODUCT_ID
GROUP BY PRODUCT_NAME;
product_row product_cursor%rowtype;
begin
UPDATE PRODUCT
SET PRICE = PRICE - 0
WHERE
    (SELECT COALESCE(MIN(ORDER_ID), 0) 
    FROM ORDER_DETAILS
     WHERE ORDER_DETAILS.PRODUCT_ID = PRODUCT.PRODUCT_ID) =
    (SELECT COALESCE(MAX(ORDER_ID), 0) 
    FROM ORDER_DETAILS
     WHERE ORDER_DETAILS.PRODUCT_ID = PRODUCT.PRODUCT_ID);
dbms_output.put_line('Product Name'||CHR(9)||CHR(9)||CHR(9)||'Demand Status');
dbms_output.put_line('------------'||CHR(9)||CHR(9)||CHR(9)||'-------------');
open product_cursor;
loop
fetch product_cursor into product_row;
exit when product_cursor%notfound;
if PRODUCT_ROW.DEMAND_STATUS < 2 THEN
STATUS := 'Low Demand';
else
STATUS := 'High Demand';
end if;
dbms_output.put_line(PRODUCT_ROW.PRODUCT_NAME||' '||STATUS);
end loop;
close product_cursor;
end;

Answer 1

You're probably better of not using tabs at all, and instead doing:

dbms_output.put_line(rpad('Product Name', 51)||'Demand Status');
dbms_output.put_line(rpad('------------', 51)||'-------------');
...
dbms_output.put_line(rpad(PRODUCT_ROW.PRODUCT_NAME, 51)||STATUS);

where 51 represents the maximum product name you actually expect, which I've guessed at 50 arbitrarily, plus one for the gap.

If you want the product name column fully underlined you can do:

dbms_output.put_line(rpad('Product Name', 51)||'Demand Status');
dbms_output.put_line(rpad('-', 50, '-')||' '||'-------------');
...
dbms_output.put_line(rpad(PRODUCT_ROW.PRODUCT_NAME, 51)||STATUS);

Using dbms_output for anything except debugging can cause you headaches later though, so it might be better to just return the product_cursor to the client and let that display it; something like (assuming SQL*Plus or similar):

var rc refcursor

declare
product_row product_cursor%rowtype;
begin
UPDATE PRODUCT
SET PRICE = PRICE - 0
WHERE
    (SELECT COALESCE(MIN(ORDER_ID), 0) 
    FROM ORDER_DETAILS
     WHERE ORDER_DETAILS.PRODUCT_ID = PRODUCT.PRODUCT_ID) =
    (SELECT COALESCE(MAX(ORDER_ID), 0) 
    FROM ORDER_DETAILS
     WHERE ORDER_DETAILS.PRODUCT_ID = PRODUCT.PRODUCT_ID);

  open :rc for (
    SELECT PRODUCT_NAME,
      CASE WHEN COUNT(*) < 2 THEN 'Low Demand' ELSE 'High Demand' END AS STATUS
    FROM PRODUCT FULL JOIN ORDER_DETAILS 
    ON PRODUCT.PRODUCT_ID = ORDER_DETAILS.PRODUCT_ID
    GROUP BY PRODUCT_NAME;
end;
/

print rc

It isn't obvious why you are using PL/SQL at all here though; you can just use two SQL statements:

UPDATE PRODUCT
SET PRICE = PRICE - 0
WHERE
    (SELECT COALESCE(MIN(ORDER_ID), 0) 
    FROM ORDER_DETAILS
     WHERE ORDER_DETAILS.PRODUCT_ID = PRODUCT.PRODUCT_ID) =
    (SELECT COALESCE(MAX(ORDER_ID), 0) 
    FROM ORDER_DETAILS
     WHERE ORDER_DETAILS.PRODUCT_ID = PRODUCT.PRODUCT_ID);

SELECT PRODUCT_NAME,
  CASE WHEN COUNT(*) < 2 THEN 'Low Demand' ELSE 'High Demand' END AS STATUS
FROM PRODUCT FULL JOIN ORDER_DETAILS 
ON PRODUCT.PRODUCT_ID = ORDER_DETAILS.PRODUCT_ID
GROUP BY PRODUCT_NAME;