Learn About Different Type Of Triggers In Oracle

Database triggers are specialized stored programs. Oracle engine allow the definition of procedure that are implicitly executed when an insert, update, delete is issued again a table from sql or thought application the trigger automatically associated DML statement is executed.They are not called directly but are triggered by events in the database. They run between the time you issue a commandand the time you perform the database management system action. You can write triggers in PL/SQL.

PL/SQL Type of Triggers based on how they triggered:-

Before Triggers: These triggers are fired before the triggering SQL statement (INSERT, UPDATE, DELETE) is executed. The execution of triggering SQL statement is stopped depending on the various conditions to be fulfilled in BEFORE trigger.

After Triggers: These triggers are fired after the triggering SQL statement  (INSERT, UPDATE, DELETE) is executed. The triggering SQL statement is executed first followed by the code of trigger.

ROW Trigger: The triggers are fired for each and every record which is inserted or updated or deleted from a table.

Statement Trigger: The Trigger are fired for each row of DML operation being performed on a table. we can not access the column values for records being inserted, updated, deleted on the table and not individual records.

PL/SQL Triggers Syntax Description:-

CREATE or REPLACE TRIGGER trigger_name: Creates a trigger with the given name otherwise overwrites an existing trigger with the same name.

{BEFORE , AFTER }: Indicates the where should trigger get fired. BEFORE trigger execute before when statement execute before time or AFTER trigger execute after when statement execute after time.

{INSERT , UPDATE , DELETE}: Determines the performing trigger event. More than one triggering events allow can be used together separated by OR keyword.

ON Table Name: Determine the perform trigger event in selected Table.

[Referencing {old AS old, new AS new}]: Reference the old and new values of the data being changed. :old use to existing row perform and :new use to execute new row to perform. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.

Note:-

Insert have no :OLD value ( before execution) and have :NEW value (After execution)

Delete have no :OLD value but it have :NEW value.

Update have both :OLD and :NEW value.

for each row: Trigger must fire when each row gets Affected (Row Level Trigger) or just once when the entire sql statement is executed(statement level Trigger).

WHEN (condition): Valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

There are various events on which a trigger can be written, such as:

1.System events

.....a. Database startup and shutdown

.....b. Server error message events

2.User events

.....a. User logon and logoff

.....b. DDL statements (CREATE, ALTER, and DROP)

.....c. DML statements (INSERT, DELETE, and UPDATE)

Based on the above condition we can classify the trigger into 5 catogory dml trigger , ddl trigger ,Compound triggers, Instead-of triggers and System or database event triggers. Out of which here i am discussing mainly ddl and dml trigger.

1.DDL Trigger

DDL triggers fire when you create, change or remove objects in a database, they support both before and after event triggers and work at the database or schema level.

DDL event supported :-

alter, analyze, associate statistics, audit, comment, create, ddl, disassociate statistics, drop, grant, noaudit, rename, revoke, truncate .

There are a number of event attribute functions that can be used to get user, client or system information , commonly used are given below.

ORA_CLIENT_IP_ADDRESS	returns the client IP address as varchar2
ORA_DATABASE_NAME	returns database name as varchar2
ORA_DES_ENCRYPTED_PASSWORD	returns DES-encrypted password as varchar2
ORA_DICT_OBJ_NAME	returns object name as varchar2
ORA_DICT_OBJ_NAME_LIST	returns the number of elements in the list as a pls_integer
ORA_DICT_OBJ_OWNER	returns the owner of the object acted upon by the event as a varchar2
ORA_DICT_OBJ_OWNER_LIST	returns the number of elements in the list as a pls_integer
ORA_DICT_OBJ_TYPE	returns the datatype of the dictionary object changed by the event as a varchar2
ORA_GRANTEE	returns the number of elements in the list as a pls_integer
ORA_INSTANCE_NUM	returns the current database instance number as a number
ORA_IS_ALTER_COLUMN	returns true or false depending if the column has been altered (true = altered)
ORA_IS_CREATING_NESTED_TABLE	returns a true or false value when you create a table with a nested table
ORA_IS_DROP_COLUMN	returns true or false depending if the column has been dropped (true = dropped)
ORA_IS_SERVERERROR	returns true or false when the error is on the error stack
ORA_LOGIN_USER	returns the current schema name as a varchar2
ORA_PARTITION_POS	returns the numeric position with the SQL text where you can insert a partition clause
ORA_PRIVILEGE_LIST	returns the number of elements in the list as a pls_integer
ORA_REVOKEE	returns the number of elements in the list as a pls_integer
ORA_SERVER_ERROR	returns the error number as a number
ORA_SERVER_ERROR_DEPTH	returns the number of errors on the error stack as a pls_interger
ORA_SERVER_ERROR_MSG	returns an error message text as a varchar2
ORA_SERVER_ERROR_NUM_PARAMS	returns the count of any substituted strings from error messages as a pls_integer
ORA_SERVER_ERROR_PARAM	returns an error message text as a varchar2
ORA_SQL_TXT	returns the number of elements in the list as a pls_integer
ORA_SYSEVENT	returns the system event that was responible for firing the trigger as a varchar2
ORA_WITH_GRANT_OPTION	returns true or false when privileges are granted with grant option (true = with grant option)
SPACE_ERROR_INFO	returns true or false when the triggering event is related to an out-of-space condition.Now for an example

