Friday 30 May 2014

Sequence in Oracle

What is a Sequence?
  • A sequence is a user created database object that can be shared by multiple users to generate unique integers.
  • A typical usage for sequences is to create a primary key value, which must be unique for each row.
  • The sequence is generated and incremented (or decremented) by an internal Oracle routine.
  • This can be a time-saving object because it can reduce the amount of application code needed to write a sequence-generating routine.
  • Sequence numbers are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.
You create a sequence using the CREATE SEQUENCE statement, which has the following syntax:
where
  1. The default start_num is 1.
  2. The default increment number is 1.
  3. The absolute value of increment_num must be less than the difference between maximum_num and minimum_num.
  4. minimum_num must be less than or equal to start_num, and minimum_num must be less than maximum_num.
  5. NOMINVALUE specifies the maximum is 1 for an ascending sequence or -10^26 for a descending sequence.
  6. NOMINVALUE is the default.
  7. maximum_num must be greater than or equal to start_num, and maximum_num must be greater than minimum_num.
  8. NOMAXVALUE specifies the maximum is 10^27 for an ascending sequence or C1 for a descending sequence.
  9. NOMAXVALUE is the default.
  10. CYCLE specifies the sequence generates integers even after reaching its maximum or minimum value.
  11. When an ascending sequence reaches its maximum value, the next value generated is the minimum.
  12. When a descending sequence reaches its minimum value, the next value generated is the maximum.
  13. NOCYCLE specifies the sequence cannot generate any more integers after reaching its maximum or minimum value.
  14. NOCYCLE is the default.
  15. CACHE cache_num specifies the number of integers to keep in memory.
  16. The default number of integers to cache is 20.
  17. The minimum number of integers that may be cached is 2.
  18. The maximum integers that may be cached is determined by the formula CEIL(maximum_num – minimum_num)/ABS(increment_num).
  19. NOCACHE specifies no integers are to be stored.
  20. ORDER guarantees the integers are generated in the order of the request.
  21. You typically use ORDER when using Real Application Clusters.
  22. NOORDER doesn’t guarantee the integers are generated in the order of the request.
  23. NOORDER is the default
Creating a sequence and then get the next value
Once initialized, you can get the current value from the sequence using currval.
You can’t use CURRVAL just after a sequence creation. It will throw an error.
When you select currval , nextval remains unchanged; nextval only changes when you select nextval to get the next value.
Getting Information on Sequences
You get information on your sequences from user_sequences.
Modifying a Sequence
  • You modify a sequence using the ALTER SEQUENCE statement.
  • You cannot change the start value of a sequence.
  • The minimum value cannot be more than the current value of the sequence ( currval ).
  • The maximum value cannot be less than the current value of the sequence ( currval ).
Removing a Sequence
• Remove a sequence from the data dictionary by using the DROP SEQUENCE statement.
• Once removed, the sequence can no longer be referenced.

Exception Handling in Oracle

What is Exception Handling?

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling. Using Exception Handling we can test the code and avoid it from exiting abruptly.
An exception is an identifier in PL/SQL that is raised during the execution of a block that terminates its main body of actions. A block always terminates when PL/SQL raises an exception, but can you specify an exception handler to perform final actions.

Types of Exception

There are 3 types of Exceptions.
a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions

Named System Exceptions (or Predefined Oracle Server Exceptions) and Unnamed System Exceptions (or Nonpredefined Oracle Server Exceptions) are implicitly raised.
User-defined Exceptions are explicitly raised.

Structure of Exception Handling

You can trap any error by including a corresponding routine within the exception handling section of the PL/SQL block. Each handler consists of a WHEN clause, which specifies an exception, followed by a sequence of statements to be executed when that exception is raised.
The exception-handling section traps only those exceptions that are specified; any other exceptions are not trapped unless you use the OTHERS exception handler.

Exceptions Trapping Rules:

  • Begin the exception-handling section of the block with the EXCEPTION keyword.
  • You can define several exception handlers, each with its own set of actions, for the block.
  • When an exception occurs, PL/SQL processes only one handler before leaving the block.
  • Place the OTHERS clause after all other exception-handling clauses.
  • WHEN OTHERS is the last clause and you can have only one OTHERS clause.
