Cursors

A cursor is a temporary work area created in the system memory when a SQL statement is executed. For every SQL statement execution certain area in memory is allocated. This private SQL area is called context area or cursor. A cursor acts as a handle or pointer into the context area. A PL/SQL program controls the context area using the cursor. Cursor represents a structure in memory and is different from cursor variable. When you declare a cursor, you get a pointer variable, which does not point any thing. When the cursor is opened, memory is allocated and the cursor structure is created. The cursor variable now points the cursor. When the cursor is closed the memory allocated for the cursor is released. Cursors allow the programmer to retrieve data from a table and perform actions on that data one row at a time. There are two types of cursors implicit cursors and explicit cursors. There are two types of cursors in PL/SQL:

Implicit cursors: These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed. Implicit cursors are easy to code, and they retrieve exactly one row. When you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit cursors are created to process these statements. Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN. For example, When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected. When a SELECT... INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.
SELECT empname, empsal INTO ename, esal FROM EMPLOYEE WHERE EMPNO = 1001;

Note: Ename and Esal are columns of the table EMPLOYEE and ename and esal are the variables used to store ename and sal fetched by the query.

The status of the cursor for each of these attributes are defined in the below table. Attributes %FOUND Return Value Example The return value is TRUE, if the DML SQL%FOUND statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT .INTO statement return at least one row. The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect row and if SELECT.INTO statement do not return a row. %NOTFOUND The return value is FALSE, if DML statements SQL%NOTFOUND like INSERT, DELETE and UPDATE at least one row and if SELECT .INTO statement return at least one row. The return value is TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect even one row and if SELECT .INTO statement does not return a row. %ROWCOUNT Return the number of rows affected by the DML SQL%ROWCOUNT operations INSERT, DELETE, UPDATE, SELECT

For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:
DECLARE var_rows number(5); BEGIN UPDATE employee SET salary = salary + 1000; IF SQL%NOTFOUND THEN dbms_output.put_line('None of the salaries where updated'); ELSIF SQL%FOUND THEN var_rows := SQL%ROWCOUNT; dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated'); END IF; END;

In the above PL/SQL Block, the salaries of all the employees in the employee table are updated. If none of the employees salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in employee table.

Explicit cursors: Explicit cursors are used in queries that return multiple rows. The set of rows fetched by a query is called active set. The size of the active set meets the search criteria in the select statement. Explicit cursor is declared in the DECLARE section of PL/SQL program. They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row. Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed. Syntax:
CURSOR <cursor-name> IS <select statement>

Sample Code:
DECLARE CURSOR emp_cur IS SELECT ename FROM EMP; BEGIN -----END;

Processing multiple rows is similar to file processing. For processing a file you need to open it, process records and then close. Similarly user-defined explicit cursor needs to be opened, before reading the rows, after which it is closed. Like how file pointer marks current position in file processing, cursor marks the current position in the active set. The following are the steps to use an explicit cursor. a. Declare the cursor -- This means give the cursor a name and associate the query that is going to return multiple rows. b. Open the cursor -- execute the query c. Fetch the cursor -- Get the result set and loop through to process them d. Close the cursor -- Close cursor processing. Declaring explicit cursors To use an explicit cursor, you must first declare it in the declaration section of a block or package. There are three types of explicit cursor declarations:

A cursor without parameters, such as:

CURSOR company_cur IS SELECT company_id FROM company;

A cursor that accepts arguments through a parameter list:

CURSOR company_cur (id_in IN NUMBER) IS SELECT name FROM company WHERE company_id = id_in;

A cursor header that contains a RETURN clause in place of the SELECT statement:

CURSOR company_cur (id_in IN NUMBER) RETURN company%ROWTYPE IS SELECT * FROM company;

Opening explicit cursors To open a cursor, use the following syntax:

OPEN cursor_name [(argument [,argument ...])];

where cursor_name is the name of the cursor as declared in the declaration section. The arguments are required if the definition of the cursor contains a parameter list. You must open an explicit cursor before you can fetch rows from that cursor. When the cursor is opened, the processing includes the PARSE, BIND, OPEN, and EXECUTE statements. This OPEN processing includes: determining an execution plan, associating host variables and cursor parameters with the placeholders in the SQL statement, determining the result set, and, finally, setting the current row pointer to the first row in the result set. When using a cursor FOR loop, the OPEN is implicit in the FOR statement. If you try to open a cursor that is already open, PL/SQL will raise an "ORA-06511: PL/SQL: cursor already open" exception. Fetching from explicit cursors The FETCH statement places the contents of the current row into local variables. To retrieve all rows in a result set, each row needs to be fetched. The syntax for a FETCH statement is:
FETCH cursor_name INTO record_or_variable_list;

