How do I give an alias name to the values printed by dbms output in PL/SQL?

Question

I have this following code which prints the department number and department name from dept table whose department number is less than 40.

declare
cursor cn is select dno, dname from dept where dno<40;
v_dno dept.dno%type;
v_dname dept.dname%TYPE;
begin
    open cn;
        loop
            fetch cn into v_dno,v_dname;
            dbms_output.put_line(v_dno||' '||v_dname);
            exit when cn%notfound;
        end loop;
    close cn;
end;

This is the output I got:

10 Administration
20 Marketing
30 Purchasing
30 Purchasing

But the output that I'm expecting is:

Department_No   Department_Name
10              Administration
20              Marketing
30              Purchasing
30              Purchasing

Answer 1

PL/SQL only outputs what we explicitly pass to DBMS_OUTPUT. So, if you want headings you need to provide them yourself:

declare
cursor cn is select dno, dname from dept where dno<40;
v_dno dept.dno%type;
v_dname dept.dname%TYPE;
begin
    dbms_output.put_line('Department_No   Department_Name');
    open cn;
        loop
            fetch cn into v_dno,v_dname;
            dbms_output.put_line(rpad(to_char(v_dno),14)||' '||v_dname);
            exit when cn%notfound;
        end loop;
    close cn;
end;

Note that to get the neat alignment you need to use RPAD() to make V_DNO same length as the heading