Oracle 11g PL/SQL New Features

The tutorial lists the major PL/SQL additions and enhancements covered in Oracle 11g. The features described here are based on their utility in a development environment.

SQL* Plus Stepped Down and Passwords made Case Sensitive

SQL* Plus is no more the part of Oracle home family. This step has been taken to recommend the use of SQL Developer. SQL Developer version 3.0 is the latest release from Oracle, in which all the earlier reported bugs were resolved.

Besides, developers can still optionally setup SQL* Plus environment on command line prompt.

Now, database connection passwords could be case sensitive. Two new parameters SEC_CASE_SENSITIVE_LOGON and SEC_MAX_FAILED_LOGIN_ATTEMPTS are introduced to enable/disable the case sensitive feature and set the maximum failure attempts.

Real Native Compilation

Code compilation has always been under scrutiny due to its memory consumption and hardware synchronization. Oracle 11g has upgraded the native compilation technique by removing the dependency on C Compiler to generate the C Code out of PL/SQL source code.

A new compilation parameter PLSQL_CODE_TYPE specifies the compilation mode used by the system or in a session. Earlier, there were bunch of parameter to be set before code compilation.

Example Syntax [1]

ALTER SYSTEM SET PLSQL_CODE_TYPE = [NATIVE | INTERPRETED]

The compilation mode can also be changed at object level to compile an object. The ALTER command in Example Code [1] natively compiles a procedure P_TESTING.

Example Code [1]

ALTER PROCEDURE P_TESTING COMPILE PLSQL_CODE_TYPE=NATIVE

At object level, the compilation mode of the object can be queried from USER_PLSQL_OBJECT_SETTINGS.

Trigger Enhancements

Oracle 11g provided considerable enhancements in Database triggers. Introduction of Compound Triggers and setting the firing sequence are the major enhancements in triggers.

i. Compound Triggers

From developer’s perspective, this was one of the most comprehensive features. A Compound trigger combines all the DML timing events into a single trigger body. It resolves Mutating table error and enhances the code maintenance. The memory variables declared in the sections enjoy their persistent state till the statement execution finishes.

Example Syntax [2]

CREATE OR REPLACE TRIGGER [TRIGGER NAME]

```
FOR [DML] ON [TABLE NAME]
```
```
COMPOUND TRIGGER
```
```
-- Initial section 
```
```
-- Declarations 
```
```
-- Subprograms
```
```
Optional SECTION
```
```
BEFORE STATEMENT IS
```
```
…;
```
```
Optional SECTION
```
```
AFTER STATEMENT IS
```
```
…;
```
```
Optional SECTION
```
```
BEFORE EACH ROW IS
```
```
…;
```
```
Optional SECTION
```
```
AFTER EACH ROW IS
```
```
…;
```
```
END;
```

For views, the trigger body contains INSTEAD OF EACH ROW section.

ii. Disabled Trigger

Prior to 11g, a trigger can always be created in ENABLED state. At later point of time, it can be explicitly disabled to restrict the trigger action. Oracle 11g allows creation of disabled trigger by specifying DISABLE keyword in the trigger definition as shown in Example Code [2].

Example Code [2]

CREATE OR REPLACE TRIGGER T_UPD_ORDER_STATUS

```
BEFORE INSERT ON ORDERS
```
```
FOR EACH ROW
```
```
DISABLE
```
```
BEGIN
```
```
. . .
```
```
-- Trigger body --
```
```
. . .
```
```
END;
```

Toggling over ENABLE or DISABLE state can be done same as earlier by using ALTER TRIGGER command.

iii. FOLLOWS keyword

Oracle 11g allows creation of multiple triggers on same DML event timing on a table and set their firing sequence. Earlier versions of Oracle too allowed the creation of multiple trigger on same event but random firing sequence.

Example Code [3]:

Trigger TRG_FOLLOWER follows TRG_FOLLOWED in the below trigger body.

SQL>CREATE OR REPLACE TRIGGER TRG_FOLLOWER

```
AFTER INSERT ON ORDER
```
```
FOLLOWS TRG_FOLLOWED
```
```
BEGIN
```
```
. . .
```
```
. . .
```
```
END;
```
```
/
```
```
 
```
```
TRIGGER created.
```

Fine Grained Dependency

Oracle server maintains dependency and references for each database object. Prior to 11g, there existed a direct relationship between an object and its referenced objects. This had an overhead implication that if the state of referenced object gets INVALID, all its dependent objects would be invalidated. For example, a database view is created on top of a table, using few columns. Now, we alter the table to add a new column. This scenario would have different results in Oracle 11g and its earlier editions. As per the dependency equation followed by earlier editions of Oracle 11g, the view would have been invalidated. Now, with 11g release, if an object does not use the altered element of the referenced object, it would be in VALID state.

An object would remain in VALID state until the elements level changes on referenced objects do not impact the object (dependent object).

Subprogram Inlining

Oracle server inlines a subprogram by replacing the subprogram calls with its copy, during execution. Oracle uses PLSQL_OPTMIZE_LEVEL initialization parameter to set the optimizer level. Oracle 11g adds a new Optimizer level ‘3’, which directs the optimizer to inline the subprograms at high priority.

