Oracle Features

9i FEATURES

           1.   CASE statements

           2.   NVL2

           3.   NULLIF

           4.   COALESCE

           5.   SYSTIMESTAMP function

           6.   ANSI JOINS

           7.   RENAMING A COLUMN

           8.   TIMESTAMP data type

           9.   MERGE statement  (Insert & update together)

           10. INSERT ALL

           11. ASSOCIATIVE ARRAY (INDEX BY VARCHAR(10)

10G FEATURES

1.    PLS_INTEGER

2.    SYS_REFCURSOR

3.    RECYCLEBIN

4.    SPOOL …. APPEND

5.    Enhancements in MEREGE for DELETE  (I/U/D)

6.    EXPDP   & IMPDP

7.    CONSTRAINTS ON VIEWS

8.    There is no difference b/w VARCHAR & VARCHAR2

9.    TIMESTAMP can store nano-seconds as well

10.REGULAR  EXPRESSIONS (REGEXP_LIKE /  REGEXP_REPLACE/REGEXP_SUBSTR/REGEXP_INSTR)

11. DBMS_SCHEDULER package  à For scheduling the PL/SQL programs.

12. INDICES OF & VALUES OF

11g FEATURES

1.  SIMPLE_INTEGER

2.  Password is case sensitive from 11g onwards

3.  i  := SEQ.NEXTVAL   (sequence pseudo-columns  can be used in procedural 

     statements)

    Select  seq.nextval  into   i  from dual;     (Before 11g);

4.  READ ONLY TABLES

5.  NAMED POSITION IN SQL

     Select get_name(peno => 2000) from dual;  à possible only in 11g

     Select get_name(2000) from dual;  à Before 11g

6.  ENABLE OR DISABLE TRIGGER (Disables the trigger during creation itself).

7.  FOLLOWS IN TRIGGERS (Overrides the default order of the trigger execution).

8.  COMPOUND TRIGGER

9.  The execute immediate (NDS) can take a string more than 32K as well

10. INDEX VISIBLE OR INVISIBLE

11. QUERY RESULT CACHE

12. REGULAR EXPRESSION (REGEXP_COUNT)

13. CONTINUE Statement in loops

14. PIVOT and UNPIVOT

12C Features

a) 32676 at the SQL Level (DB Level) for CHAR & VARCHAR datatypes

b) truncate table <Parent> cascade;
We can cascade the TRUNCATE command as well, if we have set ON DELETE CASCADE during the FK creation.

c) select * from ( select * from emp order by sal desc) where rownum <= 5;
select * from emp order by sal desc fetch first 5 rows only;

d) Session Level Sequences

SQL> create sequence sessseq session;

SQL> create sequence globseq global; -- default.

SQL> select globseq.nextval from dual;
3

SQL> select sessseq.nextval from dual;
1

e) Invisible Column

SQL> create table t4 (col1 number, col2 number invisible);

SQL> desc t4; -- only "col1" is displayed

SQL> select * from t4;  -- only col1 is displayed

SQL> select col1, col2 from t4; --both columns displayed

SQL> set colinvisible on

SQL> desc t4; -- both columns would be displayed.

SQL> create index in_t4 on t4(col2);

f) Identity Column

SQL>  create table t6 (id number, col1 number generated always as identity);

SQL>  create table t6  (id number,  col1 number generated always as identity (start with 5 increment by 4) );

SQL> insert into t6 values(-9999);

SQL> insert into t6 values(-9999 , 2);  -- gives error

g) Default Values for Sequences

sql>  create table temp   (id number default seq.nextval);