New PL/SQL Features - Oracle 11g

Oracle includes quite a cast of new features for PL/SQL in Oracle Database 11g.Although the new features don’t offer new functionality that could not be achieved in Oracle Database 10g,albeit with some difficulty,they provide enhanced performance,simpler coding,and greater read ability.

CONTINUE Statement

Finally,PL/SQL has a CONTINUE statement for its loops. Syntactically it is the same as the EXIT statement,and it allows for an optional WHEN clause and label.In the following example,an interface table is queried,and records that are flagged as comments are skipped:

LOOP
FETCH cur BULK COLLECT INTO rows;
EXIT WHEN rows.COUNT=0;
FOR I IN 1..rows.COUNT LOOP
CONTINUE WHEN NOT EVALUATORS.single_line_comment(rows(i));
-- process responsive data
...
END LOOP;
END LOOP;

For comment rows, control moves to the end of the loop, and the next I is processed.

Sequences Without Dual

It is no longer necessary to execute a SELECT statement in PL/SQL when you are retrieving either NEXTVAL or CURVAL from a sequence.The following shows how to access a sequence named my_sequence from within PL/SQL:

DECLARE
new_Val NUMBER;
BEGIN new_Val := my_sequence.nextval;
...
END;
Native Compilation

PL/SQL can now directly create native compiled PL/SQL code.Prior to this,you had to create C code translated from your PL/SQL and compile that manually using the platform’s C compiler.An example of native compilation of a PL/SQL package is shown in the “SIMPLE_ INTEGER” section.

To utilize this feature, DBAs may need to set up a special directory to contain the share libraries.Specific requirements for each platform are given in the installation guide.You can find instructions on setting up the database in the PL/SQL language reference guide.

SQL-intensive PL/SQL programs will not see significant performance improve ments since the SQL is interpreted by the database anyway,but computation-intensive tasks may see an order of magnitude improvement in speed.Not only can you compile the individual PL/SQL modules you create,but you can also compile all the PL/SQL in the database.You’re cautioned, of course, to perform a full backup before attempting this.

SIMPLE_INTEGER

A new datatype, which is a subtype of PLS_INTEGER, has beenincluded.This datatype has the same range,–2,147,483,648 to2,147,483,647,but PLS_INTEGER cannot be null. Also, instead of throwing some overflow or underflow exception when an expression results in values out of its range,variables of this datatype wrap from smallest to largest and from largest to smallest.Thus, incrementing a variable set to the maximum value results in the variable having the minimum value, and decrementing the minimum value results in the maximum value. The following example demonstrates this:

DECLARE
cnt SIMPLE_INTEGER:=-2147483646;
BEGIN
dbms_output.put_line('Decrementing');
FOR I IN 1..4 LOOP
cnt:=cnt-1;
dbms_output.put_line(to_char(cnt,'999,999,999,999'));
END LOOP;
dbms_output.put_line('Incrementing');
FOR I IN 1..4 LOOP
cnt:=cnt+1;
dbms_output.put_line(to_char(cnt,'999,999,999,999'));
END LOOP;
END;

Executing the previous code yields the following results:

Decrementing
-2,147,483,647
-2,147,483,648
2,147,483,647 -- Smallest minus one = Largest
2,147,483,646
Incrementing
2,147,483,647
-2,147,483,648 -- Largest plus one = Smallest
-2,147,483,647
-2,147,483,646

Using this datatype in native compiled PL/SQL will result in a significant performance increase over the same code with PLS_INTEGER used instead.The following package compares SIMPLE_INTEGER with PLS_INTEGER and the NUMBER datatypes:

CREATE OR REPLACE
PACKAGE SI_DEMO AS
PROCEDURE test_si;
PROCEDURE test_pls;
PROCEDURE test_num;
PROCEDURE test;
END;
/
CREATE OR REPLACE
PACKAGE BODY SI_DEMO AS
MAX_COUNT CONSTANT PLS_INTEGER:=10000000; PROCEDURE test_si IS
numVal SIMPLE_INTEGER:=0;
startTime PLS_INTEGER;
BEGIN
startTime:=dbms_utility.get_time;
FOR i IN 1..MAX_COUNT LOOP
numVal:=numVal*2-(numVal+8)*2;
END LOOP;
dbms_output.put_line('SIMPLE_INTEGER elapsed time(seconds):'||
to_char((dbms_utility.get_time-startTime)/100,'990.90'));
END; PROCEDURE test_pls IS
numVal PLS_INTEGER:=0;
startTime PLS_INTEGER;
BEGIN
startTime:=dbms_utility.get_time;
FOR i IN 1..MAX_COUNT LOOP
numVal:=numVal*2-(numVal+8)*2;
END LOOP;
dbms_output.put_line('PLS_INTEGER elapsed time(seconds):'||
to_char((dbms_utility.get_time-startTime)/100,'990.90'));
END; PROCEDURE test_num IS
numVal NUMBER:=0;
startTime PLS_INTEGER;
BEGIN
startTime:=dbms_utility.get_time;
FOR i IN 1..MAX_COUNT LOOP
numVal:=numVal*2-(numVal+8)*2;
END LOOP;
dbms_output.put_line('NUMBER elapsed time(seconds):'||
to_char((dbms_utility.get_time-startTime)/100,'990.90'));
END; PROCEDURE test IS
BEGIN
si_demo.test_si;
si_demo.test_pls;
si_demo.test_num;
END; END;

