RDBMS Course Material

Relational Database Management System

7.10.4.

Using DELETE statements in PL/SQL DELETE FROM table_name [WHERE where_clause]

The syntax for deleting a record or a set of records in PL/SQL is shown above. We can write delete statement and hard code the value for any straightforward deletion, or we can accept the value to be deleted from the end user and substitute the PL/SQL variable in the appropriate place for deletion. BEGIN DELETE FROM branch WHERE branchid='B4'; END; /

8. PL/SQL EXCEPTIONS

211 | P a g e

Infosys Foundation Program


8.1.

Introduction

In this chapter, we would be discussing about how exceptions are handled in PL/SQL. When a PL/SQL is compiled, if there are any compilation errors, such as incorrect syntax usage then the PL/SQL compilation unit would throw compilation errors. PL/SQL block which does have any compilation error, might throw some runtime errors or exceptions, during the PL/SQL code execution, by the PL/SQL runtime engine. For example, when a programmer writes an arithmetic expression which leads to division by zero situations then ZERO_DIVIDE runtime exception is thrown by the PL/SQL block. These exceptions can be trapped by writing exception handlers appropriately in the exception block of PL/SQL. Thus exception is an identifier in PL/SQL raised during the execution of a PL/SQL block. Whenever an exception arises the control leaves the main body of action and transfers the control to the EXCEPTION section of the anonymous PL/SQL block. For example, if the exception is thrown in the n th line of a PL/SQL block, the control will not return to the (n+1) th line. The execution of the program would continue in the exception handler, and then to any outer block, if it is nested. Also the program execution would never return to the subsequent statement after the exception is raised.

8.2.

How to handle exception?

Using the exception part of a PL/SQL block we can handle exceptions. If the exceptions are not trapped in the exception part of a PL/SQL, these exceptions would be propagated to the calling environment. Also note that exceptions can be raised in the declaration part, executable part as well as in the exception part.

8.3.

Exception syntax EXCEPTION WHEN exception1 [OR exception2 . . .] THEN statement1; statement2; ... [WHEN exception3 [OR exception4 . . .] THEN statement1; statement2;

212 | P a g e



. . .] [WHEN OTHERS THEN statement1; statement2; . . .] END; The above code snippet shows the syntax of writing exception handlers. EXCEPTION keyword starts the exception handling section. PL/SQL programmer can define several exception handlers, each with its own set of actions. The runtime exception identifier which need to be handled should be placed in between the WHEN and THEN keyword. If more than one exception situation need to be handled, then we can make use of optional OR keyword in between two exception identifier names. We cannot replace the optional OR keyword by AND keyword. When an exception occurs, only one among the several exception handlers would be executed before leaving the EXCEPTION block, after which the control would be transferred to the outer block or to the calling environment. As a programmer, we anticipate lot of runtime error situations which a PL/SQL block might come across, but what would happen in case, if there is an unanticipated runtime error. The solution is to go for a WHEN OTHERS exception handler, which would take care of all other unanticipated runtime error scenarios.

8.4.

Exception Types

Exceptions can be classified into following types. 1. Predefined oracle server exceptions 2. Non-predefined oracle server exceptions 3. User defined exceptions

8.4.1.

Raising exceptions

In general exceptions can be raised in the executable or in the exception section of a PL/SQL block. Predefined exceptions are raised implicitly, whenever that situation arises. PL/SQL runtime engine executes statements associated with the trapped predefined exception. We can raise our own exceptions also explicitly in the executable section or in the exception section.

8.5.

Predefined oracle server exception

Predefined exceptions are raised implicitly whenever an anticipated error situation arises while executing the statements in the PL/SQL block. PL/SQL runtime engine executes statements associated with the trapped predefined exception.

213 | P a g e



For example, division by zero is a predefined error situation. Whenever this error situation arises immediately the control would be moved over to the exception section searching for ZERO_DIVIDE exception identifier. If the programmer has written a block of statement under this ZERO_DIVIDE exception identifier, we say that the programmer has defined an exception handler. The PL/SQL runtime engine also executes the bunch of statements associated with this exception handler, after which the control is transferred to the enclosed outer block, if any or to the calling SQL environment. Oracle Error

Predefined Exception

Description

ORA-1403

NO_DATA_FOUND

SELECT statement matches no rows

ORA-1422

TOO_MANY_ROWS

SELECT statement matches more than one row

ORA-0001

DUP_VAL_ON_INDEX

Unique constraint violated

ORA-1476

ZERO_DIVIDE

Division by zero

ORA-6502

VALUE_ERROR

Truncation, Arithmetic error

ORA-1722

INVALID_NUMBER

Conversion to a number failed. Ex. “2A” is not valid

To trap a predefined oracle server exception we need to know the standard name. We can refer to oracle documentation for predefined oracle server exception identifier names.

8.5.1.

NO_DATA_FOUND predefined exception

NO_DATA_FOUND is a predefined oracle server exception that would be implicitly raised, whenever a SELECT statement enclosed in a PL/SQL block, fails to identify a matching record in the underlying table. Note that if an INSERT or UPDATE or DELETE statement does not affect one or more rows, this exception is NOT thrown or raised. Only when the SELECT statement fails in a PL/SQL block, the above predefined exception is thrown. SQL> DECLARE 2 v_branchid branch.branchid%TYPE; 3 v_seats branch.seatsavailable%TYPE; 4 BEGIN 5 v_branchid := '&branchid'; 6 SELECT seatsavailable INTO v_seats FROM branch WHERE branchid 214 | P a g e



LIKE v_branchid; 7 DBMS_OUTPUT.PUT_LINE('Seats Available: ' || v_seats); 8 EXCEPTION 9 WHEN NO_DATA_FOUND THEN 10 DBMS_OUTPUT.PUT_LINE('Invalid Branch ID'); 11 END; In the example, if the given branchid is present in the branch table, the above PL/SQL block would display the number of seats available; else it would display “Invalid Branch ID”.

8.5.2.

TOO_MANY_ROWS predefined exception

TOO_MANY_ROWS is a predefined oracle server exception that is thrown implicitly, whenever the SELECT statement fetches more than one row. --Given a valid supplierid identify whether he supplies one item or more --than one SET SERVEROUTPUT ON DECLARE v_supplierid itemsupplier.supplierid%TYPE; v_supplierrec itemsupplier%ROWTYPE; BEGIN SELECT * INTO v_supplierrec FROM itemsupplier WHERE supplierid ='&v_supplierid'; DBMS_OUTPUT.PUT_LINE('Supplier '||v_supplierid|| ' supplies only one item'); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Supplier '||v_supplierid|| ' supplies more than one item'); END; The above PL/SQL block deals with itemsupplier table, which captures the details of list of suppliers who supplies various items. Assuming that the same supplier can supply more than one item, based on supplierid when we try to fetch records from the supplier table, this might affect more than one record. Hence this would be leading to TOO_MANY_ROWS predefined exception and would display a message “Supplier <> supplies more than one item”.

215 | P a g e



8.5.3.

DUP_VAL_ON_INDEX predefined exception

DUP_VAL_ON_INDEX predefined exception is thrown whenever we try to duplicate a primary key column in a table. SQL> DECLARE 2 v_student student%ROWTYPE; 3 BEGIN 4 v_student.studentid = &studentid; 5 v_student.applicationid = &applicationid; 6 v_student.currentsemester = ¤tsemester; 7 v_student.branchid = '&branchid'; 8 v_student.userid = '&userid'; 9 v_student.password = '&password'; 10 v_student.residentialstatus = '&resstatus'; 11 INSERT INTO student VALUES(v_student.studentid, v_student.applicationid, v_student.currentsemester, v_student.branchid, v_student.userid, v_student.password, v_student.residentialstatus); 12 EXCEPTION 13 WHEN DUP_VAL_ON_INDEX THEN 14 DBMS_OUTPUT.PUT_LINE('Duplicate Student ID'); 15 WHEN OTHERS THEN 16 DBMS_OUTPUT.PUT_LINE('Transaction Failed'); 17 END; In the above PL/SQL block, while inserting a student record, if the student id is duplicated, then we would receive a message “Duplicate Student ID”.

8.5.4.

VALUE_ERROR predefined exception

VALUE_ERROR predefined exception is thrown in 2 different scenarios. 1. When an entered or accepted input data value from the user is very large. For example, v_studentid is declared as VARCHAR2 (6). If we try entering more than 6 characters as input, it would lead to truncation of the given input value, leading to VALUE_ERROR predefined exception. SQL> DECLARE 2 v_studentid VARCHAR2(6); 3 v_studentrec student%ROWTYPE; 4 BEGIN 5 v_studentid:= '&v_studentid'; 6 SELECT * INTO v_studentrec

216 | P a g e

FROM

student

WHERE



studentid=v_studentid; 7 DBMS_OUTPUT.PUT_LINE('Student Name '||v_studentrec.studentname); 8 EXCEPTION 9 WHEN VALUE_ERROR THEN 10 DBMS_OUTPUT.PUT_LINE('Entered input is very large'); 11 END;

is

2. When the expected input is numeric but the input entered by the user is characters, then VALUE_ERROR predefined exception is thrown.

8.5.5.

INVALID_NUMBER predefined exception

While we insert records to an underlying table, which expects a numeric value to be entered for a specific column, but a character value is entered (by mistake), then this would lead to INVALID_NUMBER predefined exception. SQL> BEGIN 2 --Inserting departmentid, departmentname, headofdepartment 3 --into department table 4 INSERT INTO department VALUES('X','BioMedical', ' I101'); 5 EXCEPTION 6 WHEN INVALID_NUMBER THEN 7 DBMS_OUTPUT.PUT_LINE('Not a valid number'); 8 END; The above example demonstrates the same, wherein the department table expects the department id (a numeric value) to be entered as input but a character value (‘X’) is entered while inserting a record into the same table. This PL/SQL block when compiled and executed throws INVALID_NUMBER predefined exception.

8.6.

Non-predefined oracle server exception

Every oracle error has an error code and an error message. Not for all runtime error situations, predefined exception names are available. There are runtime error situations which are unnamed in nature. These unnamed runtime error situations can be trapped either using WHEN OTHERS exception handler or we can associate an exception identifier to it using PRAGMA EXCEPTION_INIT PL/SQL compiler directive and then handle it implicitly. SQL> DECLARE 2 e_Missing_Null EXCEPTION; 3 PRAGMA EXCEPTION_INIT( e_Missing_Null, -1400); 4 BEGIN