where cursor_name is the name of the cursor as declared and opened.

Closing explicit cursors The syntax of the CLOSE statement is:

CLOSE cursor_name;

where cursor_name is the name of the cursor declared and opened. After all rows have been fetched, a cursor needs to be closed. Closing a cursor releases the private SQL area used by the cursor, freeing the memory used by that cursor. If you declare a cursor in a local anonymous, procedure, or function block, that cursor will automatically close when the block terminates. Package-based cursors must be closed explicitly, or they stay open for the duration of your session. Closing a cursor that is not open raises an INVALID CURSOR exception. Explicit cursor attributes There are four attributes associated with cursors: ISOPEN, FOUND, NOTFOUND, and ROWCOUNT. These attributes can be accessed with the % delimiter to obtain information about the state of the cursor. The syntax for a cursor attribute is:
cursor_name%attribute

where cursor_name is the name of the explicit cursor. The behaviors of the explicit cursor attributes are described in the following table. Attribute %ISOPEN Description TRUE if cursor is open. FALSE if cursor is not open. %FOUND INVALID_CURSOR is raised if cursor has not been OPENed. NULL before the first fetch. TRUE if record was fetched successfully. FALSE if no row was returned. INVALID_CURSOR if cursor has been CLOSEd. %NOTFOUND INVALID_CURSOR is raised if cursor has not been OPENed.

Attribute

Description NULL before the first fetch. FALSE if record was fetched successfully. TRUE if no row was returned. INVALID_CURSOR if cursor has been CLOSEd.

%ROWCOUNT INVALID_CURSOR is raised if cursor has not been OPENed. The number of rows fetched from the cursor. INVALID_CURSOR if cursor has been CLOSEd.

The SELECT FOR UPDATE clause By default, the Oracle RDBMS locks rows as they are changed. To lock all rows in a result set, use the FOR UPDATE clause in your SELECT statement when you OPEN the cursor, instead of when you change the data. Using the FOR UPDATE clause does not require you to actually make changes to the data; it only locks the rows when opening the cursor. These locks are released on the next COMMIT or ROLLBACK. As always, these row locks do not affect other SELECT statements unless they, too, are FOR UPDATE. The FOR UPDATE clause is appended to the end of the SELECT statement and has the following syntax:
SELECT ... FROM ... FOR UPDATE [OF column_reference] [NOWAIT];

where column_reference is a comma-delimited list of columns that appear in the SELECT clause. The NOWAIT keyword tells the RDBMS to not wait for other blocking locks to be released. The default is to wait forever.
DECLARE ENAME VARCHAR2(50); ESAL NUMBER(5); CURSOR C1 IS SELECT empname, empsal from employee; BEGIN

OPEN C1; FETCH C1 INTO ENAME, ESAL; DBMS_OUTPUT.PUT_LINE(Emp name: || ENAME ||Salary:|| FETCH C1 INTO ENAME, ESAL; DBMS_OUTPUT.PUT_LINE(Emp name: || ENAME ||Salary:|| CLOSE C1;

ESAL);

ESAL);

END;

In above example, a separate fetch is used for each row, instead of that loop can be used. Following example shows the usage of loop.
DECLARE ENAME VARCHAR2(50); ESAL NUMBER(5); CURSOR C1 IS SELECT empname, empsal from employee; BEGIN OPEN C1; LOOP FETCH C1 INTO ENAME, ESAL; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(Emp name: || ENAME ||Salary:|| END LOOP; CLOSE C1; ESAL);

END;

Above example shows the usage of Basic loop. Following example shows usage of WHILE loop.
DECLARE EMP_REC employee%rowtype; CURSOR C1 IS SELECT empname, empsal from employee; BEGIN OPEN C1; FETCH C1 INTO EMP_REC;

WHILE C1%FOUND LOOP DBMS_OUTPUT.PUT_LINE(Emp name: || EMP_REC.EMPNAME ||Salary:|| EMP_REC.EMPSAL); FETCH C1 INTO EMP_REC; END LOOP; CLOSE C1; END;

Using Cursor For Loop: The cursor for Loop can be used to process multiple records. There are two benefits with cursor forLoop 1. It implicitly declares a %ROWTYPE variable, also uses it as LOOP index 2. Cursor For Loop itself opens a cursor, read records then closes the cursor automatically. Hence OPEN, FETCH and CLOSE statements are not necessary in it.

DECLARE CURSOR C1 IS SELECT empname, empsal from employee; BEGIN FOR EMP_REC IN C1 LOOP DBMS_OUTPUT.PUT_LINE(Emp name: || EMP_REC.EMPNAME ||Salary:|| EMP_REC.EMPSAL); END LOOP; END;