Example.1

The below given ddl trigger prevent truncating table on schema level,

SQL> create or replace trigger prevent_truncates

  2    before truncate on schema

  3    begin

  4    raise_application_error(-20001,'TRUNCATE not permitted');

  5   end;

  6    /

Trigger created.

SQL> create table salary_bk as select * from salary;

Table created.

SQL> select * from salary_bk;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

    ---------- ---------------    ---------------       ----------   ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      22000

SQL> truncate table salary_bk;

truncate table salary_bk

               *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: TRUNCATE not permitted

ORA-06512: at line 2

SQL>    

Example.2 

Below given trigger update every create statement that happens in the schema level into 

log_table,

SQL> CREATE TABLE log_table(

  2     user_name       VARCHAR2(100),

  3     event_date      DATE,

  4     detail          VARCHAR2(400));

Table created.

SQL> 

CREATE OR REPLACE TRIGGER log_create_trigg

  2    AFTER CREATE ON SCHEMA

  3  BEGIN

  4    INSERT INTO log_table

  5      (user_name, event_date, detail)

  6    VALUES

  7      (USER, SYSDATE, 'created object is: ' || ora_dict_obj_name);

  8  END;

  9  /

Trigger created.

SQL> select * from log_table;

no rows selected

SQL> create table abc as select * from dba_users;

Table created.

SQL> col user_name for a12

SQL> col detail for a25

SQL> select * from log_table;

USER_NAME    EVENT_DAT DETAIL

------------ --------- -------------------------

MAHI         19-OCT-12 created object is: ABC

2.Database event trigger

These triggers fire when a system activity occurs in the database, like the logon and logoff event triggers. They are useful for auditing information of system access. These triggers let you track system events and map them to users.

Example:-

Below given trigger logs the logging information into log_trigger_table table, 

SQL> CREATE TABLE log_trigger_table (

  2        user_name       VARCHAR2(30),

  3        event_date      DATE,

  4        action     VARCHAR2(300));

Table created.

SQL> CREATE OR REPLACE TRIGGER logon_trigger

  2      AFTER LOGON ON SCHEMA

  3      BEGIN

  4      INSERT INTO log_trigger_table

  5       (user_name, event_date, action )

  6       VALUES

  7       (USER, SYSDATE, 'Logging On');

  8    END;

  9     /

Trigger created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\DELL\node1>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 19 17:39:19 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: mahi

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from log_trigger_table;

USER_NAME    EVENT_DAT ACTION

------------ --------- -----------------------------------

MAHI         19-OCT-12 Logging On

3.DML Trigger

These triggers fire when you insert, update, or delete data from a table. You can fire them once for all changes on a table, or for each row change, using statement- or row-level trigger types, respectively. DML triggers are useful to control DML statements. You can use these triggers to audit, check, save, and replace values before they are changed.

Example.1

Below given example insert each record that will deleted from salary table into sal_deleted table,

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

    ---------- ---------------   ---------------        ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200       1800      22000

     10005 Arunkumar.K     Programmer           12000       1440       1320       1080      13800

SQL> create table sal_deleted(

rollno number(5), name varchar(15),

del_date date);

Table created.
SQL>

Now create the trigger,

SQL>ed sal_delete_trig

create or replace trigger sal_delete before delete

on salary for each row

begin 

insert into sal_deleted values

 (:old.rollno, :old.empname,sysdate);

end;

/

SQL> @sal_delete_trig

Trigger created.

SQL> delete from salary where rollno = 10005;

1 row deleted.

SQL> select * from salary;

      ROLLNO  EMPNAME    DESIGN                BPAY         DA         TA         PF     NETSAL

      ---------- --------------- ---------------          ---------- ---------- ---------- ---------- ----------

     10001   S.Krishnan      HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200       1800      22000

SQL> select * from sal_deleted;

    ROLLNO NAME            DEL_DATE

---------- --------------- ---------

     10005 Arunkumar.K     19-OCT-12

Example.2

Following trigger will insert the system time automatically into DOJ field while inserting records into student_details table,

SQL> create table student_details

  2  (rollno number(5), name varchar(15),

  3  dob date, doj date, dop date );

Table created.