217 | P a g e



5 6 7 8 9

INSERT INTO department VALUES (40, NULL , 'I101'); EXCEPTION WHEN e_Missing_Null THEN DBMS_OUTPUT.PUT_LINE('Missing value for a NOT NULL column '); END;

PRAGMA EXCEPTION_INIT compiler directive during compile time associates an oracle error number with an exception identifier specified. Once the association happens, henceforth we can handle the error situation with that associated oracle error number by writing an exception handler for the same. As shown in the above example, whenever a NOT NULL constraint violation happens while inserting or updating a record an error code -1400 is thrown during runtime with an oracle defined error message. There is no predefined exception identifier which can handle this oracle error situation. e_Missing_Null is an exception identifier declared in the declaration section. Using PRAGMA EXCEPTION_INIT we have associated that exception identifier with the oracle error number -1400. While inserting a department record, value has to be provided for department name and NULL value cannot be inserted for department name, else NOT NULL constraint violation exception would be thrown. As the above PL/SQL block does NULL value insertion for department name, implicitly NOT NULL constraint violation exception is raised, which in turn is handled in the exception block and displays “Missing value for a NOT NULL column”.

8.7.

User-defined exception

Exceptions which are very much specific to the business requirements can be implemented with the help of user defined exceptions. User defined exception identifiers are defined in the declaration section. Using RAISE exceptionidentifier; statement we raise a user-defined exception and exception handlers are written to handle the user-defined exception. e_Invalid_Departmentid is a user defined exception identifier declared in the declaration section. SELECT statement in the executable section identifies the count of number of records in the department table with the given departmentid. If no record exists with the given department id then v_count is set to 0. SQL> 2 3 4

218 | P a g e

DECLARE v_departmentid department.departmentid%TYPE; v_count NUMBER; e_Invalid_Departmentid EXCEPTION;



5 BEGIN 6 v_departmentid := '&v_departmentid'; 7 SELECT count(*) INTO v_count FROM department WHERE departmentid=v_departmentid; 8 IF v_count = 0 THEN 9 RAISE e_Invalid_Departmentid; 10 END IF; 11 DBMS_OUTPUT.PUT_LINE('Valid Department id'); 12 EXCEPTION 13 WHEN e_Invalid_Departmentid THEN 14 DBMS_OUTPUT.PUT_LINE('Invalid Department id'); 15 END; If v_count is zero, e_Invalid_Departmentid is raised in the executable part and trapped in the exception handling part which prints ‘Invalid Department id’.

8.8.

WHEN OTHERS exception handler

Exceptions which are not handled by any exception handlers, will be caught with the help of WHEN OTHERS exception handler. WHEN OTHERS can be used to handled all kinds of exception, irrespective of whether it is predefined or non-predefined or user-defined. SQL> DECLARE 2 v_departmentid department.departmentid%TYPE; 3 v_count NUMBER; 4 e_Invalid_Departmentid EXCEPTION; 5 BEGIN 6 v_departmentid := '&v_departmentid'; 7 SELECT count(*) INTO v_count FROM department WHERE departmentid=v_departmentid; 8 IF v_count = 0 THEN 9 RAISE e_Invalid_Departmentid; 10 END IF; 11 DBMS_OUTPUT.PUT_LINE('Valid Department id'); 12 EXCEPTION 13 WHEN OTHERS THEN 14 DBMS_OUTPUT.PUT_LINE('Invalid Department id'); 15 END; A simple analogy to persons familiar with Java, is that this is similar to the generic Exception class. We can have only one WHEN OTHERS exception handler within the exception section of a PL/SQL block.

219 | P a g e



WHEN OTHERS should be the last among the exception handler as it refers to rest of all errors not handled in the exception block of the PL/SQL block in which it is defined. Always place the WHEN OTHERS exception handler in the outermost block of PL/SQL block, when nested blocks are present within it.

8.9.

Using SQLCODE and SQLERRM

Using WHEN OTHERS we are able to just handle the unknown or unexpected runtime errors but to know the name of Oracle error and the Oracle code because of which the PL/SQL block failed we use SQLCODE and SQLERRM. Thus it helps us in identifying the reason behind the exception raised. A programmer might be interested in inserting the reasons behind failure of PL/SQL block into an audit_log table, which has the details of log records tracking the errors which happened over a period of time. (not dealt in the code snippet ) SQL> DECLARE 2 e_Missing_Null Exception; 3 PRAGMA EXCEPTION_INIT( e_Missing_Null, -1400); 4 v_sqlcode number; 5 v_sqlerrmsg varchar2(255); 6 BEGIN 7 INSERT INTO department VALUES (40, NULL, 'I101'); 8 EXCEPTION 9 WHEN OTHERS THEN 10 v_sqlcode:=SQLCODE; 11 v_sqlerrmsg:= SUBSTR(SQLERRM,1,255); 12 DBMS_OUTPUT.PUT_LINE('SQLCODE '||v_sqlcode); 13 DBMS_OUTPUT.PUT_LINE('SQLERRM '||v_sqlerrmsg); 14 END; SQLCODE and SQLERRM can be used both in the executable part and the exception part of a PL/SQL block. SQLCODE gives the numeric value of the oracle error code and SQLERRM gives the oracle error code and message associated with the oracle error. The maximum length of SQLERRM is 512 characters. The above example depicts the best practice that can be adhered to by assigning the SQLCODE and SQLERRM to a local variable in PL/SQL block and then using it. As these functions are procedural, we cannot use these variables directly inside an SQL statement.

220 | P a g e



8.10. RAISE_APPLICATION_ERROR built in procedure RASIE_APPLICATION_ERROR is a built in procedure used to create error messages, very much similar in a manner consistent with other oracle errors. In one shot, if we want to define the customized error messages and use it without writing any separate exception handlers for the same then we can go with this built in procedure. The two mandatory parameters for this are the error number and the error message. The error number has to be in the range of -20000 to -20999. The error message should not exceed 512 characters. SQL> DECLARE 2 v_departmentid department.departmentid%TYPE; 3 v_count NUMBER; 4 BEGIN 5 v_departmentid := '&v_departmentid'; 6 SELECT count(*) INTO v_count FROM department WHERE departmentid=v_departmentid; 7 IF v_count = 0 THEN 8 RAISE_APPLICATION_ERROR(-20000, 'Invalid Department id'); 9 END IF; 10 DBMS_OUTPUT.PUT_LINE('Valid Department id'); 11 END; In the above example, given a non-existent department id it would throw, “Invalid Department id” using RAISE_APPLICATION_ERROR built in. An implicit rollback also would happen whenever this procedure is executed, and hence changes initiated by this procedure would be rolled back.

8.11. Exception Propagation Exception can be raised in the declaration section, executable section and exception section. Exception raised in the executable section alone can be handled in the same PL/SQL block. Exception raised in the declaration and exception section of a PL/SQL block can be handled in the outer block in which it is enclosed. If it is not handled in the outer block, it would check whether the outer block is enclosed with any other block and so on. This we call it as propagation of exception. If none of the block handles the exception raised it would be propagated to the calling environment.

221 | P a g e



8.11.1.

Exception raised in the declaration section

In the below PL/SQL block exception is raised in the declaration section. As the programmer assigns a character value to a numeric variable, this leads to VALUE_ERROR predefined exception. Even though WHEN OTHERS is handled in the same block, as the exception is raised in the declaration part it can be handled only in the outer block. Hence we could see the error message displayed as numeric or value error. DECLARE v_seatsavailable NUMBER(3) := 'ABC'; BEGIN DBMS_OUTPUT.PUT_LINE(v_seatsavailable); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Value error occurred'); END; DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 2 In the below code snippet a PL/SQL block is enclosed in yet another block. WHEN OTHERS handler present in the outer block, handles the exception raised in the declaration part of the inner block, producing ‘Other error’ as output. SQL> BEGIN 2 3 DECLARE 4 v_seatsavailable NUMBER(3) := 'ABC'; 5 BEGIN 6 DBMS_OUTPUT.PUT_LINE(v_seatsavailable); 7 EXCEPTION 8 WHEN OTHERS THEN 9 DBMS_OUTPUT.PUT_LINE('Value error occurred'); 10 END; 11 12 DBMS_OUTPUT.PUT_LINE('Completed'); 13 EXCEPTION 14 WHEN OTHERS THEN 15 DBMS_OUTPUT.PUT_LINE('Other error');

222 | P a g e



16 END;

8.11.2.

Exception raised in the executable section

e_Invalid_Departmentid is an exception raised in the executable part of a PL/SQL block using RAISE statement and has been handled in the same block. Hence the following code produces 'Invalid Departmentid’ and prints ‘Successful completion' as execution of the outer block continues normally.

SQL> DECLARE 2 e_Invalid_Departmentid EXCEPTION; 3 BEGIN 4 BEGIN 5 RAISE e_Invalid_Departmentid; 6 EXCEPTION 7 WHEN e_Invalid_Departmentid THEN 8 DBMS_OUTPUT.PUT_LINE('Invalid Departmentid'); 9 END; 10 DBMS_OUTPUT.PUT_LINE('Successful completion'); 11 END;

8.11.3.

Exception raised in the exception section

In the below code snippet, e_Invalid_Itemid is an exception raised in the inner block and handled in the same block. But the e_Invalid_Itemid exception handler in turn raise yet another exception named e_Invalid_Customerid and this exception as it is raised in the exception block of a PL/SQL block it has to be handled in the exception section of the outer block. DECLARE e_Invalid_Itemid EXCEPTION; e_Invalid_Customerid EXCEPTION; BEGIN BEGIN RAISE e_Invalid_Itemid; EXCEPTION WHEN e_Invalid_Itemid THEN

223 | P a g e



RAISE e_Invalid_Customerid; WHEN e_Invalid_Customerid THEN DBMS_OUTPUT.PUT_LINE('Invalid Customerid'); END; END; As there is no outer block, PL/SQL runtime engine would assume that e_Invalid_Customerid is an unhandled user-defined exception and as that exception is in turn raised by e_Invalid_Itemid, the engine would also say that e_Invalid_Itemid is also an unhandled user defined exception. Hence, if we execute the above PL/SQL block, we could see twice in our output an error message is shown saying “unhandled user-defined exception” DECLARE * ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at line 9 ORA-06510: PL/SQL: unhandled user-defined exception The above the PL/SQL block is slightly modified and shown below with e_Invalid_Customerid handled in the outer block. DECLARE e_Invalid_Itemid EXCEPTION; e_Invalid_Customerid EXCEPTION; BEGIN BEGIN RAISE e_Invalid_Itemid; EXCEPTION WHEN e_Invalid_Itemid THEN RAISE e_Invalid_Customerid; WHEN e_Invalid_Customerid THEN DBMS_OUTPUT.PUT_LINE(‘Invalid Customerid in the nested block’); END; EXCEPTION WHEN e_Invalid_Customerid THEN DBMS_OUTPUT.PUT_LINE(‘Invalid Customerid in the outer block’); END;

