Oracle Forms Exception Handling: NO_DATA_FOUND, TOO_MANY_ROWS and OTHERS

Oracle Forms Exception Handling: NO_DATA_FOUND, TOO_MANY_ROWS and OTHERS

EXCEPTION block in PLSQL Oracle Forms is used to track the exceptions. Following is the PLSQL code snippet which uses NO_DATA_FOUND, TOO_MANY_ROWS and OTHERS exceptions. If the SQL SELECT query does not return any data, NO_DATA_FOUND exception is fired. If the SQL SELECT query returns more than one row where it was expected to return only one row, TOO_MANY_ROWS exception can be used to track this kind of exception. If you are not sure what kind of exception can the code throw, use OTHERS exception.

DECLARE
  DEPARTMENT_NAME VARCHAR(60);
BEGIN
  SELECT DEPTNAME INTO DEPARTMENT_NAME FROM DEPT WHERE DEPTNO = 20;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    MESSAGE('No data found');
  WHEN TOO_MANY_ROWS THEN
    MESSAGE('More than one row found');
  WHEN OTHERS THEN
    NULL; -- don't do anything and just return from the procedure
END;

Difference between WHEN-VALIDATE-ITEM and KEY-NEXT-ITEM triggers

Difference between WHEN-VALIDATE-ITEM and KEY-NEXT-ITEM triggers

WHEN-VALIDATE-ITEM and KEY-NEXT-ITEM triggers are very close to each other and create a lot of confusion. Following are three differences between them to clear the picture a little bit.

1. Whenever the user changes the value in the item and tries to move out of that item using ENTER or TAB or MOUSE, WHEN-VALIDATE-ITEM trigger is fired. But, in case of KEY-NEXT-ITEM trigger, if user moves out using MOUSE, it will not fire. So, the validation written on this trigger will not fire. Better use, WHEN-VALIDATE-ITEM trigger in this case as it also works with MOUSE.

2. KEY-NEXT-ITEM trigger fires before the WHEN-VALIDATE-ITEM trigger.

3. KEY-NEXT-ITEM trigger will fire every time you move to the next field from that field but WHEN-VALIDATE-ITEM will fire only when you have acutally made any changes to that item. If you have made no changes in the item, it will not fire when you move out this item.

Personally, I prefer to use WHEN-VALIDATE-ITEM trigger in many situations.

Oracle Forms Tutorials: WHEN-VALIDATE-ITEM trigger

Oracle Forms Tutorials: WHEN-VALIDATE-ITEM trigger

Consider that you have an oracle form on which there is a datablock which uses EMP table. EMP table has a column called SALARY. Now there is a contraint on the SALARY column that it should be greater than or equal to $1000. Your requirement is that whenever any user fills salary in the Oracle Forms ITEM (say ITEM_SALARY) and tabs out from that item, a validation should fire and if the value filled is not valid, it should give you an error message and does not let the cursor go to the other item. In these situations WHEN-VALIDATE-ITEM trigger is used. Following is the PLSQL code you should write on the WHEN-VALIDATE-ITEM trigger of the ITEM_SALARY item.

IF :ITEM_SALARY < 1000 THEN

    MESSAGE('ERROR: Salary must be at least $1000 or more.');

    RAISE FORM_TRIGGER_FAILURE; -- To keep the cursor in the item

END IF;

You should also go through this video on YOUTUBE by Edward Honour.

In this video, he tries to pick the department name when the user enters the department number. If department number does not exist in the database, he shows the error message and does not let the cursor to go to the other item using FORM_TRIGGER_FAILURE trigger. He has used NO_DATA_FOUND exception for showing the error message. Following is the code used in this video:

BEGIN

SELECT DEPT_NAME INTO :BLOCKNAME.ITEMNAME FROM DEPT WHERE        DEPT_NO = :BLOCKNAME.ITEMNAME2;

EXCEPTION

WHEN NO_DATA_FOUND THEN

MESSAGE('Invalid Department Number');

RAISE FORM_TRIGGER_FAILURE;

END

Precautions while using Clustered and Non-Clustered Indexes on a Table

Precautions while using Clustered and Non-Clustered Indexes on a Table

 

Without an index SQL Server / Oracle has to scan entire tables to return requested data. It is like the index page in a book. You check for the keyword you want to read about in the index and you jump directly to the page where the content belongs, instead of scanning page by page for the material you want to read.

 

Similarly a table index allows you to locate data without the need to scan the entire table. You create indexes on one or more columns in a table to help SQL Server / Oracle find the data quickly in a query.

 

Types of Index

 

1. Clustered Index

 

A clustered index alters the way that the rows are stored. When you create a clustered index on a column (or a number of columns), SQL Server / Oracle sorts the table’s rows by that column(s). It is like a dictionary, where all words are sorted in alphabetical order in the entire book. Since it alters the physical storage of the table, only one clustered index can be created per table.

 

Oracle automatically creates clustered index on primary key column.

 

A clustered index should be used on a column that will be used for sorting. A clustered index is used to sort the rows on disk, so you can only have one per table.

 

2. Non Clustered Index

 

Non-clustered index, on the other hand, does not alter the way the rows are stored in the table. It creates a completely different object within the table that contains the column(s) selected for indexing and a pointer back to the table’s rows containing the data. It is like an index in the last pages of a book, where keywords are sorted and contain the page number to the material of the book for faster reference.

 