SQL> ed student_details_trig;

create trigger student_details_trig before insert

on student_details for each row

begin

:new.doj := sysdate;

end;

/

SQL> @student_details_trig

Trigger created.

SQL> select * from student_details;

no rows selected

SQL> select sysdate from dual;

SYSDATE

---------

19-OCT-12

SQL> insert into student_details (rollno,name,dob) values (1001,'MAHESH','30-OCT-86');

1 row created.

SQL> select * from student_details;

    ROLLNO NAME            DOB       DOJ       DOP

---------- --------------- --------- --------- ---------

      1001 MAHESH          30-OCT-86 19-OCT-12

SQL>

Here you can see DOJ is automatically inserted by the trigger..

Example.3

Following trigger will insert each records into salupdated table before update happens in salary table,

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

---------- ---------------         --------------- ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200       1800      22000

SQL> create table salupdated(

  2  rollno number(5),

  3  empname varchar(15),

  4  design varchar(15),

  5  bpay number(8,2),

  6  da number(6,2),

  7  total number(8,2),

  8  ta number(6,2));

Table created.

SQL> ed salupdate_trig

create or replace trigger salupdate_trig before update

on salary for each row

begin

insert into salupdated values

(:old.rollno, :old.empname, :old.design, :old.bpay, :old.da, :old.netsal, :old.ta);

end;

/

SQL> @salupdate_trig

Trigger created.

SQL> select * from salupdated;

no rows selected

SQL> update salary set BPAY=21000 where DESIGN='Asst.Manager';

3 rows updated.

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         21000       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         21000       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         21000       1500       1200       1800      22000

SQL> select * from salupdated;

    ROLLNO EMPNAME         DESIGN                BPAY         DA      TOTAL         TA

---------- --------------- --------------- ---------- ---------- ---------- ----------

     10002 K.K.Omana       Asst.Manager         20000       1500      22000       1200

     10003 Anishkumar.K    Asst.Manager         20000       1500      22000       1200

     10004 Girishkumar.K   Asst.Manager         20000       1500      22000       1200

SQL>

Example.4
Following dml trigger will raise an application error while trying to delete records belonging to Asst.Manager,

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      22000

SQL> CREATE or REPLACE TRIGGER not_del

  2  AFTER

  3  DELETE ON salary

  4  for each row

  5

  6  BEGIN

  7  IF :old.DESIGN = 'Asst.Manager' THEN

  8     raise_application_error(-20015, 'Not Delete this Row');

  9  END IF;

 10  END;

 11  /

Trigger created.

SQL> delete from salary where rollno=10004;

delete from salary where rollno=10004

            *

ERROR at line 1:

ORA-20015: Not Delete this Row

ORA-06512: at "MAHI.NOT_DEL", line 3

ORA-04088: error during execution of trigger 'MAHI.NOT_DEL

Learn about oracle Cursor with example

A cursor is a pointer, which points towards a pre-allocated memory location in th SGA. The memory location to which it points is known as Context area. Oracle associates every SELECT statement with a cursor to hold the query information in this context area.A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

Note that if you do repetitive stuff inside a loop and you fail to close your cursors, you would soon run into the ORA-01000: maximum number of open cursors exceeded error.

There are two major types of cursors: Implicit cursors and explicit cursors. Again we can classify the cursor as static (normal cursor) and dynamic cursor (Reference cursor)

Implicit cursor

PL/SQL declares an implicit cursor for every DML command, and queries that return a single row. The name of the implicit cursor is SQL. You can directly use this cursor without any declaration. Implicit cursor are used for DML statements and you have no control over them. The cursor is opened at the point in time when the SQL statement is run by Oracle, the data is fetched if the statement is a query, and the cursor is closed as soon as the SQL statement finishes - all automatically.

You can however access the results of implicit cursors (such as how many rows were updated or whether or not any rows were found) as well as the data fetched from Oracle by an implicit cursor for a SELECT statement.

The process of an implicit cursor is as follows:-

• Whenever an SQL statement is executed, any given PL/SQL block issues an implicit cursor, as long as an explicit cursor does not exist for that SQL statement.
• A cursor is automatically associated with every DML statement (UPDATE, DELETE, and INSERT).PL/SQL employs an implicit cursor for each UPDATE, DELETE, or INSERT statement you execute in a program. You cannot, in other words, execute these statements within an explicit cursor, even if you want to. You have a choice between using an implicit or explicit cursor only when you execute a single-row SELECT statement (a SELECT that returns only one row).
• All UPDATE and DELETE statements have cursors those recognize the set of rows that will be affected by the operation.
• An INSERT statement requires a place to accept the data that is to be inserted in the database; the implicit cursor fulfills this need.
• The most recently opened cursor is called the “SQL%” Cursor.

For example:-