224 | P a g e



9. PL/SQL cursors 9.1.

Cursors

Every SQL query submitted to the Oracle server affects one or more rows. The subset of row which is affected by the submitted SQL query is momentarily kept in a special place in the system memory of the oracle server. This temporary area is called private SQL work area, in which the rows affected by the query, count of number of records affected by the given query and a pointer to the parsed query, all are kept. Thus cursor is a private SQL work area. Every SQL statement executed by the oracle server has a separate private SQL work area associated with it. More than one row can be kept in the private SQL work area, but only one row can be processed at a time. This area is also called as context area by some authors. The set of rows that are held by the cursor currently is called an active set. Oracle can manage the cursor operations by itself for statements such as SELECT and DML statements then it is called implicit cursor. When the programmer manages the cursor operations then we call it as explicit cursor. Managing the cursor involves allocation of memory for the work area, opening the work area, fetching the records from the work area, closing or releasing the work area after the processing is done.

9.2.

Implicit cursors

Whenever INSERT, UPDATE or DELETE statements are executed, PL/SQL implicit cursors are created by default and rows are processed. Also when we write a SELECT statement which deals with only one row, implicit cursors are created and managed by the oracle server.

225 | P a g e



9.3.

Implicit cursors attributes

Implicit Cursor Attribute

Meaning

SQL%ROWCOUNT

Number of records affected by the most recent SQL statement

SQL%FOUND

Evaluates to TRUE if the most recent SQL statement affects one or more rows

SQL%NOTFOUND

Evaluates to TRUE if the most recent SQL statement does not affect any rows

SQL%ISOPEN

Always evaluates to FALSE because PL/SQL closes implicit cursors immediately after they are executed

After successful insertion, the values of implicit cursor attributes are as shown below. SQL%ISOPEN FALSE SQL%FOUND TRUE SQL%NOTFOUND FALSE SQL%ROWCOUNT 1 After successful updation, the values of implicit cursor attributes are as shown below. SQL%ISOPEN SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT

FALSE TRUE FALSE Depends on number of rows updated

After successful deletion, the values of implicit cursor attributes are as shown below. SQL%ISOPEN SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT

FALSE TRUE FALSE Depends on number of rows deleted

Do not make use of implicit cursor attributes to test the unsuccessfulness of SELECT statement using SQL%NOTFOUND beneath the SELECT statement. When a SELECT statement 226 | P a g e



fails, NO_DATA_FOUND predefined exception will be thrown. As soon as the control moves to NO_DATA_FOUND exception handler, if we try to check the values of all implicit cursor attributes it would be as shown below.

SQL%ISOPEN SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT

227 | P a g e

FALSE FALSE TRUE 0



9.4.

Implicit cursor example

The below example shows the usage of implicit cursor attributes. BEGIN UPDATE instructor SET remaininghours=NULL WHERE dateofjoining > '10-JAN-2007'; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ||' rows updated'); IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE(' Nobody joined after 10-JAN-2007'); END IF; COMMIT; END;

9.5.

Explicit Cursors

When we want to write SELECT statements in PL/SQL that deals with more than one row, we have to go for explicit cursors. When we specify any SELECT statement in the declaration part, we can consider that our plan is to go for explicit cursors. As mentioned earlier, we need to manage the explicit cursors of our own. Hence what are the various operations that the developer needs to do while managing explicit cursors, to gain complete control is what is discussed in the subsequent sections.

9.6.

Operations on explicit cursor

The operations on explicit cursor are as follows: 1. Declaring the cursor 2. Opening the cursor 3. Fetching the cursor 4. Closing the cursor Let’s have a closer look into all these activities in the below sections.

9.6.1.

Declaring the cursor

Use the CURSOR keyword to start the cursor declaration followed by the cursor identifier name and immediately followed by IS keyword. Note that this cursor identifier need not be declared anywhere in the PL/SQL block. Followed by IS keyword we can write any SQL query. All SQL queries supported in SQL environment are supported here in the declaration part too.

228 | P a g e



CURSOR c1 IS SELECT branchid FROM branch WHERE departmentid IN (SELECT departmentid FROM department); CURSOR c2 IS SELECT branchid, branchname, headofdepartment FROM department WHERE departmentid > 20; CURSOR c3 IS SELECT departmentid, count(*) FROM branch WHERE departmentid > 20 GROUP BY branchid; It is not necessary to write an INTO clause in the cursor declaration, as this does not make any sense. Even if by chance INTO clause is made use of in the cursor declaration, PL/SQL compiler would not throw any error. Since only when we actually fetch the records from the active set one after the other, we need to supply appropriate place holders to store the resultant value fetched and this is taken care by the FETCH statement (discussed later). Mere declaration of CURSOR alone will not immediately identify the active set.

9.6.2.

Opening the cursor OPEN cursorname; Example: OPEN c1; OPEN c2; OPEN c3;

The above code snippet shows the syntax for opening a cursor. The cursor identifier used in the cursor declaration needs to be specified while opening the cursor. We can open cursors both in the executable part as well as in the exception part of a PL/SQL block. If the cursor is already open, then the PL/SQL runtime engine would throw CURSOR_ALREADY_OPEN predefined runtime exception. The SELECT associated with the cursor declaration is executed only when we open the cursor. Thus the OPEN command prepares the cursor for use, identifies the active set associated with the given SQL query, and positions the cursor before the first row. If the SQL query fetches no rows from the database, it would not throw any exception. We need to make use of explicit cursor attributes to test the outcome after fetch. The same set of cursor attributes what we have discussed earlier with respect to implicit cursor attributes, can be made use with explicit cursor also by replacing the SQL keyword prefixed with every implicit cursor attribute with the respective cursor identifier name.

229 | P a g e



Within a PL/SQL block a cursor can be opened any number of times. Every time when we open the cursor different active sets can be identified, depending on the current state of the records in the database. Do not try to reopen the cursor without closing it as it would throw an exception, which is discussed earlier. The safest way of opening a cursor is as shown below, wherein we check whether the cursor is already open and if not, we open the cursor. IF NOT c1%ISOPEN THEN OPEN c1; END IF; Before we open the cursor, assuming that C1 is the explicit cursor which we are dealing with, the values of various explicit cursor attributes will be as shown in the below table: C1%ISOPEN C1%FOUND C1%NOTFOUND C1%ROWCOUNT

FALSE INVALID_CURSOR exception INVALID_CURSOR exception INVALID_CURSOR exception

After we open the cursor, assuming that C1 is the explicit cursor which we are dealing with, the values of various explicit cursor attributes, before fetching any record will be as shown in the below table: C1%ISOPEN C1%FOUND C1%NOTFOUND C1%ROWCOUNT

TRUE NULL NULL 0

Do not make use of implicit cursor attributes like SQL%FOUND to test the outcome of explicit cursors. If used, the outcome of recently executed SQL statement will be reflected in those variables if present and not the outcome of explicit cursor. If no SQL statement is present then all these variables would evaluate to NULL.

9.6.3.

Fetching records from the cursor

The syntax for fetching records from the cursor is as shown below. FETCH cursorname INTO listofvariables | PL/SQL record variable Example:

230 | P a g e



FETCH c1 INTO v_branchid; FETCH c2 INTO v_branchid, v_branchname, v_headofdepartment; FETCH c3 INTO v_branchid, v_count; FETCH c3 INTO v_branchrec; Immediately after opening the cursor, we can start fetching the records from the active set identified. If we try to fetch records from an unopened cursor, an INVALID_CURSOR exception would be thrown. Make sure to specify the name of cursor which is already opened. Followed by INTO keyword we need to specify the list of variable names in which the values have to be populated. Care should be taken that datatype of the list of columns mentioned in the SELECT statement should exactly match with the datatype of the variables in the FETCH statement. Usually we place this FETCH statement within a LOOP .. END LOOP construct as we need to repeatedly execute the same statement, several times for fetching all the subsequent records until we reach the last record. Here we assume that our active set has more than one record, and that is again the reason why we have opted for explicit cursors. Whenever the FETCH is successful, %FOUND is set to TRUE and if unsuccessful %NOTFOUND is set to TRUE. Thus to transfer the control outside the LOOP .. END LOOP construct we have to make use of an EXIT WHEN statement immediately after the FETCH. As soon as %NOTFOUND is set to TRUE we can transfer the control outside the LOOP .. END LOOP construct. Before we fetch the first record from the cursor, the values of various explicit cursor attributes will be as shown in the below table: C1%ISOPEN C1%FOUND C1%NOTFOUND C1%ROWCOUNT

TRUE NULL NULL 0

After we successfully fetch the first record from an explicit cursor, the values of various explicit cursor attributes will be as shown in the below table: C1%ISOPEN C1%FOUND C1%NOTFOUND C1%ROWCOUNT

231 | P a g e

TRUE TRUE FALSE 1



Subsequently for every successful fetch all other explicit cursor attributes will be present as such except C1%ROWCOUNT which is incremented by 1. After the first UNSUCESSFUL fetch from explicit cursor, the value of various explicit cursor attributes will be as shown in the below table: C1%ISOPEN C1%FOUND C1%NOTFOUND C1%ROWCOUNT

TRUE FALSE TRUE n

where n is the maximum number of records present in the explicit cursor.

9.6.4.

Closing the cursor

The below code snippet shows the syntax of close cursor statement. CLOSE cursorname; Example: CLOSE c1; CLOSE c2; CLOSE c3; Cursor name specified in close cursor is the name of the cursor to be closed. If we try to close a cursor which is already closed INVALID_CURSOR exception would be thrown. Memory allocated to an explicit cursor is released only when we close the cursor. Usually a programmer closes the cursor once he has completed processing on the set of records present in the active set. Reopen the cursor, if required. Do not attempt to fetch records from the closed cursor as this would also lead to INVALID_CURSOR exception. IF c1%ISOPEN THEN CLOSE c1; END IF;

9.7.

Explicit cursor – Simple loop

232 | P a g e