Indexes help in Performance Optimization

 

SQL server sorts the indexes efficiently by using a B-tree, which is a tree data structure that allows SQL Server to keep data sorted, to allow searches, sequential access, insertions and deletions in logarithmic amortized time. This methodology minimizes the number of pages accessed to locate the desired index key, therefore resulting an improved performance.

 

Precaution while using Indexes on Table

 

You will not notice performance issues until you have quite a bit of data in your tables. But as your data increases, you must take care of indexes.

 

1. Index should be used on a column that's going to be used (a lot) to search the table. Most important consideration is how much time your queries are taking. If a query doesn't take much time or isn't used very often, it may not be worth adding indexes. The best option is to set your clustered index on the most used unique column, usually the primary key.

 

You should always have a well selected clustered index in your tables, unless a very compelling reason.

 

2. Indexes should not be used for columns or tables that are often updated. Clustered indexes makes SQL Server / Oracle order the rows on disk according to the index order. This implies that if you access data in the order of a clustered index, then the data will be present on disk in the correct order. However if the column(s) that have a clustered index is frequently changed, then the row(s) will move around on disk, causing overhead - which generally is not a good idea.

 

This side effect of indexes is related to the cost of INSERT, UPDATE, MERGE and DELETE statements. Such statements can take longer to execute in the presence of indexes since they alter the data on the table resulting the update of the indexes too. Imagine the situation of an INSERT statement that has to add rows to a table with a clustered index. Table rows may need to be repositioned since clustered index needs to order the data pages themselves thus creating more overhead. So, it is crucial to take into account the overhead of INSERT, UPDATE and DELETE statements before designing your indexing strategy. Although there is an overhead in the above statements, you have to take into account that many times an UPDATE or DELETE statement will have to execute in a subset of data, defined by a WHERE clause, where indexing may outweigh the additional cost of index updates since SQL server has to find the data before updating them.

 

3. Having many indexes is not good either. They cost to maintain. So start out with the obvious ones, and then profile to see which ones you miss and would benefit from. You do not need them from start, they can be added later on.

 

4. Big column datatypes can be used when indexing, but it is better to have small columns indexed than large. Also it is common to create indexes on groups of columns.

 

5. There are also storage considerations. When inserting rows into a table with no clustered index, the rows are stored back to back on the page and updating a row may result in the row being moved to the end of table, leaving empty space and fragmenting the table and indexes.

 

Scan and Seek

 

A table without a clustered-index is called a “heap table”. A heap table has no sorted data thus SQL server has to scan the entire table in order to locate the data in a process called a “scan”.

 

In the case of a clustered index the data are sorted on the key values (columns) of the index. SQL server is now able to locate the data by navigating down from the root node, to the branch and finally to the leaf nodes of the B-tree structure of the index, in a process called a “seek”. The later approach is much faster when you want to filter or sort the data you want to retrieve.

 

Difference between Clustered and Non Clustered Index

 

1. A clustered index actually describes the order in which records are physically stored on the disk, hence the reason you can only have one. A Non-Clustered Index defines a logical order that does not match the physical order on disk.

 

2. There can be only one clustered index on a table while there can be up to 249 non clustered indexes on a table.

 

3. Faster to read than non clustered as data is physically stored in clustered index.

3 Simple and Interesting PLSQL Programs to Explain Triggers

Triggers in PL/SQL are the blocks which are automatically fired before or after any alteration (insert, update, delete) is done to the table. Here are 3 simple programs which will illustrate the concept of triggers very efficiently. Have a look...

Consider the following table named student. It contains roll no, name and marks of each student.

roll          name     marks
20034   SID        69
20035   HARRY 88
20036   TANK    34

1. Program to illustrate the use of BEFORE TRIGGER. This trigger will always enter the name of student in capital letters.

CREATE OR REPLACE TRIGGER capital_name BEFORE INSERT OR UPDATE ON student FOR EACH ROW
BEGIN
:NEW.name := UPPER(:NEW.name);
END;

Now if you make the following query to student table:

UPDATE student SET name = ‘Steven’ WHERE roll = 20035;

Then instead of ‘Steven’, ‘STEVEN’ is inserted into the table.

2. Program to illustrate the use of BEFORE TRIGGER. This trigger will not allow to do any action with student table on a specified day, here saturday.

CREATE OR REPLACE TRIGGER no_action BEFORE INSERT OR UPDATE OR DELETE ON student FOR EACH ROW
BEGIN
IF(LTRIM(RTRIM(TO_CHAR(SYSDATE,’DAY’)))=’SATURDAY’)
THEN
RAISE_APPLICATION_ERROR(-20998,’Action Denied’);
END IF;
END;

Now if you make the following query to student table:

UPDATE student SET name = ‘Steven’ WHERE roll = 20035;

on saturday. You will get the message ‘Action Denied’. This is very useful feature used to avoid any alteration in sensitive data on weekends when you are not around and anybody else tries to alter it.

3. Program to illustrate the use of AFTER TRIGGER. This trigger will put the altered enteries in a new table named track.

Suppose student table is very critical and crucial. Only you are allowed to alter the enteries. So you can make trigger to track other persons who login with their username and try to alter the table. For this you have to create a table track as follows:

CREATE TABLE track (roll number, name varchar2(40), oldmarks number, newmarks number, uname varchar2(40));

Now create a Trigger as

CREATE OR REPLACE TRIGGER track_action AFTER UPDATE ON student FOR EACH ROW
BEGIN
INSERT INTO track VALUES(:OLD.roll, :OLD.name, :OLD.marks, :NEW.marks, USER);
END;

Now suppose a relative of TANK works in your department and he comes to increase his marks for 34 to 94. He will login from his username and will fire the following query.

UPDATE student SET marks = 94 WHERE roll = 20036;

He will now get delighted that he has altered the table. But he doesn’t know that a secret table named track has strored all the alterations done by him.

Now when you come in morning and fire the following query:

SELECT * FROM track;

Now, there will be one record in this table containing roll as 20036, name as TANK, old marks as 34 and new marks as 94 and most importantly the username of sneaker. So he has been trapped.

3 Very Simple PLSQL Programs to Explain Procedures, Functions and Packages

Here in this tutorial, Procdures, Functions and Packages are fully explored through simple programs. Procedure is a subprogram that performs a given task while Function is same as Procedure but it returns the value. Packages group up Procedures, Functions, Variable, Constants, Cursors and Exceptions.

1. Program to illustrate the use of Procedure. The program is to multiply two numbers. This program is stored in a file name myprocedure.sql

CREATE OR REPLACE PROCEDURE product(a number, b number) AS
c number;
BEGIN
c:=a*b;
DBMS_OUTPUT.PUT_LINE(c);
END product;

Now use SQL>@ myprocedure to create this procedure.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.
Now use SQL> EXEC product(3,4); It will give output 12.

You can also call the above procedure through a program below:

SQL> ED CALLPRO
DECLARE
a number;
b number;
BEGIN
a:= &a;
b:= &b;
product(a,b);
END

SQL> @ CALLPRO;

2. Program to illustrate the use of Functions. The program is to multiply two numbers. This program is stored in a file name myfunction.sql

CREATE OR REPLACE FUNCTION product2(a number, b number) RETURN number AS
c number;
BEGIN
c:=a*b;
RETURN c;
END product2;

Now use SQL>@ myfunction to create this function.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.
Now use SQL> SELECT product2(3,4) FROM DUAL; It will give output 12.

You can also call the above function through a program below:

SQL> ED CALLFUN
DECLARE
a number;
b number;
result number;
BEGIN
a:= &a;
b:= &b;
result:= product2(a,b);
DBMS_OUTPUT.PUT_LINE(result);
END

SQL> @ CALLFUN;

3. Program to illustrate the use of Packages. This package name is combine and package specification is stored in file pack and package body is stored in file pack_body.

SQL> ED pack
CREATE OR REPLACE PACKAGE combine AS
PROCEDURE product(a number, b number);
FUNCTION product2(a number, b number) RETURN number;
END combine;

Now use SQL>@ pack to create this package.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.

Now we will make package body in file pack_body.

SQL> ED pack_body
CREATE OR REPLACE PACKAGE BODY combine AS

PROCEDURE product(a number, b number) AS
c number;
BEGIN
c:=a*b;
DBMS_OUTPUT.PUT_LINE(c);
END product;

FUNCTION product2(a number, b number) RETURN number AS
c number;
BEGIN
c:=a*b;
RETURN c;
END product2;

END combine;

Now use SQL>@ pack_body to create this package body.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.

Now we will make call to this package as

SQL> EXEC combine.product(3,4);
SQL> SELECT combine.product(3,4) FROM DUAL;

9 Very Simple PLSQL Programs to Explain Control Structures

Here you will find the list of 9 simplest plsql programs fully exploring the control structures. This tutorial covers IF, ELSE, ELSIF, LOOP, EXIT, EXIT WHEN, WHILE, FOR, GOTO and NULL statements. So have a look...

1. Program to insert values in a table student which has two fields student_id and name.

DECLARE
max_id number;
BEGIN
SELECT MAX(student_id) INTO max_id FROM student;
INSERT INTO student (student_id, name) VALUES (max_id + 1, ‘Harry’);
DBMS_OUTPUT.PUT_LINE(’Record Inserted’);
END;

2. Program illustrating the use of IF, ELSE and ELSIF

If Percentage >=80 –> Grade A
If Percentage >=60 –> Grade B
If Percentage >=45 –> Grade C
If Percentage < 45 --> Fail

 

DECLARE
n number;
BEGIN
–Enter percentage between 1 and 100
n:=&n;
IF(n>=1 AND n< =100) THEN
IF(n>=80) THEN
DBMS_OUTPUT.PUT_LINE(’Grade A’);
ELSIF(n>=60 AND n<80)
DBMS_OUTPUT.PUT_LINE(’Grade B’);
ELSIF(n>=45 AND n<60)
DBMS_OUTPUT.PUT_LINE(’Grade C’);
ELSE
DBMS_OUTPUT.PUT_LINE(’Fail’);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(’Percentage must be between 0 and 100′);
END IF;
END;

3. Program illustrating the use of LOOP with EXIT statement

DECLARE
n number:=0;
BEGIN
LOOP
n:=n+1;
IF(n>3) THEN
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 3

4. Program illustrating the use of LOOP with EXIT WHEN statement