In the following UPDATE statement, which gives everyone in the company a 10% raise, PL/SQL creates an implicit cursor to identify the set of rows in the table which would be affected by the update:

UPDATE employee SET salary = salary * 1.1;

The following single-row query calculates and returns the total salary for a department. Once again, PL/SQL creates an implicit cursor for this statement:

SELECT SUM (salary) INTO department_total FROM employee

 WHERE department_number = 10;

Thus the implicit cursor is used to process INSERT, UPDATE, DELETE, and SELECT INTO statements. Oracle automatically performs the OPEN, FETCH, and CLOSE operations, during the processing of an implicit cursor.

Common attribute of implicit cursor
%ISOPEN
SQL%ISOPEN always has the value FALSE.
%FOUND
SQL%FOUND has one of these values:

• If no SELECT or DML statement has run, NULL.
• If the most recent SELECT or DML statement returned a row, TRUE.
• If the most recent SELECT or DML statement did not return a row, FALSE.

%NOTFOUND
SQL%NOTFOUND has one of these values:

• If no SELECT or DML statement has run, NULL.
• If the most recent SELECT or DML statement returned a row, FALSE.
• If the most recent SELECT or DML statement did not return a row, TRUE.

%ROWCOUNT
SQL%ROWCOUNT has one of these values:

• If no SELECT or DML statement has run, NULL.
• If a SELECT or DML statement has run, the number of rows fetched so far.

Here's a sample piece of Oracle PL/SQL code that uses an implicit cursor for a  SELECT statement.

Example.1

Note that the only variable declared was a record (gra) to hold the employee data obtained from the database and the only difference between our select statement and one we would issue using SQL*Plus is the INTO clause.

Example.2

If you can used any DML statement in your pl-sql code. At last you use SQL cursor name with its attributes so that you will meaning full information. Like SQL%ROWCOUNT which gives how many rows are affected by this plsql block.
Consider the PL/SQL Block that uses implicit cursor attributes as shown below:-

2.Explicit Cursor

Explicit cursors are defined explicitly (using the keyword CURSOR) and used only for SQL SELECT statements. Implicit cursors are used for both DML statements (UPDATE, INSERT, DELETE) and SELECT statements. PL/SQL’s implicit cursor can handle only single-row queries. If you ever need to select more than one row using SELECT in PL/SQL then you have to use explicit cursor.

The process of working with an explicit cursor consists of the following steps:

•          DECLARING the cursor. This initializes the cursor into memory.

•          OPENING the cursor. The previously declared cursor can now be opened; memory        is allotted.  

•          FETCHING the cursor. The previously declared and opened cursor can now retrieve data; this is the process of fetching the cursor.

•          CLOSING the cursor. The previously declared, opened, and fetched cursor must  now be closed to release memory allocation

Example.1

I created a table named salary and inserted some records as shown.Now i created a procedure to insert records into salary table so that rollno should take accordingly. The procedure utilize the cursor to take the maximum of rollno from salary table .

SQL> create table salary(

rollno number(5),empname varchar(15),

design varchar(15),

bpay number(7,2),

da number(6,2),

ta number(6,2),

pf number(6,2),

netsal number(8,2));

Insert the records by,

SQL> insert into salary values ( &rollno, '&empname', '&design', &bpay, &da, &ta, &pf, &netsal);

SQL> select * from salary;

    ROLLNO EMPNAME     DESIGN                  BPAY         DA         TA         PF     NETSAL

  ---------- --------------- ---------------           ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200        700      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200        700      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200        700      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200        700      22000

Now create the procedure as follows,

C:\Users\DELL> ed salinsert

declare  

cursor ins_c is select max(rollno) from salary;---> declare section

prollno salary.rollno%type;

pname salary.empname%type;

pdesign salary.design%type;

pbpay salary.bpay%type;

pda salary.da%type;

pta salary.ta%type;

ppf salary.pf%type;

pnetsal salary.netsal%type;

begin

open ins_c;  ---> opening the cursor

fetch ins_c into prollno; ---> fetching the cursor

close ins_c; ---> closing the cursor

prollno := prollno + 1;

pname := '&pname';

pdesign := '&pdesign';

pbpay := &pbpay;

pda := pbpay * 12/100;

pta := pbpay *11/100;

ppf := pbpay *8/100;

pnetsal := pbpay + pda + pta -ppf;

insert into salary values (prollno , pname , pdesign, pbpay , pda , pta , ppf , pnetsal );

end;

/

Now execute the sql,

SQL> @salinsert

Enter value for pname: Arunkumar.K

old  24: pname := '&pname';

new  24: pname := 'Arunkumar.K';

Enter value for pdesign: Programmer

old  25: pdesign := '&pdesign';

new  25: pdesign := 'Programmer';

Enter value for pbpay: 12000