SQL> DECLARE CURSOR c1 IS SELECT branchid, seatsavailable FROM branch WHERE departmentid in (SELECT departmentid FROM department); v_branchid branch.branchid%TYPE; v_seatsavailable branch.seatsavailable%TYPE; BEGIN OPEN C1; LOOP FETCH c1 INTO v_branchid, v_seatsavailable; EXIT WHEN c1%NOTFOUND; UPDATE branch SET seatsavailable = v_seatsavailable + 1 WHERE branchid=v_branchid; DBMS_OUTPUT.PUT_LINE(v_branchid); END LOOP; CLOSE c1; COMMIT; END; / The above code snippet is an example of implementation of explicit cursors using LOOP ... END LOOP; construct. To increment the number of seats available by one in all the branches associated with every department of a university an explicit cursor implementation is done. The cursor declaration happens in the declaration part where all the branches associated with every department are identified. The cursor is opened and we fetch every record present in the identified active set into appropriate PL/SQL variables. An update statement wherein the seats available is incremented by one for every branch id present in the active set. Finally we close the cursor and the private SQL work area allocated is released and committed.

9.8.

Explicit cursor – With Group by clause SQL> DECLARE CURSOR cur_branch IS SELECT branchname, COUNT(*) as no_of_applicant_opted FROM applicant c, branch b WHERE c.optedbranch = b.branchid GROUP BY branchname ; v_branchname branch.branchname%TYPE; v_noofapplicants NUMBER; BEGIN OPEN cur_branch;

233 | P a g e



DBMS_OUTPUT.PUT_LINE('Branch Name No. of Application Opted'); LOOP FETCH cur_branch INTO v_branchname, v_noofapplicants; EXIT WHEN cur_branch %NOTFOUND; DBMS_OUTPUT.PUT(v_branchname||' '); DBMS_OUTPUT.PUT(v_noofapplicants); DBMS_OUTPUT.NEW_LINE; END LOOP; CLOSE cur_branch; END; / The above code snippet demonstrates the usage of GROUP BY clause in cursor declaration. This example deals with the display of branch name and the total number of applicants opted for every branch. As we have used an aggregate function COUNT (*) in the cursor declaration, an alias name is necessary which would help us in accessing the value for that column.

9.9. •

•

•

•

Explicit cursor attributes cursorname%ISOPEN – Is the cursor open? cursorname%ROWCOUNT – How many rows have been fetched so far? cursorname%NOTFOUND – Has a fetch failed? cursorname%FOUND – Has a row been fetched?

The discussion about the values present in these explicit cursor attributes is done along with cursor operations.

9.10. Using record variables with explicit cursors The below example demonstrates how to make use of record variable for accessing the active set record details and display of the same. v_curvar is a record variable declared as %ROWTYPE to hold both the branchname and the count of number of applicants opted in every branch. CURSOR cur_branch IS SELECT branchname, COUNT(*) as no_of_applicant_opted FROM applicant c, branch b WHERE c.optedbranch = b.branchid GROUP BY branchname ; v_curvar cur_branch%ROWTYPE; v_newrec v_curvar%TYPE; This record variable declared is made use of in FETCH statement after the INTO clause.

234 | P a g e



When we deal with more number of columns using cursors, usage of record variables will make our life easier, as no separate variables need to be declared for every column. If we want to come out with yet another record variable which has a similar structure, then we can simply say the name of newly needed record variable followed by earlier creating record variable%TYPE. Thus as shown in the above cursor declaration the record structure of v_curvar and v_newrec is similar. SQL> DECLARE CURSOR cur_branch IS SELECT branchname, COUNT(*) as no_of_applicant_opted FROM applicant c, branch b WHERE c.optedbranch = b.branchid GROUP BY branchname ; v_curvar cur_branch%ROWTYPE; BEGIN OPEN cur_branch; DBMS_OUTPUT.PUT_LINE('Branch Name No of Application Opted'); LOOP FETCH cur_branch INTO v_curvar; EXIT WHEN cur_branch %NOTFOUND; DBMS_OUTPUT.PUT(v_curvar.branchname||' '); DBMS_OUTPUT.PUT(v_curvar.no_of_applicant_opted); DBMS_OUTPUT.NEW_LINE; END LOOP; CLOSE cur_branch; END; /

9.11. Navigating cursors with WHILE LOOP The below example demonstrates how to deal with explicit cursor and WHILE construct. This construct would allow us to execute a set of statements repeatedly, when we specify a condition that evaluates to TRUE. Hence we need to identify an explicit cursor attribute which would be TRUE as long as we are able to fetch records for an active set. As we discussed earlier, cursorname%FOUND is an explicit cursor attribute that evaluates to TRUE as long as we are able to FETCH records from an active set. But this explicit cursor is NULL once we open the cursor, and is initialized with TRUE value only after the first successful fetch. SQL> DECLARE CURSOR cur_branch IS SELECT branchname, COUNT(*) as no_of_applicant_opted

235 | P a g e



FROM applicant c, branch b WHERE c.optedbranch = b.branchid GROUP BY branchname ; v_curvar cur_branch%ROWTYPE; BEGIN OPEN cur_branch; FETCH cur_branch INTO v_curvar; DBMS_OUTPUT.PUT_LINE('Branch Name No of Application Opted'); WHILE cur_branch%FOUND LOOP DBMS_OUTPUT.PUT(v_curvar.branchname||' '); DBMS_OUTPUT.PUT(v_curvar.no_of_applicant_opted); DBMS_OUTPUT.NEW_LINE; FETCH cur_branch INTO v_curvar; END LOOP; CLOSE cur_branch; END; / Hence twice the fetch statement has to be written, one outside the WHILE construct and another inside the WHILE construct.

9.12. Cursor FOR LOOP Cursor FOR construct help us to process explicit cursor, and at the same time, it relieves the PL/SQL programmer from the burden of dealing with various cursor operations such as opening, fetching records, closing, exit condition checking. Meaning, these cursor operations are implicitly taken care by this cursor FOR construct, allowing the programmer to concentrate on the implementation of business logic. Below is the syntax for dealing with cursor FOR loop. The set of statements to be repeatedly executed are enclosed within LOOP.. END LOOP; recname is the name of record variable, which is implicitly declared for us while using cursor FOR construct. This recname need not be declared in the declaration section of the PL/SQL block. FOR recname IN cursorname LOOP .. .. END LOOP; Thus the lists of operations implicitly taken care by Cursor FOR loop are 

Implicit open, fetch, exit condition check, close

236 | P a g e


Relational Database Management System 

Implicit record variable declaration

DECLARE CURSOR cur_branch IS SELECT branchname, COUNT(*) as no_of_applicant_opted FROM applicant c, branch b WHERE c.optedbranch = b.branchid GROUP BY branchname ; BEGIN DBMS_OUTPUT.PUT_LINE('Branch Name No of Applicants Opted'); FOR v_curvar IN cur_branch LOOP DBMS_OUTPUT.PUT(v_curvar.branchname||' '); DBMS_OUTPUT.PUT(v_curvar.no_of_applicant_opted); DBMS_OUTPUT.NEW_LINE; END LOOP; END; / As we could see in the above code snippet, v_curvar is a record variable implicitly declared and cursor operations are implicitly taken care of by the cursor FOR loop construct.

9.13. Implicit cursor FOR LOOP Not only the record variable can be implicitly declared, but even the cursor declaration can be implicitly declared by placing the cursor definition statements in the FOR loop itself. The below code snippet demonstrates the same. Thus we do not know the name of private SQL work area which is set aside for the below cursor operation. SQL> BEGIN DBMS_OUTPUT.PUT_LINE('Branch Name No of Applicants Opted'); FOR v_curvar IN (SELECT branchname, COUNT(*) as no_of_applicant_opted FROM applicant c, branch b WHERE c.optedbranch = b.branchid GROUP BY branchname ) LOOP DBMS_OUTPUT.PUT(v_curvar.branchname||' '); DBMS_OUTPUT.PUT(v_curvar.no_of_applicant_opted); DBMS_OUTPUT.NEW_LINE; END LOOP; END; / Thus the query is placed within parenthesis in the FOR loop itself. Apart from this all other implicit cursor operations are also available when we use the above construct.

237 | P a g e



9.14. Cursor related predefined oracle server exceptions INVALID_CURSOR predefined exception and CURSOR_ALREADY_OPEN predefined exception are the two predefined exceptions which we are discussing in this section.

9.14.1.

INVALID_CURSOR exception

Two different situations in which INVALID_CURSOR predefined exception is thrown: When we try fetching records from an unopened cursor When we try to close a cursor which is already closed  

The below example demonstrates the first one. Cursor C1 identifies the departmentid corresponding to various branches, but without opening the cursor C1, we are trying to fetch records from it. SQL> DECLARE 2 CURSOR c1 IS SELECT departmentid FROM department 3 WHERE departmentid in (SELECT departmentid FROM branch)); 4 v_departmentid department.department%TYPE; 5 BEGIN 6 FETCH C1 INTO v_departmentid; 7 WHILE C1%FOUND 8 LOOP 9 DBMS_OUTPUT.PUT_LINE(v_departmentid); 10 FETCH C1 INTO v_departmentid; 11 END LOOP; 12 CLOSE C1; 13 COMMIT; 14 EXCEPTION 15 WHEN INVALID_CURSOR THEN 16 DBMS_OUTPUT.PUT_LINE('Invalid cursor exception thrown'); 17 END;

9.14.2.

CURSOR_ALREADY_OPEN exception

The below example demonstrates when CURSOR_ALREADY_OPEN exception is thrown. As we have learnt, we can open and close the cursor any number of times within PL/SQL blocks. But before reopening the cursor, we have to make sure that it is closed. Opening a cursor which is already opened, throws CURSOR_ALREADY_OPEN exception. SQL> DECLARE 2 CURSOR c1 IS SELECT departmentid FROM department 3 WHERE departmentid in (SELECT departmentid FROM branch));

238 | P a g e



4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

v_departmentid department.department%TYPE; BEGIN OPEN C1; FETCH C1 INTO v_departmentid; WHILE C1%FOUND LOOP OPEN C1; DBMS_OUTPUT.PUT_LINE(v_departmentid); FETCH C1 INTO v_departmentid; END LOOP; CLOSE C1; COMMIT; EXCEPTION WHEN CURSOR_ALREADY_OPEN THEN DBMS_OUTPUT.PUT_LINE('Cursor already open exception thrown'); END;

