Own Preparation about plsql Version

Database Objects:

Synonyms:

A synonym is Alias for any Database object. We can create synonym for all the DB objects.

Syntax:

Create synonym syn1 for emp;

• We can do any DML operation.
• We cannot do DDL operation in Synonym except Drop command.
• Once we created synonyms on some other table, the link will be there between synonym and the table. If we dropped the table, the synonym is no longer valid. However, if we recreated the table with the same name. The link will be created again with the existing synonym.

Example:

• By using the query we can create a Synonym.

Create synonym syn1 for emp;

• By using the below query, we can able to see the data:
  Select * from syn1 or select * from emp; //Both the queries are same
• Dropping the table

Drop table emp; //The emp table will be dropped from the DB.

• Now synonym will no longer valid. It will be an inactive state.
• If we recreated the table with the same name as emp, the synonym will be activate again and mapped with the new emp table.

Types of Synonym:

1. Public Synonym
2. Private Synonym

Public Synonym:

DBA have access to create Public Synonym

Syntax:

Create public synonym syn1 for emp;

Private Synonym:

Creating the synonym by using them username & Password.

Syntax: Create synonym syn1 for emp;

• If public synonym name as SYN1 and private synonym name as SYN1, if we run the query the private synonym will run in the DB.
• If we would like to access public synonym, we should drop the private synonym 1^st to access public synonym.

Rownum:

Rownums are Result set number for the Database query. It will changes result set by result set.

Example: select rownum,id,name from mouse;

ROWNUM	ID	NAME
1	1004	Ramesh
2	1005	Barath
3	1037	YYY
4	1001	Vijay

ROWNUM	ID	NAME
1	1001	Vijay

select rownum,id,name from mouse where id=1001

To find Nth Max Salary Values:

select min(sal) from (select * from mouse order by sal desc) where rownum<=n;

While executing the above query Rownum will assign the value as below. The query will be checking the data one by one to find out the requested data. However, when it is reaching the value as 0, the rownum condition will be failed and quit to checking. For that reason we are using <= symbol, while fetching the data.

Data	Rownum Assigning theValue as
1	1
2	0
3	1
4	0 etc.,

Row Id:

• Rowids are Physical address of the row. Pseudo column.
• Primary key will be defined internally.

Inline View:

The result of the query is converting as a table and the values are assigned in where condition.

Example:

To fetch Even Record data.

Select * from (select rownum rn, e.* from mouse e)rslt where mod(rslt.rn,2)=0

To Fetch Odd Record data.

Select * from(select rownum rn, e.* from mouse e)rslt where mod(rslt.rn,3)=0

SEQUENCES:

Sequences are used to generate primary key values automatically. Once the sequences are generated, it should not rollback.

Syntax:

Create Sequence seq1
Increment by 1
Starts with 1
min value 0
max Value 1000
Cycle/no cycle // The process whether the cycle process or not, it will be decided here
Cache/no cache //

Increment by:

Specify the interval between sequence numbers. Increment by should not be 0. The value must be less than the MIN value and MAX value. If increment by value is positive, the ascending process will be happen. If the value is negative then the descending process will be happen.

Starts with:

Specify the first sequence number to be generated. The start with value should >= MIN value and < MAX value.

Min Value:

MINVALUE must be <= START WITH and < MAXVALUE.

Cycle:
Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value.

NoCycle:

Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.

Cache:

Specify how many values of the sequence the database preallocates and keeps in memory for faster access. Cache value should be less than the start with value.

Alter the sequence:

Alter sequence seq_name
increment by 124;

Properties:currval, nextval

Using Sequences through query:

• Select sequence_name.currval from dual;
• Insert into mouse(id,name) values (sequence_name.nextval,’Vijayan’);

Points to remember:

• If the sequence condition reaches maximum value, it should go to minimum value for the next cycle.
• If you used “Select seq1.currval, seq1.nextval from dual” or “Select seq1.nextval, seq1.currval from dual”. It will provide the result of nextval result of both the columns. For example, if the sequence value is 4, when we are using the above query the output will give the result of currval5, nextval5.

View:

A view is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used.

If we written 1000 lines of query, every time we should not type all those lines in DB while executing. For that purpose we are creating a view. We can simply call view, whenever we need to execute those 1000 lines query.

Syntax:

Create view v1 as select id, name from mouse;

Types of View:

1. Normal View
   1. Simple View
   2. Complex View
2. Materialized View

We can see the view in all_views table.

Query:

Select text from all_views where view_name=’v1’;

• If anything inserted after view created, the data will not fetch by the view
• Once the view created, we should not drop any data from the database. If you do so, it will throw an error.
• We can do DML operation through view, if not null column (primary key) should be added while creating the view.
• By using the view, we will not store any data. Since view is a Virtual table.
• If we need to delete or update any data, the view should contain the data.

• The updating process will be effect immediately in the database, since direct link with the table.

Simple View:

Create view v1 as select id, name from mouse;

Complex View:

Complex views contain the difficult query, which is written by the user as below

• Joining the tables
• Select count (*), deptno from emp groupby deptno.

Materialized View:

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables, are also known as snapshots.

• Once we are doing any DML operation, the data will not reflect immediately in the database
• The updating process can be done in Materialize view in the Regular Intervals.
• The performance will be improved

Force View:

We can create a view, which the table does not exist in the database by using the force view.

Syntax:

create force view invalid_view as select * from table_that_does_not_exist

A view can be created even if the defining query of the view cannot be executed, as long as the CREATE VIEW command has no syntax errors. We call such a view a view with errors. For example, if a view refers to a non-existent table or an invalid column of an existing table, or if the owner of the view does not have the required privileges, then the view can still be created and entered into the data dictionary.

Advantages:

• Data isn't dependent on structure of the database.
• Restrict access to a database.
• Subset of data contained in a table.
• Join and simplify multiple tables into a single virtual table.
• Acts as aggregated tables, where aggregated data (sum, average etc.) are calculated and presented as part of the data.
• Gives real time data.
• To hide data complexity.
• To protect the data.

Disadvantages:

• Rows available through a view are not sorted and are not ordered either.
• Cannot use DML operations on a View.
• When table is dropped view becomes inactive, it depends on the table objects.
• It affects performance, querying from view takes more time than directly querying from the table

Index

An index is a performance-tuning method of allowing faster retrieval of records.

• Index used to improve the performance.
• Indexed column should be used in the where clause.

Syntax:

Create index index_name on table_name(column_names);

Types of Index:

1. B-Tree Index (Default Index)
2. Bitmap Index
3. Composite Index
4. Unique Index
5. Reverse key Index

B-Tree Index (Default Index):

The column should contain the results of More Distinct (unique) values and less duplicate values.

Create index index_name on table_name(column_names);

Bitmap Index:

The column should contain the results of More duplicate values and less Distinct (unique) values.

create bitmap index i1 on mouse(id);

B-tree indexes are virtually useless on low-cardinality columns (columns with only a few distinct values). By the time 4% or more of a table is read via an index, it would be faster to perform a full table scan. Bitmap Indexes do not solve this problem in general, but they do solve a specific case. If a table contains 2 or more low-cardinality columns and they are used in combination in a SQL statement to reduce the number of rows returned, then bitmap indexes can perform faster than a full table scan.

Use bitmap indexes when:

1. A table has 2 or more low cardinality columns. Create a bitmap index on each column
2. Two or more bitmap indexed columns are used together in AND and OR conjunctions, using equals (=), IN, and NOT IN conditions.
3. The WHERE clauses on the bitmap indexed columns, when combined, reduce the rows returned to a small percentage of the table or partition.
4. The bitmap indexed table has low volatility (does not change much), or all updates are performed by a single session. Bitmap indexes on high volatility tables can cause locking problems.
   
   

Composite Index:

A composite index is an index that is made up of more than one column. But normal index can be used in only one column.

Create index i1 on mouse (id, name, sal, desg);

• The columns should be specified by ascending order. That means

Unique Index:

• Improving the performance by fetching the unique values. It will allow the null values.
• Composite index can be applied.
• If any constraints are used, we don’t need to create unique index explicitly. Since automatically unique index will be triggered.

Reverse Key Index:

The data most likely common, but the last digit should be differing. To this scenario we should use Reverse key index.

For example:

100001
100002
100003

