Yet another DBA blog: Oracle 11g New Features

I was going through the enhancements made for PL/SQL in Oracle 11g and learn that there are quite a few. I am going to post on these new features in two parts. Here goes the first part.

1. Usage of sequences in PL/SQL expressions

Allows you to assign a sequence value in an expression that is, you do not need to use a SQL query to generate sequence value and assign it to variable. Here is an example :-

SELECT seq_name.nextval INTO variable FROM dual; –> this was how we used to generate sequence values inside PL/SQL

From 11g you can simply do this

variable := seq_name.nextval; –> isn’t this great!

Similarly “currval” can also be used in PL/SQL expression.

2. CONTINUE statement in PL/SQL

CONTINUE is the new loop control statement in 11g. We have used “EXIT” in order to exit out of the loop on certain condition, however CONTINUE would allow us to exit the current iteration in the loop and the control would be passed to next iteration. Here is a small example of find out out even numbers :-

BEGIN
   FOR x IN 1 .. 10
   LOOP
      IF MOD (x, 2) = 0
      THEN
         DBMS_OUTPUT.put_line ('Even number');
         DBMS_OUTPUT.put_line (x);
      ELSE
         CONTINUE;
         DBMS_OUTPUT.put_line (x);
      END IF;
   END LOOP;
END;
/

3. CONTINUE-WHEN statement

It’s purpose is to replace IF…THEN…CONTINUE. Lets re-write the above example using CONTINUE-WHEN :-

BEGIN
   FOR x IN 1 .. 10
   LOOP
      CONTINUE WHEN MOD (x, 2) = 1 ;
         DBMS_OUTPUT.put_line ('Even number');
         DBMS_OUTPUT.put_line (x);
   END LOOP;
END;
/

4. New Datatypes – SIMPLE_INTEGER, SIMPLE_FLOAT and SIMPLE_DOUBLE

SIMPLE_INTEGER supports values ranging –2147483648 to 2147483648 and does not include null values which means it comes with a “NOT NULL” constraint. Apart from the fact that it’s never checked for nulls, overflow checking is also not necessary for SIMPLE_INTEGER. Due to these facts it gives better performance than PLS_INTEGER.

SIMPLE_FLOAT and SIMPLE_DOUBLE are new subtypes of BINARY_FLOAT and BINARY_DOUBLE with “NOT NULL” feature.

5. Changes to Regular expression built-ins

The new REGEXP_COUNT built-in returns the number of times a pattern is found in an input string. The built-ins REGEXP_SUBSTR and REGEXP_INSTR have been improved to return the occurrence you want to find.

I will be covering a few more PL/SQL enhancements in my next post. Do post your comments if you have any suggestions.