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
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.
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;
SQL> select regexp_count('Oracle9i Oracle10g and Oracle11g','[0-9]+') as Count from dual;
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:
select rollno.nextval into rno from dual;
-- use rno which contains next number from sequence
Now, we can refer to a sequence without having to use any SELECT command as follows:
rno := rollno.nextval; -- access sequence directly from PL/SQL
-- use rno
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.
for i in 1..100
if mod(i,3) = 0 then /* ignore numbers divisible by 3 */
-- process other numbers
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
insert into testlog values ('From test_trig1');
create or replace trigger test_trg2 before insert on test
for each row
insert into testlog values ('From test_trig2');
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;
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)
Now, call this procedure using positional, named and mixed parameter notations as follows and see
the output given after the block.
p1(10,20,30); -- positional
p1( n2=> 20, n3 =>30 ); -- named
p1( 10, n3 =>10 ); -- MIXED is new
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';
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.
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));
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.
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
BEFORE STATEMENT IS
total := 0;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
END BEFORE EACH ROW;
AFTER EACH ROW IS
total := total + :new.price - :old.price;
END AFTER EACH ROW;
AFTER STATEMENT IS
if total > 1000 then
raise_application_error(-20100,'Total increase in price cannot cross 1000');
END AFTER STATEMENT;
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'
The following are other interesting features added to Oracle Database 11g.
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.
- SIMPLE_INTEGER data type will improve performance especially when PL/SQL code is compiled to native code.
- SUPER reference can be used to access methods of super type that are overridden in sub type
- You can cache results of queries and results of PL/SQL functions
- Password has become case sensitive.