DECLARE
n number:=0;
BEGIN
LOOP
n:=n+1;
EXIT WHEN n>3;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 3

5. Program illustrating the use of WHILE LOOP statement

DECLARE
n number:=0;
BEGIN
WHILE n<3
LOOP
n:=n+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 3

6. Program illustrating the use of FOR LOOP statement

DECLARE

n number:=0;

BEGIN
FOR i IN 1..3
LOOP
n:=n+i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 6

7. Program illustrating the use of FOR LOOP REVERSE statement

DECLARE
n number:=0;
BEGIN
FOR i IN REVERSE 1..3
LOOP
n:=n+i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 6

8. Program illustrating the use of GOTO statement

BEGIN
DBMS_OUTPUT.PUT_LINE(’First Line’);
GOTO third;
DBMS_OUTPUT.PUT_LINE(’Second Line’);
< >
DBMS_OUTPUT.PUT_LINE(’Third Line’);
END;

output:
First Line
Third LIne

9. Program illustrating the use of NULL statement

DECLARE
n number:= &n;
BEGIN
IF n MOD 2 = 0 THEN
DBMS_OUTPUT.PUT_LINE(’Number is Even’);
ELSE
NULL;
END IF;
END;

output: If entered no. is even then it will display the message otherwise no message will be displayed.

6 Very Simple PLSQL Programs to Explain Cursors

Cursors in PL/SQL are used to retrieve more than one row at a time. The data that is stored in cursors is known as Active Data Set. These cursors are of two types:

1. Implicit Cursors : predefined cursors
2. Explicit Cursors : user defined cursors

Here are simple 6 programs illustrating the concept of cursors.

1. Program to illustrate the use of attribute SQL%FOUND in Implicit Cursor. The Program is to find out the salary of an employee from emp table whose two fields are emp_sal and emp_no.

DECLARE
salary number(5);
BEGIN
SELECT emp_sal INTO salary FROM emp WHERE emp_no=&empno;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(’Record Found’);
DBMS_OUTPUT.PUT_LINE(’Salary = ‘ || salary);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(’Record Not Found’);
END;

2. Program to illustrate the use of attribute SQL%NOTFOUND in Implicit Cursor. The Program is to find out the salary of an employee from emp table whose two fields are emp_sal and emp_no.

DECLARE
salary number(5);
BEGIN
SELECT emp_sal INTO salary FROM emp WHERE emp_no=&empno;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(’Record Not Found’);
ELSE
DBMS_OUTPUT.PUT_LINE(’Record Found’);
DBMS_OUTPUT.PUT_LINE(’Salary = ‘ || salary);
END IF;
END;

3. Program to illustrate the use of attribute SQL%ROWCOUNT in Implicit Cursor. The Program is to update the salary of each employee by 1000.

BEGIN
UPDATE emp SET emp_sal = emp_sal +1000;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ‘Records Updated’);
END;

4. Program to illustrate the use of Explicit Cursors. The Program is to display the information of employess (Emp No, Name and Salary) of a given department.

DECLARE
CURSOR empdata IS
SELECT emp_no, emp_name, emp_sal FROM emp WHERE emp_deptno = &deptno;
ecode emp.emp_no%TYPE;
ename emp.emp_name%TYPE;
esal emp.emp_sal%TYPE;
BEGIN
OPEN empdata;
LOOP
FETCH empdata INTO ecode, ename, esal;
EXIT WHEN empdata%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ecode || ename || esal);
END LOOP;
CLOSE empdata;
END;

5. Program to illustrate the use of Explicit Cursors with FOR LOOP. The Program is to display the information of employess of a given department (same as program 4)

DECLARE
CURSOR empdata IS
SELECT emp_no, emp_name, emp_sal FROM emp WHERE emp_deptno = &deptno;
BEGIN
FOR rec IN empdata
LOOP
DBMS_OUTPUT.PUT_LINE(rec.emp_no || rec.emp_name || rec.emp_sal);
END LOOP;
END;

6. Program to illustrate the use of Explicit Cursors with Parameter Passing Concept. The Program is to display the information of employees of a given department (same as program 4)

DECLARE
CURSOR empdata(n number) IS
SELECT emp_no, emp_name, emp_sal FROM emp WHERE emp_deptno = n;
ecode emp.emp_no%TYPE;
ename emp.emp_name%TYPE;
esal emp.emp_sal%TYPE;
BEGIN
OPEN empdata(n);
LOOP
FETCH empdata INTO ecode, ename, esal;
EXIT WHEN empdata%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ecode || ename || esal);
END LOOP;
CLOSE empdata;
END;

13 Point comparison between SQL and PLSQL

SQL and PL/SQL both are the integrated part of DATA BASE MANAGEMENT SYSTEM. SQL is basic while PL/SQL is a procedural language which uses SQL to perform multiple tasks on database transactions and manipulations. We can embed SQL in a PL/SQL program, but we cannot embed PL/SQL within a SQL statement. Here are some noteworthy differences between SQL and PL/SQL. 

1. Procedural Capabilities: SQL does not have any procedural capabilities while PL/SQL supports it. It means that PL/SQL provides conditional, iterative and sequential control structures and many more programming facilities. SQL is a data oriented language for selecting and manipulating sets of data while PL/SQL is a procedural language to create applications. SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tells the database how to do things (procedural).

