PL/SQL Tables

PL/SQL tables are the temporary memory objects that give you access to the data like array. PL/SQL tables can have only one column and one primary key. The data type of the column can be any scalar type and data type of the primary key column is BINARY_INTEGER. The size of the PL/SQL tables is unconstrained, which means the number of rows in a PL/SQL table can increase dynamically.

Declaring PL/SQL Tables
You can declare a PL/SQL table in the declarative part of the PL/SQL block, subprogram, or package. PL/SQL tables are declared in two steps: first, you need to define the TABLE type and then declare the PL/SQL table of that TABLE type.

The following syntax is used to declare a table type:

TYPE type_name IS TABLE OF
{Column_definition}
INDEX BY BINARY_INTEGER;

Here type_name is the name of the TABLE type and Column_definition contains the declaration of the field name, field type, and constraints.


After defining the TABLE type, you can declare the PL/SQL table. The syntax for declaring a PL/SQL table is as follows:

Table_name Table_typename;

In the above syntax, Table_name is the name of the PL/SQL table and Table_typename is the name of the TABLE type which you have defined. The following example shows how to declare a PL/SQL table:

DECLARE
TYPE Tbl_Emp IS TABLE OF Emp.Ename%TYPE
INDEX BY BINARY_INTEGER;

In the above example, Tbl_Emp is a table data type that stores the name of the employees. After defining the table type Tbl_Emp, you can declare PL/SQL tables of that type in the following way:

Tbl_Emp Emp_Name;


Referring PL/SQL Tables
In PL/SQL, the elements of PL/SQL tables can be referred by specifying an index number. The syntax to refer a PL/SQL table is as follows:

plsql_tablename(index)

In the above syntax, plsql_tablename is the name of the PL/SQL table and the index is an expression that returns the BINARY_INTEGER data type.

The following example will illustrate the use of PL/SQL tables.


  DECLARE
  TYPE Emp_name IS TABLE OF Emp.Ename%TYPE
  INDEX BY BINARY_INTEGER;
  TYPE Emp_Sal IS TABLE OF Emp.Sal%TYPE
  INDEX BY BINARY_INTEGER;
  E_name Emp_name;
  E_Sal Emp_Sal;

  Ctrl_index BINARY_INTEGER := 0;
  BEGIN
    FOR EnameRec IN (SELECT * FROM Emp) LOOP
        Ctrl_index := Ctrl_index + 1;
        E_name(Ctrl_index) := EnameRec.Ename;
        E_Sal(Ctrl_index) := EnameRec.Sal;
    END LOOP;
    FOR i IN 1..Ctrl_index LOOP
        DBMS_OUTPUT.PUT_LINE(‘Name: ’ || E_name(i));
        DBMS_OUTPUT.PUT_LINE(‘Salary: ’ || E_Sal(i));
        DBMS_OUTPUT.PUT_LINE(‘-----------------------------------------’);
    END LOOP;
  END;
/

CURSOR FOR Loop

The CURSOR FOR loop is defined for explicit cursors or can be implemented directly on the SELECT statement. Generally, the CURSOR FOR loop is used to retrieve and manipulate each record fetched from a cursor.

The syntax for using the CURSOR FOR loop is as follows:

FOR record_index IN cursor_name
LOOP
{.statements.}
END LOOP;

The CURSOR FOR loop automatically does the following:

1. It implicitly declares a %ROWTYPE attribute and then uses it as a loop index.
2. It opens the cursor itself.
3. It fetches a row from the cursor for each loop iteration.
4. It closes the cursor automatically when all rows have been fetched or processed.

From the above statements, it can be concluded that the OPEN, FETCH, and CLOSE
statements are not necessary for using the CURSOR FOR loop.

Example:

DECLARE
CURSOR Emp_Cur IS SELECT * FROM Emp WHERE Sal > 2000;
Ctr NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Employees having salary more than 3000’);
DBMS_OUTPUT.PUT_LINE(‘-------------------------------------------------------’);
FOR employee_rec IN Emp_Cur LOOP
DBMS_OUTPUT.PUT_LINE(‘Employee ID: ’ ||
employee_rec.Empno);
DBMS_OUTPUT.PUT_LINE(‘Employee Name: ’ ||
employee_rec.Ename);
DBMS_OUTPUT.PUT_LINE(‘Employee Salary: ’ ||
employee_rec.Sal);
DBMS_OUTPUT.PUT_LINE(‘Employee Commission: ’ ||
employee_rec.Comm);
DBMS_OUTPUT.PUT_LINE(‘================’);
Ctr := Emp_Cur%ROWCOUNT;
END LOOP;
DBMS_OUTPUT.PUT_LINE(ctr || ‘ Employees have salary more than 3000’);
DBMS_OUTPUT.PUT_LINE(‘-------------------------------------------------------’);
END;
/

The CURSOR FOR loop can also accept parameters. It follows the same rules as the manual
cursor. For example, if the cursor is defined with a parameter, it must be opened with a
parameter; otherwise, PL/SQL will raise an exception.

Example:

DECLARE
CURSOR Emp_Cur(Desg IN VARCHAR) IS
SELECT * FROM Emp
WHERE Job = Desg;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘------------------------------------------------’);
FOR Emp_rec IN Emp_Cur (‘&Designation’) LOOP
DBMS_OUTPUT.PUT_LINE(‘Name: ’ || Emp_rec.Ename || ‘ ’ ||
‘Salary: ’ || Emp_rec.Sal);
END LOOP;
END;
/

The cursor Emp_Cur will be executed and the message "Enter value for designation:" will
be prompted. Enter the value for the designation as MANAGER.

As a result, the cursor will retrieve the records of the employees that have the designation as MANAGER.

Cursors

A cursor is a pointer to a memory location where the results of a SELECT statement is stored and we can manipulate the information by fetching the records.

There are two types of cursors: implicit and explicit. PL/SQL declares a cursor
implicitly for all SQL data manipulation statements, including queries that return only single row. Whereas the queries that return more than one row, an explicit cursor should be declared.

Example: Implicit Cursor

DECLARE
EmpRec Emp%ROWTYPE;
EmpId NUMBER(4);
BEGIN
EmpId := &Empid;
SELECT * INTO EmpRec FROM Emp
WHERE Empno= EmpId;
DBMS_OUTPUT.PUT_LINE(‘Employee Details’);
DBMS_OUTPUT.PUT_LINE(‘======================’);
DBMS_OUTPUT.PUT_LINE(‘Employee ID: ’ || EmpRec.Empno);
DBMS_OUTPUT.PUT_LINE(‘======================’);
DBMS_OUTPUT.PUT_LINE(‘Employee Name: ’ || EmpRec.Ename);
DBMS_OUTPUT.PUT_LINE(‘======================’);
DBMS_OUTPUT.PUT_LINE(‘Employee Designation: ’ || EmpRec.Job);
DBMS_OUTPUT.PUT_LINE(‘======================’);
DBMS_OUTPUT.PUT_LINE(‘Employee Join Date: ’ || EmpRec.Hiredate);
DBMS_OUTPUT.PUT_LINE(‘======================’);
DBMS_OUTPUT.PUT_LINE(‘Employee Salary: ’ || EmpRec.Sal);
DBMS_OUTPUT.PUT_LINE(‘======================’);
DBMS_OUTPUT.PUT_LINE(‘Employee Commission: ’ || EmpRec.Comm);
DBMS_OUTPUT.PUT_LINE(‘======================’);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Employee does not exist’);
END;
/

Attributes of the Implicit Cursor
The attributes of the implicit cursor are used to access information about the most recently executed SQL statement.

%FOUND: Returns TRUE, if the INSERT, UPDATE, or DELETE command affect one or more rows.

