Java Development Blog

11g , the latest database released by Oracle contains many additional features that add more functionality in coding using sql and pl/sql. So among the new features added to 11g here lists some of the features which I like to point out through this article.
Following are the new features;

This function takes a string and a regular expression and returns the number of times the string has repeated itself in the regular expression. This is a new feature that oracle has added in the 11g release. The earlier version contains only those functions that could return the occurrence of a single alphabet or an integer. The usage of regexp_count function can be clearer from the expression below

Select regexp_count(‘my name is john and how do you do?’,’do’) from dual;

The result of the above query will be 2 because the number of times ‘do’ repeats itself in the sentence ‘my name is john and how do you do?’ is 2 (occurrence marked as bold)

Earlier for accessing the nextval and currval of a sequence can only be done through a query which is as follows

Select nextval(sequence_name) into v_variable from dual; (to get the next value)
Or
Select currval(sequence_name) into v_variable from dual; (to get the current value)
But the new improvement in 11g gives the provision to refer the sequence without a sql query. In order to get the next value of a sequence in 11g we just have to follow the steps below.
Declare
V_nextval Number;
Begin
V_nextval := sequence_name.nextval; (for getting next value of the sequence into the variable v_nextval)
End ;

The 11g enables to change the firing order of the database triggers i.e. if a table consists of more than two triggers the firing order can be set for the two. If we have to fire the second trigger before the first we have to specify a keyword ‘FOLLOWS TRIGGER_NAME’ in the code of the first trigger. So the execution of the triggers will be in such a way that the second trigger will fire first and then the first. This is very helpful because in the older version both the trigger would work pretty much at the same time. So extra functionality can be added if we could set the fire order.

This is yet another improvement made in the triggers in the 11g release. This compound trigger enables us to code instructions in a trigger at different execution timings. For example if we have a set of instructions that should work before the insertion and after the insertion. In the older version we have to write triggers for both. One before the insert and one after insert. In 11g the concept of compound trigger enables us to write both in the same trigger. ie. while coding the trigger is divided into different sections which will work before and then after the insert or update or deletion of records. So more calculations with old and new values can be done in the same trigger itself.