2. Flexibility: PL/SQL is much more flexible than SQL as we can program any thing as we desire while in case of SQL you have to write just one statement to retrieve the results.

3. Server Performance: PL/SQL improves server performance by reducing the number of calls from application to oracle server. The application pass block of SQL statements to oracle server at one time instead of passing each statement individually. This reduces the network traffic between application and oracle server.

4. Error Detection and handling: PL/SQL provides the facility of error detection and handling. It enables the user to define exceptions on their won which is very poor in SQL.

5. Modular Programming: PL/SQL is modular programming because it allows you to divide your application into managable well defined logic modules such as procedures and functions.

6. Reduced Recompilation: PL/SQL reduces recompilation work as the combination of SQL commands can be executed at the same time which is not possible in SQL where you have to execute only one statement at a time.

7. Reduced I/O: The input output operations occur very less in the case of PL/SQL as the set of SQL commands can be handled at a single time while you have to execute all the statement individually in the case of SQL.

8. Platform Independence and Portability: The code of PL/SQL can be used on any platform which runs oracle while you can not do the same with SQL commands. You have to explicitly write all the SQL commands when you shift from one platform to another. While in case of PL/SQL, if you have made a program, you can run it on any platform.

9. Dynamic SQL: PL/SQL supports dynamic SQL which makes your application more flexible and versatile.

10. Security: By using PL/SQL you can provide security to the sensitive data by moving the code from client to server, you can protect data from tampering, hide the internal details and can restrict who has access to this code.

11. Traffic Congestion: PL/SQL uses block of SQL commands and are passed at one time not frequently as in the case of SQL, so the traffic to the server is considerably reduced. 

12. Transaction Performance: PL/SQL also improves Transaction performance as the many calculations can be performed very efficiently and quickly without calling the oracle engine. 

13. Reusability: PL/SQL code once created can be used any time anywhere but this is not possible for SQL Commands. So the same code can be used by many applications and you have no need of creating the same functions or procedures again and again.

Relation between Tablespace, Datafile and Control File

Databases, tablespaces, and datafiles are closely related, but they have important differences:

An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data.

Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.

A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).

Tablespace:

A database is divided into one or more logical storage units called tablespaces. Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks.

Default Tablespaces: System, SysAux, Undo and Temporary

Other Tablespaces: Bigfile, Read-only, Temporary Tablespaces for Sort Operation

1. Tablespaces can be made online and offline.
2. Tablespaces can be transported from one database to another.

Datafiles:

When a datafile is first created, the allocated disk space is formatted but does not contain any user data. However, Oracle reserves the space to hold the data for future segments of the associated tablespace—it is used exclusively by Oracle. As the data grows in a tablespace, Oracle uses the free space in the associated datafiles to allocate extents for the segment.

Control Files:

The database control file is a small binary file necessary for the database to start and operate successfully. A control file is updated continuously by Oracle during database use, so it must be available for writing whenever the database is open. If for some reason the control file is not accessible, then the database cannot function properly.

Each control file is associated with only one Oracle database.

6 Advantages of using stored procedures in your application

Applications that use stored procedures have the following advantages:

1. Stored Procedures are Precompiled

Once created, these can be used again and again  without compilation.

2. Reduced network usage between clients and servers

A client application passes control to a stored procedure on the database server. The stored procedure performs intermediate processing on the database server, without transmitting unnecessary data across the network. Only the records that are actually required by the client application are transmitted. Using a stored procedure can result in reduced network usage and better overall performance.

Applications that execute SQL statements one at a time typically cross the network twice for each SQL statement. A stored procedure can group SQL statements together, making it necessary to only cross the network twice for each group of SQL statements. The more SQL statements that you group together in a stored procedure, the more you reduce network usage and the time that database locks are held. Reducing network usage and the length of database locks improves overall network performance and reduces lock contention problems.

Applications that process large amounts of SQL-generated data, but present only a subset of the data to the user, can generate excessive network usage because all of the data is returned to the client before final processing. A stored procedure can do the processing on the server, and transmit only the required data to the client, which reduces network usage.

3. Enhanced hardware and software capabilities

Applications that use stored procedures have access to increased memory and disk space on the server computer. These applications also have access to software that is installed only on the database server. You can distribute the executable business logic across machines that have sufficient memory and processors.

4. Improved security

By including database privileges with stored procedures that use static SQL, the database administrator (DBA) can improve security. The DBA or developer who builds the stored procedure must have the database privileges that the stored procedure requires. Users of the client applications that call the stored procedure do not need such privileges. This can reduce the number of users who require privileges.

5. Reduced development cost and increased reliability

In a database application environment, many tasks are repeated. Repeated tasks might include returning a fixed set of data, or performing the same set of multiple requests to a database. By reusing one common procedure, a stored procedure can provide a highly efficient way to address these recurrent situations.

6. Centralized security, administration, and maintenance for common routines

By managing shared logic in one place at the server, you can simplify security, administration, and maintenance . Client applications can call stored procedures that run SQL queries with little or no additional processing.

DECODE Function vs CASE Statement in Oracle

Decode Function and Case Statement in Oracle: Decode Function and Case Statement are used to transform data values at retrieval time. DECODE and CASE are both analogous to the "IF THEN ELSE" conditional statement.