9.15. Parameterized cursors We can pass one or more parameters or arguments to a parameterized cursor. This helps us to identify different active sets at runtime by passing different input values. While opening cursor we have to pass parameters, which could either hardcoded as shown in the example below or we can accept input values from the user. Parameters need to start with p_ as a mark of coding convention. CURSOR cursorname (parameter datatype) IS query;

Every formal parameter mentioned in the cursor declaration, should have a corresponding actual parameter in the open statement. The datatype of the formal and actual parameters also should match. Do not specify the size while mentioning the formal parameter in the cursor declaration. For example, p_branchid is even though a NUMBER variable of size 3, we don’t mention it as NUMBER (3) in the cursor declaration, whereas we say only NUMBER, without special mention to the size. Merely passing the parameters along would not suffice, but use these parameters in the WHERE clause of the SQL query mentioned in the cursor declaration, as this is the one, which will help us in identifying different active sets based on different inputs. SQL> DECLARE 2 CURSOR c1(p_branchid NUMBER) IS SELECT branchid, seatsavailable 3 FROM branch where branchid = p_branchid; 4 v_branchid branch.branchid%TYPE;

239 | P a g e



5 6 7 8 9 10 11 12 13 14

v_seatsavailable NUMBER(3); BEGIN OPEN c1(1001); LOOP FETCH c1 INTO v_branchid, v_seatsavailable; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_branchid||' '||v_seatsavailable); END LOOP; CLOSE c1; END;

9.16. Explicit cursor – FOR UPDATE CURSOR cursorname IS SELECT .. FROM .. FOR UPDATE [OF column_reference] [NOWAIT];

The syntax of SELECT statement in cursor declaration is one and the same as we have seen earlier but with an additional FOR UPDATE clause, which should be last clause even after ORDER BY (if any). When we plan for updation of records present in the active set, we can use FOR UPDATE clause in the cursor declaration of SELECT statement, which helps us to gain exclusive row lock on the set of records present in the active set. Thus the rows cannot be modified by other users who intend to operate on the same set of records. The user who has applied an exclusive row lock has to relinquish the lock by executing either COMMIT or ROLLBACK statement so that other users can modify the records present in the active set. If some other session has already acquired an exclusive row lock on one or more records already, then the current session has to wait for these locks to be released by the other session. This might lead to an indefinite wait. To avoid this, we can include a NOWAIT clause in the cursor declaration. Inclusion of NOWAIT in the cursor declaration checks whether records are not locked by anybody. If locked by somebody, it throws an oracle error and terminates the execution of PL/SQL block.

9.17. FOR UPDATE cursor declaration CURSOR cursorname IS SELECT ... FROM ... FOR UPDATE [OF column_reference] [WAIT n];

240 | P a g e



We can even instruct PL/SQL runtime engine to wait for n seconds, as shown in the above syntax. If the rows are not unlocked within n seconds, then it would return an oracle error. SQL> DECLARE CURSOR c1 IS SELECT empno, sal FROM emp FOR UPDATE OF sal; v_empno emp.empno%TYPE; v_sal emp.sal%TYPE; BEGIN FOR rec IN c1 LOOP UPDATE emp SET sal=sal + 100 WHERE empno=rec.empno; END LOOP; COMMIT; END; The above example demonstrates incrementing the salary of all employees by 100 in the EMP table. The list of columns present after FOR UPDATE clause specifies the column(s) to be updated. Do not try to update any derived column(s) or column(s) with aggregate functions in the SELECT query, as it is not possible. For example, in the below code snippet max (hoursremaining) is an aggregate function based column, meaning there is no column in the instructor table with name max (hourseremaining). SQL> DECLARE 2 CURSOR c1 IS SELECT instructorid, max(hoursremaining) 3 as maximumhours FROM instructor 4 GROUP BY instructorid FOR UPDATE OF maximumhours; 5 BEGIN 6 FOR rec IN c1 7 LOOP 8 DBMS_OUTPUT.PUT_LINE(rec.instructorid ||' '||rec.maximumhours); 9 END LOOP; 10 END; ERROR at line 6: ORA-06550: line 2, column 76: PL/SQL: ORA-01786: FOR UPDATE of this query expression is not allowed ORA-06550: line 2, column 15: PL/SQL: SQL Statement ignored

241 | P a g e



9.18. WHERE CURRENT OF clause WHERE CURRENT OF cursorname;

Whenever we make use of FOR UPDATE in the cursor declaration, we are allowed to make use of WHERE CURRENT OF clause with the UPDATE statement. This clause can be used just to say that the updation has to happen at the current row pointed by the explicit cursor. In other words, the updation should be applied only to the row which we have just fetched (recently). The below code snippet demonstrates the usage of WHERE CURRENT OF clause. SQL> DECLARE CURSOR c1 IS SELECT empno, sal FROM emp FOR UPDATE OF sal; v_empno emp.empno%TYPE; v_sal emp.sal%TYPE; BEGIN FOR rec IN c1 LOOP UPDATE emp SET sal=sal + 100 WHERE CURRENT OF c1; END LOOP; COMMIT; END; As we have empno column which distinguishes one row from the other in the active set, we can use empno to uniquely identify a row, in the WHERE condition of UPDATE statement, instead of WHERE CURRENT OF cursorname, which was our earlier implementation. Thus without using WHERE CURRENT OF clause, we can still implement SELECT with FOR UPDATE. Updates are allowed on columns which are not mentioned in the FOR UPDATE clause, but this is not a good programming practice.

10.

Transaction processing in PL/SQL

Transaction processing available allows multiple users to work on the database concurrently. At the same time it also ensures that each user sees a consistent version of data and that all the changes are applied in the right order. There is no need to write extra code to prevent problems with multiple users accessing data concurrently. Oracle uses locks to control concurrent access to data and locks only the minimal amount of data necessary, for the least possible time.

10.1. Using COMMIT statement in PL/SQL

242 | P a g e