Compiling the SI_DEMO package as interpreted and executing the test() procedure yields the following:

SIMPLE_INTEGER elapsed time(seconds): 1.60 PLS_INTEGER elapsed time(seconds): 1.75NUMBER elapsed time(seconds): 3.84

Repeated execution of the test() procedure, although not exactly matching the same timing, always resulted in PLS_INTEGER being a little slower than SIMPLE_INTEGER and resulted in NUMBER being more than twice as slow as SIMPLE_INTEGER.

The following compiles the SI_DEMO package into native code:

alter package si_demo compile plsql_code_type=native; Executing yields the following results:

SIMPLE_INTEGER elapsed time(seconds): 0.10
PLS_INTEGER elapsed time(seconds): 0.80
NUMBER elapsed time(seconds): 3.22

The native code runs more than an order of magnitude faster for SIMPLE_INTEGER, twice as fast for PLS_INTEGER,and (not so surprisingly) just a little bit faster for Oracle NUMBER types. Clearly, if you are performing a great deal of integerarithmetic, SIMPLE_INTEGER is a must.

Regular Expression Enhancements

A new argument to REGEXP_INSTR() and REGEXP_SUBSTR() allows you to select the nth subexpression in the regular expression being evaluated. Sub expressions are identified in the regular expression by enclosing that bit of the pattern in parentheses.There is also a new function, REGEXP_COUNT() ,which counts the number of matches.In the following expression, REGEXP_INSTR()is being used to determine the location of the repeated word the in the evaluated string:

SELECT REGEXP_INSTR('When in the the course of human events...' -- source
,'(the) (the)' -– regular expression with two subexpressions
,1 -– starting position to begin evaluation
,1 -– match occurrence
,0 –- 0 = return position of start, 1 = position after end
,'i' -– case insensitive search
,2 –- which subexpression to return position
) dup_loc
from dual; DUP_LOC
----------------------
13

Sube xpressions can be nested.The subexpressions are counted by the left parenthesis from one to a maximum of nine from left to right.The default is zero, which means the substring that matches the entire regular expression is used.In the example, 2 is given, which corresponds to the second the in the evaluated string.As you can see from the example, 13 is returned, which is the position of the t in the second .

In the following example,REGEXP_SUBSTR() is used to retrieve either thetimestamp,the severity code, or the message text from a hypothetical entry in a log file.Lines in the log file have the following form:

<timestamp>: [INFORMATION|WARNING|ERROR]: <text>

So, the regular expression assigned to the following parse_RE variable is constructed to return the relevant piece of the log line based on the subexpression number argument:

DECLARE
log_Line VARCHAR2(256):='01-JAN-2007 13:56:03: ERROR: Network Unreachable';
-- Constants to identify which piece of info is plucked from the line
TS_IND CONSTANT SIMPLE_INTEGER:=1;
SC_IND CONSTANT SIMPLE_INTEGER:=2;
TB_IND CONSTANT SIMPLE_INTEGER:=3;
-- The regular expression used. Three subexpressions.
parse_RE VARCHAR2(4000):='(.*): (INFORMATION|WARNING|ERROR): (.*)';
event_timestamp VARCHAR2(4000);
event_severity VARCHAR2(4000);
event_text VARCHAR2(4000);
-- Function to improve readability.
FUNCTION get_Match(pInd IN SIMPLE_INTEGER) RETURN VARCHAR2 IS
BEGIN
RETURN REGEXP_SUBSTR(log_Line,parse_RE,1,1,'i',pInd);
END;
BEGIN
pragma INLINE(get_Match,'YES'); -- request inlining
event_timestamp:=get_Match(TS_IND);
event_severity:=get_Match(SC_IND);
event_text:=get_Match(TB_IND);
dbms_output.put_line('Severity Level: '||event_severity);
dbms_output.put_line('Timestamp: '||event_timestamp);
dbms_output.put_line('Text: '||event_text);
END;

Executing yields the following:

Severity Level: ERROR
Timestamp: 01-JAN-2007 13:56:03
Text: Network Unreachable

A function,get_Match(),is declared to make the code easier to write and understand.The pragma INLINE(),which will be covered later in the “ Sub program Inlining” section,ensures that performance is not sacrificed for the sake of readability. REGEXP_SUBSTR() is called three times with different values for the subexp argument. In the first call,1 is passed as the subexp number argument,and the text matching the first (.*) in the regular expression is returned. Likewise,for the second and third call,the matching text for(INFORMATION| WARNING| ERROR)and (.*) is returned accordingly.

