Where Current OF

• If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.
• When the session opens a cursor with the FOR UPDATE clause, all rowsin the return set will hold row-level exculsive locks. Other sessions can only query the rows, but they cannot update, delete, or select withFOR UPDATE.
• Allows the developer to lock a set of Oracle rows for the duration of a transaction
• Subqueries cannot have this clause
  

Explain Plan

An execution plan defines how Oracle finds or writes the data For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan. SQL statement EXPLAIN PLAN to determines this. The general syntax of EXPLAIN PLAN is:

explain plan for sql-statement;

If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicate which table has to be filled with the following SQL command:

explain plan into table_name for sql-statement;

If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.

The Plan Table
The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN). If you like, however, you can choose any other name for the plan table, as long as you have been granted insert on it and it has all the fields as here.
Option tells more about how an operation would be done. For example, the operation TABLE ACCESS can have the options: FULL or BY ROWID or many others.

Full in this case means, that the entire table is accessed (takes a long time if table is huge) whereas BY ROWID means, Oracle knows where (from which block) the rows are to be retrieved, which makes the time to access the table shorter.

FULL - means that the entire table is accessed.

sql*plus automatically explains the plan for you if autotrace is enabled.

INDEX (RANGE SCAN) - basically means, that the index was used, but that it can return more than one row

INDEX (UNIQUE SCAN) - means, that this index is used, and it sort of guarantees that this index returnes exactly one rowid

NESTED LOOPS - For each relevant row in the first table (driving table), find all matching rows in the other table (probed table).

SORT (AGGREGATE) - Whenever a result set must be sorted, the operation is sort. If this sort is used to return a single row (for example max or min) the options is AGGREGATE

Row Level Security

Oracle Row level security (known as Virtual Private Databse- also known as fine grained access control), which comes as part of Oracle Enterprise Edition. The database can be set up the way that users authorized to access data not only at a table but at the each record level as well.

Transparently modifying requests for data to present a partial view of the tables to the users based on a set of defined criteria.

Oracle Label Security - optional add-on for providing easy to use interface for row-level security. no coding needed. Enables to enfore security, directly on tables, views and synonyms. Allows to define which rows users may have access to.

VPD was introduced in Oracle 8i Version 8.1.5 as a new solution to enfore granular access control
of data at server level.

Oracle 9i Expanded the features.
- Oracle Policy manager
- partitioned fine-grained access control
- global application context
- VPD support of synonyms

Oracle 10g makes the following three major enhancements in VPD

Column level Privacy
It Increases performance by limiting the number of queries that the database rewrites.
rewrite occur when the statement references relevant columns. this feature also leads to more
privacy.

Cutomization
with the introduction of four new types of policies, you can customize VPD to always
enfore the same predicate with a static policy or you can have VPD predicates that change
dynamically with a non-static policy.

Shared Policies
you can apply a single VPD policy to multiple objects, and therfore reduce administration
costs.

Why use VPD

Protect confidential and secret information. Control the delivery of the data to the right people.

VPD Components
- Application context
- PL/SQL Function
- Security Policies

Application Context
Holds Environment variables
- Application name
- USername
Gathers information using dbms_session.set_context

How VPD Works
The virtual private database enabled by associating one or more security policies with tables or views. Direct or indirect access to a table with an attached security policy causes the database to consult a pl/sql function that implements the policy. The policy function returns an access condition known as as predicate (a WHERE Clause), which the database appends to the user's sql statements, thus dynamically modifying the user's data access.
Can implement VPD by writing a stored procedure to append a SQL predicate to each sql statement that controls row level access for that statement.

VPD Policy can be implemented as a pl/sql function. VPD policy function that automatically adds WHERE clause to an incoming select query to limit the data access.

Connect by Clause

• The start with connect by clause can be used to select data that has a hierarchical relationship. Recurse condition can make use of the keyword perior.
• Start with specifies the rows to be identified as a root
• Siblings by preserves any ordering specified in the hierarchical query clause and then applies the order_by_clause to the siblings of the hierarchy