emp_rec is automatically created variable of %ROWTYPE. We have not used OPEN, FETCH , and CLOSE in the above example as for cursor loop does it automatically. The above example can be rewritten as shown in the Fig , with less lines of code. It is called Implicit for Loop. Deletion or Updation Using Cursor: In all the previous examples I explained about how to retrieve data using cursors. Now we will see how to modify or delete rows in a table using cursors. In order to Update or Delete rows, the cursor must be defined with the FOR UPDATE clause. The Update or Delete statement must be declared with WHERE CURRENT OF The WHERE CURRENT OF clause UPDATE and DELETE statements can use a WHERE CURRENT OF clause if they reference a cursor declared FOR UPDATE. This syntax indicates that the UPDATE or DELETE should modify the current row identified by the FOR UPDATE cursor.
The syntax is:
[UPDATE | DELETE ] ... WHERE CURRENT OF cursor_name;

By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the SELECT statement.
DECLARE CURSOR C1 IS SELECT empname, empsal from employee FOR UPDATE; BEGIN FOR EMP_REC IN C1 LOOP IF EMP_REC.EMPSAL < 2500 THEN UPDATE EMPLOYEE SET SAL = SAL + 1000 WHERE CURRENT OF C1; END IF; END LOOP; END;

Procedures

A stored procedure is a named PL/SQL block which performs an action. A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage. The syntax for a procedure is: CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ] IS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [procedure_name]; IS - marks the beginning of the body of the procedure and is similar to DECLARE in anonymous PL/SQL Blocks. The code between IS and BEGIN forms the Declaration section. The syntax within the brackets [ ] indicate they are optional. By using CREATE OR REPLACE together the procedure is created if no other procedure with the same name exists or the existing procedure is replaced with the current code. Attributes of Procedure Parameters Parameter Attribute Name Mode

Description This must be a legal PL/SQL identifier. This indicates whether the parameter is an input-only parameter (IN), an outputonly parameter (OUT), or is both an input and an output parameter (IN OUT). If the mode is not specified, then IN is assumed. This is a standard PL/SQL datatype.

Datatype

There are three types of parameter: IN, OUT and IN OUT. 1. An IN parameter is used as input only. An IN parameter is passed by reference but cannot be changed by the called program. 2. An OUT parameter is initially NULL. The program assigns the parameter a value and that value is returned to the calling program. 3. An IN OUT parameter is a combination of IN and OUT. The value of the actual parameter is passed into the procedure when procedure is invoked. Inside the procedure, the formal parameter can be read from and written to. When the procedure finishes and control returns to the calling environment, the contents of the formal parameter are assigned to the actual parameter. An IN parameter is an rvalue inside a procedure; it can only appear on the right-hand side of an assignment statement. An OUT parameter is an lvalue; it can only appear on the left-hand side of an assignment statement, and not on the right-hand side, even if it has been assigned to in the procedure. An IN OUT parameter is both an rvalue and an lvalue, and thus can appear on either side of an assignment statement.

The Procedure Body

The body of a procedure is a PL/SQL block with declarative, executable, and exception sections. The declarative section is located between the IS or AS keyword and the BEGIN keyword. The executable section is located between the BEGIN and EXCEPTION keywords. The exception section is located between the EXCEPTION and END keywords. When a procedure is called, the values of the actual parameters are passed in, and they are referred to using the formal parameters inside the procedure. Not only the values are passed, but the constraints on the variables are passed as well, as part of the parameter passing mechanism. In a procedure declaration, it is illegal to constrain CHAR and VARCHAR2 parameters with a length, and NUMBER parameters with a precision and/or scale. For example, the following procedure declaration is illegal and will generate a compilation error.
CREATE OR REPLACE PROCEDURE get_Employees( pEmpID IN NUMBER(5), pEmpName IN VARCHAR2(50) ) IS BEGIN pEmpID = 1001; pEmpName = Bhavesh Patankar; END;

The correct declaration of the above procedure would be

CREATE OR REPLACE PROCEDURE get_Employees( pEmpID IN NUMBER, pEmpName IN VARCHAR2 ) IS BEGIN pEmpID = 1001; pEmpName = Bhavesh Patankar; END;

So, what are the constraints on pEmpId and pEmpName? They come from the actual parameters. See below example.
DECLARE v_EmpID IN NUMBER(5); v_EmpName IN VARCHAR2(50); BEGIN get_Employees(v_EmpId, v_EmpName); END;

The parameter pEmpID will have a precision of 5(coming from the actual parameter v_EmpID) and pEmpName will have a maximum length of 50. It is important to be aware of this. %TYPE and procedure parameters The only way to get constraints on the formal parameter is to use %TYPE. If a formal parameter is declared using %TYPE, and the underlying type is constrained, then the constraint will be on the formal parameter rather than the actual parameter. Positional and Named Notation Whatever we have seen so far in this chapter, the actual parameters are associated with the formal parameters by position. E.g.
CREATE OR REPLACE PROCEDURE get_Students( pMarks IN NUMBER, pCity IN VARCHAR2 ) IS BEGIN SELECT * from Students where marks >= pMarks city = pCity; END;