a) Named System Exceptions
System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions.
Note: PL/SQL declares predefined exceptions in the STANDARD package.
Few Predefined Exceptions:
  • NO_DATA_FOUND (ORA-01403) — When a SELECT…INTO clause does not return any row from a table.
  • TOO_MANY_ROWS (ORA-01422) — When you SELECT or fetch more than one row into a record or variable.
  • ZERO_DIVIDE (ORA-01476) — When you attempt to divide a number by zero.
  • CURSOR_ALREADY_OPEN (ORA-06511) — You tried to open a cursor that is already open.
  • INVALID_CURSOR (ORA-01001) — Illegal cursor operation occurred. You tried to reference a cursor that does not yet exist. This may have happened because you’ve executed a FETCH cursor or CLOSE cursor before Opening the cursor.
  • INVALID_NUMBER (ORA-01722) — You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
  • DUP_VAL_ON_INDEX (ORA-00001) — Attempted to insert a duplicate value.
  • LOGIN_DENIED (ORA-01017) — You tried to log into Oracle with an invalid username/password combination.
  • NOT_LOGGED_ON (ORA-01012) — You tried to execute a call to Oracle before logging in.
  • VALUE_ERROR (ORA-06502) — You tried to perform an operation and there was an error on a conversion, truncation, or invalid constraining of numeric or character data.
Named system exceptions are:
  • Not declared explicitly.
  • Raised implicitly when a predefined Oracle error occurs.
  • Caught by referencing the standard name within an exception-handling routine.

For Example:

b) Unnamed System Exceptions
Those system exception for which oracle does not provide a name is known as unnamed system exception. These exceptions do not occur frequently. These Exceptions have a code and an associated message.
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.

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 explicitly.
  • To handle the exception explicitly, they must be declared using Pragma EXCEPTION_INIT as given above and handled referencing the user-defined exception name in the exception section.
The general syntax to declare unnamed system exception using EXCEPTION_INIT is:
For Example:
Let’s trap for Oracle server error number –2292, which is an integrity constraint violation.
1] Declare the name for the exception within the declarative section.
exception EXCEPTION;
Where: exception is the name of the exception.
2.] Associate the declared exception with the standard Oracle server error number using the
PRAGMA EXCEPTION_INIT statement.
PRAGMA EXCEPTION_INIT(exception, error_number);
Where: exception is the previously declared exception.
error_number is a standard Oracle Server error number.
3] Reference the declared exception within the corresponding exception-handling routine.
c) User-defined Exceptions
Apart from system exceptions we can explicitly define exceptions based on business rules. These are known as user-defined exceptions.
Steps to be followed to use user-defined exceptions:
• They should be explicitly declared in the declaration section.
• They should be explicitly raised in the Execution Section.
• They should be handled by referencing the user-defined exception name in the exception section.

For Example:
Functions for Trapping Exceptions:
When an exception occurs, you can identify the associated error code or error message by using two functions. Based on the values of the code or message, you can decide which subsequent action to take based on the error.
SQLCODE: Returns the numeric value for the error code. 
SQLERRM: Returns the message associated with the error number.
You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement, as shown in the following example:
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,
a) to create a unique id for an user-defined exception.
b) to make the user-defined exception look like an Oracle error.

The General Syntax to use this procedure is:
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.

RAISE_APPLICATION_ERROR can be used in either (or both) the executable section and the exception section of a PL/SQL program. The returned error is consistent with how the Oracle server produces a predefined, nonpredefined, or user-defined error. The error number and message is displayed to the user.
Executable section:
Exception section:
What is Exception propagation in Oracle? 
Exceptions which are not handled in a sub block get propagated to the outer block. When an exception occurs, it terminates from the line where the exception occurs and the control goes to the calling program or the next outer block. If not handled in the outer block, it terminates that block and propagates to the next outer block and so on. And, if exception occurs in the outermost block, then the whole program gets terminated.

All About Triggers

All About Triggers

Triggers in Oracle:

A trigger is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, or the database. The triggers executes implicitly whenever a particular event takes place.

Types of Triggers:

1] Application Triggers:
Application triggers execute implicitly whenever a particular data manipulation language (DML) event occurs within an application (ex. Oracle Forms, Oracle Reports)
2] Database Triggers:
These triggers are fired whenever a data event (such as DML) or system event (such as logon or shutdown) occurs on a schema or database.
There are four types of database triggers:
  • Table-level triggers can initiate activity before or after an INSERT, UPDATE, or DELETE event.
  • View-level triggers define what can be done to the view.
  • Database-level triggers can be activated at startup and shutdown of a database.
  • Session-level triggers can be used to store specific information.

Benefits of Database triggers:

  • Improved data security:
          – Provide enhanced and complex security checks
          – Provide enhanced and complex auditing
  • Improved data integrity:
         – Enforce dynamic data integrity constraints
         – Enforce complex referential integrity constraints
         – Ensure that related operations are performed together implicitly

