Column format $9,999.99 : Column « SQL Plus « Oracle PL / SQL

Column format $9,999.99

    
SQL>
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>
SQL> select ename, sal
  2        from emp
  3       where ename like 'A%'
  4      /

Employee Name     Salary
------------- ----------
ALLEN               1600
ADAMS               1100

SQL> column sal format $9,999.99
SQL>
SQL> select ename, sal
  2  from emp
  3  where ename like 'A%';

Employee Name     Salary
------------- ----------
ALLEN          $1,600.00
ADAMS          $1,100.00

SQL>
SQL> drop table emp;

Table dropped.

SQL>
SQL>

1.	Use 'format a30 heading' to define column name
2.	Column heading format a13
3.	column localtimestamp format a28
4.	Use a13 to set the column length during displaying
5.	Aligning decimals
6.	Adding a group separator
7.	Including a currency symbol
8.	Wrapping text
9.	JUSTIFY CENTER
10.	JUSTIFY RIGHT
11.	Disable the column formatting
12.	SET string to display when value is NULL
13.	COLUMN Salary heading "Current\|Salary" format $9999.99
14.	COLUMN fname heading "emp\|Name" format a10
15.	COLUMN id heading "emp\|Number" format 9999
16.	Copy column format with 'col ... like'
17.	column format: ascii type, 26 letter long
18.	column number format
19.	Column data is aligned by type
20.	Word Wrapped column format
21.	Set number column format
22.	Set column format before doing the query
23.	Set column heading with column command
24.	Set column separation with colsep

Column format $9,999.99 : Column « SQL Plus « Oracle PL / SQL

Related examples in the same category