Select Day count Excluding Saturday and Sunday for the Date range

SELECT
COUNT(DECODE(TO_CHAR(TO_DATE('Start Date','DD/MM/YYYY')+LEVEL-1,'D'),7,NULL,1,NULL,1)) FROM DUAL
CONNECT BY LEVEL <= TO_DATE('End Date','DD/MM/YYYY') -TO_DATE('Start Date','DD/MM/YYYY')+1;

Select Numbers without any Base Tables

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100

To_Char Funtion on Date

Translate Function

SELECT TRANSLATE('bernandas', 'abcdefghijklmnopqrstuvxyz', '0123456789qwertyuiop[kjhbv')
FROM DUAL;

Output
------
14ir0r30o


SELECT TRANSLATE('14ir0r30o', '0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvxyz')
FROM DUAL;

Output
------
bernandas

Occurance of a Letter in a String

SELECT LENGTH('BERNADNAS')-NVL(LENGTH(REPLACE('BERNADNAS','A')),0)
FROM DUAL

Regular Expression Patterns.

Regular Expressions have a formal definition as part of the POSIX standard. Different symbols define how a pattern is described. Let’s start with the basic symbols.
Characters and Numbers represent themselves. If you are searching for ‘abc’ then the matching pattern is abc.
Period (.) represents any single character or number. The pattern ‘b.e’ will match bee, bye, b3e but not bei, or b55e. Likewise the pattern ‘..-..=…’ matches any two characters, followed by a dash, followed by any two characters, followed by an equal sign, followed by any three characters.
Star (*) represent zero or more characters. The pattern ‘b.*e will match bee, bye, beee, bzyxe and be. The pattern

‘..-..=.*’ can end with zero or more characters after the equal sign.
Plus (+) represents one or more characters. This pattern is the same a ‘.*’ except that there must be one character. Using the pattern ‘b.+e’ the string “be” will not match.
Question Mark (?) represents zero or one character. The pattern ‘..-..=.?’ can only end with one character or no character after the equal sign.
If I wanted to match a US telephone number, I could use the pattern ‘…-…-….’. This pattern will match any three characters, followed by a dash, followed by 3 more characters, followed by a dash and four final characters. So the string “123-456-7891” will match this pattern. Well so will “abc-def-ghij”. So this simple patter will match a lot of strings that are not phone numbers. We will improve on this pattern in a moment.
Brackets are used to define numbers of characters.
{count} defines an exact number of characters. The pattern a{3) defines exactly three character ‘a’. Used with the period, the {count} defines the number of characters. The phone number example could be written as the pattern