Example:

DECLARE
EmpID NUMBER;
BEGIN
EmpID := &EmpID;
UPDATE Emp SET Sal = Sal + Sal * 0.25
WHERE Empno = EmpID;
IF SQL%FOUND THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE(‘Salary of the employee updated
successfully’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Employee ID does not exist’);
END IF;
END;
/

%ISOPEN: Implicit cursors are closed automatically after the execution of the SQL DML statements. As a result, the %ISOPEN attribute always returns FALSE.

%NOTFOUND: Returns TRUE, if the INSERT, UPDATE, or DELETE command does not affect any row.

Example:

DECLARE
EmpID NUMBER;
BEGIN
DELETE FROM Emp WHERE Empno= &EmpID;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Employee does not exist’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘The employee details have been
deleted successfully.’);
END IF;
END;
/

%ROWCOUNT: Returns the number of rows affected by the INSERT, UPDATE, or DELETE command.

Example:

DECLARE
Deptname VARCHAR2(20);
Rows_affected NUMBER;
BEGIN
Deptname := ‘&Deptname’;
UPDATE Emp set Sal = Sal + Sal * 0.25
WHERE Deptno= (SELECT Deptno FROM Dept
WHERE Dname = Deptname);
Rows_affected := SQL%ROWCOUNT;
IF Rows_affected > 0 THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE(‘Salary of ’ || Rows_affected ||
‘ employees of the ’ || Deptname || ‘ department is updated
successfully’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Department does not exist’);
ROLLBACK;
END IF;
END;
/

Explicit Cursor
The explicit cursors are those cursors that are assigned to a SELECT statement explicitly. It is used when more than one row has to be retrieved by a SELECT statement.

Example:

DECLARE
CURSOR myEmpCursor(Emp_Id NUMBER) IS
SELECT Empno, Ename FROM Employee
WHERE Empno = Emp_Id;
BEGIN
Emp_Id := 101; /* Illegal reference */
OPEN myEmpCursor (Emp_Id);
END;
/

Attributes of the Explicit Cursor
The attributes of the explicit cursor are used to provide information about the status of a cursor.


%FOUND: It is used to determines whether the last fetch returns a row or not.

Example:

DECLARE
CURSOR E_Cur IS
SELECT Ename, Sal FROM Emp
WHERE ROWNUM < 11; my_Name Emp.Ename%TYPE; my_Salary Emp.Sal%TYPE; BEGIN OPEN E_Cur; LOOP FETCH E_Cur INTO my_Name, my_Salary; IF E_Cur%FOUND THEN -- fetch succeeded DBMS_OUTPUT.PUT_LINE(‘Name = ’ || my_Name || ‘ Salary = ’ || my_Salary); ELSE -- fetch failed, so exit loop EXIT; END IF; END LOOP; END; /

%ISOPEN: It is used to check whether the cursor is open.

Example:

DECLARE
CURSOR E_Cur IS
SELECT Ename, Sal FROM Emp
WHERE ROWNUM <= 5; my_Name Emp.Ename%TYPE; my_salary Emp.Sal%TYPE; BEGIN IF NOT E_Cur%ISOPEN THEN OPEN E_Cur; END IF; LOOP FETCH E_Cur INTO my_Name, my_salary; IF E_Cur%FOUND THEN -- fetch succeeded DBMS_OUTPUT.PUT_LINE(‘Name = ’ || my_Name || ‘ Salary = ’ || my_salary); ELSE -- fetch failed, so exit loop EXIT; END IF; END LOOP; END; /

%NOTFOUND: This attribute is logically opposite to the %FOUND attribute.

Example:

DECLARE
CURSOR myEmpCursor IS
SELECT Empno, Ename, Sal FROM Emp
WHERE Sal > 2000
ORDER BY Sal DESC;
myID Emp.Empno%TYPE;
myName Emp.Ename%TYPE;
mySalary Emp.Sal%TYPE;
BEGIN
OPEN myEmpCursor;
LOOP
FETCH myEmpCursor INTO myID, myName, mySalary;
EXIT WHEN myEmpCursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘=================’);
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE ID: ’ || myID);
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE Name: ’ || myName);
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE Salary: ’ || mySalary);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘======================’);
END;
/

%ROWCOUNT: It works like a counter and returns zero when the cursor is opened for the first time. You can use this attribute if you want only a few rows of the result set to be returned by the cursor.

Example:

DECLARE
CURSOR myEmpCursor IS
SELECT Empno, Ename FROM Emp
ORDER BY Empno;
myID Emp.Empno%TYPE;
myName Emp.Ename%TYPE;
BEGIN
OPEN myEmpCursor;
LOOP
FETCH myEmpCursor INTO myID, myName;
EXIT WHEN myEmpCursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(myID ||‘ ’|| myName);
EXIT WHEN myEmpCursor%ROWCOUNT > 4;
END LOOP;
CLOSE myEmpCursor;
END;
/

DATA TYPES

A data type is the name or label for a set of values. It specifies what type of value an attribute or a variable holds. Also, it specifies how the information will be stored in a computer. Each attribute or variable has a data type.

Character Data Type
The Oracle Database provides character data types to store character values. These data types are discussed next.

CHAR
The CHAR data type is used to store the fixed length character data. The maximum length of data that it can store is 2000 bytes or characters. The default value for CHAR data type is 1.

Syntax:

column_name CHAR(width)

NCHAR
The NCHAR data type is used to store the fixed length character string in the national character set of the database. This data type can hold up to 2000 characters. Defining national character set in the database determines the maximum length of the column. When you create a table with a column having NCHAR data type, you define the column length in characters.

Syntax:

column_name NCHAR(width)

VARCHAR
The VARCHAR data type is used to store a variable-length character string. The maximum width of the VARCHAR data type is 4000 bytes or characters. It is recommended to use the VARCHAR2 data type rather than the VARCHAR data type.

Syntax:

column_name VARCHAR(width)

VARCHAR2
The VARCHAR2 data type is also used to store a variable-length character string. While creating the VARCHAR2 column, you can specify the maximum number of bytes or characters of data that can be stored in this column. If you enter a value that is smaller than the column size, the Oracle database will store the actual value of the data and set the remaining space free. The maximum width of the VARCHAR2 data type is 4000 bytes.

Syntax:

column_name VARCHAR(width)

NVARCHAR2
The NVARCHAR2 data type is used to store variable-length or multibytes character set data. While creating the NVARCHAR2 column, you can specify the maximum number of bytes or characters of data that can be stored in this column. The maximum length of the column is determined by the national character set defined in the column and is up to 4000 bytes.

Syntax:

column_name NVARCHAR(width)

NUMBER Data Type
The NUMBER data type stores variable-length numeric data with a precision between 1 and 38, and the scale has a range between –84 and 127. It can store the zero, positive numbers, or negative fixed numbers with absolute values from 1.0 x 10-130 to 1.0 x 10126 digits as well as fixed and floating point numbers. The Oracle database provides three subtypes of the NUMBER data type: Fixed-point, Floating-point, and Integer.

Fixed-point Number
To define the Fixed-point number data type, you have to specify the values of both precision and scale.

Syntax:

Column_Name NUMBER(P,S)

P is the precision or the total number of digits with precision up to 38 digits and S is the scale or the number of digits on the right of the decimal point. The value of S can range from
-84 to 127.

Integer Numbers
An integer is a whole number with no digit on the right of the decimal point. You can define a column of integer data type by omitting the scale value.

Syntax:

Column_Name NUMBER(P)

P is the precision or the total number of digits with precision up to 38 digits.

Floating-point Number
The Floating-point numbers can have a decimal point anywhere between the first and the last digits, or it can be a number without any decimal point as there is no restriction for the decimal point.