Functional Index:

If we are using any functional statement on the table, we can create functional index.

• It's easy and provides immediate value.
• It can be used to speed up existing applications without changing any of their logic or queries.
• It can be used to supply additional functionality to applications with very little cost.

Create Index i1 on mouse(upper(name);

Locks:

Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource.

Types of Index:

1. Row level locking
2. Table level locking
   2.1) Share mode

2.2) Share update mode

2.3) Exclusive

Row level Locking:

A, B users logged on to the same database, if A is trying to do update on any particular row to the particular table, A is explicitly locked the particular table. Hence the user B should not able to update any record to the particular table until commit/Rollback operation by A.

If the commit/rollback given by A, the locks will be released.

Lock the Rowlevel:

Syntax: Select * from mouse where id=1001for update;

Table Level Lock;

We can able to lock the whole table.

Share mode:

Multiple users can lock the single table at a time. However, if a user trying to do DML operation on the particular table, the other users unable to do anything on the table until commit/Rollback operation by the particular user.

Syntax:

Lock table emp in share mode;

Share update mode:

It is similar to Row level lock, but it is lock the whole table not the single row.

Lock table emp in share update mode;

Exclusive lock:

If you locked by exclusive mode, none other user could not do any operation on the particular table until commit/Rollback operation by the particular user.

Syntax: lock table emp in exclusive mode;

Deadlock:

A deadlock is a condition where two or more users are waiting for data locked by each other. Oracle automatically detects a deadlock and resolves them by rolling back one of the statements involved in the deadlock, thus releasing one set of data locked by that statement. Statement rolled back is usually the one which detects the deadlock.

Deadlocks are mostly caused by explicit locking because oracle does not do lock escalation and does not use read locks. Multitable deadlocks can be avoided by locking the tables in same order in all the applications, thus precluding a deadlock

Example:

User 1 is locked emp table.
User 2 is locked department table.

Now, user1 typed query in the DB to lock the department table, which is locked by user2. The same as user2 typed query in the DB to lock the emp table, which is locked by user1. In this scenario the deadlock occurs.

PL/SQL

Boolean value  Null, True, False

• Boolean default value is Null
• It is not printing anywhere, just checking the condition.

Structure of PL/SQL code:

1. Declaration Part
2. Execution Part  Mandatory
3. Exception Part

%type  name1 mouse.name%type

If you are specifying any particular column %type will be used.

• The table mouse.name data type and exact column lengths are assigned to the name1 variable.
• While execution only the values will assign to the name1 variable
• It is a formal argument

%rowtype  emprec emp%rowtype;

We can get the whole table columns values to the emprec variable. We can call it as Record.

Simple explanation:

%type  for the single column values
%rowtype  for all the column values

Types of Loops:

1. Normal Loop
2. For Loop
3. While Loop

Loop:

Syntax:

Declare
//declare the variables
begin
loop
exit when (condition)
end loop
end

For Loop:

begin

for I in 1..50
loop
if (mod(1,2)=0)
dbms_output.put_line(i);
end if;
end loop;
end;

While Loop:

Begin
i:=0;
while(i<=50)
loop
i:=i+2;
if (mod(I,2)=0)
dbms_output.put_line(i);
end if;
end loop;
end;

Cursor:

Cursor is used to fetch more than one value from the select query. ( To handle more than one row).

Types of Cursors:

1. Static
   1. Implicit Cursor
   2. Explicit Cursor
2. Dynamic
3. Reference Cursor

Implicit Cursor:

While doing the insert & update through the sql query the implicit cursor automatically called by the oracle.

Explicit Cursor:

If the select statements fetching more than one record at a time we will use Explicit cursor.

We should manually define the cursor by the below way:

Cursor cursor_name is select id, name from mouse;

Cursor Attributes:

1. %is open
2. %found
3. %not found
4. %rowcount

%Isopen, %found & %notfound attributes are Boolean datatype. Because these attributes are a yes or no option.

%Rowcount will tells current number of record, which is fetching from the DB.

Cursor Commands:

• Open
• Fetch
• Close

Five steps needs to be follow, when we specifying Cursor:

1. Declare the variables to store the columns values from the SELECT statements.
2. Declare the cursor to specify the SELECT statement.
3. Open the cursor
4. Fetch the values from the cursor
5. Close the cursor

Simple example:

Declare
Cursor c1 is select id, name from mouse;
cid mouse.id%type;
cname mouse.name%type;
begin
open c1;
fetch c1 into cid, cname;
dbms_output.put­_line(‘ ‘ || cid ‘ ‘ || cname);
exit when c1%notfound
endloop;
close c1;
end;

For Cursor:

By using the normal cursor, If 100 variables are there on the tables; we should define the each and every variable on the declaration section and fetch the 100 data by using the fetch command. To avoid these we are going to For cursor.

Usage

• No need to declare the variables
• No need to open the cursor
• No need to fetch the data.
• No need to close the cursor.

Simple example:

Declare
cursor c1 is select * from mouse;
begin
for rec in c1
loop
dbms_output.put_line(c1%rowcount || rec.sal);
end loop;
end;

If we are using any cursor attribute in For cursor, we should define those attributes within the loop section. Because after ending the loop For cursor automatically closed the cursor. Or else, it will throw an error.

Parameterized Cursor:
We can pass the value in the run time.
Example with Normal cursor:

Declare
Cursor c1 (id1 in number) is select id, name from mouse where id=id1;
a_id mouse.id%type;
a_name mouse.name%type;
begin
open c1(&id1)
loop
fetch c1 into a_id, a_name;
exit when c1%notfound;
dbms_output.put_line(a_id || ‘ ‘ || a_name);
end loop;
end;

Example with For cursor:

begin
for rec in (select id,name from mouse where id=&id)
loop
dbms_output.put_line(rec.id || ' ' || rec.name);
end loop;
end;

To find out the how many number of records are updating in DB:

default cursor name: sql

example: sql%rowcount

begin
update temp1 set product_id=10032 where product_id=10033;
dbms_output.put_line(sql%rowcount || ' Records are updated');
end;

Reference Cursor:

A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time.  The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).

By using the Reference cursor we can open multiple select statements at a time. Also, it is not require closing the cursor. Automatically cursor will be closed by Ref cursor.

Characteristics:

• Ref cursor resides in server memory not in client machine.
• Ref cursor is not updatable.
• Can access only serial manner not reverse.
• Ref cursor is a plsql data type    

Types:

1. Strong  Return type defined.

For the strong ref cursor the returning columns with datatype and length need to be known at compile time.

Example: TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;

2. Weak  Return type not defined.

For the weak ref cursor the structure does not need to be known at compile time.

Example : TYPE WEAK8i_REF_CURSOR IS REF CURSOR;

We have to manually define the datatype in Ref cursor.

To define the data type:

Type typ1 is Ref Cursor;

C1 typ1;

• Here typ1 is the data type name;

Reference Cursor examples:

declare
type typ1 is ref cursor;
c1 typ1;
id1 mouse.id%type;
name1 mouse.name%type;
sal1 mouse.sal%type;

begin
begin
open c1 for select id,name from mouse;
loop
fetch c1 into id1,name1;
exit when c1%notfound;
dbms_output.put_line(' ' || id1 || ' ' || name1);
end loop;
end;

begin
open c1 for select id,name,sal from mouse;
loop
fetch c1 into id1,name1,sal1;
exit when c1%notfound;
dbms_output.put_line(' ' || id1 || ' ' || name1 || ' ' || sal1);
end loop;
end;
end;

Exceptions:

Run-time errors arise from design faults, coding mistakes, hardware failures, and many other sources. Although you cannot anticipate all possible errors, you can plan to handle certain kinds of errors meaningful to your PL/SQL program.

Types of Exceptions:

1. Pre-defined Exception (Named System Exceptions)
2. User-defined Exception
3. Un-defined Exception (Unnamed System Exceptions)

Pre-defined Exception (Named System Exceptions):

There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Pre-defined Exception.

Example:

• Too_many_rows
• Zero_divide
• No_data_found
• CURSOR_ALREADY_OPEN
• INVALID_CURSOR

Example:

Declare
id1 mouse.id%type;
begin
select id into id1 from mouse where id='1003';
Exception
When too_many_rows then
dbms_output.put_line('too many rows ');
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
End;