old  26: pbpay := &pbpay;

new  26: pbpay := 12000;

PL/SQL procedure successfully completed.

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan      HOD                       25000       1500       1200        700      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200        700      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200        700      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200        700      22000

     10005 Arunkumar.K     Programmer          12000       1440       1320        960      13800

SQL>

Example.2

I created a table named grade , Now i want to insert salary table's rollno , name and their

grade accordig to their bpay into grade table.

SQL> create table grade (

rollno number(5),

name varchar(15),

grade char(1));

Now prepare the pl-sql code,

C:\Users\DELL> ed gradeinsert

declare

cursor crr is select rollno,empname,bpay from salary;

prollno salary.rollno%type;

pname salary.empname%type;

pbpay salary.bpay%type;

pgrade grade.grade%type;

begin

open crr;

loop

fetch crr into prollno,pname,pbpay;

exit when crr%notfound;

if pbpay > 20000 then

   pgrade := 'A';

elsif pbpay > 15000 then

   pgrade := 'B';

else

   pgrade := 'C';

end if;

insert into grade values (prollno,pname,pgrade);

end loop;

close crr;

end;

/

SQL> @gradeinsert

PL/SQL procedure successfully completed.

SQL> select * from grade;

    ROLLNO NAME            G

    ---------- --------------- -

     10001 S.Krishnan      A

     10002 K.K.Omana       B

     10003 Anishkumar.K    B

     10004 Girishkumar.K   B

     10005 Arunkumar.K     C

Example.3

This example shows the cursor to count the number of lines

C:\Users\DELL> ed count_sal

declare

        cursor c_countEmps is select count(*) from salary;

        v_out NUMBER;

    begin

        open c_countEmps;

        fetch c_countEmps into v_out;

        close c_countEmps;

       DBMS_OUTPUT.put_line('number of emps is:'||v_out);

   end;

/

SQL> set serveroutput on

SQL> @count_sal

number of emps is:5

PL/SQL procedure successfully completed.
SQL>

Parameterised cursors

PL/SQL Parameterized cursor pass the parameters into a cursor and use them in to query.Parameterizing the cursor makes it more usable and avoids the limitation of hard coding values in where the clause.

C:\Users\DELL>ed para_cursor

declare

cursor crr is select rollno,empname,bpay from salary

where rollno = &prollno; ----->> see the difference

prollno salary.rollno%type;

pname salary.empname%type;

pbpay salary.bpay%type;

pgrade grade.grade%type;

begin

open crr;

loop

fetch crr into prollno,pname,pbpay;

exit when crr%notfound;

if pbpay > 20000 then

   pgrade := 'A';

elsif pbpay > 15000 then

   pgrade := 'B';

else

   pgrade := 'C';

end if;

insert into grade values (prollno,pname,pgrade);

end loop;

close crr;

end;

/

SQL> delete from grade;

5 rows deleted.

SQL> commit;

Commit complete.

\SQL> select * from grade;

no rows selected

Now tun the sql,

SQL> @para_cursor

Enter value for prollno: 10003

old   4: where rollno = &prollno;

new   4: where rollno = 10003;

PL/SQL procedure successfully completed.

SQL> select * from grade;

    ROLLNO   NAME              G

     ---------- ---------------     -

     10003     Anishkumar.K    B

SQL>

How to Master-Slave replication in mysql 

First you have to configure /etc/my.cnf in both master and slave server,
1.Master server configuration minimum needed

server_id=10 (it may any number and may unique)
sync_binlog=1
then you have to specify the bin log location
log_bin_index=/myarch/master_bin_log.index
log-bin=/myarch/master_bin_log

/myarch should have mysql:mysql ownership

2.Slave server minimum configuration needed

server_id=20 (should be different from master)
sync_binlog=1
log_bin_index=/myarch/slave_bin_log.index
log-bin=/myarch/slave_bin_log
relay-log-info-file=/myreplog/slave_rep.info
relay-log=/myreplog/slave_rep.log
relay-log-index=/myreplog/slave_rep.index

/myreplog also have mysql.mysql ownership

3. Procedure for creating master-slave replication

1. Stop any application  that points to the production db that we  are going to sync.

2. login as root and create replication user on master server

$mysql -u root -p

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slaveuser'@'%' IDENTIFIED BY 'slave';
mysql>FLUSH PRIVILEGES;

3. check all the open connections to the server
mysql> show processlist;
kill the process if any;
mysql> KILL processid;

4. take database full backup
$mysqldump -v -u root -p --all-databases >/root/dumpfilename.bkp

5. Closes all open tables and locks all tables for all databases with a global read lock.

mysql>FLUSH TABLES WITH READ LOCK;

Leave the terminal open, otherwise, the database will be unlocked. Write down theFile and Position values, we will need them later.
Then copy this sql dump backup to slave server