Creating DML Triggers:

A triggering statement contains:
1] Trigger timing:
BEFORE: Execute the trigger body before the triggering DML event on a table.
AFTER: Execute the trigger body after the triggering DML event on a table.
INSTEAD OF: Execute the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable.
2] Triggering event:
Triggering user event tells which DML statement causes the trigger to execute? You can use any of the following:
• INSERT
• UPDATE
• DELETE
3] Trigger type:
Trigger type tells should the trigger body execute for each row the statement affects or only once?
• Statement: The trigger body executes once for the triggering event. This is the default. A statement trigger fires once, even if no rows are affected at all.
• Row: The trigger body executes once for each row affected by the triggering event. A row trigger is not executed if the triggering event affects no rows.
4] Trigger body:
Trigger body tells what action should the trigger perform? The trigger body is a PL/SQL block or a call to a procedure.

PL/SQL Trigger Execution Hierarchy:

The following hierarchy is followed when a trigger is fired.
1) BEFORE statement trigger fires first.
2) Next BEFORE row level trigger fires, once for each row affected.
3) Then AFTER row level trigger fires once for each affected row. This event will alternates between BEFORE and AFTER row level triggers.
4) Finally the AFTER statement level trigger fires.

Syntax of Triggers:

The Syntax for creating a trigger is:
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
   --- sql statements
END;
 
  • CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
  • {BEFORE | AFTER | INSTEAD OF } - This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view.
  • {INSERT [OR] | UPDATE [OR] | DELETE} – This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
  • [OF col_name] - This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
  • CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
  • [ON table_name] - This clause identifies the name of the table or view to which the trigger is associated.
  • [REFERENCING OLD AS o NEW AS n] - This clause is used to reference the old and new values of the data being changed. By default, you reference the values as : old.column_name or :new.column_name. 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.
  • [FOR EACH ROW] - This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).
  • WHEN (condition) - This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