Optimizer level ‘2’ is mostly recommended as it logically inlines the subprograms. Level ‘3’ grants right to database users to explicitly direct Oracle, to inline a subprogram, as per their calculative and logical requirements. Level ‘1’ restricts any type of inlining in the database.

Additionally, at optimizer level ‘2’, PRAGMA INLINE can be used to explicitly inline a subprogram, which could have been ignored by the optimizer but required by the programmer. Its usage is depicted in the Example Code [4].

Example Code [4]

```
DECLARE
```
```
l_result NUMBER;
```

FUNCTION l_inline_reqd (param IN NUMBER)

```
RETURN NUMBER AS
```
```
BEGIN
```
```
RETURN param*5;
```
```
END l_inline_reqd;
```
```
BEGIN
```
```
FOR i IN 1 .. 500 LOOP
```
```
PRAGMA INLINE (l_inline_reqd, 'YES');
```
```
l_sum := l_sum + l_inline_reqd(i);
```
```
END LOOP;
```
```
END;
```

PL/SQL Function Result Cache

Oracle 11g has allocated a part of SGA dedicatedly for the storage of some frequently used results. This is known as Cache memory and process of caching the results is named as Result Cache. Oracle 11g has enabled this provision in SQL as well as PL/SQL. In SQL, caching is achieved by an optimizer hint (RESULT_CACHE) while in PL/SQL, the RESULT CACHE keyword has to be specified with the stored function.

If a function, marked for result cache, is executed, server restores its result with the set of input parameters and signature of the function and dependent objects like tables or views. Now, on the subsequent invoking of the function for the same set of input parameters, server would fetch the result from cache, instead of re-executing the function body again. This enhances the code performance by almost 40%, if the function involves SQL statements. For pure PL/SQL code, expected results may go high up to 100%.

If the function is invoked with different set of input parameters, Oracle would execute the complete function body, arrive at the results and cache them.

Certain initialization parameters need to be set to enable the feature as listed below.

RESULT_CACHE_MODE – Mode of Result cache [FORCE |MANUAL (default)].
RESULT_CACHE_MAX_SIZE – It is part of SGA allocated for cache memory to store the cached results. If the feature is disabled, its value is zero. It can be set as 0.25% of memory_target parameter value, 0.5% of sga_target parameter value,
or 1% of shared_pool_size.
RESULT_CACHE_MAX_RESULT – Percentage memory allocation for one cache result (default 5%).
RESULT_CACHE_REMOTE_EXPIRATION – Retaining time for the cached result.

Example Code [5]:

The function F_AVG_SAL returns the average salary of a department, whose id is passed as a parameter.

CREATE OR REPLACE FUNCTION F_AVG_SAL (P_DEPTID NUMBER)

```
RETURN VARCHAR2
```
```
RESULT_CACHE
```
```
IS
```
```
L_AVG_SAL NUMBER;
```
```
BEGIN
```
```
SELECT AVG(SALARY )
```
```
INTO L_AVG_SAL
```
```
FROM EMPLOYEES
```
```
WHERE DEPARTMENT_ID=P_DEPTID
```
```
GROUP BY DEPARTMENT_ID;
```
```
RETURN L_AVG_SAL;
```
```
END;
```

Currently, Oracle 11g does not support result caching in anonymous PL/SQL blocks, pipelined table functions, LOBs and collection variables.

Secure Files

Oracle 11g widened the scope of Large Objects on ANSI standards and named them Secure Files, similar to LOB but not the same. Older LOBs would be brought forward as Basic Files. The reason behind different naming convention is to maintain different storage specs associated with them and ease the migration too.

Secure files have upper hand in performance, space consumption, and data object security. Databases using Secure Files could easily be managed in single security model and make use of advanced features like DEDUPLICATION and COMPRESSION. Secure files support both structural as well as unstructured data for storage.

This feature can be enabled through a parameter DB_SECUREFILE as shown in the example syntax [3].

Example Syntax [3]

ALTER SYSTEM SET DB_SECUREFILE = [PERMITTED | FORCE | ALWAYS | IGNORE | NEVER]

A table can be created to store the Secure Files as in Example Syntax

Example Syntax: [4]

```
CREATE TABLE employees
```
```
( [COLUMN] BLOB )
```
```
TABLESPACE tools
```
```
LOB ([COLUMN]) STORE AS SECUREFILE
```
```
(LOB storage parameters);
```

Storage parameters are CHUNK, [ENABLE | DISABLE] STORAGE IN ROW, [CACHE | NOCACHE], RETENTION, PCTVERSION, and [LOGGING | NOLOGGING].

CONTINUE Statement

A new PL/SQL statement, CONTINUE has been inducted into Oracle 11g family to eliminate the use of NULL as PL/SQL construct. It surely improves the logical code structure. Its usage is demonstrated in Example Code [6].

Example Code [6]

```
…
```
```
FOR I IN 1..100
```
```
LOOP
```
```
IF MOD(I,3)=0 THEN
```
```
CONTINUE;
```
```
ELSE
```
```
SUM := SUM + I;
```
```
END IF;
```
```
END LOOP;
```
```
…
```