7.Determine the current binary log file name and position and note down this,
Which we need later.

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 3303     |                    |                  |  
+------------------+----------+--------------+------------------+
The File column shows the name of the log file and Position shows the position within the file. In this example, the binary log file is mysql-bin.000003   and the position is 3303. Record these values. You need them later when you are setting up the slave.

4. Restoring and syncing the database at DR

1.restore the full backup or particular database backup in DR
(here we are going sync all database in the master server )
$ mysql -v -u root -p  </thepathofthedumpfile.bkp

2. stop the Slave I/O threads
mysql > stop slave;

To use CHANGE MASTER TO, the slave replication threads must be stopped (use STOP SLAVE if necessary).

3. Now issue the following to mysql using the right parameters taken from SHOW MASTER STATUS above:
   CHANGE MASTER TO MASTER_HOST='10.10.40.212',
   MASTER_USER='slaveuser', MASTER_PASSWORD='slave',
   MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=3303;

4.then start slave i/o threads.
mysql>slave start;

5.check slave status by
mysql> show slave status \G;

6.At last revoke the lock hold on tables from master server , go to live server and execute

Mysql > unlock tables;

UNLOCK TABLES explicitly releases any table locks held by the current session.also it release the  global read lock acquired with the FLUSH TABLES WITH READ LOCK statement

New features in Oracle database 11g in AMM , data guard , expdp , rman etc

1.New features in Auotmatic memory management (AMM) 
Oracle simplified memory management over the last few versions of the database. Oracle 9i automated PGA management by introducing PGA_AGGREGATE_TARGET parameter. Oracle 10g continued this trend by automating SGA management using the SGA_TARGET parameter. Oracle 11g takes this one step further by allowing you to allocate one chunk of memory, which Oracle uses to dynamically manage both the SGA and PGA.
Automatic memory management is configured using two new initialization parameters:
•MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is "0".
•MEMORY_MAX_TARGET: This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.
When using automatic memory management, the SGA_TARGET and PGA_AGGREGATE_TARGET act as minimum size settings for their respective memory areas. To allow Oracle to take full control of the memory management, these parameters should be set to zero.

2. New features in  Data guard 
2.a Active Data Guard
In Oracle Database 10g and below you could open the physical standby database for read-only activities, but only after stopping the recovery process. 
In Oracle 11g, you can query the physical standby database in real time while applying the archived logs. This means standby continue to be in sync with primary but can use the standby for reporting. 
Let us see the steps now..
First, cancel the managed standby recovery:
SQL> alter database recover managed standby database cancel;
Database altered.
Then, open the database as read only: 
SQL> alter database open read only;
Database altered.
While the standby database is open in read-only mode, you can resume the managed recovery process. 
SQL> alter database recover managed standby database disconnect;
Database altered.

2.b Snapshot Standby database 
In Oracle Database 11g, physical standby database can be temporarily converted into an updateable one called Snapshot Standby Database.
In that mode, you can make changes to database. Once the test is complete, you can rollback the changes made for testing and convert the database into a standby undergoing the normal recovery. This is accomplished by creating a restore point in the database, using the Flashback database feature to flashback to that point and undo all the changes.
Steps:
Configure the flash recovery area, if it is not already done.
SQL> alter system set db_recovery_file_dest_size = 2G;
System altered.
SQL> alter system set db_recovery_file_dest= '+FRADG';
System altered.
Stop the recovery. 
SQL> alter database recover managed standby database cancel;
Database altered.
Convert this standby database to snapshot standby using command
SQL> alter database convert to snapshot standby;
Database altered.
Now recycle the database
SQL> shutdown immediate
...
SQL> startup
ORACLE instance started.
Database is now open for read/write operations
SQL> select open_mode, database_role from v$database;
OPEN_MODE DATABASE_ROLE
---------- ----------------
READ WRITE SNAPSHOT STANDBY
After your testing is completed, you would want to convert the snapshot standby database back to a regular physical standby database by following the steps below
SQL> connect / as sysdba
Connected. 
SQL> shutdown immediate
SQL> startup mount
...
Database mounted.
SQL> alter database convert to physical standby; 
Database altered.
Now shutdown, mount the database and start managed recovery. 
SQL> shutdown
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
...
Database mounted.
Start the managed recovery process
SQL> alter database recover managed standby database disconnect;
Now the standby database is back in managed recovery mode. When the database was in snapshot standby mode, the archived logs from primary were not applied to it. They will be applied now.

2.c Redo Compression
In Oracle Database 11g you can compress the redo that goes across to the standby server via SQL*Net using a parameter compression set to true. This works only for the logs shipped during the gap resolution. Here is the command you can use to enable compression.
alter system set log_archive_dest_2 = 'service=STDBYDB LGWR ASYNC 
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=STDBYDB compression=enable'

