Oracle 10g PL/SQL New Features (Part 1)

Oracle 10g PL/SQL has a number of new features to make application development easier and faster including:

Oracle 10g PL/SQL Compiler Enhancements

Let's start with the enhancements to the Oracle 10g PL/SQL compiler. Oracle have improved integer and floating point performance, the elimination of unreachable code, and the calling of library routines to give an overall improvement of 50-75% in the performance of PL/SQL code compared to Oracle 9i. 

It needs to be stressed that this improvement is overall - PL/SQL code with many database calls will benefit less than compute-intensive PL/SQL code.

PL/SQL Floating-point Performance

Floating-point performance has been improved by the introduction of 2 new data types BINARY_FLOAT and BINARY_DOUBLE which meet the IEEE 754 standard and have native support on many platforms making them much more efficient for use in scientific applications.

PL/SQL Integer Performance

Integer performance has been improved by changing the BINARY_INTEGER data type to be the same as the PLS_INTEGER type which had better performance in Oracle 9i and below. These 2 data types are now interchangeable.

Oracle 10g PL/SQL Compiler Warnings

Another enhancement to the Oracle 10g PL/SQL compiler is the introduction of compiler warnings to supplement the compiler errors that were generated in previous versions of Oracle. 

There are 3 levels of warnings: severe, informational and performance-related. The idea of these is to notify you of any potential problems or  performance issues with your code so that  you can review them and if necessary correct them. 


As with syntax errors, any warnings will be displayed by the show errors command after compilation. Unlike syntax errors, any warnings will not prevent the code from being compiled and run. 

PL/SQL compilation warnings are enabled/disabled at the system level by the value of the plsql_warnings parameter or alter system command and at the session level by use of the alter session command. 

For example
 alter session set plsql_warnings='enable:performance' 
will turn on performance-related warning messages for the current session and
 alter system set plsql_warnings='disable:performance scope=memory' 
will turn off performance-related warning messages for all sessions.

PL/SQL Regular Expressions

Regular expressions have long been a powerful feature of text editors and other utilities on UNIX and LINUX and have now been brought to the database greatly enhancing the primitive pattern matching facilities previously available in Oracle with the LIKE clause.


Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber you're  missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer.Subscribe now and ignite your career.


In Oracle 10g PL/SQL there are 4 new functions supporting pattern matching with regular expressions: REGEXP_LIKE,REGEXP_INSTRREGEXP_SUBSTR and REGEXP_REPLACE which can be used anywhere you would previously have used LIKE,INSTR, SUBSTR and REPLACE. Of course the original functions can still be used, you only need to use the new ones when you want to use regular expressions.

If you have used regular expressions before with UNIX utilities such as SED or AWK then the good news is that the meta symbols used in regular expressions in PL/SQL and SQL in Oracle 10g and above are much the same. If you've not encountered regular expressions before they're easy to follow once you understand the meanings of the meta symbols - the most common of which are as follows:

Symbol Description Example
* Wild card - matches zero or more occurrences '*Ad' matches 'mAd', 'bAd','sAdness', 'lAd'
? Wild card - matches zero or one characters '?eg' matches 'eg', 'egg','beg','legging'
^ matches beginning of string '^top' matches 'topping', 'topper','topped' but not 'stop'
$ matches end of string 'top$' matches 'stop' but not stopping
. matches any character except NULL 's.*s' matches 'success', 'stars','slaves'

Let's have a look at a few examples of how to use these new functions in Oracle 10g PLSQL.

select 'True' from dual where regexp_like ('Heather','^Heath')

returns 'True' because we've asked if our test string starts with a capital 'H' and is followed by 'eath' which it does.  One thing to note is that these functions are case sensitive so 'Heather' would not match '^heath'.

dnms_output.put_line(regexp_instr('Heather','THe'));
This will display 0 because we've asked for the position of 'The' in our test string which is not present.
dnms_output.put_line(regexp_instr('Theatre','The'));

x will be 1 because we've asked for the position of 'The' in our test string.

dnms_output.put_line(regexp_instr('Theatre','?he'));
x will be 2 because the substring 'he' starts at the 2nd letter in our test string.
dbms_output.put_line(regexp_substr('Theatre','?he'));
This will display 'he' because the substring 'he' starts at the 2nd letter in our test string.
dnms_output.put_line(regexp_substr('Theatre','^he'));

This will print nothing because the substring 'he' does not start at the beginning of our test string.

dnms_output.put_line(regexp_replace('Theatre','^The'));

This will display 'atre' because the substring 'The' has been replaced with nulls.

dnms_output.put_line(regexp_replace('Theatre','^The','***')); 

This will display '***atre' because the substring 'The' has been replaced with ***.

This article just scratches the surface of these new features in Oracle 10g PLSQL. See Oracle 10g PLSQL part 2 for details on enhancements to collections.


Looking for cost effective training on PL/SQL, SQL or Oracle database administration? 

With our partners we offer scheduled Oracle training and consulting in the UK. All courses can be customised to your requirements. 

If you don't want to travel or want the luxury of taking a course sitting at your desk then check out Gogo's on-demand courses available 24*7. Be sure you book through us to get your discount.

Before you go, sign up to our newsletter for special deals on training and books, you'll receive an avalanche of PL/SQL coding tips and techniques to ensure you have the knowledge you need to work better, faster and smarter.


Return to Oracle 10g New Features Overview