Srikanth Technologies

New Features of Oracle Database 11g

Oracle Database 11g was released on July 11 ,2007. Though they released Linux version first eventually Windows version was also released. In this article, I want to show the new features that Oracle developers would find interesting.  You can download Oracle Database 11g by going to www.oracle.com.

REGEXP_COUNT Function

New function, REGEXP_COUNT takes a string and regular expression and returns the number of times the expression is present in the string.
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

Oracle Database 11g has now provided support for Sequence in PL/SQL. Earlier to get a number from a sequence in PL/SQL we had to use SELECT command with DUAL table as follows:
declare
    rno number;
begin
   select  rollno.nextval into rno from dual;
   -- use rno which contains next number from sequence
end;
Now, we can refer to a sequence without having to use any SELECT command as follows:
declare
    rno number;
begin
    rno := rollno.nextval;   -- access sequence directly from PL/SQL
    -- use rno
end;

Continue Statement

Continue statement is introduced in PL/SQL loops to skip rest of the statements in an iteration and restart the next iteration. Of course its functionality is same as continue statement of C language. Remember EXIT statement is same as break statement of C language.
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

When two or more triggers are defined for the same event, in the past it was not possible to define the order in which those triggers are fired when the event occurs. Starting from 11g Oracle allows you to specify that a trigger must be fired only after another trigger for the same event is fired. It is done using FOLLOWS keyword followed by trigger name after which current trigger is to be invoked. Assume we have TEST and TESTLOG tables as follows.
create table test( n varchar(10));

create table testlog (log varchar(50));

Create the following two triggers that are to be fired before INSERT command of TEST table.
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;

In the above example, both TEST_TRG1 and TEST_TRG2 are associated with the same event (before insert of test table). As we specified at the time of creating TEST_TRG2 that it must be called only after TEST_TRG1 is called using FOLLOWS keyword, it is always invoked after TEST_TRG1. To test the order, insert a row into TEST table and see what rows are placed in TESTLOG table.
SQL> insert into test values(1);

1 row created.

SQL> select  * from testlog;

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

Mixed Notation

Oracle 11g allows mixed notation for function calls. It was possible to use either positional parameters or named parameters in the previous versions. Now, Oracle supports mixing named and positional notations. Create a procedure that takes three parameter that are optional as follows:
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;

Now, call this procedure using positional, named and mixed parameter notations as follows and see the output given after the block.
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

Virtual column is a column whose value is derived from an expression. Oracle doesn't store any data related to virtual column, only expression given at the time of creating virtual column is stored in data dictionary. The following example creates virtual column called DIFFERENCE, which returns the difference between MAX_SALARY and MIN_SALARY columns in JOBS table.
ALTER TABLE JOBS ADD (DIFFERENCE AS ( MAX_SALARY  - MIN_SALARY))

Select  job_title, difference from jobs;

Fine Grained Dependency Tracking

Prior to Oracle11g, modifying the structure of a table would make dependent views invalid even thought the change to table has nothing to do with view. As we have a table called T1 and view V1 as follows.
create table t1 (n1 number(5), n2 number(5));

create view v1 as select n1, n2 from t1;

In Oracle10g, any change to table T1 would mark view V1 invalid whether or not the change effects view logically. For example the following change to table T1 would make view V1 invalid.
SQL>alter table t1 add( n3 number(5));

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

STATUS
-------
INVALID

However, the change made to table T1 has nothing to do with view and logically doesn't effect view.

That is why starting from Oracle11g a change to table that does not effect view logically, will NOT make view invalid. So, if you add a column to table T1 then because of fine grained dependency tracking system applied by Oracle 11g, it doesn't make view invalid.

Compound Trigger

A compound trigger allows different blocks within a trigger to be executed at different timing points. It has a declaration section and a section for each of its timing points. All of these sections can access a common PL/SQL state.

Assume we need to ensure the total amount of increase in prices must not cross 1000 for all products put together in a single UPDATE statement. A compound statement can achieve this by adding the difference between new price and old price to total and checking whether total crosses 1000 at the end of the statement.

To demonstrate compound trigger, create a table and insert some data as follows:
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;

Now, create a compound trigger to ensure the total increase in all prices is not more than 1000 in a single UPDATE statement.
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;

Now make the following changes. First change is accepted as it doesn't increase price by more than 1000 for all products put together. But, second change is not allowed as it causes the total increase to cross 1000.
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

The following are other interesting features added to Oracle Database 11g.
  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.
The above list of new features by no means is complete. I have written about the features that i personally found interesting and useful. For complete list of more than 400 new features, please read Oracle documentation.

Post Your Comment

Image

Enter the code given in the above image :
Enter Your Name :
Enter Your Email Address :
Enter Your Comment :

Comments


Posted By saivinodh On 17-Feb-16 12:57:26 PM

I really thankful to sir for providing fundamentals concept of sql,pl/sql and about oracle latest version 12cloud.it is very helpful to beginners who like to learn relational database concept.



Posted By kamaltheen On 23-Feb-16 08:11:31 PM

Thank you very much for this post and it is very helpful for my interview preparation.