Create Table Using Object Type In Oracle Database.

Note :- In Oracle database we can create table using object type.

SQL> CREATE TYPE TY_EMPLOYEES
  2  AS OBJECT
  3  ( EMP_ID NUMBER,
  4    EMP_NAME VARCHAR2(300),
  5    STREET VARCHAR2(10),
  6    CITY   VARCHAR2(200),
  7    SALARY NUMBER(6),
  8    JOINING_DATE DATE
  9  )
 10  /

Type created.

SQL> CREATE TABLE EMPLOYEES OF TY_EMPLOYEES;

Table created.

SQL> INSERT INTO EMPLOYEES(EMP_ID, EMP_NAME, STREET, CITY, SALARY, JOINING_DATE)
  2  VALUES(100,'RAJIB','DHAKA','DHAKA',5000,SYSDATE);

1 row created.

SQL> SELECT * FROM EMPLOYEES;

    EMP_ID
----------
EMP_NAME
--------------------------------------------------------------------------------
STREET
----------
CITY
--------------------------------------------------------------------------------
    SALARY JOINING_D
---------- ---------
       100
RAJIB
DHAKA

    EMP_ID
----------
EMP_NAME
--------------------------------------------------------------------------------
STREET
----------
CITY
--------------------------------------------------------------------------------
    SALARY JOINING_D
---------- ---------
DHAKA
      5000 02-FEB-14


SQL>

Using Virtual Column In a Table In Oracle Database.

Description: Virtual column are generally use for calculate data. Insert or Update operation are not allowed in virtual column. Virtual column are allowed in Oracle 11g Database.

CREATE TABLE EMPLOYEE_INFO
(EMPLOYEE_ID VARCHAR2(20),
 FIRST_NAME VARCHAR2(200),
 SALARY_DAILY NUMBER,
 DAY_IN_MONTH NUMBER,
 MONTHLY_SALARY NUMBER GENERATED ALWAYS AS  (SALARY_DAILY*DAY_IN_MONTH) VIRTUAL)

SQL>
SQL> Insert into EMPLOYEE_INFO
  2     (EMPLOYEE_ID, FIRST_NAME, SALARY_DAILY, DAY_IN_MONTH)
  3   Values
  4     ('120', 'RAJIB', 150, 30);

1 row created.

SQL> select * from EMPLOYEE_INFO;

EMPLOYEE_ID
--------------------
FIRST_NAME
----------------------------------------------------------------
SALARY_DAILY DAY_IN_MONTH MONTHLY_SALARY
------------ ------------ --------------
120
RAJIB
         150           30           4500

ora-01950 no privileges on tablespace 'users'

Cause: User does not have privileges to allocate an extent in the users tablespace.

Action: Grant the user space resource on the users tablespace.

alter user eftmw quota unlimited on users;