Syntax:

Column_Name NUMBER

Oracle provides two numeric data types for floating-point numbers: BINARY_FLOAT and BINARY_DOUBLE.

BINARY_FLOAT
The BINARY_FLOAT data type is a single-precision floating-point number data type. Each BINARY_FLOAT value requires 5 bytes, including a length byte.

BINARY_DOUBLE
The BINARY_DOUBLE is a double-precision floating-point number data type. Each BINARY_DOUBLE value requires 9 bytes, including a length byte.

Datetime and Interval
The Oracle datetime data types store date and time values. The datetime data types are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. The interval data types are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. The datetime and interval data types are discussed next.

DATE
The DATE data type is used to store date and time. Oracle stores the following information for each date value: century, year, month, date, hour, minute, and second. You can represent the date and time in both character and number data types.

Syntax:

Column_Name DATE

TIMESTAMP
The TIMESTAMP data type stores all information that the DATE data type stores, including the fractional part of seconds. It is an expansion of the DATE data type. It stores century, year, month, day, hour, minute, second, and fractional seconds. This data type is useful for storing precise time values.

Syntax:

Column_Name TIMESTAMP [(Fractional_Seconds_Precision)]

Fractional_Seconds_Precision can range from 0 to 9 and its default value is 6.

TIMESTAMP WITH TIME ZONE
The TIMESTAMP WITH TIME ZONE data type is an alternative to the TIMESTAMP data type. The value stored by this data type includes time zone offset. This data type is useful for collecting and evaluating date information across geographic regions.

Syntax:

Column_Name TIMESTAMP [(Fractional_Seconds_Precision)] WITH TIME ZONE

Fractional_Seconds_Precision can range from 0 to 9 and its default value 6.

TIMESTAMP WITH LOCAL TIME ZONE
The TIMESTAMP WITH LOCAL TIME ZONE data type is another alternative to the TIMESTAMP data type. It also includes a time zone offset in its value. Unlike the TIMESTAMP WITH TIME ZONE data type, the TIMESTAMP WITH LOCAL TIME ZONE data type does not store the time zone offset as part of the column data.

Syntax:

Column_Name TIMESTAMP [(Fractional_Seconds_Precision)] WITH LOCAL TIME ZONE

INTERVAL YEAR TO MONTH
The INTERVAL YEAR TO MONTH data type is used to store the period of time that
represents year and month.

Syntax:

Column_Name INTERVAL YEAR [(year_precision)] TO MONTH

year_precision can range from 0 to 9 and its default value is 2.

INTERVAL DAY TO SECOND
The INTERVAL DAY TO SECOND data type is used to store the period of time that represents days, hours, minutes, and seconds with a fractional part.

Syntax:

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

day_precision can range from 0 to 9 and its default value 2. fractional_seconds_precision can range from 0 to 9 and its default is 6.

LOB
LOB stands for Large Object. It is a data type and stores unstructured information upto 4 gigabytes such as sound clips, video files, and so on. The LOB data types allow efficient, random, and easy access to the data. The values stored in this data type are known as locators. These locators store the locations of large objects and location may be inside or outside the database.

The LOB data types available in Oracle database are BLOB, CLOB, NCLOB, and BFILE.

BLOB
BLOB stands for Binary Large Objects. This data type is used to store binary data up to 4 GB in length.

Syntax:

Column_Name BLOB

CLOB
CLOB stands for Character Large Objects and can store character data up to 4 GB in length.

Syntax:

Column_Name CLOB

BFILE
BFILE stands for Binary FILE. It is a pointer (reference) to the external file. The files referenced by BFILE exist in the file system and enables you to access the binary file that are stored outside the Oracle database. The database only maintains a pointer to the file. The size of the external file is limited only by the operating system because the data is stored outside the database.

Syntax:

Column_Name BFILE