‘.{3}-.{3}-.{4}’.
Note: When used with many applications, the bracket already has a meaning and to use it in a expression is must be escaped, normally with a slash. ‘.\{3\}-\{3\}-\{4\}’ In this example the slash ‘\’ simply escapes the bracket. With Oracle, this is not necessary.
{min,max} defines a minimum and maximum number of characters. The pattern ‘.{2,8}’ will match any 2 or more characters, up to 8 characters.
{min,} defines the minimum or more number of characters. The pattern ‘sto{1,}p’ will match any string that has ‘st’ followed by one or more ‘o’, followed by a ‘p’. This includes stop, stoop, stooooop, but not stp or stoip.
Square Brackets are used to define a subset of the expression. Any one character in the bracket will match the pattern. If I want only a number character then I could use a pattern like ‘[0123456789]’. The phone number pattern could be written as: ‘[0123456789]{3}-[0123456789]{3}-[0123456789]{4}’
With this pattern, I have excluded all the letters from matching strings. A range of characters can also be defined in square brackets. This is easier to type and read. The range is defined using the dash between the min and max. The phone example now becomes: ‘[0-9]{3}-[0-9]{3}-[0-9]{4}’
Ranges of letters can also be defined. The pattern ‘st[aeiou][A-Za-z]’ matches any string with the characters ‘st’ followed by a vowel, followed by any character, upper or lower case. This pattern matches stop, stay, staY, stud. The pattern ‘abc[1-9]’ matches abc1, abc2, abc3,…
The caret [^] in square brackets matches any character except those following the caret. The pattern ‘st[^o]p will match step, strp, but not stop.
So far, all the patterns match is the pattern is found anywhere in the line of text. Use the caret and dollar sign to define patterns that match the start or end of a string.
^ defines that start of a string or column 1 of the string.
$ defines the end of a string or the last column. This does not included carriage returns and line feeds.
The pattern ‘^St[a-z]*’ matches a string that starts with ‘St’ followed by zero or more lower case letters. The pattern ‘stop$’ only matches “stop” if it is the last word on the line. or vertical line defines the Boolean OR. The patter ‘[1-9][a-z]’ matched any number or lower case letter. The pattern ‘stopstep’ matches the strings stop or step. \ or backward slash is the escape character. This is use to tell the parser that the character following it is to be taken literally. In the note earlier, it was pointed out that some characters have special meaning in some applications and must be escaped to tell the application to use that literal character. Another reason to escape a character is when you want to actually use the character in your matching pattern. For example, if you want to match a number that has two decimal places you could use the pattern: ‘[0-9]+.[0-9]{2}’
This example looks right but will not match the pattern that we are looking for. The period we use to represent the decimal place, will actually match any character. We must tell the expression parser that we want the character period and we do that by escaping the period character. ‘[0-9]+\.[0-9]{2}’
Now the pattern will match one or more digits followed by a period and exactly two digits.
Class Operators
Class operators are used as an alternative way to define classes of characters.

They are defined in the format [: class :].

[:digit:] Any digit
[:alpha:] Any upper or lower case letter
[:lower:] Any lower case letter
[:upper:] Any upper case letter
[:alnum:] Any upper or lower case letter or number
[:xdigit:] Any hex digit
[:blank:] Space or Tab
[:space:] Space, tab, return, line feed, form feed
[:cntrl:] Control Character, non printing
[:print:] Printable character including a space
[:graph:] Printable characters, excluding space.
[:punct:] Punctuation character, not a control character or alphanumeric
Again, these class operators represent other characters. The phone number example can be rewritten using class operators. ‘[:digit:]{3}-[:digit:]{3}-[:digit:]{4}’
Being Greedy
Regular expressions are greedy. By this we mean that the expression will match the largest string it can. Think of it as the expression parser takes the entire sting and compares it to the pattern. The parser then gives back characters until it finds that the string has no match or if finds the match.
Lets use a string ‘0123423434’. If my pattern is ‘.*4’ (zero of more characters followed by the digit 4).
The first match will be the entire sting.
Expression Grouping
Expression Grouping allows part of the pattern to be grouped. This is also called tagging or referencing. You group an expression by surrounding it with parens. There can be only 9 groups in a pattern. Below is an example that contains two groups. ‘([a-z]+) ([a-z]+)’
This pattern matches two lower case words. Using a string defined as ‘fast stop’, the first group would contain ‘fast’ and the second group ‘stop’. The groups are referenced by a backward slash and the group number. ‘\1’ references ‘fast’ while ‘\2’ reference ‘stop’. Thus \2 \1 results in ‘stop fast’.

Varchar Vs Varchar2

If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space.
Varchar is of ANSI SQL standart while Varchar2 is of Oracle standard.
Varchar and Varchar2 both are of variable character. Varchar can have MAximum 2000 character while Varchar can contain maximum 4000 character.

VARCHAR(5) is fixed length, right padded with null.
VARCHAR2(5) is variable length.

Char Vs Varchar

The char is a fixed-length character data type, the varchar is a variable-length character data type.

Because char is a fixed-length data type, the storage size of the char value is equal to the maximum size for this column. Because varchar is a variable-length data type, the storage size of the varchar value is the actual length of the data entered, not the maximum size for this column.

You can use char when the data entries in a column are expected to be the same size.
You can use varchar when the data entries in a column are expected to vary considerably in size.

CHAR(5) is fixed length, right padded with spaces.
VARCHAR(5) is fixed length, right padded with null