Lets call this procedure as follows:

DECLARE v_Marks IN NUMBER(5); v_City IN VARCHAR2(50); BEGIN get_Students(v_Marks, v_City);

END;

The actual parameters are associated with the formal parameters by position: v_Marks is associated with the pMarks, and v_City is associated with pCity. Alternatively, we can call the procedure using named notation:
DECLARE v_Marks IN NUMBER(5); v_City IN VARCHAR2(50); BEGIN get_Students(pMarks => v_Marks, pCity => v_City); END;

Basic Examples: Example 1:

CREATE OR REPLACE PROCEDURE add_Numbers( pNum1 IN NUMBER, pNum2 IN NUMBER, pSum OUT NUMBER, ) IS BEGIN

-- adding two numbers

pSum := pNum1 + pNum2; END;

There are three parameters in the procedure. Two parameters pNum1 and pNum2 are input parameters and one parameter pSum is an output parameter. In the procedure body weve just added two numbers and stored the sum in the output parameter.

Example 2:
CREATE OR REPLACE PROCEDURE adjust_salary( pemployee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE, ppercent IN NUMBER ) IS BEGIN UPDATE employees SET salary = salary + salary * in_percent / 100 WHERE employee_id = in_employee_id; END;

-- update employee's salary

There are two parameters of the procedure pEMPLOYEE_ID and pPERCENT. This procedure will update salary information by a given percentage (pPERCENT) for a given employee specified by pEMPLOYEE_ID. In the procedures body, we use SQL UPDATE statement to update salary infor`mation.

Calling PL/SQL Procedures

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;

Removing PL/SQL Procedures

When a stored procedure is no longer required, we can remove the stored procedure from the database using DROP command.

Functions
A function is similar to a procedure. Both take arguments, which can be of any mode. Both are different forms of PL/SQL blocks, with a declarative, executable and exception section. Both can be stored in the database or declared within a block. However a procedure call is a PL/SQL statement by itself, while a function call is called as part of an expression. In all we can say a function is a named PL/SQL block which returns a value.
Syntax of function

The PL/SQL block must have a return statement.

A function can have more than one return statement in the body, although only one of them would be executed. It is an error for a function to end without executing a RETURN. The following example illustrates multiple RETURN statements in one function. Even though there are five different RETURN statements in the function, only one of them is executed.

CREATE OR REPLACE FUNCTION getResult( pStudentID IN NUMBER ) RETURN VARCHAR2 IS v_Percentage Number(2); BEGIN

-- get student marks and display the result

SELECT (AvailMarks/TotalMarks)*100 INTO v_Percentage FROM Students WHERE studentID = pStudentID; IF v_Percentage >= 70 THEN RETURN Distinction; ELSIF v_Percentage >= 60 THEN RETRUN First Class; ELSIF v_Percentage >= 50 THEN RETRUN Second Class; ELSIF v_Percentage >= 40 THEN RETRUN Pass Class; ELSE RETURN FAIL; END;

Executing PL/SQL Functions

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;

If employee_name is of datatype varchar we can store the name of the employee by assigning the return type of the function to it. 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);

This line displays the value returned by the function.

Function Execute as a PL/SQL expression Must contain a RETURN clause in header Must return a single value Function body must contain at least one return statement.

Procedure Execute as a PL/SQL statement No RETURN clause in header Can return none, one or more return values Can contain a return statement

Removing PL/SQL Functions

When a function is no longer required, we can remove the function from the database using DROP command.

Error Handling
In general two types of errors can be occurred in PL/SQL program. 1) Compile-time 2) Run-time

Exceptions are designed for run-time error handling rather than compile-time error handling. Errors that can occur during compilation phase are detected by PL/SQL engine and reported back to the user. The program cannot handle these since the program has yet to run. For example, following block will raise compilation error.

DECLARE v_Employees Number; BEGIN SELECT count(*) from employeess; END;

This block will raise following compilation error.

PLS-201: identifier EMPLOYEESS must be declared

Exceptions and exception handlers are the method by which the program reacts and deals with run-time errors. Run-time errors include SQL errors such as
ORA-1: unique constrained violated

When an error occurs, an exception is raised. When this happens, control is passed to the exception handler, which is a separate section of the program. This separated the error handling from the rest of the program, which makes the logic of the program easier to understand. This also ensures that all the errors are trapped.

Advantages of PL/SQL Exceptions

Using exceptions for error handling has several advantages. Without exception handling, every time you issue a command, you must check for execution errors:

BEGIN SELECT... -- check for no data found error SELECT... -- check for no data found error SELECT... -- check for no data found error Error processing is not clearly separated from normal processing; nor is it robust. If you neglect to code a check, the error goes undetected and is likely to cause other, seemingly unrelated errors. With exceptions, you can handle errors conveniently without the need to code multiple checks, as follows: BEGIN SELECT ... SELECT ... SELECT ... ... EXCEPTION WHEN NO_DATA_FOUND THEN -- catches all no data found errors. Exceptions improve readability by letting you isolate error-handling routines. The primary algorithm is not obscured by error recovery algorithms. Exceptions also improve reliability. You need not worry about checking for an error at every point it might occur. Just add an exception handler to your PL/SQL block. If the exception is ever raised in that block (or any sub-block), you can be sure it will be handled. There are two types of exceptions user-defined and predefined. Scope Rules for PL/SQL Exceptions You cannot declare an exception twice in the same block. You can, however, declare the same exception in two different blocks. Exceptions declared in a block are considered local to that block and global to all its sub-blocks. Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a sub-block. The following example illustrates the scope rules:
DECLARE past_due EXCEPTION; acct_num NUMBER;

BEGIN DECLARE ---------- sub-block begins past_due EXCEPTION; -- this declaration prevails acct_num NUMBER; BEGIN ... IF ... THEN RAISE past_due; -- this is not handled END IF; END; ------------- sub-block ends EXCEPTION WHEN past_due THEN -- does not handle RAISEd exception ... END;

Predefined exceptions
Oracle has predefined several exceptions that correspond to the most common Oracle errors. Like the predefined types (NUMBERS, VARCHAR2 and so on), the identifiers for these exceptions are defined in package STANDARD. Because they are already available to the program, they are not required to be declared in the declarative section of the block. These predefined exceptions are described in the following

NO_DATA_FOUND This error is raised in two different situations. The first is when a SELECT... INTO statement does not return any rows. If the statement returns more than one row then TOO_MANY_ROWS is raised. The second situation is an attempt to reference a PL/SQL table element that has not been assigned a value. For example following block will raise NO_DATA_FOUND;
DECLARE TYPE t_EmployeeNames IS TABLE OF VARCHAR2 INDEX BY BINARY INTEGER; v_EmployeeTable t_EmployeeNames; v_TempEmployee varchar2(50); BEGIN v_TempEmployee := v_EmployeeTable(1); END;

INVALID_CURSOR This error is raised when an illegal cursor operation is performed, such as attempting to close the cursor which is already closed. Analogous situation of attempting to open a cursor that is already open causes CURSOR_ALREADY_OPEN to be raised.

Handling Predefined exception

When an error occurs, normal execution of the program/block stops and control transfer to its exception-handling part, which is formatted as shown above. To catch raised exception, exception handlers are written. Each handler contains a WHEN clause, which specifies an exception, followed by a sequence of statements to be executed when that exception raised. These statements complete the execution of block or program; control doesnt return to where the exception was raised. The optional OTHERS exception handler, which if present is the last handler in a block, acts as a handler for all exceptions which are not named in the section.

NonPredefined exceptions
A nonpredefined oracle server exception has attached an oracle error code, but it is not named by oracle server. You can trap such error with WHEN OTHERS clause or by declaring them with the name in the DECLARE section of the block. The declared exception is raised by oracle server implicitly or you can raise the error explicitly.

Pragma Exception Init Pragma is a compiler directive that associates an exception name with an internal oracle error code. The PRAGMA directive is not processed with the execution of a PL/SQL block, but it directs the PL/SQL compiler to associate a name with the error code. You can use more than one PRAGMAEXCEPTION_INIT directive in your DECLARE section to assign names to different error codes. You may even assign more than one name to the same error number. Naming an internal error code makes your program more readable.

Naming and associating are two separate statements in the declaration section. First, an exception name is declared as an EXCEPTION. Second the declared name is associated with an internal error code returned by SQLCODE with the PRAGMA directive. The general syntax is Exceptionname EXCEPTION; PRAGMA EXCEPTION_INIT(exceptionname, errornumber);

User-defined exceptions
A user-defined exception is an error that is defined by the program. It is not necessary that it is an Oracle error; it could be an error with the data. Predefined error, on the other hand corresponds to common SQL errors. Unlike predefined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE statements. User-defined exceptions are declared in the declarative section of a PL/SQL block. Just like variables, exceptions have a type (EXCEPTION) and scope. For example:
DECLARE e_TooManyEmployees EXCEPTION;

e_TooManyEmployees is an identifier that will be visible until the end of this block. Note that the scope of the exception is the same as the scope of any variable or cursor in the same declarative section. You must perform three steps for exceptions you want to define: 1. You must declare the exception in the DECLARE section. There is no need to use a PRAGMA directive, because there is no standard error number to associate. 2. You must raise the exception in the execution section of the program with an explicit RAISE statement. 3. You must write the handler for the exception. RAISE_APPLICATION_ERROR The RAISE_APPLICATION_ERROR procedure allows you to display nonstandard error codes and user-defined error messages from stored subprograms. The general syntax is
RAISE_APPLICAION_ERROR(error_code, error_message, [,TRUE/FALSE]);

Where the error_code is a user-specified number between -20000 and -20999 and error_message is a user-supplied message. The third parameter is optional. TRUE means place the on the stack of other errors. FALSE means replace all previous errors.

Functions for trapping exceptions

SQLCODE: It returns the numeric value for the error code. SQLERRM: It returns the message associated with the error code. When an exception occurs, you can trap error number and message associated with the error by using two functions. Based on the code and message of the error, you can decide the subsequent action to take. SQLCODE returns the error number for the Oracle internal exception. We can pass the error number to the SQLERRM to get the message associated with the error number. For example:
DECLARE v_error_num number; v_error_msg varchar2(100); BEGIN . . . EXCEPTION . . . WHEN OTHERS THEN v_error_num := SQLCODE; v_error_msg := substr(SQLERRM,1,100); END;

How PL/SQL Exceptions Propagate

When an exception is raised, if PL/SQL cannot find a handler for it in the current block or subprogram, the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search. In the latter case, PL/SQL returns an unhandled exception error to the host environment.

Propagation Rules: Example 1

Propagation Rules: Example 2

Propagation Rules: Example 3

Packages
Package encapsulates related PL/SQL types, items and subprograms. Package is a PL/SQL construct that allows related items to be stored together. Package has two parts- the specification and the body. A package may contain objects from the following list: Cursors Scalar variables Composite variables Constants Exception names Type declarations for records and tables Functions and procedures

Package specification contains the information about the package. It doesnt contain the actual code. It contains only the declaration of the database items. Package body contains the actual code for the package. It contains the implementation of the declarative section. Syntax for package specification is:
CREATE OR REPLACE Package packagename IS/AS [variables, costants and Types declaration] [exception declaration] [cursor specification] [function specification] [procedure specification]

END [packagename]

If the specification of the package declares only types, constants, variables, exceptions, and call specs the package body is not required there. This type of packages only contains global variables that will be used by subprograms or cursors.
CREATE PACKAGE tempPack AS -- bodiless package TYPE Emprec IS RECORD ( FirstName VARCHAR2(25), LastName VARCHAR2(25)); AgeLimit CONSTANT INTEGER := 30; END tempPack;

For accessing the types, subprograms etc from a package we write Package_name.type_name Package_name.subprogram_name

e.g.
CREATE OR REPLACE PACKAGE PackTest AS -- package specification PROCEDURE proc1; FUNCTION func1 return varchar2; END PackTest; CREATE OR REPLACE PACKAGE BODY PackTest AS -- package body PROCEDURE proc1 as begin; dbms_output.put_line(procedure called); end; FUNCTION func1 return VARCHAR2 AS Begin Return(function called); End; END PackTest;

The following package spec and package body declare and define 2 procedures. First the procedure is used to insert new records into the table Employee, where the second procedure deletes a record from it.
CREATE OR REPLACE PACKAGE EmpPack AS -- package specification PROCEDURE AddEmployee(EmpID number, FirstName varchar2,Lastname varchar2); PROCEDURE DeleteEmployee(pEmpID number); END EmpPack; CREATE OR REPLACE PACKAGE BODY EmpPack AS -- package body PROCEDURE AddEmployee(EmpID number, FirstName varchar2,Lastname varchar2) as begin Insert into Employee values (EmpId, Firstname, Lastname); end; PROCEDURE DeleteEmployee(EmpID number) as begin Delete from Employee where empid = pEmpId; end; END EmpPack;

Advantages:

It allows you to group together related items, types and subprograms as a PL/SQL module. Top-down design When a procedure in a package is called entire package is loaded, though it happens to be expensive first time the response is faster for subsequent calls. Package allows us to create types, variable and subprograms that are private or public

Switching between public and private

When you first create a package, your decision about which elements of a package are public and which private is not cast in stone. You can, in fact, switch a public element to private and vice versa at any time.

If you find that a private element program or cursor should instead be made public, simply add the header of that element to the package specification and recompile. It will then be visible outside of the package. Notice that you do not need to make any changes at all to the package body.

If you want to make a private variable accessible directly from outside the package, you will need to remove the declaration of that data structure from the body and paste it into the specification. You cannot declare the same element in both the body and the specification. If you do make a public element private, you will need to remember that any program that referenced that element will no longer compile successfully. Calling methods of the package: After the specification is created, you create the body of the package. The body of a package is a collection of schema objects that was declared in the specification. If you perform any initialization in the package body, it is executed once when the package is initially referenced. To reference the package's subprograms and objects, you must use dot notation. The Syntax for Dot Notation

package_name.type_name package_name.object_name

Removing/Droping PL/SQL Packages When a package is no longer required, we can remove the package from the database using DROP command. DROP Package package_name;

Sequences
Sequences are special database objects that provide numbers in sequence for input to a table. They are useful for providing generated primary key values and for input of number type columns such as purchase order, employee number, sample number, and sales order number, where the input must be unique and in some form of numerical sequence. Creation of Sequences Sequences are created by use of the CREATE SEQUENCE command. where: sequence_name. The name you want the sequence to have. This may include the user name if created from an account with DBA privilege. n. An integer, positive or negative. INCREMENT BY. Tells the system how to increment the sequence. If it is positive, the values are ascending; if it is negative, the values are descending. START WITH. Tells the system which integer to start with. MINVALUE. Tells the system how low the sequence can go. For ascending sequences, it defaults to 1; for descending sequences, the default value is 10e27-1. MAXVALUE. Tells the system the highest value that will be allowed. For descending sequences, the default is 1; for ascending sequences, the default is 10e27-1. CYCLE. Causes the sequences to automatically recycle to minvalue when maxvalue is reached for ascending sequences; for descending sequences, it causes a recycle from minvalue back to maxvalue. CACHE. Caches the specified number of sequence values into the buffers in the SGA. This speeds access, but all cached numbers are lost when the database is shut down. The default value is 20; maximum value is maxvalue-minvalue. ORDER. Forces sequence numbers to be output in order of request. In cases where they are used for timestamping, this may be required. In most cases, the sequences numbers will be in order anyway, so ORDER will not be required. ORDER is necessary only to guarantee ordered generation if you are using Oracle with the Oracle Real Application Clusters option in parallel mode. If you are using exclusive mode, Oracle sequences numbers are always generated in order.

Triggers
A database triggers is stored PL/SQL program unit associated with a specific database table or view. The code in the trigger defines the action the database needs to perform whenever some database manipulation (INSERT, UPDATE, DELETE) takes place. Unlike the stored procedure and functions, which have to be called explicitly, the database triggers are fires (executed) or called implicitly whenever the table is affected by any of the above said DML operations. A database trigger has three parts 1. A triggering event 2. A trigger constraint (Optional) 3. Trigger action A triggering event can be an insert, update, or delete statement or a instance shutdown or startup etc. The trigger fires automatically when any of these events occur A trigger constraint specifies a Boolean expression that must be true for the trigger to fire. This condition is specified using the WHEN clause. The trigger action is a procedure that contains the code to be executed when the trigger fires. Types of Triggers The following are the different types of triggers. Row triggers and statement triggers A Row trigger fires once for each row affected. It uses FOR EACH ROW clause. They are useful if trigger action depends on number of rows affected. Statement Trigger fires once, irrespective of number of rows affected in the table. Statement triggers are useful when triggers action does not depend on the data provided by the triggering statement or the rows affected. Before and afterTriggers While defining the trigger we can specify whether to perform the trigger action (i.e. execute trigger body) before or after the triggering statement. BEFORE and AFTER triggers fired by DML statements can only be defined on tables.

BEFORE triggers The trigger action here is run before the trigger statement.

BEFORE triggers are used when the trigger action should determine whether the triggering statement should be allowed to complete. By using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its

eventual rollback in cases where an exception is raised in the trigger action.

BEFORE triggers are used to derive specific column values before completing a triggering INSERT or UPDATE statement.

AFTER triggers The trigger action here is run after the trigger statement.

AFTER triggers are used when you want the triggering statement to complete before executing the trigger action. If a BEFORE trigger is already present, an AFTER trigger can perform different actions on the same triggering statement.

:old and :new in Row-Level Triggers A row-level triggers fires once per row processed by the triggering statement. Inside the trigger, you can access the row that is currently being processed. This is accomplished through two pseudo-records-:old and :new. :old and :new are not true records. The :old and :new records are only valid inside row-level triggers. If you try to reference either inside a statement-level trigger, you will get a compile error. Since a statement-level trigger executes once-even if there are many rows prcessed by the statement-:old and :new have no meaning. Which row would they refer to?

The WHEN clause The WHEN clause is valid for row-level triggers only. If present, the trigger body will be executed only for those rows that meet the condition specified by the WHEN clause. The WHEN clause looks like WHEN condition Where condition is a Boolean expression. It will be evaluated for each row. The :new and :old records can be referenced inside condition as well, but the colon is not used there. The colon is only valid in the trigger body. For example, the body of the Print_salary_changes is only executed if the Empno Is greater than 0. Example:
CREATE OR REPLACE TRIGGER Print_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab FOR EACH ROW WHEN (new.Empno > 0) DECLARE sal_diff number; BEGIN

sal_diff := :new.sal - :old.sal; dbms_output.put('Old salary: ' || :old.sal); dbms_output.put(' New salary: ' || :new.sal); dbms_output.put_line(' Difference ' || sal_diff); END; /

Trigger Type Combinations

Using the options listed previously, you can create four types of row and statement triggers:

BEFORE statement trigger

Before executing the triggering statement, the trigger action is run.

BEFORE row trigger

Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is run, if the trigger restriction was not violated.

AFTER row trigger

After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is run for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.

AFTER statement trigger

After executing the triggering statement and applying any deferred integrity constraints, the trigger action is run. You can have multiple triggers of the same type for the same statement for any given table. For example, you can have two BEFORE statement triggers for UPDATE statements on the employees table. Multiple triggers of the same type permit modular installation of applications that have triggers on the same tables. Also, Oracle materialized view logs use AFTER row triggers, so you can design your own AFTER row trigger in addition to the Oracle-defined AFTER row trigger. You can create as many triggers of the preceding different types as you need for each type of DML statement, (INSERT, UPDATE, or DELETE). For example, suppose you have a table, SAL, and you want to know when the table is being accessed and the types of queries being issued. The following example contains a sample package and trigger that tracks this information by hour and type of action (for example, UPDATE, DELETE, or INSERT) on table SAL. The global session variable STAT.ROWCNT is initialized to zero

by a BEFORE statement trigger. Then it is increased each time the row trigger is run. Finally the statistical information is saved in the table STAT_TAB by the AFTER statement trigger.

INSTEAD of Triggers provide a way of modifying views that can not be modified directly using DML statements. An updatable view is one that lets you perform DML on the underlying table. Some views are inherently updatable, but others are not because they were created with one or more of the constructs listed in Views that Require INSTEAD OF Triggers. Any view that contains one of those constructs can be made updatable by using an INSTEAD OF trigger. INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through UPDATE, INSERT, and DELETE statements. These triggers are invoked INSTEAD OF triggers because, unlike other types of triggers, the database fires the trigger instead of executing the triggering statement. The trigger must determine what operation was intended and perform UPDATE, INSERT, or DELETE operations directly on the underlying tables. 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.

Enabling and Disabling Triggers

A trigger can be in one of two distinct modes: Enabled. An enabled trigger executes its trigger body if a triggering statement is entered and the trigger restriction (if any) evaluates to TRUE. Disabled. A disabled trigger does not execute its trigger body, even if a triggering statement is entered and the trigger restriction (if any) evaluates to TRUE.

Enabling Triggers
By default, a trigger is automatically enabled when it is created; however, it can later be disabled. After you have completed the task that required the trigger to be disabled, re-enable the trigger, so that it fires when appropriate.

Enable a disabled trigger using the ALTER TRIGGER statement with the ENABLE option. To enable the disabled trigger named REORDER of the INVENTORY table, enter the following statement:
ALTER TRIGGER Reorder ENABLE;

All triggers defined for a specific table can be enabled with one statement using the ALTER TABLE statement with the ENABLE clause with the ALL TRIGGERS option. For example, to enable all triggers defined for the INVENTORY table, enter the following statement:
ALTER TABLE Inventory ENABLE ALL TRIGGERS;

Disabling Triggers
You might temporarily disable a trigger if:

An object it references is not available. You need to perform a large data load, and you want it to proceed quickly without firing triggers. You are reloading data.

By default, triggers are enabled when first created. Disable a trigger using the ALTER TRIGGER statement with the DISABLE option. For example, to disable the trigger named REORDER of the INVENTORY table, enter the following statement:
ALTER TRIGGER Reorder DISABLE;

All triggers associated with a table can be disabled with one statement using the ALTER TABLE statement with the DISABLE clause and the ALL TRIGGERS option. For example, to disable all triggers defined for the INVENTORY table, enter the following statement:
ALTER TABLE Inventory DISABLE ALL TRIGGERS;

Restrictions of Triggers
A trigger may not issue any transaction control statements- COMMIT, ROLLBACK, or SAVEPOINT. Likewise, any procedures or functions that are called by the trigger body cannot issue any transaction control statements. The trigger body cannot declare any LONG or LONG RAW variables. We cannot pass parameters to Database triggers and they cannot return a value.

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;
Points to ponder

A trigger cannot include COMMIT, SAVEPOINT and ROLLBACK. We can use only one trigger of a particular type . A table can have any number of triggers. We use correlation names :new and :old can be used to refer to data in command line and data in table respectively.