Method to Create New Column in Oracle Table with Combination Other Columns

advertisements

_____________________________________________________________________________________________________________________

It is not a regular requirement. But sometimes we need it such cases. This can be achieved by the virtual columns in the Oracle.

See some examples below.

SQL>alter table emp add name_job as (ename||'-'||job);

Table altered.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO NAME_JOB

---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------------------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 SMITH-CLERK
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 ALLEN-SALESMAN
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 WARD-SALESMAN
 
14 rows selected.

You should not use + symbol because the target column will get create with number as data type. Then you will get ORA-01722 error message while selecting from the table.

SQL> alter table emp add name_job as (ENAME+'-'+job);

Table altered.

SQL> select * from emp;

select * from emp
*
ERROR at line 1:
ORA-01722: invalid number
 
Method to create combination column of 2 columns with different data type.

alter table emp add name_sal as (ename||'-'||to_char(sal));

 
  1*  select * from emp
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO NAME_SAL

---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 SMITH-800
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 ALLEN-1600
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 WARD-1250

_____________________________________________________________________________________________________________________

Website Stats