Use rpad to format a report : RPAD « Character String Functions « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Character String Functions
3. RPAD

SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));

Table created.

SQL>
SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7521, 'WARD',  'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER',  7839, TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

1 row created.

SQL>
SQL>
SQL> set echo on
SQL>
SQL> create or replace procedure emp_report as
  2  begin
  3      dbms_output.put_line(rpad('Empno',7)||rpad('Ename',12)||rpad('Job',11));
  4      dbms_output.put_line( rpad('-',5,'-')||rpad('  -',12,'-') ||rpad('  -',11,'-') );
  5      for x in ( select * from emp )
  6      loop
  7          dbms_output.put_line(to_char(x.empno,'9999')||'  '||rpad( x.ename, 12 ) ||rpad( x.job,11));
  8      end loop;
  9  end;
 10  /

Procedure created.

SQL>
SQL> set serveroutput on format wrapped
SQL>
SQL> exec emp_report
Empno  Ename       Job
-----  ----------  ---------
 7369  SMITH       CLERK
 7499  ALLEN       SALESMAN
 7521  WARD        SALESMAN
 7566  JONES       MANAGER
 7654  MARTIN      SALESMAN
 7698  BLAKE       MANAGER
 7782  CLARK       MANAGER
 7788  SCOTT       ANALYST
 7839  KING        PRESIDENT
 7844  TURNER      SALESMAN
 7876  ADAMS       CLERK
 7900  JAMES       CLERK
 7902  FORD        ANALYST
 7934  MILLER      CLERK

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table emp;

Table dropped.

SQL>
SQL>