REGEXP_COUNT() has been added to simply count the number of matches a regular expression has with the source text.The following counts the number of one-digit numbers found in a piece of text:

SELECT REGEXP_COUNT('In the sample only 1 person in 8 8 crackers',-- source text
'[0-9]' -- regular expression
,1 -- starting position
,'i' -- ignore case
) BAD_ONES
FROM dual
Executing yields the following:
BAD_ONES
----------------------
3

The starting position and case-insensitive arguments were included for completeness.

Named and Mixed Arguments in SQL Statement PL/SQL Function Calls

The use of NAME=>value is now supported in PL/SQL function calls that are contained in expressions in SQL statements.So for example, all of the following SELECT statements are now valid:

SELECT VENDORS_PKG.vendor_Id(vendor_name,organization_id)
FROM VENDORS;

SELECT VENDORS_PKG.vendor_Id(vendor_name,pOrgId=>organization_id)
FROM VENDORS;

SELECT VENDORS_PKG.vendor_Id(pVendorName=>vendor_name,pOrgId=>organization_id)
FROM VENDORS;

Subprogram Inlining

Inlining in PL/SQL is an optimization where the PL/SQL compiler replaces calls to subprograms(functions and procedures) with the code of the subprog rams.A performance gain is almost always achieved because calling a subprogram requires the creation of a callstack entry,possible creation of copies of variables,and the handling of return values.With inlining,those steps are avoided.

As an example,consider the following, where the PL/SQL optimization level is explicitly set to 2 for reasons explained later,and then an anonymous PL/SQL block is executed:

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2;

DECLARE
PROCEDURE wr(pStr IN VARCHAR2) IS
BEGIN
dbms_output.put_line(rpad(lpad(pStr,15,'='),30,'='));
dbms_output.put_line(dbms_utility.format_call_stack());
END;
BEGIN
wr('At Start');
wr('Done');
END;

The procedure WR() in this block prints the passed-in string as well as the current call stack. Running this outputs the following:

=======At Start===============
----- PL/SQL Call Stack -----
object line object
handle number name
0x44b90390 5 anonymous block
0x44b90390 8 anonymous block
===========Done===============
----- PL/SQL Call Stack -----
object line object
handle number name
0x44b90390 5 anonymous block
0x44b90390 9 anonymous block

This is what you would expect—two calls to WR() on lines 8 and 9,with the call to dbms_utility. format_call_stack() on line 5 within the WR() procedure. Now,change PLSQL_ OPTIMIZE_LEVEL to 3, which is the level that includes automatic inlining, and let’s try that anonymous block again:

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=3;
DECLARE
....
END;
=======At Start===============
----- PL/SQL Call Stack -----
object line object
handle number name
0x45d309a4 8 anonymous block
===========Done===============
----- PL/SQL Call Stack -----
object line object
handle number name
0x45d309a4 9 anonymous block

This is different. The PL/SQL optimizer has moved the code executed in WR() to the main body of the anonymous PL/SQL block.Although the performance increase in this example is negligible, the readability and maintainability of this code is superior to the case where WR() is not used and four DBMS_OUTPUT.PUT_LINE() are called instead.The example given for REGEXP_SUBSTR() would be an example where performance improvements could be realized.

The PLSQL_OPTIMIZE_LEVEL parameter has a default value of 2, which means inlining will occur only if the correct pragma INLINE directives are included in your code.Since it’s possible your code will be maintained by someone who is unaware of inlining,it is a good practice to include this directive in your code for particularly critical sections where performance would suffer if inlining were not done.The following is the same anonymous block as before with two pragma directives added.The first requests inlining, and the second requests that inlining not be done.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2;
DECLARE
PROCEDURE wr(pStr IN VARCHAR2) IS
BEGIN
dbms_output.put_line(rpad(lpad(pStr,15,'='),30,'='));
dbms_output.put_line(dbms_utility.format_call_stack());
END;
BEGIN
pragma inline(wr,'YES');
wr('At Start');
pragma inline(wr,'NO');
wr('Done');
END;

Executing yields the following:

=======At Start===============
----- PL/SQL Call Stack -----
object line object
handle number name
0x44b0b99c 9 anonymous block
===========Done===============
----- PL/SQL Call Stack -----
object line object
handle number name
0x44b0b99c 5 anonymous block
0x44b0b99c 11 anonymous block

As you can see,the first pragma directive instructed the compiler to replace the call to WR() with the inline version,while the second pragma directive disabled inlining for the same subprogram.If the second pragma directive were not there,then the compiler would inline the second call, as you can test by removing the second pragma directive.

pragma INLINE(identifier,mode)

There are two arguments to the pragma INLINE compiler directive,as described in Table.

pragma INLINE(identifier,mode)

PLSQL_OPTIMIZE_LEVEL Allowed Values