3.New features in expdp
COMPRESSION parameter in expdp
One of the big issues with Data Pump was that the dumpfile couldn't be compressed while getting created. In Oracle Database 11g, Data Pump can compress the dumpfiles while creating them by using parameter COMPRESSION in the expdp command line. The parameter has three options: 
METDATA_ONLY - only the metadata is compressed
DATA_ONLY - only the data is compressed; the metadata is left alone. 
ALL - both the metadata and data are compressed. 
NONE - this is the default; no compression is performed. 
Encryption

The dumpfile can be encrypted while getting created. The encryption uses the same technology as TDE (Transparent Data Encryption) and uses the wallet to store the master key. This encryption occurs on the entire dumpfile, not just on the encrypted columns as it was in the case of Oracle Database 10g. 
Data Masking 

when you import data from production to test system, you may want to make sure sensitive data are altered in such a way that they are not identifiable. Data Pump in Oracle Database 11g enables you do that by creating a masking function and then using that during import.
the sensitive data is masked using  this new remap_data parameter available in Oracle 11g Datapump utility.

REMAP_TABLE
Allows you to rename tables during an import operation. 
 Example
The following is an example of using the REMAP_TABLE parameter to rename the employees table to a new name of emps:
impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmp TABLES=hr.employees REMAP_TABLE=hr.employees:emps

4.New Features in RMAN
Advice on recovery
To find out failure...
RMAN> list failure;
To get the advice on recovery
RMAN> advise failure;
Recovery Advisor generates a script that can be used to repair the datafile or resolve the issue. The script does all the work.
To verify what the script actually does ...
RMAN> repair failure preview;
Now execute the actual repair by issuing...
RMAN> repair failure;

Proactive Health Checks
In Oracle Database 11g, a new command in RMAN, VALIDATE DATABASE, can check database blocks for physical corruption.
RMAN> validate database;
Parallel backup of the same datafile.
In 10g each datafile is backed by only one channel. In Oracle Database 11g RMAN, the multiple channels can backup one datafiles parallel by breaking the datafile into chunks known as "sections." 

Optimized backup of undo tablespace.

In 10g, when the RMAN backup runs, it backs up all the data from the undo tablespace. But during recovery, the undo data related to committed transactions are no longer needed.
In Oracle Database 11g, RMAN bypasses backing up the committed undo data that is not required in recovery. The uncommitted undo data that is important for recovery is backed up as usual. This reduces the size and time of the backup.

Improved Block Media Recovery Performance
If flashback logs are present, RMAN will use these in preference to backups during block media recovery (BMR), which can significantly improve BMR speed.

Block Change Tracking Support for Standby Databases
Block change tracking is now supported on physical standby databases, which in turn means fast incremental backups are now possible on standby databases.

Faster Backup Compression
RMAN now supports the ZLIB binary compression algorithm as part of the Oracle Advanced Compression option. The ZLIB algorithm is optimized for CPU efficiency, but produces larger zip files than the BZIP2 algorithm available previously, which is optimized for compression.

Archived Log Deletion Policy Enhancements
The archived log deletion policy of Oracle 11g has been extended to give greater flexibility and protection in a Data Guard environment. The Oracle 10g and Oracle 11g syntax is displayed below.

# Oracle 10g Syntax.
CONFIGURE ARCHIVELOG DELETION POLICY {CLEAR | TO {APPLIED ON STANDBY | NONE}}
# Oracle 11g Syntax.
ARCHIVELOG DELETION POLICY {CLEAR | TO {APPLIED ON [ALL] STANDBY |BACKED UP integer TIMES TO DEVICE TYPE deviceSpecifier |NONE | SHIPPED TO [ALL] STANDBY}[ {APPLIED ON [ALL] STANDBY | BACKED UP integer TIMES TO DEVICE TYPE deviceSpecifier |NONE | SHIPPED TO [ALL] STANDBY}]...}
The extended syntax allows for configurations where logs are eligible for deletion only after being applied to, or transferred to, one or more standby database destinations.

5.Read only tables
Read-Only Tables in Oracle Database 11g 
Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command.
ALTER TABLE table_name READ ONLY;
ALTER TABLE table_name READ WRITE;
Any DML statements that affect the table data and SELECT ... FOR UPDATE queries result in an ORA-12081 error message.

Transparent application failover (TAF) - A practical study

When considering the availability of the Oracle database, Oracle RAC 11g provides a superior solution with its advanced failover mechanisms. Oracle RAC 11g includes the required components that all work within a clustered configuration responsible for providing continuous availability; when one of the participating systems fail within the cluster, the users are automatically migrated to the other available systems.
 

