logo for asktheoracle.net
Home
What's new?
Oracle Tutorials
PL/SQL
SQL
Oracle Tips & Tricks
Advanced Tutorials
Certification
Oracle 10g
Oracle 11g
Oracle and .Net
Oracle Tools
Disclaimer
Privacy Policy
Search This Site
Oracle Questions?
leftimage for asktheoracle.net

Oracle 10g PLSQL New Features (Part 1)

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


Oracle 10g PLSQL Compiler Enhancements

Let's start with the enhancements to the Oracle 10g PLSQL 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 - PLSQL code with many database calls will benefit less than compute-intensive PLSQL code.

PLSQL 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.

PLSQL 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 PLSQL Compiler Warnings

Another enhancement to the Oracle 10g PLSQL 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.

PLSQL 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.

PLSQL 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 to Oracle Tips and Tricks, you're  missing out on a myriad of tips and techniques to help you work better, faster and smarter.
Subscribe now to enhance your career.

In Oracle 10g PLSQL there are 4 new functions supporting pattern matching with regular expressions: REGEXP_LIKE, REGEXP_INSTR, REGEXP_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 PLSQL 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 Oracle 10g PLSQL, SQL or Oracle database administration? 

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

Before you go, why not sign up to our newsletter? 

Along with special deals on training and books, you'll receive an avalanche of PLSQL coding tips and techniques to ensure you have the knowledge you need to work better, faster and smarter.