COMMIT statement in PL/SQL marks the end of current transaction. This statement can be used both in the executable section and exception section. It helps us to save changes made during that transaction permanent and is visible to all users. Transactions are not tied to PL/SQL BEGIN ... END blocks. There can be more than one transaction implemented in the same PL/SQL block. There might be a situation where in not even one transaction could have been implemented completely within a PL/SQL block. A block can contain multiple transactions and a transaction can span multiple blocks. SQL> BEGIN UPDATE emp SET sal=sal + 100 WHERE empno=7935; END; With reference to the above PL/SQL block, there could have been some other DML statements which might have been executed before this UPDATE statement. Hence, there is no assurance that this is the first DML statement which modifies the database, hence may or may not be the beginning of transaction. Moreover, there is no commit statement present in the PL/SQL block and hence this is not an end of the transaction. SQL> DECLARE --assume declaration of appropriate variables and exceptions BEGIN COMMIT; --Generation of bill and insertion of record to billing table INSERT INTO billing VALUES(1002, 2345610001, 'C2', 09','creditcard');

62,'21-Mar-

COMMIT;

--updation of stock in the item table UPDATE item set qtyonhand=qtyonhand-1 WHERE itemid='STN001'; UPDATE item set qtyonhand=qtyonhand-1 WHERE itemid='BAK001'; COMMIT; EXCEPTION --assume appropriate exceptions are handled END;

243 | P a g e



With reference to the above PL/SQL block, the first commit statement ends the earlier transaction. A DML statement for generation of bill starts the transaction. The commit statement beneath that ends the generation of bill transaction. Updation of stock in the item table is another transaction which ends with a commit statement. Thus a PL/SQL block can contain multiple transactions.

10.2. Using ROLLBACK statement in PL/SQL ROLLBACK statement present in a PL/SQL block ends the current transaction. This statement helps in undoing any changes made during that transaction. Thus making mistakes such as deleting a wrong row can be restored with the help of this statement. This statement can be used both in the executable section and exception section. SQL>DECLARE --assume that the itemid is unique v_itemid item.itemid%TYPE:='STN003'; --assume that an itemname called Pen already exists in the ITEM table v_itemname item.itemname%TYPE:='Pen'; v_itemrec item%ROWTYPE; BEGIN UPDATE item SET qtyonhand=qtyonhand + 50 WHERE itemid='STN001'; INSERT INTO item(itemid, itemname) VALUES (v_itemid, v_itemname); SELECT * INTO v_itemrec FROM item WHERE itemname=v_itemname; DBMS_OUTPUT.PUT_LINE('Item name is unique'); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Item name duplicated'); ROLLBACK; END; As shown in the above PL/SQL block, if we try to duplicate item records, by inserting a new item with an existing item name, TOO_MANY_ROWS exception is thrown. Thus “Item name duplicated” message is printed on the screen after which the database is rollbacked immediately undoing the above insertion and updation.

10.3. Using SAVEPOINT in PL/SQL SAVEPOINT statement lets us to rollback part of a transaction instead of the whole transaction. These are similar to the bookmarks that we create while reading a book, where at any point of time, we can revert to a particular location, for later reference.

244 | P a g e



SAVEPOINT names and marks the current point in the processing of a transaction. Hence when we want to rollback to specific point, we can do so by using savepoint name with the ROLLBACK statement. SQL> INSERT INTO emp VALUES( 1004, 6000); 1 row created. SQL> SAVEPOINT S1; Savepoint created. SQL> UPDATE emp SET sal=1000 WHERE empno=1002; 1 row updated. SQL> SAVEPOINT S2; Savepoint created. SQL> DELETE FROM emp WHERE empno=1003; 1 row deleted. SQL> SAVEPOINT S3; Savepoint created. For example, we have done insertion, updation and deletion on EMP table and savepoint created after every DML operation. Savepoint S1 created after inserting an employee record, S2 created after updating the sal of an employee record and S3 created after deleting an employee record. Now if we simply say ROLLBACK in the SQL prompt all the changes made in EMP table would be restored. Instead if we want to retain INSERTION and UPDATION happened earlier, and discarding the DELETION alone then we need to say ROLLBACK to S2; in the SQL prompt. In this case, as we have rolled back to S2, whatever the savepoint(s) which we have after S2 will be cleared. Thus S3 will be cleared. The same behavior is exhibited within PL/SQL block also. Instead if we want to retain INSERTION alone, discarding the UPDATION and DELETION then we need to say ROLLBACK to S1. In this case, as we have rolled back to S1, whatever the

245 | P a g e



savepoint(s) which we have after S1 will be cleared. Thus both S2 and S3 will be cleared. The same behavior is exhibited within PL/SQL block also. Another important thing is that these savepoint names are undeclared identifiers in a PL/SQL block. This means that there is no need to do any separate declaration for variables which are used as savepoint names. The number of save points for each session is also unlimited. These savepoints are alive only for the current session in which it is created.

10.4. Concurrency control A simple way to think of oracle read consistency is •

readers do not wait for writers ( or other readers of the same data)

John the writer does some update operation on a record, while at the same time Jack who is reading the record sees the consistent version of the data. Even though John has updated the supplier name to XYZ, Jack cannot see this updation, as John has not committed. At the same time Jack need not wait until John completes updation. This proves that readers do not wait for writers or other readers of the same data.

246 | P a g e


Relational Database Management System •

writers do not wait for readers (of the same data)

John the writer here does not wait until the reader Jack completes reading of a record. Simultaneously while John is writing some record, Jack can still read the same record, but the consistent, committed version of the data alone would be given to him. This proves that writers do not wait for readers of the same data

•

247 | P a g e

Writers only wait for other writers if they attempt to update identical rows in concurrent transactions



Two writers cannot plan for an updation of the same record at the same time. Individual who gains exclusive access to the record first alone, would be allowed to do modification. Others have to wait. As shown in the above screenshot, John gains exclusive access to the record first and does an updation of supplier name. Jack also tries to update the same record later, but as John has gained exclusive access Jack has to wait, until John releases the lock on the record. This proves that writers only wait for other writers of the same data.

11.

On Line Analytical Processing (OLAP)

Data is the one of the most valuable assets of any organization or enterprise. Operational activities of an organization include day-to-day business processes necessary to run it. Systems that support such processes are called the On Line Transaction Processing (OLTP) systems. Operational data are highly structured data that is continuously generated and stored in what is typically called as operational or transactional or OLTP databases. An organization’s success also depends on its ability to analyze data and to make intelligent decisions that would potentially affect its future. Systems that facilitate such analysis are called On Line Analytical Processing (OLAP) systems.

248 | P a g e



An OLTP application rarely requires historical data. An OLAP application requires historical data because an analysis is generally based on a substantial amount of historical data to enable trend analysis and future predictions. An OLTP transaction is characterized by several users creating, updating or retrieving individual records whereas OLAP application is characterized by higher level views of the data. Thus the focus of OLTP and OLAP are fundamentally different. The following section gives the difference between OLTP and OLAP.

11.1. Difference between OLTP and OLAP

Definition Data

Data Atomicity

Normalization

History

Queries

Updates Response time

249 | P a g e

OLTP OLAP On Line Transaction On Line Analytical Processing Processing Dynamic (day to day Static (historical data) transaction / operational data) Data is stored at microscopic Data is aggregated or level summarized and stored at the higher level Normalized Databases to De-normalized Databases to facilitate insertion, deletion facilitate queries and analysis and updation Old data is purged or archived Historical data stored to enable trend analysis and future predictions Simple queries and updates Complex queries Queries use small amounts of Queries use large amounts of data data ( one record or a few records) Example: Example: update account balance Total annual sales for north enroll for a course region Total monthly sales for north region Updates are frequent Updates are infrequent Fast response time is Transactions are slow important Queries consume a lot of Data must be up-to-date, bandwidth



Joins in queries

Data models Focus

consistent at all times Joins are more and complex as Joins are few and simple as tables are normalized tables are de-normalized An OLTP system aims at one An OLAP integrates data from specific process different processes Example: ordering from an Example: Combines sales, online store inventory and purchasing data Complex data models, many Simple data models, fewer tables tables OLTP focuses on performance OLAP focuses on flexibility and broader scope

A practical solution to enable analytical processes is to implement a data warehouse.

11.2. Data Warehouse A data warehouse is a repository which stores integrated information for efficient querying and analysis. Data warehouse has data collected from multiple, disparate sources of an organization. It is the basis for decision support and data analysis systems.

11.2.1.   

Why data warehouse is needed?

Analysis requires millions of records of data which are historical in nature Data is collected from heterogeneous sources (e.g. RDBMS, flat files, etc.) Need to make quick and effective strategic decisions

In essence, it is a copy of the organization’s operational data adequately modified to support the needs of analytical processes and stored outside the operational database.

11.2.2.

Characteristics of Data Warehouse:

According to Bill Inmon, known as the father of Data Warehousing, a data warehouse is a subject oriented, integrated, time-variant, nonvolatile collection of data in support of management decisions. 

Subject-oriented: means that all data pertinent to a subject/ business area are

collected and stored as a single unit 

Integrated: means that data from multiple disparate sources are transformed and

stored in a globally accepted fashion 

Static/non-volatile: means data once entered into the warehouse does not change. It

is periodically added if required

250 | P a g e





Time variant: Data warehouse maintains historical data which are used to analyze the

business or market trends and facilitate future predictions Data Mining

MOLAP

Operational

Data Warehouse

databases

Reporting

ETL Operational

Output

Process

databases

Analysis

Flat

Data Marts

Data Marts

Files

Data Sources

Data Warehouse Server

OLAP Servers

Presentation Tier

Figure 11-1: Data warehouse architecture

11.2.3.

Data Warehousing Terminology

Data sources: An organization has many functional units with their own data. Data from all

such sources have to be consolidated and put into a consistent form that would reflect the business of an organization as a whole. These sources of data for a data warehouse are known as data sources or operational data sources. Metadata: Metadata is the data about the data. Metadata is the layer of the data warehouse,

which stores the information like the source data, transformed data, date and time of data extraction, target databases, date and time of data loading, etc. Measure attributes: A numerical value that can be summarized or can be aggregated upon. Example: Consider an inventory application. Assume that the inventory store sells twenty

products in one day, each for 5 dollars. Thus it generates 100 dollars in total sales for the day. Therefore, sales dollars is one measure. The store owner might want to get the 251 | P a g e



information about the number of customers they had that day. Did 5 customers buy 4 products each, or did one customer buy twenty products. Thus, customer count is another measure. Dimension attributes: Dimensions can be defined as the perspectives used for looking at the

data. “How you want your data to be seen?” this answers your question about what is a Dimension? Some examples of dimensions are: Product Time Location Customer Age Customer Income There is almost always a time dimension on anything which is being analyzed. Considering the example given for measure attributes, sales of a product can be analyzed by day, or by month or by quarter, or by half year, or by year. Sales can also be analyzed by category or by product. The time, product, geographic dimensions are very common. Data that can be modeled as dimension attributes and measure attributes are called multidimensional data.

252 | P a g e



11.2.4.

Data Collection for Data Warehouse Applications

Extraction, transformation and loading (ETL): This is the most important step in Data

Warehousing. Definition of ETL: The processes such as Extract, Transform and Load are described as the

process of selecting, migrating, transforming, cleansing and converting mapped data from the operational environment to data warehouse environment. Data needs to be taken from various disparate sources to the data preparation area. This process is known as data extraction. This data preparation area, also known as data staging area consists of relational tables. Data from various heterogeneous sources are altered into a uniform format and put into relational tables of data preparation area which can be readily loaded into the data warehouse database. This process is known as loading. The data is loaded into the fact table and dimension tables in the data warehouse database. Refer to Figure 11-2. Data is periodically extracted

Data is cleansed and transformed

Data Staging Area

User query the data warehouse

Data Warehouse

Source Systems

Figure 11-2: Extraction, Transformation and Loading Process

11.2.5.

Storing of data in Data warehouse

Dimensional Modeling: The dimensional modeling is also known as star schema because in

dimensional modeling there is a large central fact table with many dimension tables surrounding it. Fact Tables: Each data warehouse or data mart includes one or more fact tables. A fact table

is the central table of a star or snowflake schema. This central table captures the data that measures the organization's business operations. Fact tables usually contain large numbers of rows.

253 | P a g e



One of the main features of the fact table is that, it has numerical data or facts, which can be summarized to give the information about the operational history of the organization. The fact tables also contain a multipart index which is nothing but a foreign key to the primary key of a related dimension table. The dimension tables contain the attributes of the fact records. The fact tables should not contain the attributes, which hold descriptive information. Dimension Tables:

The attributes in these tables describe the fact records in the fact table. It contains attributes which summarize the useful information required by the analyst. Dimension table even contains attributes providing descriptive information. Some attributes have hierarchies for example a dimension containing information about product may contain a hierarchy that separates products into categories, with each of these categories further subdivided into manufacturer. Cube: The OLAP tools allows you to turn data stored in relational databases into meaningful,

easy to navigate business information by creating data cube. The dimensions of a cube represent distinct categories for analyzing business data. Categories such as time, geography or product line breakdowns are typical cube dimensions. Dimension hierarchies: Refer to Figure 11-3. The product dimension contains individual

products. Products are further divided into categories, and further divided according to manufacturer. The dimension table stores the hierarchy for the dimension.

Day

Products_ Manufacturer

Month

Products_ Category

Quarter

Products

Year

Figure 11-3: Dimension Hierarchies

Available Schemas for dimensional modeling:

Star schema Snowflake Schema

254 | P a g e



Star Schema: It is the simplest data warehouse schema. It resembles a star. The center of the

star consists of at least one or more fact tables and the points radiating from the center are the dimension tables. Refer to Figure 11-4. Star Schema Dimension Table

Dimension Table

Fact Table

Dimension Table

Dimension Table

Figure 11-4: Star Schema

Snowflake Schema: It is a complex data warehouse schema. The snowflake schema consists

of a single, central fact table, which is surrounded by dimension hierarchies which are normalized. Each level of the dimension is represented in a table. Refer to Figure 11-5. Products_ Manufacturer

Products_ Category Dimension Table

Products

Fact Table E.g. Sales Dimension Table

Customers

Countries

Cities

Figure 11-5: Snowflake Schema

Disadvantages of Snowflake Schema:  

It increase the number of dimension tables It requires more foreign key joins

255 | P a g e



11.2.6.

Reporting of a Data warehouse application

A data mart is a subset of a data warehouse which focuses on a single area of data and it is organized for quick analysis. It can be a small data warehouse itself. 11.2.6.1.   





 

Advantages of Data Marts:

It focuses on presentation rather than the organization of data It facilitates data reporting It provides meaningful reports to the users pertaining to their business area thereby allowing them to view and concentrate only on the data that is related to their business area Example: providing sales data to the sales department, providing financial data to the financial department It makes the data design simpler and easier. It breaks the whole design into several smaller sub units which is beneficial to the customers and the team that is involved in development. It is also easier to maintain. Reporting of data becomes faster and more efficient because reporting is generally done at the sub unit level and data marts assist in faster retrieval compared to querying the entire data warehouse It helps in incrementally building up the enterprise data warehouse It helps to ensure security

Data Warehouse

Data Mart1

Data Mart2

Data Mart3

End User 1

End User 2

End User 3

Figure 11-6: Each end user works with a f ocused subset of Data Warehouse called Data Mart

256 | P a g e



Several data marts can be built, each for a particular business area provided they all conform to the data warehouse architecture from where they get the data for reporting. Data marts can be used in conjunction with each other. Refer to Figure 11-6.

257 | P a g e



11.2.7.

Difference between Data Warehouse and Data Mart

Data Warehouse Data Mart A data warehouse is a repository which A data mart is a subset of a data stores integrated information from warehouse which focuses on a single area multiple disparate sources for efficient of data and it is organized for quick querying and analysis analysis. It mainly focuses on the organization of data and offers little focus about the presentation of data. There is usually a central data warehouse system Data Warehouse is used on an enterprise level Data Warehouse contains data from heterogeneous sources for analysis

11.2.8.

It focuses mainly on the presentation of data to the customers rather than the way in which the data is organized in the data warehouse There can be several data marts that operate on the central data warehouse Data Mart is used on a business division / department level Data Mart only contains the required subject specific data for local analysis

Popular tools available for data warehousing

Reporting / Analysis Tools:     

Micro Strategy: DSS Agent / Server Cognos: Improptu Brio Technology: Brio Query Seagate Software: Crystal Reports MS-SQL Server 2005 SQL Server Reporting Service (SSRS)

ETL:    

Oracle Warehouse Builder Informatica: Power Center Acta: ActaWorks MS-SQL Server 2005 SQL Server Integration Service (SSIS)

Databases: 

MDDB o o

258 | P a g e

Oracle MS-SQL Server 2005 SQL Server Application Service (SSAS)



11.3. Summary 











An OLAP application requires historical data because an analysis is generally based on a substantial amount of historical data to enable trend analysis and future predictions A data warehouse is a repository which stores integrated information for efficient querying and analysis Extract, transform and load process (ETL) is described as the process of selecting, migrating, transforming, cleansing and converting mapped data from the operational environment to data warehouse environment A data mart is a subset of a data warehouse which focuses on a single area of data and it is organized for quick analysis. Star schema is the simplest data warehouse schema. It resembles a star. The center of the star consists of at least one or more than one fact tables and the points radiating from the center are the dimension tables. The snowflake schema consists of a single, central fact table, which is surrounded by dimension hierarchies which are normalized.

259 | P a g e



Appendix-A Boyce Codd Normal Form (BCNF) A relation is said to be in Boyce Codd Normal Form (BCNF) if and only if all the determinants are candidate keys. BCNF relation is a strong 3NF, but not every 3NF relation is BCNF. Let us understand this concept using slightly different Result table structure. Student#

EmailID

Course#

Marks

[email protected] [email protected]

M4

82

M4

62

101

[email protected]

H6

79

103

[email protected]

C3

65

104

[email protected]

B3

77

102

[email protected]

P3

68

105

[email protected]

P3

89

103

[email protected]

B4

54

105

[email protected]

H6

87

104

[email protected]

M4

65

101 102

RESULT Table

Student#

Course#

EmailID

Overlapping Candidate Key

In the RESULT table, we have two candidate keys namely Student# Course# and Course# EmaiIId. Course# is overlapping among those candidate keys. Hence these candidate keys are called as “overlapping candidate keys ” as shown above.

260 | P a g e


Appendix

The non-key attribute, Marks is non-transitively and fully functionally dependant on key attributes. Hence this is in 3NF. But this is not in BCNF because there are four determinants in this relation namely: Student# (Student# decides EmailiD) EMailID (EmailID decides Student#) Student# Course# (decides rest of the attributes in RESULT table) Course# EMailID (decides rest of the attributes in RESULT table)    

All above determinants are not candidate keys. EMailID decides Student# but EMailID on its own is not a candidate key. Similarly Student# decides EMailID of a student but Student# alone is not a candidate key. Only combination of Student# Course# and Course# EMailID are candidate keys. To make this table BCNF, we need to split this table into the following structure:

Student#

Course#

Marks

STUDENT TABLE

Student#

EmailID 101

[email protected]

102

[email protected]

103

[email protected]

104

[email protected]

105

[email protected]

101

M4

82

102

M4

62

101

H6

79

103

C3

65

104

B3

77

102

P3

68

105

P3

89

103

B4

54

105

H6

87

104

M4

65

Boyce Codd Normal Form

Now both the tables are not only in 3NF, but also in BCNF because all the determinants are candidate keys. In the first table, Student# decides EMailID and EMailID decides Student# and both are candidate keys. In second table, Student# Course# is only determinant and candidate key. Hence it qualifies BCNF definition that every determinant must be a candidate key. Note: If the table has only one non-composite candidate key and if it

is in 3NF, then the table will also be in BCNF.

Basically 2NF and 3NF takes away the redundancy, anomalies which exist among the key and non-key attributes on other hand BCNF takes away the redundancy, anomalies which exist

261 | P a g e


Appendix

among the key attributes. At Infosys, we rarely (around 1% of database design) normalize the databases to BCNF.

Embedded SQL Purpose To blend SQL language statements directly into a program written in a host programming languages, such as C, Pascal, COBOL, FORTRAN and PL/I, use embedded SQL statements. The following techniques are used to embed the SQL statements: 







SQL statements are intermixed with statements of the host language in the source program. This embedded SQL source program is submitted to a SQL pre-complier, which processes the SQL statements Variables of the host programming language can be referenced in the embedded SQL statements, allowing values calculated by the program to be used by the SQL statements Program language variables are also used by the embedded SQL statements to receive the results of SQL queries, allowing the program to use and process the retrieved values Special program variables are used to assign NULL values to database columns and to support the retrieval of NULL values from the database

Why Embedded SQL? SQL has the following limitations:     

No provision to declare variables No unconditional branching/jump statement No IF statement to test conditions No FOR, DO or WHILE statements to construct loops No block structure

In order to understand the embedded SQL program, one has to be familiar with the following terminologies: EXEC SQL: Every embedded SQL statement begins with an introducer that flags it as a SQL

statement. The IBM SQL products use the introducer exec sql for most host languages. 262 | P a g e


Appendix

SQLCA: The sqlca (SQL Communication Area) is a data structure that contains error variables

and status indicators. By examining the SQLCA, the application program can determine the success or failure of its embedded SQL statements. exec sql include sqlca; This statement tells the SQL pre-complier to include a SQL Communications Area in the program. As RDBMS executes each embedded SQL statement, it sets the value of the variable sqlcode in the SQLCA to indicate the completion status of the statement. A sqlcode of zero indicates successful completion of the statement A negative sqlcode indicates a serious error that prevented the statement from executing correctly A positive sqlcode indicates a warning condition. The most common warning with a value of 100, is the out of data warning returned when a program tries to retrieve the next row of query results and no more rows are left to retrieve.  



Host variables: A host variable is a program variable. It is declared using the data types of

the programming language such as “C” and manipulated by programming language statements. A host variable is also used in embedded SQL statements to store/retrieve data to/from the database. To identify the host variable, the variable is prefixed by a colon (:) when it appears in an embedded SQL statement. A host variable can appear in an embedded SQL statement wherever a constant can appear. The two embedded SQL statements begin declare section and end declare section bracket the host variable declarations and are non-executable. Use of host variables to store data into the database: 



The input provided by the user using the standard input device is stored in the host variables Values of the host variable is then written to the database using the INSERT SQL statement

Use of host variables to retrieve data from the database: 



The data values retrieved from the database using the SELECT SQL statement are held in the host variables The contents of the host variables are then displayed on the standard output device using functions such as printf() in “C”

Indicator variables: To store NULL values in the database or retrieve NULL values from the

database, embedded SQL allows each host variable to have a companion host indicator 263 | P a g e


Appendix

variable. In an embedded SQL statement, the host variable and the indicator variable together specify a single SQL-style value, as follows: An indicator value of zero indicates that the host variable contains a valid value A negative indicator value indicates that the host variable should be assumed to have a NULL value; the actual value of the host variable is irrelevant and should be disregarded A positive indicator value indicates that the host variable contains a valid value, which may have been rounded off or truncated  



A host variable is immediately followed by the name of the corresponding indicator variable. Both variable names are preceded by a colon. Example: A simple embedded SQL program written in C. Problem statement: This program asks the customer for his Cust_ID, retrieves his record from

the Customer_Details table and displays it on the standard output device. int main(int argc, char* argv) { / exec sql include sqlca; / exec sql begin declare section char Mem_Cust_ID[5]; char Mem_Cust_Last_Name[25]; char Mem_Account_No[5]; char Mem_Bank_Branch[25]; char Mem_Cust_Email[30]; short iBank_Branch; exec sql end declare section; / printf(“Enter Customer ID:”); scanf(“%s”,Mem_Cust_ID); / / / / h / : exec sql SELECT Cust_ID, Cust_Last_Name, Account_No, Bank_Branch, Cust_Email FROM Customer_Details WHERE Cust_ID =:Mem_Cust_ID INTO :Mem_Cust_ID, :Mem_Cust_Last_Name, :Mem_Account_No, 264 | P a g e


Appendix

:Mem_Bank_Branch :iBank_Branch, :Mem_Cust_Email; / /

o if (sqlca.sqlcode = = 0) { printf(“Customer ID: %s\n”, Mem_Cust_ID); printf(“Customer Name: %s\n”, Mem_Cust_Last_Name); printf(“Account No.: %s\n”, Mem_Account_No); / if (iBank_Branch < 0) { printf(“Bank Branch is NULL\n”); } else { printf(“Bank Branch: %s\n”, Mem_Bank_Branch); } printf(“Customer Email: %s\n”, Mem_Cust_Email); } else if (sqlca.sqlcode = = 100) { printf(“No customer with that Customer ID.\n”); } else { printf(“SQL error: %ld\n”, sqlca.sqlcode); } / return 0;

}

Timestamping Another concurrency management technique is Timestamping. Every resource in database will be associated with last successful read and last successful write timestamp (time of occurrence up to milliseconds Ex: 12th December 2004 11:22:33.345). Let us consider:  

RDBMS author by name Hanu is modifying this course material as one transaction Trainees reading this course material as another transaction

If other RDBMS author, say Seema, is also modifying the same course material at the same time, it leads to Lost-update and Phantom record conditions. If Hanu starts modifying while trainees are studying this material, it leads to dirty read or incorrect summary problems.

265 | P a g e


Appendix

To avoid these problems we can follow these two rules: Hanu can start the course modification transaction only if: Course material is successfully modified before starting this transaction No trainees are currently reading this course material  

Similarly trainees can start reading course material transaction only if: It was successfully updated before they start reading it 

Let us consider an example of database DB_BANK_DETAILS as discussed earlier. In table ACC_DETAILS, a particular row is read successfully by transaction BalanceEnquiry at 12:11:45.345 of 12th December 1945. This will be the last read timestamp of this row. If any other transaction reads this row after this time, that particular time will be the last read timestamp of the row. Similarly every row will have last updated timestamp. If transaction BalanceUpdate updates the row R1 at 13:32:22.345 of 12th December 1945, this will be recorded as last updated timestamp of the row R1. A transaction can read only rows or columns that have been updated by an older transaction if not, transaction is rolled back. Let us assume that Row R7 of the table ACC_DETAILS was successfully updated at 09:24:22.46 Hrs of 15th August 1947 by some transaction. Any transactions started after 09:24:22.46 Hrs of 15th August 1947 can read this row. Transactions started before 09:24:22.46 Hrs of 15th August 1947 need to be rolled back and start afresh to read this data. In general for read, the condition can be defined as TS > TU where TS is the start time of transaction and TU is the last successful update timestamp of the resource.

A transaction can update only rows or columns that have been read and updated by an older transaction else this transaction is rolled back. Similarly any transaction can update row R7 only if it is started after the last successful update and the last successful read. Assume a transaction started at 10:24:23.49 Hrs of 15th August 1947 and wishes to update row R7 at 10:29:11.34 Hrs of 15th August 1947.

266 | P a g e


Appendix

It is possible to update this row only if the row R7 was successfully updated and read before 10:24:23.49 Hrs of 15th August 1947. Any transaction started after 10:24:23.49 Hrs of 15th August 1947 cannot change the value of this row. Generic rule for updating data is TS > TU and TS > TR. Where TS is transaction start timestamp, TU is the last successful updated timestamp and TR is the last successful read time.

The biggest advantage of timestamping is it leads to no dead lock condition as no resources are locked. Timestamping technique leads to large number of rollbacks. Due to this reason timestamping technique is not implemented as the concurrency control mechanism in most of the commercial RDBMS applications. Note: Almost all the commercial RDBMS packages use a locking technique as

the concurrency controlling mechanism while maintaining the consistency in the system.

267 | P a g e


Glossary

Glossary Abstract: Conceptual/theoretical object. Abstraction: A simplified representation of something that is potentially quite complex. It is often not necessary to know the exact details of how something works, is represented or is implemented, because it can still be used in its simplified form. Ambiguity: Uncertainty. Anomalies: Irregularities. Anomaly: A departure from the expected; an abnormality. Atomic: The smallest levels to which a data can be broken down and still remains meaningful. Attribute: The literal meaning is quality; characteristic; trait or feature. Entities get their meaning in a database with the help of a set of attributes. Consider for e.g., in the bank system, Cust_ID, Cust_Email etc. describe Customer-Detail entity set. Backup: A second copy of a file or a set of files to be used if the primary or the main file(s) are destroyed or corrupted. Backups are essential for every data but it is one of the most trivial work. For critical work, two backup sets are recommended. Business rules: The rules or the policies which govern the functioning of the application. Business users: The users who owns the application. Cardinality of a relation: It is the number of rows or tuples in a table. Centralized: Systems where the flow of data or the beginning of activities, decision making are initiated at the same central point and spread to other remote points in the organization Conceptual: To generalize abstract ideas from specific instances. Concurrent Access: Performing two (or more) operations on the same piece of data at the same time. Constraints: restriction, limitation. Data manipulation: Data manipulation refers to the insertion of new data, modification of existing data, etc. Data Redundancy: The same data is stored in more than one place in a database. Decomposable: Further split or reduce. Degree of a relation: It is the number of attributes or columns in a table. Distinct: Not identical.

268 | P a g e


Glossary

Distributed: We say that a computer system is distributed when many different types of components and objects related to an application can be situated on different computers, which are connected to a network. Encryption: The process of manipulating the data in such a way that it should not be interpreted by all but should be interpreted by the intended users. End User: The person for whom a system is being developed. Example: a bank teller or a bank manager is an end user of a bank system. Entity: An entity is a “thing” or “object” in the real world that is distinguishable from other objects. Example: employee is an entity, and book can be considered to be another entity. Flat files: File containing records that has no structured interrelationship. Files used in programming fundamentals (PF) projects were essentially flat files. Fourth Generation Language (4GL): A 4GL is typically non-procedural and designed so that end users can specify what they want without having to know how the computer will process their requirement. Grant Privilege: To assign a privilege to a user or to a group. Heterogeneous: diverse, mixed, varied. Heterogeneous Network: A network that consists of network interface cards, servers, workstations, operating systems, and applications from many vendors, all these working together as a single unit. The network usually uses different media and different types of protocols on different network links. Homogeneous: All the same, uniform, harmonized. Homogeneous Network: A network composed of systems of similar architecture and runs a single network layer protocol. Inconsistency: lacking uniformity or agreement. Instance: Occurrence. Integrated: United into a larger unit. Something, which is brought together in order to form a working whole in a satisfactory manner. Integrity Constraints: It is a set of rules to ensure the correctness and accuracy of data. Interrelated: interconnected Intuitive: Natural. Iterative: Process of repeating the same task. Jargons: It is a specialized language or a technical language of a profession or a trade. Main Memory: This concept is discussed in OS course - All the read and write operations happen in main memory before they are written into hard disks. Model: A representation or a scaled down structure of an object.

269 | P a g e


Glossary

Page: It is part of a table. Usually in one page multiple rows are stored. Participating entities: The entities which are joined by the relation. Queries: A request that a user makes on the database. Recovery: Restoration, return to an original state. Requirement specification: A document which contains requirement for a specific application. Revoke Privilege: Cancel, withdraw. Schema: A description of a database. It specifies (among other things) the relations or Tables, their attributes or columns, and the domains of the attributes. Semantic: Meaning. Shared: It is a type of database access, which allows multiple users to log on to the database at the same time. Simulate: To make a model. Site: Geographical location. Software application designer: The person who designs software applications. SQL: (Structured Query Language). It is a language, which is used by relational databases to request or to query, or to update and manage data. Static: Something which does not change. (Example: the typical web page is static in which the content of the webpage does not change until the owner of the web page or the web master physically alters the document.) Superset: Given two sets, X and Y, we say X is a superset of Y if all the elements of Y are also elements of X. Every set is a superset of itself. Every set is a superset of the empty set. Table: It is a two dimensional space having columns and rows. A table contains a specified number of attributes or columns but can have any number of records or rows. Tablespace: The logical part of the database which represents collection of the structures like tables, etc created by various users. Tangible: Physical object. Transaction: It is a set of processing steps, which are considered as a single activity or unit of work to achieve a desired result. In DBMS, collection of processing steps that form a single logical unit of work is called a transaction. A database system ensures proper execution of transactions despite failures – either the entire transaction executes, or none of it does. Transient: Temporary, transitory, momentary. Transitive: In-direct.

270 | P a g e


Glossary

Tuple: This is a mathematical term for a finite sequence of n terms. E.g., the set {1, 2, 3, 4} is a fourtuple. A tuple is equivalent of a record. In RDBMS, a table has n tuples. Unauthorized: Not permitted, illegal, unlawful. View: A virtual table in the database defined by a query.

271 | P a g e


Index

Index A ABORT .............................................. 137 ACID ................................................ 139 ALTER TABLE ....................................... 80 Application Programmer ........................ 21 Attribute ............................................ 37 B Bottom-Up .......................................... 55 Boyce Codd Normal Form ....................... 249 C

Candidate Key ...................................... 27 Cardinality of a Relation ......................... 26 Cardinality of relationship ....................... 37 Cartesian product ................................ 118 Centralized ......................................... 15 CHECK OPTION .................................... 128 Check-Points ...................................... 160 COMMIT ............................................ 137 Conceptual / Logical level ...................... 18 Concurrency ....................................... 141 Concurrent Access ................................... 4 Concurrent Access Anomalies ................. 13 Co-Related Sub-Queries ......................... 114 CREATE TABLE ..................................... 75 Cube ................................................ 244 D

Data Control Language (DCL) ................... 129 Data Definition Language (DDL)................. 74 Data Isolation ...................................... 12 Data Manipulation Language (DML)............. 87 Data Model .......................................... 22 Data Redundancy ................................. 11 Data Security ...................................... 10 Data Warehouse .................................. 240 Database .............................................. 2 Database Administrator ......................... 21 Database Management System .................... 1 DBMS Interface....................................... 8 Deadlock ........................................... 154 Deferred Update .................................. 157 Degree of a Relation .............................. 26 DELETE .............................................. 90 Derived Attribute ................................. 41 Determinant ........................................ 56

272 | P a g e

Dimension Hierarchies ......................... 244 Dimension Tables ................................ 244 Dimensional Modeling .......................... 243 Dirty Read ......................................... 143 Distributed .......................................... 15 Domain Integrity ................................. 140 DROP TABLE ........................................ 83 DROP VIEW ......................................... 126 E

Embedded SQL .................................... 251 End User ............................................ 21 Entity ................................................ 37 Entity Integrity Constraint ...................... 29 Exclusive Lock ..................................... 146 EXISTS .............................................. 124 External / View level ............................ 18 F Fact Tables ........................................ 243 File System ........................................... 6 First Normal Form ................................. 60 Flat Files .............................................. 6 Foreign Key ......................................... 30 Full Functional Dependency ................... 58 Functionally Dependent ........................ 57 G

GRANT .............................................. 130 GROUP BY .......................................... 103 H

HAVING ............................................. 107 Heterogeneous ..................................... 17 Hierarchical Data Model .......................... 23 Homogeneous ...................................... 17 Horizontal View ................................... 126 I

Immediate Update ............................... 158 Incorrect Summary ............................... 143 Independent Sub-Queries ....................... 112 Index ................................................. 84 INNER JOINS ....................................... 120 INSERT ............................................... 87 Integrated............................................. 3 Intent Locking ..................................... 149 Internal / Physical level ......................... 19


RDBMS Course Material

Recommend Documents