A major component of Oracle RAC 11g that is responsible for failover processing is the Transparent Application Failover (TAF) option. All database connections (and processes) that lose connections are reconnected to another node within the cluster. The failover is completely transparent to the user.

One important note is that TAF happens automatically within the OCI libraries. Thus your application (client) code does not need to change in order to take advantage of TAF. Certain configuration steps, however, will need to be done on the Oracle TNS file tnsnames.ora.

i created following entry in my client tnsnames.ora file in my laptop

rac =

  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.212)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC.localdomain)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

Note:- Both ip used here are the vips of the cluster nodes.Also all these things TYPE,METHOD,RETRIES,DELAY are importanct parameter while configuring TAF.
Before going ahead check the tnsping connectivity .

C:\Users\DELL>tnsping rac

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 18-SEP-2012 14:34:49Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
e:\oracle11g\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.212)(PORT = 1
521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC.localdomain) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 1
80) (DELAY = 5))))
OK (280 msec)

SQL Query to Check the Session's Failover Information

The following SQL query can be used to check a session's failover type, failover method, and if a failover has occurred. We will be using this query throughout this example. Here i connect to my rac instance through MAHI user.

col instance_name for a15
col host_name for a17
col failover_method for a17
col failed_over for a18
set lines 300
select distinct  v.instance_name as instance_name, v.host_name as host_name,
s.failover_type as failover_type, s.failover_method as failover_method,
s.failed_over as failed_over  from v$instance v , v$session s
where s.username ='MAHI';

We can see, that we are connected to the Instance RAC1 which is running on racha1.localdomain . Now we stop this Instance without disconnecting from the client. Either you can use srvctl or through sqlplus to shutdown the RAC1 instance. Here i used srvctl command. I used separate env files for rdbms and grid environment.Here i sourced the grid.env file , in order to invoke srvctl utility.

Now let's go back to our SQL session on the client and rerun the SQL statement:

We can see that the above session has now been failed over to instance RAC2 on racha2.localdomain. After completing the test start the RAC1 instance .

hope it will help you...:)

PGA_AGGREGATE_TARGET concept

In order to  understand About PGA_AGGREGATE_TARGET parameter let’s have a look at parameter *_AREA_SIZE .

SQL> SHOW PARAMETER _AREA_SIZE

NAME TYPE VALUE
———————————— ———– ——————————
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_size integer 65536
workarea_size_policy string AUTO

Here we see the parameter workarea_size_policy is set to AUTO because we have set non-zero value to pga_aggregate_target.

SQL> SHOW PARAMETER PGA_AGGREGATE_TARGET

NAME TYPE VALUE
———————————— ———– ——————————
pga_aggregate_target big integer 525M

Now we try to set pga_aggregate_target to a zero value.

SQL> ALTER SYSTEM SET pga_aggregate_target=0;
ALTER SYSTEM SET pga_aggregate_target=0
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_target must be between 10M and 4096G-1

Whenever we try to set to as scope=spfile it is not set because 0 is not valid value.
So, I set it to zero in the pfile.

SQL> CREATE PFILE=’/export/home/oracle/pfile.ora’ FROM SPFILE;
File created.

SQL> !vi /export/home/oracle/pfile.ora
*.pga_aggregate_target=0

Now I have started database with this pfile.

SQL> STARTUP FORCE PFILE=’/export/home/oracle/pfile.ora’;
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size 2021216 bytes
Variable Size 218106016 bytes
Database Buffers 1426063360 bytes
Redo Buffers 14753792 bytes
Database mounted.
Database opened.

Now have a look at the values. We will see that workarea_size_policy parameter is set to MANUAL.

SQL> SHOW PARAMETER _AREA_SIZE

NAME TYPE VALUE
———————————— ———– ——————————
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_size integer 65536
workarea_size_policy string MANUAL
SQL> SHOW PARAMETER PGA_AGGREGATE_TARGET

NAME TYPE VALUE
———————————— ———– ——————————
pga_aggregate_target big integer 0

Some notes on pga_aggregate_target

• PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

• The default value for PGA_AGGREGATE_TARGET is non zero.Unless you specify otherwise Oracle sets it’s value to 20% of the SGA or 10 MB, whichever is greater.

• Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators such as sort, group-by, hash-join, bitmap merge, and bitmap create will be automatically sized. In that case we don’t have to bother about settings of sort_area_size , hash_area_size etc.

• If you set PGA_AGGREGATE_TARGET to 0 then oracle automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_AREA_SIZE parameters.

• Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the work areas to private memory.

• The memory allocated for PGA_AGGREGATE_TARGET has no relation with SGA_TARGET. The similarity is both is taken from total memory of the system.

• The minimum value of this parameter is 10 MB and maximum is 4096 GB – 1.

source:- http://samadhandba.wordpress.com