New Features of Oracle Database 11g

• REGEXP_COUNT function
• PL/SQL Sequence
• Continue in PL/SQL
• Triggers Firing Order
• Mixed Notation
• Virtual Columns
• Fine Grained Dependency Tracking
• Compound Trigger
• Other Misselaneous Features

REGEXP_COUNT Function

SQL> select regexp_count('How do you do?','do') from dual;

REGEXP_COUNT('HOWDOYOUDO?','DO')
--------------------------------
                               2

SQL> select regexp_count('Oracle9i Oracle10g and Oracle11g','[0-9]+') as Count from dual;

     COUNT
----------
         3

PL/SQL Sequence

declare
    rno number;
begin
   select  rollno.nextval into rno from dual;
   -- use rno which contains next number from sequence
end;

declare
    rno number;
begin
    rno := rollno.nextval;   -- access sequence directly from PL/SQL
    -- use rno
end;

Continue Statement

begin
   for i in 1..100 
    loop
         if mod(i,3) = 0 then  /* ignore numbers divisible by 3 */
               continue;
         end if;
         -- process other numbers 
         dbms_output.put_line(i);
    end loop;
end;

Trigger Firing Order

create table test( n varchar(10));

create table testlog (log varchar(50));

create or replace trigger test_trg1 before insert on test 
for each row
begin
   insert into testlog values ('From test_trig1');
end;

create or replace trigger test_trg2 before insert on test 
for each row
follows test_trg1
begin
   insert into testlog values ('From test_trig2');
end;

SQL> insert into test values(1);

1 row created.

SQL> select  * from testlog;

LOG
--------------------------------------------------
From test_trig1
From test_trig2

Mixed Notation

create or replace procedure  p1(n1 number:=1, n2 number :=2, n3 number := 3) 
is
begin
    dbms_output.put_line(n1);
    dbms_output.put_line(n2);
    dbms_output.put_line(n3);
end;

begin
   p1(10,20,30);   -- positional
   p1( n2=> 20, n3 =>30 );  -- named
   p1( 10, n3 =>10 );  -- MIXED is new
end;

10
20
30
1
20
30
10
2
10

Virtual Columns

ALTER TABLE JOBS ADD (DIFFERENCE AS ( MAX_SALARY  - MIN_SALARY))

Select  job_title, difference from jobs;

Fine Grained Dependency Tracking

create table t1 (n1 number(5), n2 number(5));

create view v1 as select n1, n2 from t1;

SQL>alter table t1 add( n3 number(5));

SQL>select status from user_objects where object_name = 'V1';

STATUS
-------
INVALID

Compound Trigger

SQL> create table products (name varchar(10), price number(5));
Table created.
SQL> insert into products values('a',1000);
1 row created.
SQL> insert into products values('b',3000);
1 row created.
SQL> insert into products values('c',2300);
1 row created.
SQL> commit;

CREATE OR REPLACE TRIGGER total_price_check
FOR UPDATE ON PRODUCTS COMPOUND TRIGGER
 total number(5);
BEFORE STATEMENT IS
BEGIN
    total := 0;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
  null;
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
     total :=  total + :new.price - :old.price;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
   if  total > 1000 then
      raise_application_error(-20100,'Total increase in price cannot cross 1000');
   end if;
END AFTER STATEMENT;
END;

SQL> update products set price = price + 100;

3 rows updated.

SQL> update products set price = price + 500;
update products set price = price + 500
       *
ERROR at line 1:
ORA-20100: Total increase in price cannot cross 1000
ORA-06512: at "HR.TOTAL_PRICE_CHECK", line 18
ORA-04088: error during execution of trigger 'HR.TOTAL_PRICE_CHECK'

Miscellaneous Features

1. SIMPLE_INTEGER data type will improve performance especially when PL/SQL code is compiled to native code.
2. SUPER reference can be used to access methods of super type that are overridden in sub type
3. You can cache results of queries and results of PL/SQL functions
4. Password has become case sensitive.