User-defined Exception:

We can explicitly define the exceptions based on business rules. These are known as user-defined exceptions.

Three parts of the exceptions:

1. Declare the Exceptions
2. Raise an Exceptions
3. Handle the exceptions

declare
e1 exception;
m1 number;
m2 number;
m3 number;
begin
m1 :=&m1;
m2 :=&m2;
m3 :=&m3;

if(m1 < 25 or m2 < 25 or m3 < 25) then
raise e1;
end if;
dbms_output.put_line('Total ' || (m1+m2+m3));
exception
when e1 then
dbms_output.put_line('Enter the number < 25');
end;

Raise_Application_Error:

RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.

Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements).

RAISE_APPLICATION_ERROR raises an exception but does not handle it.

RAISE_APPLICATION_ERROR is used for the following reasons,

• To create a unique id for an user-defined exception.
• To make the user-defined exception look like an Oracle error.

Syntax:

RAISE_APPLICATION_ERROR (error_number, error_message);

• The Error number must be between -20000 and -20999
• The Error_message is the message you want to display when the error occurs.

declare
e1 exception;
m1 number;
m2 number;
m3 number;
begin
m1 :=&m1;
m2 :=&m2;
m3 :=&m3;

if(m1 < 25 or m2 < 25 or m3 < 25) then
raise e1;
end if;
dbms_output.put_line('Total ' || (m1+m2+m3));
exception
when e1 then
RAISE_APPLICATION_ERROR(-20001, 'Enter the number < 25');
end;

Un-defined Exception (Unnamed System Exceptions):

The system exception for which oracle does not provide a name is known as unnamed system exception (Pragma_exception_init).

There are two ways to handle unnamed system exceptions:

1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.

Note: If we are using when others exception, it should be define in the last of the pl/sql block. Or else, it will throw an error.

We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT.
EXCEPTION_INIT will associate a predefined Oracle error number to a programmer_defined exception name.

Steps to be followed to use unnamed system exceptions are :

• They are raised implicitly.

• If they are not handled in WHEN Others they must be handled explicity.

• To handle the exception explicity, they must be declared using Pragma EXCEPTION_INIT as given above and handled referecing the user-defined exception name in the exception section.

Syntax:

DECLARE
exception_name EXCEPTION;
PRAGMA
EXCEPTION_INIT (exception_name, Err_code);
BEGIN
Execution section
EXCEPTION
WHEN exception_name THEN
handle the exception
END;

Simple program:

Declare
v_ename varcahar2(10);
e1 exception;
prgma_exception_init(e1, -1)
begin
insert into emp select * from emp;
exception
when e1 then
dbms_output.put_line(‘Unique constraint violation’);
When others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrom);
end;

SQLCODE  Returns the type and oracle code
SQLERRM  Returns the text of the oracle error message

Note:

• Exceptions are stored in Standard package;
• Here no_data_found is a variable name:
  
  example: no_data_found exception or e1 exception

Sub-Programs:

The subprograms called as a Named block.

1. Procedures
2. Functions
3. Packages
   
   

Procedures:

A stored procedure or in simple a proc is a named PL/SQL block which performs one or more specific task

A stored procedure is a set of SQL commands that has been compiled and stored on the database server.

Once the stored procedure has been "stored", client applications can execute the stored procedure over and over again without sending it to the database server again and without compiling it again.

Stored procedures improve performance by reducing network traffic and CPU load.

Procedures may or may not return a value

Advantages:

1. Modularity  split the codes of some modules
2. Maintainability
3. Reusability

Three Parameters:

1. In
2. Out
3. Inout

In:

We can pass the values. But we cannot do any manipulation.

Out & Inout:

It will allow manipulation. If we are passing any values through the out parameters, the final result of the out parameter value should be the null.

We will get and print the values. Also, it will allow doing the manipulation.

Syntax:

Create procedure prc1(A in number, b out varchar2, c out number)
is
//declaration section
begin
//execution partEXCEPTION
Exception section
end;

Simple example:

create or replace procedure p1 As
i number(10);
begin
for i in 1..50
loop
dbms_output.put_line(i);
end loop;
end;

Execute a Stored Procedure

There are two ways to execute a procedure.

1) From the SQL prompt.

EXECUTE [or EXEC] procedure_name;

2) Within another procedure – simply use the procedure name.

procedure_name;

Functions:

A function is a named PL/SQL Block which is similar to a procedure. A function must always return a value.

Syntax:

CREATE [OR REPLACE] FUNCTION function_name [parameters]

RETURN return_datatype;

IS

Declaration_section

BEGIN

Execution_section

Return return_variable;

EXCEPTION

exception section

Return return_variable;

END;

1. Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2. The execution and exception section both should return a value which is of the datatype defined in the header section.

Simple Program:

CREATE OR REPLACE FUNCTION employer_details_func
RETURN VARCHAR2
IS
emp_name VARCHAR(20);
BEGIN
SELECT name INTO emp_name
FROM mouse WHERE id = 1001;
RETURN emp_name;
END;

Execute a PL/SQL Function:

A function can be executed in the following ways.

1) Since a function returns a value we can assign it to a variable.
employee_name := employer_details_func;

2) As a part of a SELECT statement
SELECT employer_details_func FROM dual;

3) In a PL/SQL Statements like,
dbms_output.put_line(employer_details_func);

Note:

• If the functions contain out/inout parameters, we should not execute the functions through the select statement. If you are trying to execute the functions through the select statments, which is contains out/inout parameters, it will return a null value.
• By using the function we cannot do any DML operation.
• Functions are compiled and executed at run time

Package:

A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the specification.

Package specification is a mandatory part.

Advantages of Package:

1. Information Hiding
2. Better Performance
3. Global Variable Declaration
4. Function Overloading  Different parameters and different datatypes.

Program:

c

Global variable declaration. We can call this variable outside package also.

reate or replace package firstpack1
is
cnt number :=0005413510; 
procedure prc1;
procedure prc2;
function fun1 return number;
end;

c

If we are declaring the subprograms inside the package body, we can call this subprogram within the package only, could not call outside of package. For information hiding purpose we are declaring subprograms and writing code within the package.

reate or replace package body firstpack1
is
procedure prc_hide; // 

procedure prc1 is
begin
dbms_output.put_line('This is my first procedure');
end;

Function Overloading. Because procedure prc1 we are using twice with different parameters.

procedure prc1(a out number) is 
begin
dbms_output.put_line('This is my first procedure and A value is : ' || a);
end;

procedure prc2
is
begin
dbms_output.put_line('2nd procedure');
firstpack1.prc_hide();
end;

function fun1 return number
is
begin
return 10;
end;

procedure prc_hide is
begin
dbms_output.put_line('information hiding');
end;

end;

Better Performance:

If 10 standalone procedures/functions are there in the DB, whenever while executing those subprograms, it will hit the disk directly and load only one subprogram at a time. Even though, if we called a procedure or a function in another subprogram, it will again hit the disk directly and load only one subprogram at a time. So performance will get reduced.

But if we have written packages with the subprograms, it will load whole packages (All the subprograms, whichever we written within the package) into buffer. So while executing the subprogram, it fetch the necessary information from the buffer not from the disk. So performance will get increased.

Triggers:

Like a stored procedure, a trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly. Unlike a stored procedure, you can enable and disable a trigger, but you cannot explicitly invoke it. While a trigger is enabled, the database automatically invokes it—that is, the trigger fires.

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

• 12 types of triggers we can define in the single table

Reasons to Use Triggers

Triggers let you customize your database management system. For example, you can use triggers to:

•Automatically generate virtual column values
•Log events
•Gather statistics on table access
•Modify table data when DML statements are issued against views
•Enforce referential integrity when child and parent tables are on different nodes of a distributed database
•Publish information about database events, user events, and SQL statements to subscribing applications
•Prevent DML operations on a table after regular business hours
•Prevent invalid transactions
•Enforce complex business or referential integrity rules that you cannot define with constraints

Caution:

Triggers are not reliable security mechanisms, because they are programmatic and easy to disable. For high-assurance security, use Oracle Database Vault,

Order in Which Triggers Fire

If two or more triggers with different timing points are defined for the same statement on the same table, then they fire in this order:

1.All BEFORE STATEMENT triggers
2.All BEFORE EACH ROW triggers
3.All AFTER EACH ROW triggers
4.All AFTER STATEMENT triggers

Note: If you are creating two or more triggers with the same timing point, and the order in which they fire is important, then you can control their firing order using the FOLLOWS and PRECEDES

Trigger Types:

1. DML Triggers  Insert, update and delete
2. DDL Triggers  Alter or create
3. System Triggers  initiating the job in oracle

• DDL triggers and system triggers mostly used by the DBA.

DML Trigger:
If the trigger is created on a table or view, then the triggering event is composed of DML statements, and the trigger is called a DML trigger.

DML Trigger types:

1. Statement Level
2. Row Level.

Statement Level		Row Level
Before	After	Before	After
1. insert	1. insert	1. insert	1. insert
2. update	2. update	2. update	2. update
3. delete	3. delete	3. delete	3. delete

Statement Level:

� Statement-level triggers execute once for each transaction. For example, if a single transaction inserted 500 rows into the Customer table, then a statement-level trigger on that table would only be executed once.
� Statement-level triggers therefore are not often used for data-related activities; they are normally used to enforce additional security measures on the types of transactions that may be performed on a table.
� Statement-level triggers are the default type of triggers created and are identified by omitting the FOR EACH ROW clause in the CREATE TRIGGER command.

• It will execute the block only once.
• We could not track the history of data by using the statement level trigger.
• It is the default trigger.

Example:

CREATE OR REPLACE TRIGGER T3
BEFORE INSERT ON mouse
BEGIN
DBMS_OUTPUT.PUT_LINE('New employees are about to be added');
END;

Whenever values are inserting in mouse tables, the dbms messages will printed.

Row Level:

Row-level triggers for data-related activities

� Row-level triggers execute once for each row in a transaction.
� Row-level triggers are the most common type of triggers; they are often used in data auditing applications.
� Row-level trigger is identified by the FOR EACH ROW clause in the CREATE TRIGGER command.

• It will execute the statement for each row
• We can track the old data and new data by using the keyword old and new. Example (new.empid, new.empname, old.sal)

Example:

CREATE or REPLACE TRIGGER T2
BEFORE Update OF unit_price
ON tempp2
FOR EACH ROW
BEGIN
INSERT INTO tempp1 VALUES (:old.product_id, :old.product_name, :old.supplier_name, :old.unit_price);
END;

If you are updating any data in the tempp2 tables, the same updated values will inserted in the tempp1 tables.

• We can find out the triggers in user_triggers tables.

Advantages:

• Replication purpose (Reflecting the data in the same table)
• We can track the history of data by using old and new keyword.
• Data validation (By using constraints)

Mutating tables:

A mutating table is a table that is currently being modified by an update, delete, or insert statement(possibly by the effects of a DELETE CASCADE constraint). (A view being modified by an INSTEAD OF trigger is not considered to be mutating.) . When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating", and raises an error since Oracle should never return inconsistent data.

Mutating errors:

• A Row level trigger throws an error.
• This error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires.

Caution:

Oracle Database does not enforce the mutating-table restriction for a trigger that accesses remote nodes, because the database does not support declarative referential constraints between tables on different nodes of a distributed database.

Similarly, the database does not enforce the mutating-table restriction for tables in the same database that are connected by loop-back database links. A loop-back database link makes a local table appear remote by defining an Oracle Net path back to the database that contains the link.
Example Trigger Causes Mutating-Table Error

-- Create log table

DROP TABLE log;
CREATE TABLE log ( emp_id NUMBER(6),
l_name VARCHAR2(25),
f_name VARCHAR2(20));

-- Create trigger that updates log and then reads employees

CREATE OR REPLACE TRIGGER log_deletions
AFTER DELETE ON employees
FOR EACH ROW
DECLARE
n INTEGER;
BEGIN
INSERT INTO log VALUES ( :OLD.employee_id, :OLD.last_name,:OLD.first_name);

SELECT COUNT(*) INTO n FROM employees;
DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.');
END;

-- Issue triggering statement:

DELETE FROM employees WHERE employee_id = 197;
Result:

DELETE FROM employees WHERE employee_id = 197
*
ERROR at line 1:
ORA-04091: table HR.EMPLOYEES is mutating, trigger/function might not see it
ORA-06512: at "HR.LOG_DELETIONS", line 10
ORA-04088: error during execution of trigger 'HR.LOG_DELETIONS'

DDL triggers:

If the trigger is created on a schema or the database, then the triggering event is composed of either DDL or database operation statements, and the trigger is called a system trigger.

Autonomous transaction

An autonomous transaction is an independent transaction that is initiated by another transaction, and executes without interfering with the parent transaction. When an autonomous transaction is called, the originating transaction gets suspended. Control is returned when the autonomous transaction does a COMMIT or ROLLBACK.

A trigger or procedure can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION;. You may need to increase the TRANSACTIONS parameter to allow for the extra concurrent transactions.

_Example

SQL> CREATE OR REPLACE TRIGGER tab1_trig

2 AFTER insert ON tab1

3 DECLARE

4 PRAGMA AUTONOMOUS_TRANSACTION;

5 BEGIN

6 INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');

7 COMMIT; -- only allowed in autonomous triggers

8 END;

9 /

Instead of triggers:

• When the triggers needs to create on Views then instead of triggers should be used.
• An INSTEAD OF DML trigger is a DML trigger created on a noneditioning view, or on a nested table column of a noneditioning view. The database fires the INSTEAD OF trigger instead of running the triggering DML statement. An INSTEAD OF trigger cannot be conditional.
• An INSTEAD OF trigger is the only way to update a view that is not inherently updatable.
• An INSTEAD OF trigger is always a row-level trigger. An INSTEAD OF trigger can read OLD and NEW values, but cannot change them.

Examples:

CREATE OR REPLACE TRIGGER Dept_emplist_tr
INSTEAD OF INSERT ON Dept_view
REFERENCING NEW AS Employee
PARENT AS Department
FOR EACH ROW
BEGIN-- The insert on the nested table is translated to an insert on the base table: INSERT INTO Emp_tab VALUES (
:Employee.Empno, :Employee.Empname,:Employee.Salary, :Department.Deptno);
END;

Compound DML Triggers:

A compound DML trigger created on a table or editioning view can fire at multiple timing points. Each timing point section has its own executable part and optional exception-handling part, but all of these parts can access a common PL/SQL state. The common state is established when the triggering statement starts and is destroyed when the triggering statement completes, even when the triggering statement causes an error.

A compound DML trigger created on a noneditioning view is not really compound, because it has only one timing point section.

A compound trigger can be conditional, but not autonomous.

Two common uses of compound triggers are:

• To accumulate rows destined for a second table so that you can periodically bulk-insert them

• To avoid the mutating-table error (ORA-04091)

Compound DML Trigger Structure

The optional declarative part of a compound trigger declares variables and subprograms that all of its timing-point sections can use. When the trigger fires, the declarative part runs before any timing-point sections run. The variables and subprograms exist for the duration of the triggering statement.

A compound DML trigger created on a noneditioning view is not really compound, because it has only one timing point section. The syntax for creating the simplest compound DML trigger on a noneditioning view is:

CREATE trigger FOR dml_event_clause ON view
COMPOUND TRIGGER
INSTEAD OF EACH ROW IS BEGIN
  statement;
END INSTEAD OF EACH ROW;

A compound DML trigger created on a table or editioning view has at least one timing-point section in Table 9-2. If the trigger has multiple timing-point sections, they can be in any order, but no timing-point section can be repeated. If a timing-point section is absent, then nothing happens at its timing point.

Table 9-2 Compound Trigger Timing-Point Sections

Timing Point	Section
Before the triggering statement runs	BEFORE STATEMENT
After the triggering statement runs	AFTER STATEMENT
Before each row that the triggering statement affects	BEFORE EACH ROW
After each row that the triggering statement affects	AFTER EACH ROW

A compound DML trigger does not have an initialization section, but the BEFORE STATEMENT section, which runs before any other timing-point section, can do any necessary initializations.

If a compound DML trigger has neither a BEFORE STATEMENT section nor an AFTER STATEMENT section, and its triggering statement affects no rows, then the trigger never fires.

Compound DML Trigger Restrictions

