PL/SQL enhancements in Oracle database 10g

Oracle 10g Database has a lot new things for everybody... May it be Database Administrators or the Developers. A lot of people have been discussing the DBA features that Oracle 10g Database provides, but a very few have said anything about PL/SQL features in 10g.

So, what’s New in PL/SQL in Oracle Database 10g?

Well, a lot of things are new in PL/SQL in 10g db. In this post we will just list some of them and in the next post:

A. Changes in 10g PL/SQL compiler

1. Freedom in order of evaluation of operands
eg: operators of equal precedence a+b+c can be evaluated in any order like: a+b+c or a+c+b or a+c+b or any other combination that the compiler feels correct

2. Freedom of not raising an exception
eg: if some operation can be performed in two ways, 1 of which may raise and exception, the compiler can choose not to select that option.

3. Freedom to raise the exception
eg: if an operation raises two exceptions, compiler has freedom to choose which exception it wants to raise

4. Freedom of computing correct result
eg: A*null = null; no matter A is of any datatype

.5 Takes care of the side effects
eg: RANDOM + 1/BSD
If RANDOM is a function call manipulating BSD, then sequence is taken care of

B. Freedom and Side Effects

Though Oracle 10g Database gives the compiler a lot of freedoms, the DB has to take care of the side effects arising out of such freedoms:

1. PL/SQL Compiler will not reorder, add or remove explicit procedure, function or method calls appearing in sepereate statements
2. PL/SQL Compiler has the freedom not to do an operation whose only effect is to raise and exception or to initialize a package
3. IF a PL/SQL subprogram requires initialization of the package, then PL/SQL is free to perform the initialization at any time between the beginning of the call to the subprogram which immediately encloses the package element reference and the moment when the package element is actually used
4. If the PL/SQL compiler can determine that a use of a package element does not require the initialization of the package, then PL/SQL is not required to initialize the package when the element is used
5. If an operation may raise an exception when executed, but there is an alternate possible method of execution which will not raise that exception, the PL/SQL compiler is free to choose the alternate method
6. The PL/SQL compiler may not introduce a computation as an alternate which may raise an exception which otherwise would not have been raised during the execution of a program

Example 1:

- A + B …
-
- A + B …

can be changed to

T := A + B;
… T …
…
… T …

Example 2:

for i in 1 .. 10 loop
A := B + C;
…
end loop;

can be changed to
A := B + C;
for i in 1 .. 10 loop
…
end loop;

Example 3:

if pkg.A = null then null; end if;

The entire if statement may be eliminated if A is not a procedure or method call because the PL/SQL compiler knows that the result of the comparison is always null.

These were some of the features/enhancements in PL/SQL compiler of Oracle 10g Database. So the next time you write a piece of code for Oracle database 10g, keep these things in mind and the database will give you the best output. In the subsequent post, we will try to debunk few performance myths in Oracle database 10g.