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.
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
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
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.
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
Executing the previous code yields the following results:Decrementing
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
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
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
Executing yields the following:Severity Level: ERROR
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
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:
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
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===============
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;
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;
Executing yields the following:=======At Start===============
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.
There are two arguments to the pragma INLINE compiler directive,as described in Table.
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.
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.
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(
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();
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>)
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 (
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
Executing yields the following:FUNCTION Name: UPPER
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
When executing FCACHE_DEMO.TEST(), the following ten lines are produced:GMOD: 1
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');
The following anonymous block is then used to exercise this function anddemonstrate when the caching is used:DECLARE
Executing this anonymous block results in the following output:1. First Call for "Tasty Pastries"
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.
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:
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
The output of the previous PL/SQL block produces the following error:PLS-00436: implementation restriction:
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
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))
The following errors are emitted when the preceding code is run on Oracle Database 10g:Error report:
On Oracle Database 11g, this is the result:NUMB ITA
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
The following is a short anonymous PL/SQL block that exercises the get_Matching_Orders() function:declare
Note the following conditionals have been created:
Executing yields the following:15330 248
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;
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.
Oracle 11g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 11g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 11g Tutorial
Installing, Upgrading, And Managing Change
Database Diagnosability And Failure Repair
Backup And Recovery
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.