History of DECODE and CASE:

Before version 8.1, the DECODE was the only thing providing IF-THEN-ELSE functionality in Oracle SQL. Because DECODE can only compare discrete values (not ranges), continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1, Oracle introduced the searched CASE statement, which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting). In version 9.0, Oracle introduced the simple CASE statement, that reduces some of the verbosity of the CASE statement, but reduces its power to that of DECODE.

Decode Function and Case Statement Example: 

Example with DECODE function

Say we have a column named REGION, with values of N, S, W and E. When we run SQL queries, we want to transform these values into North, South, East and West. Here is how we do this with the decode function:

select
decode (
region,
‘N’,’North’,
‘S’,’South’,
‘E’,’East’,
‘W’,’West’,
‘UNKNOWN’
)
from
customer;

Note that Oracle decode starts by specifying the column name, followed by set of matched-pairs of transformation values. At the end of the decode statement we find a default value. The default value tells decode what to display if a column values is not in the paired list.

Example with CASE statement

select
case
region
when ‘N’ then ’North’
when ‘S’ then ’South’
when ‘E’ then ’East’,
when ‘W’ then ’West’
else ‘UNKNOWN’
end
from
customer;

Difference between DECODE and CASE:

Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot. Differences between them are listed below:

1. DECODE can work with only scaler values but CASE can work with logical oprators, predicates and searchable subqueries.
2. CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.CASE can be used as parameter of a function/procedure.
3. CASE expects datatype consistency, DECODE does not.
4. CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
5. CASE executes faster in the optimizer than does DECODE.
6. CASE is a statement while DECODE is a fucntion.

Oracle Streams: An Overview

Oracle Streams enables information sharing. Each unit of shared information is called a message. The stream can propagate information within a database or from one database to another. Oracle Streams can be set up in homogeneous (all Oracle databases) or heterogeneous (non-Oracle and Oracle databases) environments.

Oracle Streams Information Flow

The database changes (DDL and DML) are captured at the source; those are then staged and propagated to one or more destination databases to be applied there.

Capturing ---> Staging ----> Propagating ---> Consuming

Capturing a Message:

Oracle Streams provides two ways to capture database changes implicitly: capture processes and synchronous captures.

Capture Process (Implicit Capture)

A capture process can capture DML changes made to tables, schemas, or an entire database, as well as DDL changes. Database changes are recorded in the redo log for the database. A capture process captures changes from the redo log and formats each captured change into a message called a logical change record (LCR - A message with a specific format that describes a database change). The messages captured by a capture process are called captured LCRs. A capture process can capture changes locally at the source database, or it can capture changes remotely at a downstream database. Capture Processes always caputre change from REDO LOGS.

Synchronous Process (Implicit Capture)

A synchronous capture can capture DML changes made to tables. Rules determine which changes are captured by a capture process or synchronous capture. A synchronous capture uses an internal mechanism to capture changes and format each captured change into an LCR. The messages captured by a synchronous capture are called persistent LCRs. A synchronous capture can only capture changes locally at the source database.

Explicit Capture:

Users and applications can also enqueue messages manually. These messages can be LCRs, or they can be messages of a user-defined type called user messages. When users and applications enqueue messages manually, it is referred to as explicit capture.

Staging a Message

Messages are stored (or staged) in a queue. These messages can be logical change records (LCRs) or user messages. Capture processes and synchronous captures enqueue messages into an ANYDATA queue, which can stage messages of different types. Users and applications can enqueue messages into an ANYDATA queue or into a TYPED queue. A TYPED queue can stage messages of one specific type only.

Propagating a Message

Oracle Streams propagations can propagate messages from one queue to another. These queues can be in the same database or in different databases.

Oracle Streams enables you to configure an environment in which changes are shared through directed networks. In a directed network, propagated messages pass through one or more intermediate databases before arriving at a destination database where they are consumed. The messages might or might not be consumed at an intermediate database in addition to the destination database.

Consuming a Message

A message is consumed when it is dequeued from a queue. An apply process can dequeue messages implicitly. A user, application, or messaging client can dequeue messages explicitly. The database where messages are consumed is called the destination database. In some configurations, the source database and the destination database can be the same.

Message Types:

Raw Bytes
Oracle Objects
XML

Network Configuration Files in Oracle

Oracle uses three files (tnsnames.ora, listener.ora and sqlnet.ora) for network configuration. These are explained below:

1. tnsnames.ora file in oracle

TNS stands for Transparent Network Substrate. The "tnsnames.ora" file contains client side network configuration parameters. tnsnames.ora files contains the information which is used by the system to connect to oracle database.

Location of tnsnames.ora in oracle: By default, tnsnames.ora is located in the $ORACLE_HOME/network/admin directory on UNIX operating systems and in the ORACLE_HOME\network\admin directory on Windows operating systems.

Format / Syntax of tnsnames.ora:

net_service_name=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = yourHostIPAddress)(PORT = portNumber))
    )
    (CONNECT_DATA =
      (SID = databaseName)
    )
  )

Explanation: Syntax contains:

PROTOCOL: Here TCP is used.
HOST IP ADDRESS: Where oracle is installed
PORTNO: Port number of server where oracle is installed
SID: SID stands for System Identifier. SID is the name of database.

Failover and Load-Balancing in tnsnames.ora