The PLSQL_OPTIMIZE_LEVEL initialization parameter specifies the level ofoptimization used to compile the PL/SQL library unit. Table describes the PLSQL_OPTIMIZE_LEVEL allowed values.

PLSQL_OPTIMIZE_LEVEL Allowed Values

You can modify this parameter at the system level or at the session level.DBAs can use the ALTER SYSTEM command to set this level.In the following example, we will set PLSQL_ OPTIMIZE_LEVEL to 3 for automatic inlining of subprograms:

System altered.

Scope and Usage

Inlining may occur only with subprogram calls that reside in the same package, procedure, function,or anonymous PL/SQL block.The pragma INLINE directive,if present,must appear immediately before the subprogram call.When inlining subprograms in nonanonymous modules,the compiler can provide a number of useful warnings to help you verify that inlining did or did not occur.Table shows several of the possible warning codes and descriptions associated with the warning codes.

Scope and Usage

Generalized Invocation

In prior versions of PL/SQL if you wanted to call an overridden method of a supertype from one of its subtypes,you would have to create a static method in the supertype and create an overrid able wrapper member subprogram that called that static method.Within the subtypes,you would call the static member as needed. Here is an example of this idiom:

CREATE OR REPLACE TYPE SUP_T AS OBJECT(
...
STATIC PROCEDURE super_init,
MEMBER PROCEDURE init
)
NOT FINAL; CREATE OR REPLACE TYPE BODY SUP_T AS
STATIC PROCEDURE super_init IS
BEGIN
-- actual init code
...
END;
MEMBER PROCEDURE init IS
BEGIN
-- the wrapper procedure
...
super_init;
END;
END; CREATE OR REPLACE TYPE SUB_T UNDER SUP_T(
...
OVERRIDING MEMBER PROCEDURE init
)
CREATE OR REPLACE TYPE BODY SUB_T AS
OVERRIDING MEMBER PROCDURE init IS
BEGIN
super_init;
...
END;
END;

Although this works,the programmer would much rather cast SELF to the correct supertype and call the method directly.With Oracle Database 11g,this functionality has been added to PL/SQL.For the previous example, the syntax for calling init() in SUP_T from SUB_T is as follows:

(SELF AS SUP_T).init();
init(SELF AS SUP_T);

The first essentially casts SELF to SUP_T and calls its init() procedure, while in the second, init(SELF AS SUP_T),the compiler selects the correct init() procedure based on the implicit first argument,SELF.We prefer the first style because it appears more like casting than overloading. This syntax is not limited to within method bodies of subtypes only.The pattern is as follows:

(<variable> AS <parent type>)
where <variable> is a variable instantiated to some type at or below the <parent type> in its type hierarchy.

The following example utilizes this new feature (highlighted in bold)in the simple hierarchy of PL/SQL subprograms and their implementations as either functions or procedures:

CREATE OR REPLACE type plsql_subprogram_t as OBJECT (
name VARCHAR2(30),
description VARCHAR2(256),
MEMBER PROCEDURE print_doc)
NOT FINAL NOT INSTANTIABLE; CREATE OR REPLACE TYPE BODY plsql_subprogram_t AS
MEMBER PROCEDURE print_doc IS
BEGIN
dbms_output.put_line('Name: '||name);
dbms_output.put_line('Description: '||description);
END;
END; CREATE OR REPLACE TYPE plsql_procedure_t UNDER plsql_subprogram_t (
overriding member PROCEDURE print_doc)
FINAL; CREATE OR REPLACE TYPE BODY plsql_procedure_t AS
OVERRIDING MEMBER PROCEDURE print_doc IS
BEGIN
dbms_output.put('PROCEDURE ');
(self as plsql_subprogram_t).print_doc();
END;
END; CREATE OR REPLACE TYPE plsql_function_t UNDER plsql_subprogram_t (
returning_type VARCHAR2(256),
overriding member PROCEDURE print_doc)
FINAL; CREATE OR REPLACE TYPE BODY plsql_function_t AS
OVERRIDING MEMBER PROCEDURE print_doc IS
BEGIN
dbms_output.put('FUNCTION ');
(self as plsql_subprogram_t).print_doc();
dbms_output.put_line('Returning: '||returning_type);
END;
END;

A noninstantiable type plsql_subprogram_t object is created with the single member procedure print_doc() that prints the subprogram name and description.Since the subprogram is an abstract concept implemented as either a function or a procedure, it is declared NOT INSTANTIABLE.

plsql_procedure_t is created as a subtype of plsql_subprogram_t and overrides the print_doc() member procedure to output the text “PROCEDURE” before calling the plsql_ subprogram_t print_doc() member procedure to print the name and description.

plsql_function_t is created similarly as plsql_procedure_t except that it includes a returning_type attribute that identifies the datatype returned from the function.The print_doc() member procedure includes this as well as identifying itself as a function and calling the plsql_subprogram_t print_doc() member procedure.

The following example demonstrates these objects in use:

DECLARE
TYPE subarr_t IS TABLE OF plsql_subprogram_t INDEX BY PLS_INTEGER;
subs subarr_t;
BEGIN
subs(1):=plsql_function_t('UPPER'
,'Folds lowercase characters in passed in string to uppercase.','VARCHAR2');
subs(2):=plsql_procedure_t('DBMS_OUTPUT.PUT'
,'Writes characters to internal dbms_output buffer');
FOR i IN 1..subs.COUNT LOOP
subs(i).print_doc();
dbms_output.new_line;
END LOOP;
END;

Executing yields the following:

FUNCTION Name: UPPER
Description:Folds lowercase characters in passed in string to uppercase.
Returning: VARCHAR2 PROCEDURE Name: DBMS_OUTPUT.PUT
Description: Writes characters to internal dbms_output buffer

As you can see,plsql_subprogram_t print_doc() was called using this new language element and augmenting the subtype implementations of print_doc().

Cross-Session PL/SQL Function Result Cache

This new feature allows the developer to request that the result of a PL/SQL function call be cached in the SGA and returned from the cache if the same arguments are passed to the function in future calls.

Functions that perform time-consuming queries on rarely modified tables are ideal candidates for this feature.As shown in the following example,the keyword RESULT_CACHE is added both to the function in the package specification as well as in the package body. Cached functions do not need to be declared in the package specification; they can be private functions.However, if they are public functions, they must include the RESULT_CACHE keyword.

CREATE PACKAGE FCACHE_DEMO IS
FUNCTION GMOD(pNumb IN BINARY_INTEGER)
RETURN BINARY_INTEGER
RESULT_CACHE;
PROCEDURE TEST;
END;
/
PACKAGE BODY FCACHE_DEMO IS
FUNCTION GMOD(pNumb IN BINARY_INTEGER)
RETURN BINARY_INTEGER
RESULT_CACHE
IS
BEGIN
dbms_output.put_line('GMOD: '||pNumb);
RETURN pNumb*pNumb;
END;
PROCEDURE TEST IS
ret BINARY_INTEGER:=0;
BEGIN
FOR i IN 1..1000 LOOP
ret:=GMOD(MOD(ret+i,10));
END LOOP;
END;
END;
/

When executing FCACHE_DEMO.TEST(), the following ten lines are produced:

GMOD: 1
GMOD: 3
GMOD: 2
GMOD: 8
GMOD: 9
GMOD: 7
GMOD: 6
GMOD: 4
GMOD: 5
GMOD: 0

Usually, you would expect a very long list, but this list has only ten lines. From this you can see that the function body for GMOD() is executed a total of ten times (cache misses),while within the TEST() procedure GMOD() is called 1,000 times with a total of 990 cache hits.

This feature is handy when coupled with rarely changing tables.An additional clause, RELIES_ON(),can be inserted after the RESULT_CACHE keyword to identify tables and views that, if updated, will cause the function’s cache entries to become invalid.Recompiling the function or the function’s package will also invalidate the cache.

The following code creates a table, VENDORS, and inserts a couple of rows into it.A cached function that relies on records in the VENDORS table is then created.This function looks up a vendor’s VENDOR_ID when passed in the vendor’s name.

To identify function calls that are cached vs.those that are not,a
DBMS_OUTPUT.PUT_LINE() is included in the function’s body.Oracle recommends for cached functions that you do not include such “side effects” because the function’s cache-hit behavior is not identical to the cache-miss behavior, but for proving that caching is working, as we are doing, inclusion is ideal.

CREATE TABLE vendors(vendor_id INTEGER NOT NULL primary key, vendor_name VARCHAR2 (80) NOT NULL UNIQUE);

insert into vendors values(1,'The Cookie Place');
insert into vendors values(2,'Tasty Pastries');

CREATE OR REPLACE FUNCTION find_Vendor_Id(pVendorName IN VARCHAR2)
RETURN vendors.vendor_id%TYPE
RESULT_CACHE RELIES_ON(vendors) IS
CURSOR c_vendors(pName IN VARCHAR2) IS
SELECT vendor_id
FROM vendors
WHERE vendor_name=pName;
ret_Id vendors.vendor_id%TYPE;
try_Name vendors.vendor_name%TYPE;
BEGIN
dbms_output.put_line('cache miss: '||pVendorName);
OPEN c_vendors(pVendorName);
FETCH c_vendors INTO ret_Id;
CLOSE c_vendors;
RETURN ret_Id;
END;

The following anonymous block is then used to exercise this function anddemonstrate when the caching is used:

DECLARE
try_Number SIMPLE_INTEGER:=0;
PROCEDURE TRY_IT(pNote IN VARCHAR2,pVendorName IN VARCHAR2) IS
BEGIN
try_Number:=try_Number+1;
dbms_output.put_line(rpad(try_Number||'.',4)||pNote);
dbms_output.put_line(rpad('=',40,'='));
dbms_output.put_line('Calling find_Vendor_Id with: '||pVendorName);
dbms_output.put_line('Returned Vendor_id : '||find_Vendor_Id(pVendorName));
dbms_output.new_line;
END;
BEGIN
TRY_IT('First Call for "Tasty Pastries"','Tasty Pastries');
TRY_IT('Second Call for "Tasty Pastries", no cache miss','Tasty Pastries');
TRY_IT('First Call "The Cookie Place"','The Cookie Place');
INSERT INTO vendors(vendor_id,vendor_name) VALUES(4,'Curiosity, A');
TRY_IT('After INSERT INTO vendors, Call for "Tasty Pastries",
notice cache miss','
TRY_IT('After INSERT INTO vendors, Call for "Tasty Pastries",
notice second cache ,'The Cookie Place');
TRY_IT('First Call for inserted "Curiosity, A", an uncommitted record'
,'Curiosity, A');
TRY_IT('Second Call for inserted "Curiosity, A",
an uncommitted record with a seco ,'Curiosity, A');
ROLLBACK;
TRY_IT('After ROLLBACK, Call or "The Cookie Place", notice cache hit'
,'The Cookie Place');
END;

Executing this anonymous block results in the following output:

1. First Call for "Tasty Pastries"
========================================
Calling find_Vendor_Id with: Tasty Pastries
cache miss: Tasty Pastries
Returned Vendor_id : 2 2. Second Call for "Tasty Pastries", no cache miss
========================================
Calling find_Vendor_Id with: Tasty Pastries
Returned Vendor_id : 2 3. First Call "The Cookie Place"
========================================
Calling find_Vendor_Id with: The Cookie Place
cache miss: The Cookie Place
Returned Vendor_id : 1 4. After INSERT INTO vendors, Call for "Tasty Pastries", notice cache miss
========================================
Calling find_Vendor_Id with: The Cookie Place
cache miss: The Cookie Place
Returned Vendor_id : 1 5. After INSERT INTO vendors, Call for "Tasty Pastries", notice second cache miss
========================================
Calling find_Vendor_Id with: The Cookie Place
cache miss: The Cookie Place
Returned Vendor_id :1 6.First Call for inserted "Curiosity, A", an uncommitted record
========================================
Calling find_Vendor_Id with: Curiosity, A
cache miss: Curiosity, A
Returned Vendor_id : 4 7. Second Call for inserted "Curiosity, A", an uncommitted record with a second
cache miss
========================================
Calling find_Vendor_Id with: Curiosity, A
cache miss: Curiosity, A
Returned Vendor_id : 4 8. After ROLLBACK, Call or "The Cookie Place", notice cache hit
========================================
Calling find_Vendor_Id with: The Cookie Place
Returned Vendor_id : 1

Table Code Execution Explanation

Table Code Execution Explanation

Tries #4 through #7 illustrate an important point about cached functions: to ensure that a session sees its uncommitted changes,caching is bypassed when the session has made changes to the tables identified in the RELIES_ON() clause.So as a general rule,do not rely on cached functions in subprograms that maintain the tables on which the cached functions rely.

What Does “Cross-Session” Mean?

The return values cached for function calls are available to all sessions that have EXECUTE privilege for this function.This means once a function call is cached,it remains cached for any future session that calls the function with a matching set of arguments.

Security Considerations

Since the cross-session caching mechanism will return cached values based solely on matching the passed-in arguments regardless of any special context processing,the existence of a virtual private database,or the security testing performed within the body of the function,functions that return sensitive data should probably not be created as public cached functions.

If done,it is possible for someone without the appropriate privileges to view data they have no business seeing.Oracle’s documentation shows a way of passing a user’s context into a function to allow the caching of context-sensitive function calls.

This method uses an argument with a default value being the value returned from a call to SYS_CONTEXT().Although this works,there is nothing to prevent a malefactor from supplying a value for that default value and thus retrieving contextsensitive data that they might not be approved to view.

Since cached functions do not need to be public functions,sensitive but time-intensive data can be cached by creating a private cached function (a function that appears only in the package body) and a noncached function that checks the caller’s security context, called the private cached function,and returns the correct values as required by the security context.

Restrictions and Limitations

The following types of functions cannot be cached:

  • No functions that have OUT or IN OUT arguments (which is bad form for functions anyway).
  • No functions declared with invoker rights;the values returned from a function declared with AUTHID CURRENT_USER could vary according to the user.
  • No functions defined in an anonymous block.
  • No pipeline table functions.
  • No functions with BLOB, CLOB, NCLOB, or ref cursor arguments.
  • No functions with record, collection, or object arguments.
  • The return type is not a BLOB, a CLOB, an NCLOB, a ref cursor,an object, or a record or collection that contains one or more of the preceding types.

Bulk In-Bind Table of Records in FORALL

Before Oracle Database 11g,attempts to use a table of records,as shown here,would result in the following error:

DECLARE
CURSOR c1 IS SELECT rowid,bar FROM foo ;
TYPE rows_t IS TABLE OF c1%ROWTYPE;
rows rows_t;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO rows;
-- modify bar attribute of rows table
FORALL I IN rows.COUNT
UPDATE foo
SET bar=rows(i).bar
WHERE rowid=rows(i).rowid;
...
END;
/

The output of the previous PL/SQL block produces the following error:

PLS-00436: implementation restriction:
cannot reference fields of BULK In-BIND table of records

PL/SQL did not support tables of records,which is why we used rows(i).bar and rows(i). rowid, while a more natural syntax was not allowed.The workaround was to create separate collections for each column, as shown here:

DECLARE
CURSOR c1 IS SELECT rowid.bar from foo;
TYPE rowID_t IS TABLE OF ROWID;
TYPE bar_t IS TABLE of foo.bar%TYPE;
rids rowId_t;
bars bar_t;
...
BEGIN
...
FETCH c1 BULK COLLECT INTO rids,bars;
....
FORALL I IN rows.COUNT
UPDATE foo
SET bar=bars(i)
WHERE rowid=rids(i);

Creating separate tables for each column returned from a SELECT statement made writing such code tedious and would encourage the programmer to not bother with bulk collects in cases where FORALL statements would be later used with that data,especially since with fewer lines of code the programmer could use a cursor FOR loop and achieve the desired brevity and clarity at the expense of performance.

With this release of PL/SQL, the restriction on tables of records in bulk operations has been lifted.This significant change means you can now use the more natural table of records within FORALL statements.In the following example, a small table is created and populated with ten rows. The anonymous PL/SQL block then immediately fetches all the rows and updates them using this new functionality (highlighted in bold):

create table nums(numb number,ita varchar2(256))
/
declare
TYPE nums_t IS table of nums%ROWTYPE;
ns nums_t:=nums_t();
begin
-- create new records
for i in 1..10 loop
ns.extend;
ns(ns.last).numb:=i;
ns(ns.last).ita:='Insert='||i;
end loop;
-- insert into table using forall
FORALL I in 1..ns.count
INSERT INTO nums values ns(i);
-- fetch them back
SELECT * BULK COLLECT INTO ns FROM nums;
-- update collection.
FOR I IN 1..ns.count LOOP
ns(i).ita:='Update='||i;
END LOOP;
-- update table.
FORALL I IN 1..ns.count
UPDATE nums
SET ita=ns(i).ita
WHERE numb=ns(i).numb;
end;
/
select * from nums
/

The following errors are emitted when the preceding code is run on Oracle Database 10g:

Error report:
ORA-06550: line 22, column 13:
PLS-00436: implementation restriction: cannot reference fields of BULK
In-BIND tableORA-06550: line 22, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 23, column 16:
PLS-00436: implementation restriction: cannot reference fields of BULK
In-BIND table...<other peg-legging errors>

On Oracle Database 11g, this is the result:

NUMB ITA
------- --------------
1 Update=1
2 Update=2
3 Update=3
4 Update=4
5 Update=5
6 Update=6
7 Update=7
8 Update=8
9 Update=9
10 Update=10

The update was performed,and it was not necessary to declare separate variables for numb and ita.This is a fine improvement,especially with tables that have hundreds of columns.

Dynamic SQL Symmetry and New Functionality

The package DBMS_SQL and the native dynamic SQL in PL/SQL have been changed to support the mixing of the two in PL/SQL.For example,you can use DBMS_SQL to parse and execute a SQL statement with an arbitrary number of bind variables and convert that cursor into a ref cursor for use with native dynamic SQL statements such as FETCH-BULK COLLECT and the like.

Likewise,the ref cursor created with an OPEN-FOR statement may be converted to a cursor for use in DBMS_SQL subprograms such as DBMS_SQL.FETCH_ROWS();the only restriction on these conversions is that they are one-way.Once you convert away from its defining type, you cannot convert a cursor back.

This means you will need to close the cursor using the target type’s close mechanism (CLOSE or DBMS_SQL.CLOSE_CURSOR())as required.The following example is a function that creates a ref cursor on the table POLY_ORDER_HEADERS,based on a variable number of passed-in conditionals. This function uses DBMS_SQL to create a cursor, parse it, bind variables with their correct datatypes, and execute the resultant cursor.The DBMS_SQL cursor ID is converted to the correct ref cursor and returned from the function.

CREATE OR REPLACE package poly_dyn_query AS
TYPE order_cursor_t IS REF CURSOR RETURN poly_order_headers%ROWTYPE;
TYPE conditional_rec_t IS RECORD
(column_name VARCHAR2(30),
operator VARCHAR2(10),
val varchar2(32000));
TYPE conditionals_t IS TABLE OF conditional_rec_t;
FUNCTION get_Matching_Orders(pConds conditionals_t) RETURN order_cursor_t;
end;
/
CREATE OR REPLACE package body poly_dyn_query AS
FUNCTION get_Matching_Orders(pConds conditionals_t)
RETURN order_cursor_t IS
sqlStmt CLOB;
cursorId NUMBER;
idx PLS_INTEGER;
ret PLS_INTEGER;
whereOp VARCHAR2(30):=' WHERE ';
retCur order_cursor_t;
strval VARCHAR2(3200);
numVal NUMBER;
dateVal DATE;
descTab DBMS_SQL.DESC_TAB;
colCnt PLS_INTEGER;
colType PLS_INTEGER;
FUNCTION get_Column_Type(pColumnName IN VARCHAR2)
RETURN INTEGER IS
BEGIN
FOR i IN 1..colCnt LOOP
IF descTab(i).col_name=pColumnName THEN
return descTab(i).col_type;
END IF;
END LOOP;
RETURN NULL;
END;
BEGIN
sqlStmt:='SELECT * FROM poly_order_headers';
cursorId:=DBMS_SQL.open_cursor;
idx:=pConds.FIRST;
WHILE idx IS NOT NULL LOOP
sqlStmt:=sqlStmt||whereOp||pConds(idx).column_name||
pConds(idx).operator||':BIND'||idx;
IF idx=pConds.FIRST THEN
whereOp:=' AND ';
END IF;
idx:=pConds.NEXT(idx);
END LOOP;
DBMS_SQL.parse(cursorId,sqlStmt,DBMS_SQL.NATIVE);
-- describe columns so right datatype can be bound
-- to the bind variable
DBMS_SQL.describe_columns(cursorId,colCnt,descTab);
idx:=pConds.FIRST;
WHILE idx IS NOT NULL LOOP
colType:=get_Column_Type(pConds(idx).column_name);
IF colType=2 THEN
numVal:=to_number(pConds(idx).val);
DBMS_SQL.BIND_VARIABLE(cursorId,'BIND'||idx,numVal);
ELSIF colType=12 THEN
dateVal:=to_date(pConds(idx).val,'YYYYMMDD hh24:MI:SS');
DBMS_SQL.BIND_VARIABLE(cursorId,'BIND'||idx,dateVal);
ELSE -- charaters and anything else
DBMS_SQL.BIND_VARIABLE(cursorId,'BIND'||idx,pConds(idx).val);
END IF;
idx:=pConds.NEXT(idx);
END LOOP;
ret:=DBMS_SQL.execute(cursorId);
retCur:=DBMS_SQL.TO_REFCURSOR(cursorId);
return retCur;
END;
END;
/

The following is a short anonymous PL/SQL block that exercises the get_Matching_Orders() function:

declare
rec poly_order_headers%ROWTYPE;
cur POLY_DYN_QUERY.order_cursor_t;
conds POLY_DYN_QUERY.conditionals_t:=POLY_DYN_QUERY.conditionals_t();
idx SIMPLE_INTEGER:=0;
begin
conds.extend;
idx:=conds.last;
conds(idx).column_name:='ORDER_TYPE';
conds(idx).operator:='=';
conds(idx).val:='CREDIT_CARD';
conds.extend;
idx:=conds.last;
conds(idx).column_name:='ORDER_TOTAL_AMT';
conds(idx).operator:='>';
conds(idx).val:='100';
conds.extend;
idx:=conds.last;
conds(idx).column_name:='ORDER_DATE';
conds(idx).operator:='>=';
conds(idx).val:='20070501 00:00:00';
conds.extend;
idx:=conds.last;
conds(idx).column_name:='ORDER_DATE';
conds(idx).operator:='<=';
conds(idx).val:='20070601 00:00:00';
cur:=POLY_DYN_QUERY.get_Matching_Orders(conds);
LOOP
FETCH cur into rec;
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line(rec.header_id||' '||rec.order_total_amt);
END LOOP;
CLOSE cur;
end;

Note the following conditionals have been created:

  • ORDER_TYPE='CREDIT CARD'
  • ORDER_TOTAL_AMT>100
  • ORDER_DATE>= 1 May 2007
  • ORDER_DATE <= 1 Jun 2007

Executing yields the following:

15330 248
15333 288
15148 250
15149 192
15150 198
15151 208
15155 192
15426 132
15433 172
....

The results are,indeed,a subset of the orders actually residing in poly_order_headers for the month of May.Adding the following line:

idx:=dbms_sql.to_cursor_number(cur);

before the CLOSE cur call, as shown in this excerpt:

EXIT WHEN cur%NOTFOUND;
dbms_output.put_line(rec.header_id||' '||rec.order_total_amt);
END LOOP;
idx:=dbms_sql.to_cursor_number(cur);
CLOSE cur;
end; causes the following error to be thrown: ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SQL", line 2508
ORA-06512: at line 35
01001. 00000 - "invalid cursor"
*Cause:
*Action:

This demonstrates that attempting to convert a cursor to its starting form will now work. OPEN-FOR,EXECUTE IMMEDIATE and DBMS_SQL.parse() all now accept CLOB arguments,so the former 32KB limit has be lifted.The get_Matching_Orders() function described earlier demonstrates this new functionality.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 11g Topics