NCLOB
The NCLOB data type supports both fixed-width and variable-width character sets. The
NCLOB data type can store up to 4 gigabytes of character text data.

Syntax:

Column_Name CLOB

CONSTRAINTS

Constraints are a set of predefined rules, which ensure that the valid data values are stored in the columns of a table. Oracle provides some predefined commands that enable you to define the constraints for a table or a column. There are two types of constraints: integrity and value. The integrity constraints include primary key and foreign key. The value constraints define specific data values or data ranges. The values entered in columns should not be Null. There are two levels of constraints: table level constraint and column level constraint.

The table level constraints restrict the values that a table can store. These constraints can be referred to one or more columns in a table. The table level constraint includes the following constraints: PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK.

The column level constraints can be referred to a single column in a table, and they do not specify a column name, except the CHECK constraint. As a result, they limit the values that can be placed in a specific column, irrespective of values that exist in other table rows. The column level constraint can be one of the following: UNIQUE, NOT NULL, PRIMARY KEY, and FOREIGN KEY.

The syntax and behavior of the table level constraint and the column level constraint is similar with only the following difference:

1. The syntax for table level constraints is separated from the column definitions by comma.
2. The table level constraints must follow the definition of the columns to which they are referred.
3. The table level constraint can be defined for more than one column and SQL evaluates the constraint based on the combination of values stored in all columns.

Syntax for declaring a constraint:

CONSTRAINT [Constraint_Name] Constraint_Type


Primary Key Constraint
The primary key constraints ensure that the Null values are not entered in a column and also the value entered is unique. Thus, these constraints avoid the duplication of records. A primary key constraint can be defined in the CREATE TABLE and ALTER TABLE commands. This constraint can be declared at both levels: within the column level and at the table level.

The syntax for declaring a primary key constraint at the column level is as follows:

CONSTRAINT Constraint_Name PRIMARY KEY

The syntax for declaring a primary key constraint at the table level is as follows:

CONSTRAINT Constraint_Name PRIMARY KEY (Column_Name)

You can also create a primary key constraint for more than one column. The syntax for declaring the primary key for more than one column is as follows:

CONSTRAINT Constraint_Name PRIMARY KEY (Column_Name1,
Column_Name2, Column_Name3, Column_Name4 ...)


Foreign Key Constraint
The foreign key constraint is the property that guarantees the dependency of data values of one column of a table with another column of a table. A foreign key constraint, also known as referential integrity constraint, is declared for a column to ensure that the value in one column is found in the column of another table with the primary key constraint. The table containing the foreign key constraint is referred to as the child table, whereas the table containing the referenced (Primary key) is referred to as the parent table. The foreign key reference will be created only when a table with the primary key column already exists. The foreign key constraint can be declared in two ways: within the column declaration and at the end of the column declaration.

The syntax for using the foreign key constraint within the column declaration is as follows:

CONSTRAINT Constraint_Name REFERENCE Primary_Key_Table_Name
(Primary_Key_Column_Name)

The syntax for declaring the foreign key constraint at the end of the column declaration:

CONSTRAINT Constraint_Name FOREIGN KEY (Column_Name) REFERENCE
Primary_Key_Table_Name (Primary_Key_Column_Name)


NOT NULL Constraint
A column in a table can be declared with the NOT NULL constraint. On declaring this constraint, you cannot insert Null value in the column. You can add this constraint while creating the table by using the CREATE TABLE command. You can also add this constraint after creating the table by using the ALTER command. The ALTER command will be discussed later in the chapter.

The syntax for declaring the NOT NULL constraint within the column declaration is as follows:

CONSTRAINT Constraint_Name NOT NULL

CHECK Constraint
The CHECK constraint ensures that all values inserted into the column satisfy the specified condition. This constraint checks data against the expression defined in the INSERT and UPDATE statement. The CHECK constraint can be declared at the column level.

The syntax for declaring the CHECK constraint within the column declaration is as follows:

CONSTRAINT Constraint_Name CHECK(Col_Condition)

UNIQUE Key Constraint
The UNIQUE key constraint is used to prevent the duplication of data values within the rows of a specified column or a set of columns in a table. The column defined with the UNIQUE key constraint can also allow a null value. Moreover, this constraint can be added to the existing columns. The UNIQUE key constraint can be declared both at the column level and the table level.

The syntax for declaring the UNIQUE key constraint at the column level is as follows:

CONSTRAINT Constraint_Name UNIQUE

DEFAULT Constraint
The DEFAULT constraint is used to set the default value for a column. This constraint ensures that a default value is set automatically by Oracle for each column of a table. The DEFAULT
constraints are declared at the column level declaration.

The syntax for declaring the DEFAULT constraint is as follows:

DEFAULT ‘default_value’

Example:

Country VARCHAR2 (50) DEFAULT ‘USA’

ORACLE DATABASE FEATURES

Oracle provides a number of features such as data concurrency and consistency, manageability, backup and recovery, client-server architecture, business intelligence, database security, and data integrity. These features are discussed next.


Data Concurrency and Consistency
Data concurrency is the property which implies that the data can be accessed by multiple users at the same time. Data consistency is a property that ensures that each user can see a consistent view of data, which means the user can view the changes made by him in his own transactions as well as the transactions of other users.


Manageability
Oracle manages the database itself and it tunes the database automatically. Oracle has the following self-managing database features:

1. Automatic undo management.
2. Dynamic memory management.
3. Oracle-managed files.
4. Free space management.
5. Multiple block sizes.
6. Recovery management system.

Backup and Recovery
It is one of the most important features of Oracle. The backup of data is created to protect data in case the system crashes or the data gets corrupt. It consists of files such as datafiles, control files, redo logs files, and so on. There are different types of backups available in Oracle 11g.


Recovery means restoring a backup file into a specified location. In case of crash of database, Database Administrator recovers the data by using the recovery method. 


Client-Server Architecture
The Oracle database allows multiprocessing, which is also known as distributed processing system. In this system, many users access data at the same time. This system reduces the load on the processor as the tasks are assigned to different processors. In the client-server architecture, the database system is divided into two parts: client machine and server machine. 


Business Intelligence
Oracle provides various business intelligence features such as data warehousing, ELT (extraction, transformation, and loading), materialized views, table compression, parallel execution, bitmap indexes in data warehousing, analytic SQL, and so on. 


Database Security
Oracle provides security features for controlling unauthorized access to database. These features include prevention of unauthorized data access, prevention of unauthorized access to schema objects, and storing all user actions. Oracle associates each database user with a schema by the user’s name. By default, each database user creates and has access to all objects in the corresponding schema.


Data Integrity
Data integrity allows you to specify certain rules for the quality of the data, which the data in a database needs to satisfy. If a user inserts data that doesn't meet the specified requirements, Oracle will not allow the user to insert the data. Therefore, it is important that the data adheres to the set of rules determined by the database administrator or the application developer. There are five data integrity constraints in Oracle that enable you to enforce the rules on the data. These constraints are Not Null, Unique Key, Primary Key, Foreign Key, and Check.

INTRODUCTION

A database is a collection of data that is used to store and retrieve related information. The Oracle database is the fourth generation relational database management system, which manages a large amount of data and provides multi-user environment. Thus, multiple users can concurrently access the same data in a database.


Oracle provides several useful features for database developers such as integrity constraints, transaction controls, stored procedures and packages, database triggers, cost-based optimizer, shared SQL, locking, and sequences. Basically, oracle database is divided into two structures: logical and physical. Both the structures are managed separately in Oracle.


The Oracle database is a relational database and is therefore called Relational Database Management System (RDBMS). Any RDBMS must possess certain characteristics, which are as follows:

• Constraints
• Objects
• Operations
• Data Integrity
• Database Normalization