Consider the following example

net_service_name=
 (DESCRIPTION=
  (ADDRESS_LIST=
   (LOAD_BALANCE=on)
   (FAILOVER=off)
   (ADDRESS=(PROTOCOL = TCP)(HOST = yourHostIPAddress1)(PORT = portNumber1))
   (ADDRESS=(PROTOCOL = TCP)(HOST = yourHostIPAddress2)(PORT = portNumber2)))
  (ADDRESS_LIST=
   (LOAD_BALANCE=off)
   (FAILOVER=on)
   (ADDRESS=(PROTOCOL = TCP)(HOST = yourHostIPAddress3)(PORT = portNumber3))
   (ADDRESS=(PROTOCOL = TCP)(HOST = yourHostIPAddress4)(PORT = portNumber4)))
  (CONNECT_DATA=
   (SID = databaseName)))

In the above example, we specify the list of addresses which will take over another address in case of failure or overload.

Note: Typically you could have two tnsnames.ora files in the system, one that is set for the entire system and is called the system tnsnames.ora file, and a second file that is used by each user locally so that he can override the definitions dictated by the system tnsnames.ora file.

tnsping: tnsping is used to ping the tnsnames.ora file. If there is no error in tsnnames.ora, it will ping it otherwise error will be displayed.

Format of tnsping command: tsnping hostIPAddress

2. listener.ora file in oracle

The listerner.ora file contains server side network configuration parameters. It is found in the $ORACLE_HOME/network/admin" directory on the server.

Example:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = port))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = databaseName.WORLD)
      (ORACLE_HOME = /u01/app/oracle/product/9.2.0)
      (SID_NAME = databaseName)
    )
  )

After the "listener.ora" file is amended the listener should be restarted or reloaded to allow the new configuation to take effect.

C:> lsnrctl help
C:> lsnrctl status
C:> lsnrctl stop
C:> lsnrctl start
C:> lsnrctl reload

3. sqlnet.ora file in oracle: The "sqlnet.ora" file contains client side network configuration parameters. It is present in the "$ORACLE_HOME/network/admin" It contains following settings:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

Above setting is necessary on Windows if OS authentication is required.

NAMES.DIRECTORY_PATH= (TNSNAMES, ,ONAMES, HOSTNAME)

A list of naming adaptors to be used when resolving a name. These will be used in the order listed. TNSNAMES = tnsnames.ora file, ONAMES = Oracle Names, HOSTNAME = use the hostname

SQL Replay: A new feature of Oracle 11g

SQL Replay is a new feature introduced in Oracle 11g. SQL Replay features is used to capture the commands executed in a database and then replay them elsewhere. You can use the capture and replay features to perform diagnostic operations or to test applications under production conditions.

Steps to create SQL Replay:

Step 1: Creating a Workload Directory

The log file for the SQL workload will be written to a directory on the source server. To specify the physical location for that directory, you must create a directory object within the database.

Use the create directory command:

create directory workload_dir as '/u01/workload'

Step 2: Starting the Capture of Source Database

To start a capture, execute the START_CAPTURE procedure of  DBMS_WORKLOAD_CAPTURE package.

BEGIN
  DBMS_WORKLOAD_CAPTURE.START_CAPTURE
        (name => 'practice_capture',
          dir => 'workload_dir',
     default_action => 'EXCLUDE');
END;
/

Note:
1. The name parameter is the name you assign to the workload capture.
2. dir is the name of the directory object you created to store the workload-capture files.

Stopping the Capture:

If you have not specified a duration, you must stop the capture manually. To stop the capture, execute the FINISH_CAPTURE procedure, as shown here:

BEGIN
  DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE ();
END;
/

Step 3: Processing the Workload Logs:

Workload Logs are transformed into Replay Files and metadata is created. It can be done by PROCESS_CAPTURE procedure of DBMS_WORKLOAD_REPLAY package.

BEGIN
  DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE
   (capture_dir => 'workload_dir');
END;
/

The capture_dir variable value must be the name of a directory object within the database that points to the physical directory where the workload logs are stored.

Step 4: Replaying the Workload on Target Database

To start the replay, use the START_REPLAY procedure of DBMS_WORKLOAD_REPLAY package, as shown in the following listing:

BEGIN
  DBMS_WORKLOAD_REPLAY.START_REPLAY ();
END;
/

To stop the replay, use the CANCEL_REPLAY procedure, as shown in the following listing:

BEGIN
  DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();
END;
/

Precautions while Replaying SQL

1. Before starting the capture on the source database, you should make sure there are no active transactions within the database. Best practice is to restart the database.

2. For best results during the replay, you should alter / reset the system time on the target system.

3. The target database should run the same version of Oracle as the source system.

4. Target database should be separate from the production database and should be as isolated as possible from the external structures accessed by the production database.
Note: Replay is best suited for uses in which the target system is a testing environment, rather than generating the replay in a testing environment and then executing it in a production database

Partitioned Tables: Types and Advantages

As the number of rows in your tables grows, the management and performance impacts will increase. Backups will take longer, recoveries will take longer, and queries that span an entire table will take longer. You can mitigate the administrative and performance issues for large tables by separating the rows of a single table into multiple parts.

Advantages of Partitioned Tables

1. The performance of queries against the tables may improve because Oracle may have to search only one partition (one part of the table) instead of the entire table to resolve a query.