•OLD, NEW, and PARENT cannot appear in the declarative part, the BEFORE STATEMENT section, or the AFTER STATEMENT section.

•Only the BEFORE EACH ROW section can change the value of NEW.

•A timing-point section cannot handle exceptions raised in another timing-point section.

•If a timing-point section includes a GOTO statement, the target of the GOTO statement must be in the same timing-point section.
Performance Benefit of Compound DML Triggers

A compound DML trigger has a performance benefit when the triggering statement affects many rows.

For example, suppose that this statement triggers a compound DML trigger that has all four timing-point sections.

INSERT INTO Target
  SELECT c1, c2, c3
  FROM Source
  WHERE Source.c1 > 0

Although the BEFORE EACH ROW and AFTER EACH ROW sections of the trigger run for each row of Source whose column c1 is greater than zero, the BEFORE STATEMENT section runs only before the INSERT statement runs and the AFTER STATEMENT section runs only after the INSERT statement runs.

A compound DML trigger has a greater performance benefit when it uses bulk SQL

A compound DML trigger is useful for accumulating rows destined for a second table so that you can periodically bulk-insert them. To get the performance benefit from the compound trigger, you must specify BULK COLLECT INTO in the FORALL statement (otherwise, the FORALL statement does a single-row DML operation multiple times).
Using Compound DML Triggers to Avoid Mutating-Table Error

A compound DML trigger is useful for avoiding the mutating-table error (ORA-04091).

Scenario: A business rule states that an employee's salary increase must not exceed 10% of the average salary for the employee's department. This rule must be enforced by a trigger.

Solution: Define a compound trigger on updates of the table hr.employees.

Triggers for Ensuring Referential Integrity

You can use triggers and constraints to maintain referential integrity between parent and child tables,

Table	Constraint to Declare on Table	Triggers to Create on Table
Parent	PRIMARY KEY or UNIQUE	One or more triggers that ensure that when PRIMARY KEY or UNIQUE values are updated or deleted, the desired action (RESTRICT, CASCADE, or SET NULL) occurs on corresponding FOREIGN KEY values. No action is required for inserts into the parent table, because no dependent foreign keys exist.
Child	FOREIGN KEY, if parent and child are in the same database. (The database does not support declarative referential constraints between tables on different nodes of a distributed database.) Disable this foreign key constraint to prevent the corresponding PRIMARY KEY or UNIQUE constraint from being dropped (except explicitly with the CASCADE option).	One trigger that ensures that values inserted or updated in the FOREIGN KEY correspond to PRIMARY KEY or UNIQUE values in the parent table.

System Triggers

A system trigger is created on either a schema or the database. Its triggering event is composed of either DDL statements (listed in "ddl_event") or database operation statements (listed in "database_event").

A system trigger fires at exactly one of these timing points:

•Before the triggering statement runs
(The trigger is called a BEFORE statement trigger or statement-level BEFORE trigger.)

•After the triggering statement runs
(The trigger is called a AFTER statement trigger or statement-level AFTER trigger.)

•Instead of the triggering CREATE statement
(The trigger is called an INSTEAD OF CREATE trigger.)

Types:

•SCHEMA Triggers
•DATABASE Triggers
•INSTEAD OF CREATE Triggers

SCHEMA Triggers

A SCHEMA trigger is created on a schema and fires whenever the user who owns it is the current user and initiates the triggering event.
Example: BEFORE Statement Trigger on Sample Schema HR

CREATE OR REPLACE TRIGGER drop_trigger
  BEFORE DROP ON hr.SCHEMA
  BEGIN
    RAISE_APPLICATION_ERROR (
      num => -20000,
      msg => 'Cannot drop object');
  END;
/

DATABASE Triggers:

A DATABASE trigger is created on the database and fires whenever any database user initiates the triggering event.

Note:
An AFTER SERVERERROR trigger fires only if Oracle relational database management system (RDBMS) determines that it is safe to fire error triggers.

Example Triggering monitoring logons.

CREATE OR REPLACE TRIGGER check_user
  AFTER LOGON ON DATABASE
  BEGIN
    check_user;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR
        (-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack);
 END;
/

INSTEAD OF CREATE Triggers:

An INSTEAD OF CREATE trigger is a SCHEMA trigger whose triggering event is a CREATE statement. The database fires the trigger instead of executing its triggering statement.

Example INSTEAD OF CREATE Trigger on Schema

CREATE OR REPLACE TRIGGER t
  INSTEAD OF CREATE ON SCHEMA
  BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE T (n NUMBER, m NUMBER)';
  END;
/

Trigger Design Guidelines

• Use triggers to ensure that whenever a specific event occurs, any necessary actions are done (regardless of which user or application issues the triggering statement).

For example, use a trigger to ensure that whenever anyone updates a table, its log file is updated.

• Do not create triggers that duplicate database features.

For example, do not create a trigger to reject invalid data if you can do the same with constraints.

• Do not create triggers that depend on the order in which a SQL statement processes rows (which can vary).

For example, do not assign a value to a global package variable in a row trigger if the current value of the variable depends on the row being processed by the row trigger. If a trigger updates global package variables, initialize those variables in a BEFORE statement trigger.

• Use BEFORE row triggers to modify the row before writing the row data to disk.
• Use AFTER row triggers to obtain the row ID and use it in operations.

An AFTER row trigger fires when the triggering statement results in ORA-2292.

Note:

AFTER row triggers are slightly more efficient than BEFORE row triggers. With BEFORE row triggers, affected data blocks are read first for the trigger and then for the triggering statement. With AFTER row triggers, affected data blocks are read only for the trigger.

• If the triggering statement of a BEFORE statement trigger is an UPDATE or DELETE statement that conflicts with an UPDATE statement that is running, then the database does a transparent ROLLBACK to SAVEPOINT and restarts the triggering statement. The database can do this many times before the triggering statement completes successfully. Each time the database restarts the triggering statement, the trigger fires. The ROLLBACK to SAVEPOINT does not undo changes to package variables that the trigger references. To detect this situation, include a counter variable in the package.
• Do not create recursive triggers.

For example, do not create an AFTER UPDATE trigger that issues an UPDATE statement on the table on which the trigger is defined. The trigger fires recursively until it runs out of memory.

• If you create a trigger that includes a statement that accesses a remote database, then put the exception handler for that statement in a stored subprogram and invoke the subprogram from the trigger..
• Use DATABASE triggers judiciously. They fire every time any database user initiates a triggering event.
• If a trigger runs the following statement, the statement returns the owner of the trigger, not the user who is updating the table:

SELECT Username FROM USER_USERS;

• Only committed triggers fire.

A trigger is committed, implicitly, after the CREATE TRIGGER statement that creates it succeeds. Therefore, the following statement cannot fire the trigger that it creates:

CREATE OR REPLACE TRIGGER my_trigger
AFTER CREATE ON DATABASE
BEGIN
NULL;
END;
/

• To allow the modular installation of applications that have triggers on the same tables, create multiple triggers of the same type, rather than a single trigger that runs a sequence of operations.

Each trigger sees the changes made by the previously fired triggers. Each trigger can see OLD and NEW values.

Trigger Restrictions

In addition to the restrictions that apply to all PL/SQL units triggers have these restrictions:

• Trigger Size Restriction
• Trigger LONG and LONG RAW Data Type Restrictions
• Mutating-Table Restriction
• Only an autonomous trigger can run TCL or DDL statements.

For information about autonomous triggers.

• A trigger cannot invoke a subprogram that runs transaction control statements, because the subprogram runs in the context of the trigger body.

For more information about subprograms invoked by triggers.

• A trigger cannot access a SERIALLY_REUSABLE package.
• The size of the trigger cannot exceed 32K.

If the logic for your trigger requires much more than 60 lines of PL/SQL source text, then put most of the source text in a stored subprogram and invoke the subprogram from the trigger. For information about subprograms invoked by triggers.

Trigger LONG and LONG RAW Data Type Restrictions

Note:

Oracle supports the LONG and LONG RAW data types only for backward compatibility with existing applications.

In addition to the restrictions that apply to all PL/SQL units, triggers have these restrictions:

• A trigger cannot declare a variable of the LONG or LONG RAW data type.
• A SQL statement in a trigger can reference a LONG or LONG RAW column only if the column data can be converted to the data type CHAR or VARCHAR2.
• A trigger cannot use the correlation name NEW or PARENT with a LONG or LONG RAW column.

Collection:

A collection is an ordered group of elements all of the same type. It is a general concept that encompasses lists, arrays, and other familiar data types.

Each element has a unique subscript that determines its position in the collection.

The data stored in the collection may be accessed more rapidly by the database than if you were to use two tables instead.

Advantages:

• We can collect the Bulk data.
• Better Performance
• Fetching process will be reduced.

Types of collections: 

1. Associative arrays (formerly known as index-by tables)
2. Varrays
3. Nested tables

List of Collection methods:

1. EXISTS(row) returns TRUE if the row specified exists.
2. COUNT returns the number of rows.
3. FIRST returns the row number of the first populated row.
4. LAST returns the row number of the last populated row.
5. PRIOR(row) returns the row number of the last row populated before the row specified.
6. NEXT(row) returns the row number of the next row populated after the row specified.
7. DELETE removes all rows.
8. DELETE(row) removes the specified row.
9. DELETE(start_row,end_row) removes all rows between and including the start_row and end_row.
10. TRIM removes the last row.
11. TRIM(n) removes the last n rows.
12. EXTEND adds one row.
13. EXTEND(n) adds n rows.
14. EXTEND(n,m) adds n copies of row m.

Index-by-tables:

Index-by-tables is the integer and it is contain positive and negative values.

• An associative array is a set of key and value pairs.
• You can get the value from the array using the key (which may be a string) or an integer.

Program:declare
Type typ1 is table of varchar2(25) index by binary_integer;
name1 typ1;
cursor c1 is select name from mouse;
begin
open c1;
loop
fetch c1 bulk collect into name1 limit 5;
if(name1.count>0) then
dbms_output.put_line(name1.first);
dbms_output.put_line(name1.last);
dbms_output.put_line(name1.count);
for i in name1.first..name1.last
loop
dbms_output.put_line('Name is : ' || name1(i));
end loop;
end if;
exit when c1%notfound;
end loop;
end;

Nested Tables:

• A nested table is a table that is embedded within another table.
• You can insert, update, and delete individual elements in a nested table.
• Because you can modify individual elements in a nested table, this makes them more flexible than a varray.
• The elements for nested tables are stored in separate tables.

Program:

declare
Type typ1 is table of varchar2(25);
name1 typ1;
cursor c1 is select name from mouse;
begin
open c1;
loop
fetch c1 bulk collect into name1 limit 5;
if(name1.count>0) then
dbms_output.put_line(name1.first);
dbms_output.put_line(name1.last);
dbms_output.put_line(name1.count);
for i in name1.first..name1.last
loop
dbms_output.put_line('Name is : ' || name1(i));
end loop;
end if;
exit when c1%notfound;
end loop;
end;

Varrays:

• You can use a varray to store an ordered set of elements having an index associated with it.
• The elements in a varray are of the same type.
• A varray has a maximum size that you set when creating it.
• Elements in a varray can only be modified as a whole, not individually.
• The elements stored in a varray are stored with the table when the size of the varray is 4KB or less, otherwise the varray is stored outside of the table.
• When a varray is stored with the table, accessing its elements is faster than accessing elements in a nested table.

Program:

declare
Type typ1 is VARRAY(5) of varchar2(25);
name1 typ1;
cursor c1 is select name from mouse;
begin
open c1;
loop
fetch c1 bulk collect into name1 limit 5;
if(name1.count>0) then
dbms_output.put_line(name1.first);
dbms_output.put_line(name1.last);
dbms_output.put_line(name1.count);
for i in name1.first..name1.last
loop
dbms_output.put_line('Name is : ' || name1(i));
end loop;
end if;
exit when c1%notfound;
end loop;
end;

Creating Collection Data type:

CREATE Or Replace TYPE Address AS VARRAY(2) OF VARCHAR2(100);

Assigning in Table:

Create table table_name (Addressdata Address);

Insert the value:

Insert into table_name(Addressdata) values (Address(‘102, Nehru nagar’, ‘Chennai-118’);

Avoiding Collection Exceptions

In most cases, if you reference a nonexistent collection element, PL/SQL raises a predefined exception. Consider the following example:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
nums NumList; -- atomically null
BEGIN
/* Assume execution continues despite the raised exceptions. */
nums(1) := 1; -- raises COLLECTION_IS_NULL (1)
nums := NumList(1,2); -- initialize table
nums(NULL) := 3 -- raises VALUE_ERROR (2)
nums(0) := 3; -- raises SUBSCRIPT_OUTSIDE_LIMIT (3)
nums(3) := 3; -- raises SUBSCRIPT_BEYOND_COUNT (4)
nums.DELETE(1); -- delete element 1
IF nums(1) = 1 THEN ... -- raises NO_DATA_FOUND (5)

The following list shows when a given exception is raised:

Collection Exception	Raised when...
COLLECTION_IS_NULL	you try to operate on an atomically null collection.
NO_DATA_FOUND	a subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT	a subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT	a subscript is outside the allowed range.
VALUE_ERROR	a subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

Bulk Binds :

The assigning of values to PL/SQL variables in SQL statements is called binding. PL/SQL binding operations fall into three categories:

1. in-bind When a PL/SQL variable or host variable is stored in the database by an INSERT or UPDATE statement.
2. out-bind When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause of an INSERT, UPDATE, or DELETE statement.
3. define When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement.

A DML statement can transfer all the elements of a collection in a single operation, a process known as bulk binding. If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. With bulk binds, entire collections, not just individual elements, are passed back and forth.

To do bulk binds with INSERT, UPDATE, and DELETE statements, you enclose the SQL statement within a PL/SQL FORALL statement.

To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.

Example: Performing a Bulk Bind with DELETE

The following DELETE statement is sent to the SQL engine just once, even though it performs three DELETE operations:

DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE deptno = depts(i);
END;
Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute

The syntax follows:
FORALL index IN lower_bound..upper_bound SAVE EXCEPTIONS
{insert_stmt | update_stmt | delete_stmt}

• All exceptions raised during the execution are saved in the new cursor attribute %BULK_EXCEPTIONS, which stores a collection of records. Each record has two fields. The first field, %BULK_EXCEPTIONS(i).ERROR_INDEX, holds the "iteration" of the FORALL statement during which the exception was raised. The second field, %BULK_EXCEPTIONS(i).ERROR_CODE, holds the corresponding Oracle error code.
• The values stored by %BULK_EXCEPTIONS always refer to the most recently executed FORALL statement. The number of exceptions is saved in the count attribute of %BULK_EXCEPTIONS, that is, %BULK_EXCEPTIONS.COUNT. Its subscripts range from 1 to COUNT.
• If you omit the keywords SAVE EXCEPTIONS, execution of the FORALL statement stops when an exception is raised. In that case, SQL%BULK_EXCEPTIONS.COUNT returns 1, and SQL%BULK_EXCEPTIONS contains just one record. If no exception is raised during execution, SQL%BULK_EXCEPTIONS.COUNT returns 0.

The following example shows how useful the cursor attribute %BULK_EXCEPTIONS can be:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
DELETE FROM emp WHERE sal > 500000/num_tab(i);
EXCEPTION
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of errors is ' || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line('Error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Oracle error is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;

Output:

Number of errors is 3
Error 1 occurred during iteration 2
Oracle error is ORA-01476: divisor is equal to zero
Error 2 occurred during iteration 6
Oracle error is ORA-01476: divisor is equal to zero
Error 3 occurred during iteration 10
Oracle error is ORA-01476: divisor is equal to zero

Restrictions on BULK COLLECT

The following restrictions apply to the BULK COLLECT clause:

• You cannot bulk collect into an associative array that has a string type for the key.
• You can use the BULK COLLECT clause only in server-side programs (not in client-side programs). Otherwise, you get the error this feature is not supported in client-side programs.
• All targets in a BULK COLLECT INTO clause must be collections, as the following example shows:

DECLARE
TYPE NameList IS TABLE OF emp.ename%TYPE;
names NameList;
salary emp.sal%TYPE;
BEGIN
SELECT ename, sal BULK COLLECT INTO names, salary -- illegal target
FROM emp WHERE ROWNUM < 50;
…
END;

• Composite targets (such as objects) cannot be used in the RETURNING INTO clause. Otherwise, you get the error unsupported feature with RETURNING clause.
• When implicit datatype conversions are needed, multiple composite targets cannot be used in the BULK COLLECT INTO clause.
• When an implicit datatype conversion is needed, a collection of a composite target (such as a collection of objects) cannot be used in the BULK COLLECT INTO clause.

Materialized Views:

A Materialized View is effectively a database table that contains the results of a query. The power of materialized views comes from the fact that, once created, Oracle can automatically synchronize a materialized view's data with its source information as required with little or no programming effort.

Also, It would be getting the data from remote database to local db. It is called as Snapshots.

Materialized views can be used for many purposes, including:

1. Denormalization
2. Validation
3. Data Warehousing
4. Replication.
Views vs Materialized Views

1. Views and Materialized views store the same data.

2. The rowid would be different for materialized view.

3. Once the data has been updated in the source table, then the data would be reflected immediately in views. But, Materialized view does not. It should be updated in the regular intervals or have to refresh the MV to synch the data manually.

Different types of refresh method in Materialized view:

1. COMPLETE REFRESH
2. FAST REFRESH
3. FORCE REFRESH
4. NEVER REFRESH
COMPLETE REFRESH:
The row id would be change everytime when the complete refresh done on the materialized view data. Everytime changing the rowid would be the expensive operation. So, if it is having the large data then the complete refresh would not be suitable.
Syntax:
create materialized view mv
REFRESH COMPLETE
as select * from t;
execution:
execute DBMS_MVIEW.REFRESH( LIST => 'MV', METHOD => 'C' );
Materialized View logs:
As mentioned earlier, complete refreshes of materialized views can be expensive operations. Fortunately there is a way to refresh only the changed rows in a materialized view's base table. This is called fast refreshing. Before a materialized view can perform a fast refresh however it needs a mechanism to capture any changes made to its base table. This mechanism is called a Materialized View Log.

create materialized view log on emp;

It is not require to give any name to materialized view logs. Because, each table can have only one materialized view logs.

After you done the any modification on the source table, you could execute the below query to find the number of rows are modified.

select * from MLOG$_emp;

The MV logs can be created by using WITH Column List , WITH SEQUENCE , WITH ROWID and WITH PRIMARY KEY.

FAST REFRESH :
create materialized view mv
REFRESH FAST
as select * from emp;
Execution:
execute dbms_mview.refresh( list => 'MV', method => 'F' );

During the fast refresh, rowid never been changed after the update in the source table and fast refresh execution done.
Purging Materialized View Logs :

Materialized view logs automatically purged after the any kinds of refresh done.

If a materialized view log needs to be purged manually for some reason a procedure called DBMS_MVEW.PURGE_LOG can be used.
Syntax:
execute DBMS_MVIEW.PURGE_LOG( master => 'emp', num => 9999, flag => 'delete' ) ;

Once a materialized view log has been purged any materialized views dependent on the deleted rows cannot be fast refreshed. Attempting a fast refresh will raise an ORA-06512 error. Such materialized views will need to be refreshed completely.
REFRESH FAST Categories
There are three ways to categorize a materialized view's ability to be fast refreshed.

1.It can never be fast refreshed.

Materialized logs should be created 1st before create materialized view with FAST refresh. else, it will throw an error.

2.It can always be fast refreshed.

It is a normal one. 1st materialized view logs would be created on the source table, then materialized view would be created.

3.It can be fast refreshed after certain kinds of changes to the base table but not others

Materialized view would be created for insert only and not for the update or deletion. If you done so, it will throw an error. In such a case you have to complete refresh.

create materialized view log on t2
with primary key, rowid, sequence ( t_key, amt )
including new values;

create materialized view mv
REFRESH FAST
as select t_key, max( amt ) amt_max from t2
group by t_key;
Restrictions on Fast Refresh:
In general materialized views cannot be fast refreshed if the base tables do not have materialized view logs or the defining query:

•contains an analytic function|
•contains non-repeating expressions like SYSDATE or ROWNUM
•contains RAW or LONG RAW data types
•contains a subquery in the SELECT clause
•contains a MODEL clause
•contains a HAVING clause
•contains nested queries with ANY, ALL, or NOT EXISTS
•contains a CONNECT BY clause
•references remote tables in different databases
•references remote tables in a single database and defaults to the ON COMMIT refresh mode
•references other materialized views which are not join or aggregate materialized views.
FORCE REFRESH :
The REFRESH FORCE method performs a FAST refresh if possible, otherwise it performs a COMPLETE refresh. The force refresh would be good for many scenarios to decide the refresh type oracle instead of us.

create materialized view log on t2
with primary key, rowid, sequence ( t_key, amt )
including new values;

create materialized view mv
REFRESH FORCE as
select t_key, max( amt ) amt_max from t2 group by t_key;

execute dbms_mview.refresh( list => 'MV' );
NEVER REFRESH
For some reason we need to prevent refresh operations of any sort, FAST or COMPLETE, on our materialized views we can use the NEVER REFRESH method.

create materialized view mv
NEVER REFRESH
as select * from t;

When we attempt to refresh the data then we receive ORA-06512: at "SYS.DBMS_SNAPSHOT" error.
ON COMMIT:
In some situations it would be convenient to have Oracle refresh a materialized view automatically whenever changes to the base table are committed. This is possible using the ON COMMIT refresh mode.

create materialized view log on t ;

create materialized view mv
REFRESH FAST ON COMMIT
as select * from t;

Restrictions

Materialized views can only refresh ON COMMIT in certain situations.

1. The materialized view cannot contain object types or Oracle-supplied types.
2. The base tables will never have any distributed transactions applied to them.

The first case produces an error during the CREATE MATERIALIZED VIEW command.

-- this materialized view is not fast refreshable
-- because the materialized view contains an Oracle-supplied type

create materialized view mv2
REFRESH FAST ON COMMIT
as select key, val, sys_xmlgen( val ) as val_xml from t;

as select key, val, sys_xmlgen( val ) as val_xml from t
*
ERROR at line 3:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

The second case generates an error when a distributed transaction is attempted on the base table. In the following example materialized view MV (created at the top of this page) was created with REFRESH FAST. Attempting a distributed transaction on its base table, T, will therefore raise an error.

insert into t select key+10, val from T@REMOTE ;
commit;
commit
*

ERROR at line 1:
ORA-02050: transaction 5.21.5632 rolled back, some remote DBs may be in-doubt
ORA-02051: another session in same transaction failed
QUERY_REWRITE:
It is a performance tuning method, when the data is fetching from materialize view.
It is a query optimization technique that transforms a user query written in terms of tables and views, to execute faster by fetching data from materialized views. It is completely transparent to the end user, requiring no intervention or hints in the SQL application because the Oracle Database will automatically rewrite any appropriate SQL application to use the materialized views.
Syntax:
CREATE MATERIALIZED VIEW some_cust_sales_mv BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT query...

PARTITION:
Partitioning addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions. Once partitions are defined, SQL statements can access and manipulate the partitions rather than entire tables or indexes. Partitions are especially useful in data warehouse applications, which commonly store and analyze large amounts of historical data.
Advantages:

1. Partitions can be stored in different tablespaces.
2. Partitions can be added/ removed while users are working. DBAs can perform maintenance without having to bring down an entire table (import/ export/ load/ etc).

Partition information can be found in this table: user_tab_partitions

Disadvantages:

Performance Disadvantages
Indexes can pose a serious problem if they fail. If an index goes down or is damaged, the underlying tables can be damaged beyond repair, making them unusable and unrecoverable. The only solution is to revert to backup -- if you have one -- or to rebuild the index. In a partition context, this can take a very long time.
Manageability Disadvantages
Partitions are much harder to manage than standard tables devoid of them. This is because the partition aspects have to be identified and managed as part of an operation, such as the use of "truncate." It's not enough to use the simple truncate command; the correct command would be "alter table truncate partition." Therefore, partitions are harder to manage.

Partitioning types:
1. Range partitioning
2. Hash partitioning
3. Composite partitioning
4. List partitioning
5. Interval partitioning
6. System partitioning
7. Reference partitioning

Range partitioning:
Range partitioning is a partitioning technique where ranges of data is stored separately in different sub-tables.

MAXVALUE is provided as a catch-all for values that exceed all ranges specified. Note that Oracle sorts NULLs greater than all other values, except MAXVALUE.

Example:
CREATE TABLE emp ( empno NUMBER(4), ename VARCHAR2(30), sal NUMBER)
PARTITION BY RANGE(empno) ( partition e1 values less than (1000) tablespace ts1,
partition e2 values less than (2000) tablespace ts2,
partition e3 values less than (MAXVALUE) tablespace ts3);
by varchar2:
PARTITION BY RANGE ( name ) ( PARTITION p1 VALUES LESS THAN ('L') TABLESPACE ts1,
PARTITION p2 VALUES LESS THAN (MAXVALUE) TABLESPACE ts2 )
by Time based:
(PARTITION t1p1 VALUES LESS THAN (TO_DATE('2007-11-01', 'YYYY-MM-DD')),
PARTITION t1p2 VALUES LESS THAN (TO_DATE('2007-12-01', 'YYYY-MM-DD')),
PARTITION t1p3 VALUES LESS THAN (TO_DATE('2008-01-01', 'YYYY-MM-DD')),
PARTITION t1p4 VALUES LESS THAN (MAXVALUE) );
Hash partitioning:
Hash partitioning is a partitioning technique where a hash key is used to distribute rows evenly across the different partitions (sub-tables). This is typically used where ranges aren't appropriate, i.e. employee number, productID, etc.
example:
create table emp2 ( empno number(4), ename varchar2(30), sal number)
partition by hash(empno) ( partition e1 tablespace emp1,
partition e2 tablespace emp2,
partition e3 tablespace emp3,
partition e4 tablespace emp4); Composite partitioning:
Composite partitioning is a partitioning technique that combines some of the other partitioning methods. The table is initially partitioned by the first data distribution method and then each partition is sub-partitioned by the second data distribution method
Composite partitioning methods:
1. Range-hash partitioning was introduced in Oracle 8i
2. Range-list partitioning was introduced in Oracle 9i
3. Range-range partitioning was introduced in Oracle 11g
4. List-range partitioning was introduced in Oracle 11g
5. List-hash partitioning was introduced in Oracle 11g
6. List-list partitioning was introduced in Oracle 11g
7. Interval-range partitioning was introduced in Oracle 11g
8. Interval-list partitioning was introduced in Oracle 11g
9. Interval-hash partitioning was introduced in Oracle 11g
10. Hash-hash partitioning was introduced in Oracle 11gR2

Example:
CREATE TABLE orders( ord# NUMBER, orderdate DATE, prod# NUMBER, quantity NUMBER)
PARTITION BY RANGE(orderdate)
SUBPARTITION BY HASH(prod#) SUBPARTITIONS 4 -- STORE IN(ts1, ts2, ts3, ts4)
( PARTITION q1 VALUES LESS THAN (TO_DATE('01-APR-2009', 'DD-MON-YYYY')),
PARTITION q2 VALUES LESS THAN (TO_DATE('01-JUL-2009', 'DD-MON-YYYY')),
PARTITION q3 VALUES LESS THAN (TO_DATE('01-OCT-2009', 'DD-MON-YYYY')),
PARTITION q4 VALUES LESS THAN (MAXVALUE));
List partitioning:
List partitioning is a partitioning technique where you specify a list of discrete values for the partitioning key in the description for each partition.
Example:
CREATE TABLE myemp_work ( emp# NUMBER PRIMARY KEY, ename VARCHAR2(30), salary NUMBER(8,2), deptno NUMBER)
PARTITION BY LIST (deptno) ( -- Add list partitioning
PARTITION p10 VALUES (10),
PARTITION p20 VALUES (20),
PARTITION p30 VALUES (30,40));
Interval partitioning:
Interval partitioning is a partitioning method introduced in Oracle 11g. This is a helpful addition to range partitioning where Oracle automatically creates a partition when the inserted value exceeds all other partition ranges.
The following restrictions apply:
1. You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
2. Interval partitioning is NOT supported for index-organized tables.
3. You can NOT create a domain index on an interval-partitioned table
System partitioning:
System partitioning is a partitioning method, introduced in Oracle 11g, that allows an application to control partition selection. System partitions doesn't have partition keys like other partitioning schemes. As such, partition extended syntax must be used when inserting rows into system partitioned tables.
The following restrictions apply:
1. Unique local indexes are not supported (no partition key)
2. CTAS into system partitioned tables (into what partition should it go?)
3. SPLIT PARTITION operations.
Examples:
CREATE TABLE syspart (c1 NUMBER, c2 NUMBER)
PARTITION BY SYSTEM (
PARTITION p1 TABLESPACE ts1,
PARTITION p2 TABLESPACE ts2,
PARTITION p3 TABLESPACE ts3);

Reference partitioning:
Reference partitioning is a partitioning method introduced in Oracle 11g. Using reference partitioning, a child table can inherit the partitioning characteristics from a parent table.
Example:
Create a parent table with range partitioning:

CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE NOT NULL, customer_id NUMBER NOT NULL, shipper_id NUMBER)
PARTITION BY RANGE (order_date) ( PARTITION y1 VALUES LESS THAN (TO_DATE('01-JAN-2006', 'DD-MON-YYYY')),
PARTITION y2 VALUES LESS THAN (TO_DATE('01-JAN-2007', 'DD-MON-YYYY')),
PARTITION y3 VALUES LESS THAN (TO_DATE('01-JAN-2008', 'DD-MON-YYYY')));
Create child table with reference partitioning:
CREATE TABLE order_items ( order_id NUMBER NOT NULL, product_id NUMBER NOT NULL, price NUMBER, quantity NUMBER, CONSTRAINT order_items_fk FOREIGN KEY (order_id) REFERENCES orders)
PARTITION BY REFERENCE (order_items_fk);
Rowid Stored in Oracle as:
The format of the rowid is: BBBBBBB.RRRR.FFFFF

Where BBBBBBB is the block in the database file;
RRRR is the row in the block;
FFFFF is the database file.

PRAGMA SERIALLY_REUSABLE?

Serially Reusable PL/SQL Packages
PL/SQL packages normally consume user global area (UGA) memory corresponding to the number of package variables and cursors in the package. This limits scalability because the memory increases linearly with the number of users. The solution is to allow some packages to be marked as SERIALLY_REUSABLE (using pragma syntax).

For serially reusable packages, the package global memory is not kept in the UGA per user, but instead it is kept in a small pool and reused for different users. This means that the global memory for such a package is only used within a unit of work. At the end of that unit of work, the memory can therefore be released to the pool to be reused by another user (after running the initialization code for all the global variables).

The unit of work for serially reusable packages is implicitly a CALL to the server, for example, an OCI call to the server, or a PL/SQL client-to-server RPC call or server-to-server RPC call.

Package States
The state of a nonreusable package (one not marked SERIALLY_REUSABLE) persists for the lifetime of a session. A package's state includes global variables, cursors, and so on.

The state of a serially reusable package persists only for the lifetime of a CALL to the server. On a subsequent call to the server, if a reference is made to the serially reusable package, Oracle creates a new instantiation (described below) of the serially reusable package and initializes all the global variables to NULL or to the default values provided. Any changes made to the serially reusable package state in the previous CALLs to the server are not visible.

Note: Creating a new instantiation of a serially reusable package on a CALL to the server does not necessarily imply that Oracle allocates memory or configures the instantiation object. Oracle simply looks for an available instantiation work area (which is allocated and configured) for this package in a least-recently used (LRU) pool in SGA. At the end of the CALL to the server this work area is returned back to the LRU pool. The reason for keeping the pool in the SGA is that the work area can be reused across users who have requests for the same package.
Why Serially Reusable Packages?
Since the state of a non-reusable package persists for the lifetime of the session, this locks up UGA memory for the whole session. In applications such as Oracle Office a log-on session can typically exist for days together. Applications often need to use certain packages only for certain localized periods in the session and would ideally like to de-instantiate the package state in the middle of the session once they are done using the package.

With SERIALLY_REUSABLE packages the application developers have a way of modelling their applications to manage their memory better for scalability. Package state that they care about only for the duration of a CALL to the server should be captured in SERIALLY_REUSABLE packages.