"data not found" error in PLSQL

When we working with PLSQL statements some common errors will raise.In this one of the common Error in PLSQL is " data not found" .It will arise when there is no particular field in table. Here i will shown an example how to prevent the exception when a record is not found in PLSQL code.
Example:

select Id,Name, Address from Details where Address = City.Address;

Solution:
The above example give an error because of the record"Address" is not found.So for this we have to get the count of records then using the condition we will execute the PL/SQL code

select count(1) into testcount from Details where Address=city.Address;

if testcount>0 then 
select Id,Name, Address
from Details
where Address = City.Address;
end if;

R3MQ7XNAWTMR

Rows into Columns in Oracle

The oracle database 11g has some new features such as PIVOT and UNPIVOT clauses,By using these clauses we can rotate the rows into column in output from a query.PIVOT and UNPIVOT are useful to see in large amount of data such as trends data over a period of time.Hera Ive shown a simple query to get the income details for three in year

Example:

SELECT *FROM
(SELECT month,id,income FROM all_business WHERE year="2011" and id IN(1,2,3))
PIVOT
(SUM(income) FOR month IN(1 AS JUNE,2 AS JULY,3 AS AUGUST)) ORDER BY id

CREATE TABLE ALL_Income AS
SELECT *FROM
(SELECT month,id,income FROM all_business WHERE year="2011" and id IN(1,2,3))
PIVOT
(SUM(income) FOR month IN(1 AS JUNE,2 AS JULY,3 AS AUGUST)) ORDERBY id


SELECT *FROM ALL_Income
UNPIVOT(income FOR month IN(JUNE,JULY,AUGUST))ORDER BY id

Create B-tree Index in Oracle

CREATE [UNIQUE] INDEX maxamount ON
Sales(amount)
TABLESPACE tab_space;

where
UNIQUE means that the values in the indexed columns must be unique.
index_name is the name of the index.
table_name is a database table.
column_name is the indexed column. You can create an index on multiple columns
(these kind of  index is known as a composite index).
tab_space is the tablespace for the index. If you don’t provide a tablespace, the index
is stored in the user’s default tablespace.

Note:
For performance reasons, you should typically store indexes in a
different tablespace from tables.

Converting DATETIME() to string in Oracle

oracle database Provide some special functions to convert the value from one data type to another data type.To_CHAR() function will be using to convert the DATE TIME() to string .

The syntax for TO_CHAR():

use TO_CHAR(x([,format]) to convert the date time x to string.here i want to get the full name of month ,2 digit day format and 4 digit year

SELECT student_id, TO_CHAR(dob, 'MONTH DD, YYYY')
FROM students;

note:The conversion of string to date time will be get by using TO_DATE () function.

Update or Insert the rows into Table or VIew using Oracle || MERGE Class in Oracle

Oracle database has one class to update or insert the rows into table or view which is "MERGE".
MERGE class can do this kind of operations very easily.Now i will shown a simple example in below

Example:

MERGE  into Employee USING Users
         ON(Employee.name=Users.name)
        WHEN matched  then UPDATE SET Employee.role=Users.role,Employee.Add=Users.Add
        WHEN matched  Then Insert(name,role,add,phno) values (Users.name,Users.role,Users.add,Users.phno)

Difference between Oracle 9i and 10g

9i is based on Internet technology and 10g is grid based one.

In 10g has some extra features are introduced comparing with 9i those are Automated storage management(ASM),
Automatic Database Diagnostic monitor(ADDM),
Automatic workload repository,
Automatic Checkpoint Tunning,
Streams Technology(STREAMS POOL),
Automatic SQL Tunning,Recovery Manager Enhancements(RMAN).
Built-in packages—(DBMS_SCHEDULER, DBMS_CRYPTO, DBMS_MONITOR)
Compile-time warnings
Number data type behaviors
An optimized PL/SQL compiler
Regular expressions
Set operators
Stack tracing errors
Wrapping PL/SQL stored programs

we can rollback after drop in 10g but in 9i can't rollback

10g is web based database management