Example of DML Statement Trigger:

 
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
    (TO_CHAR(SYSDATE,'HH24:MI')
    NOT BETWEEN '08:00' AND '18:00')
    THEN RAISE_APPLICATION_ERROR (-20500,'You may
                  insert into EMPLOYEES table only during business hours.');
END IF;
END

What are conditional predicates?

When creating a database trigger, several triggering events can be combined into one by using the INSERTING, UPDATING, and DELETING conditional predicates within the trigger body. In this way, a user can create a trigger that runs a different code based on the type of the triggering statement that fires the trigger. For example, conditional predicates can be used to create a database trigger that restricts all data manipulation events (INSERT, UPDATE, DELETE, etc.) on a table to certain business hours Monday through Friday.

Example of using conditional predicates in triggers:

CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
IF (TO_CHAR (SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '18')
 
THEN
 
IF DELETING THEN
RAISE_APPLICATION_ERROR (-20502,'You may delete from
EMPLOYEES table only during business hours.');
 
ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR (-20500,'You may insert into
EMPLOYEES table only during business hours.');
 
ELSIF UPDATING ('SALARY') THEN
RAISE_APPLICATION_ERROR (-20503,'You may update
SALARY only during business hours.');
 
ELSE
RAISE_APPLICATION_ERROR (-20504,'You may update
EMPLOYEES table only during normal hours.');
 
END IF;
 
END IF;
END;
 
 

What are OLD and NEW qualifiers?

The OLD and NEW qualifiers are used to reference the values of a column before and after the data change, respectively.
The OLD and NEW qualifiers can be used only with row triggers. They cannot be used with statement triggers. The OLD and NEW qualifiers must be prefixed with a colon (:) in every SQL and PL/SQL statement except when they are referenced in a WHEN restricting clause.

Example of using OLD and NEW qualifiers:

CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employees
 
FOR EACH ROW
 
BEGIN
 
     INSERT INTO audit_emp_table (user_name, timestamp,
     id, old_last_name, new_last_name, old_title,
     new_title, old_salary, new_salary)
     VALUES (USER, SYSDATE, :OLD.employee_id,
     :OLD.last_name, :NEW.last_name, :OLD.job_id,
     :NEW.job_id, :OLD.salary, :NEW.salary );
END;
 

INSTEAD OF trigger:

The Oracle INSTEAD-OF trigger has the ability to update normally non-updateable views. Simple views are generally updateable via DML statements issued against the view. However, when a view becomes more complex it may lose its “updateable-ness,” and the Oracle INSTEAD-OF trigger must be used.
INSTEAD OF triggers are valid for DML events on views. They are not valid for DDL or database events.
If a view is inherently updatable and has INSTEAD OF triggers, then the triggers take preference. In other words, the database fires the triggers instead of performing DML on the view.
Restrictions on INSTEAD OF Triggers
  • INSTEAD OF triggers are valid only for views. You cannot specify an INSTEAD OF trigger on a table.
  • You can read both the :OLD and the :NEW value, but you cannot write either the :OLD or the :NEW value.

Example of INSTEAD OF trigger:

In this example, an order_info view is created to display information about customers and their orders:

CREATE VIEW order_info AS
   SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
          o.order_id, o.order_date, o.order_status
   FROM customers c, orders o
   WHERE c.customer_id = o.customer_id;
 
Normally this view would not be updatable, because the primary key of the orders table (order_id) is not unique in the result set of the join view. To make this view updatable, create an INSTEAD OF trigger on the view to process INSERT statements directed to the view.

 
CREATE OR REPLACE TRIGGER order_info_insert
    INSTEAD OF INSERT ON order_info
DECLARE
      duplicate_info EXCEPTION;
     PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
     INSERT INTO customers
        (customer_id, cust_last_name, cust_first_name)
     VALUES (
    :new.customer_id,
    :new.cust_last_name,
      :new.cust_first_name);
 
    INSERT INTO orders (order_id, order_date, customer_id)
    VALUES (
      :new.order_id,
      :new.order_date,
      :new.customer_id);
 
EXCEPTION
      WHEN duplicate_info THEN
        RAISE_APPLICATION_ERROR (
         num=-20107,
          msg='Duplicate customer or order ID');
 
END order_info_insert;

 You can now insert into both base tables through the view (as long as all NOT NULL columns receive values):

INSERT INTO order_info VALUES
   (999, 'Smith', 'John', 2500, '13-MAR-2001', 0);
 

Difference between Database Triggers and Stored Procedures:

Database Triggers:
  • Database triggers are defined with CREATE TRIGGER statement.
  • The data dictionary that contains the source code is USER_TRIGGERS.
  • They are implicitly invoked
  • COMMIT, SAVEPOINT, and ROLLBACK are not allowed.
  • We cannot pass parameters to Database triggers and they cannot return a value.
Stored Procedures:
  • Stored Procedures are defined with CREATE PROCEDURE statement.
  • The data dictionary that contains the source code is USER_SOURCE.
  • They are explicitly invoked.
  • COMMIT, SAVEPOINT, and ROLLBACK are allowed.
  • We can pass parameters to Stored procedures and they can return a value.

How to know Information about Triggers:

We can use the data dictionary view ‘USER_TRIGGERS’ to obtain information about any trigger.
The below statement shows the structure of the view ‘USER_TRIGGERS’
 
DESC USER_TRIGGERS;
 
TRIGGER_NAME — Name of the trigger
TRIGGER_TYPE  – The type is BEFORE, AFTER, INSTEAD OF
TRIGGER_EVENT — The DML operation firing the trigger
TABLE_OWNER   – Owner of the table
BASE_OBJECT_TYPE  – Object type
TABLE_NAME    – Name of the database table
COLUMN_NAME – Name of the Column
REFERENCING_NAMES  – Name used for :OLD and :NEW
WHEN_CLAUSE     – The when_clause used
STATUS           – The status of the trigger
DESCRIPTION  – Description
ACTION_TYPE  – Type of action
TRIGGER_BODY — The action to take

We can see PL/SQL Syntax errors (compilation errors) in the ‘USER_ERRORS’ data dictionary view

Managing triggers:

  • Disable or reenable a database trigger:
             ALTER TRIGGER trigger_name DISABLE | ENABLE
  • Disable or reenable all triggers for a table:
             ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS
  • Recompile a trigger for a table:
           ALTER TRIGGER trigger_name COMPILE
  • To remove a trigger from the database, use the DROP TRIGGER syntax:
           DROP TRIGGER trigger_name;

Oracle mutating trigger table errors:

The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger, resulting in the “ORA-04091: table name is mutating, trigger/function may not see it.” message.
How to solve it?
Don’t use triggers – The best way to avoid the mutating table error is not to use triggers.  While the object-oriented Oracle provides “methods” that are associated with tables, most savvy PL/SQL developers avoid triggers unless absolutely necessary.
Use an “after” trigger - If you must use a trigger, it’s best to avoid the mutating table error by using an “after” trigger. For example, using a trigger “:after update on xxx”, the original update has completed and the table will not be mutating.
Use autonomous transactions – You can avoid the mutating table error by marking your trigger as an autonomous transaction, making it independent from the table that calls the procedure.