2. The table may be easier to manage. Because the partitioned table's data is stored in multiple parts, it may be easier to load and delete data in the partitions than in the large table.

3. Backup and recovery operations may perform better. Because the partitions are smaller than the partitioned table, you may have more options for backing up and recovering the partitions than you would have for a single large table.

Types of Partition

1. Range Partition: Divides the table according to the specified range.

Example: Create a Table EMP

create table EMP (EmpID  VARCHAR2(32) primary key, EmpName  VARCHAR2(100));

Lets make partition of this table:

create table EMP (EmpID  VARCHAR2(32) primary key, EmpName  VARCHAR2(100))

partition by range (EmpName)

(partition PART1 values less than ('K') tablespace PART1_TS,
partition PART2 values less than (MAXVALUE) tablespace PART2_TS);

Explanation: The EMP table will be partitioned based on the values in the EmpName column:

partition by range (EmpName)

For any EmpName values less than ‘K’, the records will be stored in the partition named PART1. The PART1 partition will be stored in the PART1_TS tablespace. Any other EmpName will be stored in the PART2 partition.

Note that in the PART2 partition definition, the range clause is

partition PART2 values less than (MAXVALUE)

You do not need to specify a maximum value for the last partition; the maxvalue keyword tells Oracle to use the partition to store any data that could not be stored in the earlier partitions.

2. Hash Partition: A hash partition determines the physical placement of data by performing a hash function on the values of the partition key. In range partitioning, consecutive values of the partition key are usually stored in the same partition. In hash partitioning, consecutive values of the partition key are not generally stored in the same partition. Hash partitioning distributes a set of records over a greater set of partitions than range partitioning does, potentially decreasing the likelihood for I/O contention.

3. List Partition: In list partitioning, you tell Oracle all the possible values and designate the partitions into which the corresponding rows should be inserted.

4. SubPartitions: You can create subpartitions—that is, partitions of partitions. You can use subpartitions to combine all types of partitions: range partitions, list partitions, and hash partitions. For example, you can use hash partitions in combination with range partitions, creating hash partitions of the range partitions. For very large tables, this composite partitioning may be an effective way of separating the data into manageable and tunable divisions

Cautions while dropping a tablespace

DROP TABLESPACE drops the tablespace from database. But, there are few things which you should take care while firing this statement.

1. DROP TABLESPACE myTablespace;

- drops the tablespace

Cautions:

A) You cannot drop the SYSTEM tablespace.

B) You can drop the SYSAUX tablespace only if you have the SYSDBA system privilege and you have started the database in MIGRATE mode.

C) You cannot use this statement to drop a tablespace group. However, if tablespace is the only tablespace in a tablespace group, then Oracle Database removes the tablespace group from the data dictionary as well.

D) When you drop a tablespace, Oracle Database does not place it in the recycle bin means your data is not recoverable. Therefore, make sure that all data contained in a tablespace to be dropped will not be required in the future.

E) Also, immediately before and after dropping a tablespace from a database, back up the database completely. This is strongly recommended so that you can recover the database if you mistakenly drop a tablespace, or if the database experiences a problem in the future after the tablespace has been dropped.

2. DROP TABLESPACE myTablespace

    INCLUDING CONTENTS

    CASCADE CONSTRAINTS;

-drops the myTablespace tablespace and drops all referential integrity constraints that refer to primary and unique keys inside myTablespace

Cautions:

1. For partitioned tables, DROP TABLESPACE will fail even if you specify INCLUDING CONTENTS, if the tablespace contains some, but not all. If all the partitions of a partitioned table reside in tablespace, then DROP TABLESPACE ... INCLUDING CONTENTS will drop tablespace.

3. DROP TABLESPACE myTablespace

   INCLUDING CONTENTS AND DATAFILES;

-drops the myTablespace tablespace and deletes all associated operating system datafiles.

Suggestion: You can drop a tablespace regardless of whether it is online or offline. But it is suggested that you take the tablespace offline before dropping it to ensure that no SQL statements in currently running transactions access any of the objects in the tablespace.

Database FLASHBACK mode: Overview

Database FLASHBACK mode: Overview

Oracle flashback database is an extension of the "rollback" functionality, allowing the DBA to flashback a table to a specific date in history.  With Oracle flashback, the length of the flashback recovery is determined by the storage dedicated to Oracle UNDO and the settings for flashback database parameters.

Flashback Database Command

Oracle flashback database is implemented via the flashback database command.  Flashback database allows you to quickly bring your database to a prior point in time by undoing all of the changes that have taken place since that time. The Oracle database flashback process is fast, because you do not need to restore the backups.

How to check whether FLASHBACK is ON/OFF on your database?

SELECT FLASHBACK_ON FROM V$DATABASE;

How to ON flashback of your database?

ALTER DATABASE FLASHBACK ON;

How to OFF flashback of your database?

ALTER DATABASE FLASHBACK OFF;

Example

Enable database to ON the flashback mode and open the database with the following statements:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

With your database open for at least a day, you can flash back the database one day with the following statements:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1;

Turning ON/OFF Flashback on a particular Namespace:

ALTER TABLESPACE <tablespace_name> FLASHBACK ON;
ALTER TABLESPACE <tablespace_name> FLASHBACK OFF;