The following are the new features of Oracle9i Database.

I will add a few new features at a

time. So please keep on visiting this place to find out what's new in Oracle9i Database.

 Suite and not single coat

 CASE Expression
 List Partitioning
 Index Skip Scan
 MERGE statement
 Multitable Insert
 Flashback Query
 ANSI Join
 New Functions
 External Table
 Table Function
 Object-relational Extensions

Suite and not single coat

First of all, Oracle9i is more than a single database management system. It is a complete
suite. It is consisting of the following:

 Oracle9iAS ( Application Server)

 Oracle9iDB ( Database)
 Oracle9iDS ( Developer Suite)

I have to say it again and again because most of the people take Oracle9i as synonymous
with the next version of Oracle8i. Loosely speaking yes, but strictly speaking no.

Oracle9iDB is the successor of Oracle8i.

Oracle Corporation released Oracle9iAS in year 2000 itself and released Oracle9i Database
in year 2001.

To get more information about the history of Oracle, see Oracle History article.

CASE Expression

This is one of the features that PL/SQL lacked till now. We used DECODE to substitute this.
But now Oracle provides a full-blown case structure.

The following is the syntax of CASE expression.

CASE selector
WHEN expression1 THEN result1
[WHEN expression2 THEN result2]...

[ELSE elseresult]
END;
The following example will give you some idea how to use it. Programmer with knowledge of
SWITCH statement of C should not jump to any conclusion that it resembles SWITCH. Yes, it
is in concept but in practice these are two different ways.
The following example copies discount percentage base on grade of the product.

declare
grade char(1);
disrate number(2);

begin
-- get grade into variable GRADE

disrate :=
case grade
when 'A' then 10
when 'B' then 15
when 'c' then 12
when 'd' then 20
else 0
end;

-- use disrate

end;

The point that I want new comer to understand is that CASE expression returns a value. It
checks whether the given selector is matching with first expression. If so, it return the first
value, otherwise it checks second and so on until it finds a match or until it finds ELSE or end
of the CASE expression.

The above case is one where a single value is compared with multiple values and an action
is taken based on the value of the selector.

There is another syntax for CASE expression. Here it is.

CASE
WHEN condition THEN result1
[WHEN condition THEN result2]...

[ELSE elseresult]
END;

The above version provides more flexibility then the earlier one. Of course, these two cater
to different needs.

The second one is appropriate when a value is to be returned based on a set of conditions.
The following example finds out discount rate based on the quantity purchased by the
customer.

declare
qty number(2);
disrate number(2);

begin
-- get quantity purchased into QTY variable
 disrate:=
case
when qty > 10 then 20
when qty > 6 then 15
when qty > 3 then 10
else 0
end;

-- use discount rate here

end;

The above CASE statement returns discount rate based on the condition given after WHEN.
It first checks whether QTY > 10. If condition is true then it returns 20 otherwise it proceeds
to next condition and so on.

The ability to given a full-fledged condition gives a lot of flexibility to CASE statement. It is a
perfect alternative to multiple if statement.

Not to mention in both the versions the conditions are checked sequentially and the process
stops with WHEN where the condition is true.

List Partitioning

Oracle started providing the facility to divide a large table into partitions right from Oracle8.
But the paritioning is primarly based on the range. That means, Oracle determines into
which partition a row is placed based on the range to which the value in the column belongs.

The following example shows how to create a partition based on the rate of the product.

create table sales_history

( pid number(5),
qty number(3),
rate number(5),
dp date,
state char(2)
)
partition by range(rate)
( partition low values less than (1000),
partition normal values less than(5000),
partition high values less than (maxvalue)
);
The above command creates SALES_HISTORY table and then places a row into either of the
three partitions based on the value of RATE column.

Now, starting from Oracle9i database, Oracle supports list partition in addition to range
partition. In list partitioning, a table is divided into partitions based on a list of values. For
example the following command creates SALES_HISTORY table with four partitions based on
the values of STATE column.

create table sales_history

( pid number(5),
qty number(3),
 rate number(5),
dp date,
state char(2)
)
partition by list (state)
( partition south values('AP','TN','KE'),
partition north values('DE','JK','PU'),
partition west values('MA','PU'),
partition east values('WB','SI')
);

If a rows contains the value AP or TN or KE in STATE column then the row is placed in SOUTH
partition. Similarly if the value of STATE is DE or JK or PU then the row is placed in NORTH
partition and so on.

Though the overall concept of partition and its benefits are the same between range and
partitions, the possible of dividing table into partitions based on discrete values of the
column is new in Oracle9i database.

Index Skip Scan

Until Oracle8i, a composite index (an index that is based on multiple columns) is used only
when either all columns in the index are referred in the query or at least leading columns
are referred.

But in Oracle9i, Oracle uses index even when leading columns are not used.A composite
index can be used even when leading column(s) are not used in the query through a
technique called as Index Skip Scan.

During index skip scan, index is searched for each distinct value and then for each distinct
value the index is searched for target values (values in the remaining column(s)). As the
result the index scan skips leading values and starts searching for target values even when
they do not belong to leading columns.

For example assume we have the following SALES table:

create table sales

( prodid number(5),
custid number(5),
qty number(2),
.
.
.
);

Now, if we create an index on PRODID and CUSTID as follows:

create index sales_prodid_custid_idx on sales(prodid, custid);
Then the following query will use index as the leading column is referred.
select * from sales where prodid = 100;
But the following query will not use index in Oracle8i.
select * from sales where custid = 1002;
However, in Oracle9i, the same above query will use index by using Index Skip Scan
technique.
MERGE Statement
This new statement is used to combine Insert and Update commands into a single
command. This is also called as Upsert functionality.

This is one of the new features provided for ETL (Extraction, Transformation, and Loading)
applications.

This command is used where we have to insert row of one table into another table if the new
row is not available in the old table. If new row is already available in the old table then the
row in the old table is updated.

The following example shows how to use MERGE statement to insert row of NEWPRODUCTS
table into PRODUCTS table if PRODID is not found in the PRODUCTS table. If PRODID of
NEWPRODUCTS is found in PRODUCTS table then the RATE column is updated with RATE
column of NEWPRODUCTS.

MERGE INTO PRODUCTS P

USING NEWPRODUCTS NP
ON (P.PRODID = NP.PRODID)
WHEN MATCHED THEN
UPDATE
SET RATE = NP.RATE
WHEN NOT MATCHED THEN
INSERT (P.PRODID, P.NAME,P.RATE)
VALUES (NP.PRODID,NP.NAME,NP.RATE);

The above MERGE command reads data from NEWPRODUCTS only for once. The same
operations in Oracle8i needs two different scans of NEWPRODUCTS table - one for INSERT
and another for UPDATE command.

Multitable Insert
This is another feature of ETL (Extraction, Transformation, and Loading). It is used to take
data from one table and insert the data into multiple tables.

Let us say, we have to take data from OLDCUSTOMERS table and insert the data into
CUSTOMERS and SPECIAL_CUSTOMERS tables based on the credit limit of the customer. It
can be done with Oracle8i by using INSERT .. SELECT construct. But we have to give two
commands one command to insert into CUSTOMERS from OLDCUSTOMERS and another to
insert details of customers who have credit limit more than 50000 into SPECIAL_CUSTOMERS
table.

That means OLDCUSTOMERS table is to be scanned for twice.

Multitable insert facility of Oracle9i will allow the source table to be scanned only for once
and insert the data into multiple tables.

The following example will explain how to use the new feature.
INSERT FIRST
WHEN credit_limit >=50000 THEN
INTO special_customers VALUES(custid,name,credit_limit)
INTO customers
ELSE
INTO customers
SELECT * FROM oldcustomers;

The above command takes data from OLDCUSTOMERS table and first inserts a row into
SPECIAL_CUSTOMERS table if column CREDIT_LIMIT is more than 50000 and then it inserts
the same row into CUSTOMERS table also.

If the condition given in WHEN clause is not satisfied then it will execute the ELSE part,
where the row taken from OLDCUSTOMERS is inserted into CUSTOMERS table.

That means, if condition is satisfied then it will insert the source row into two tables -
SPECIAL_CUSTOMERS and CUSTOMERS, otherwise it will insert row into only one table -
CUSTOMERS.

Flashback Query

This is the most interesting of all the new features. It allows you to take data as it was at a
point in time in the past. It is mainly used to allow users to recover from their mistakes. For
example, if a user deleted rows accidentally and committed the change then he cannot
rollback the change. In Oracle8i, it need DBA(Database Administrator) to perform incomplete
recovery to recover the rows that were deleted.

Incomplete is an operation performed by DBA and for this he has to shutdown the database
and copy old database from backup and take databack upto the point of failuer to recover
the lost data. But, in this process the changes made to database since deletion are lost.
Consider the following example.

User Bob deleted all rows from PRODUCTS table by using the following seqence of
commands at 10:10 A.M.

delete from products;

commit;

Then he realized that 11:00 AM that he deleted rows from wrong table. But he cannot
rollback the deletion since it was already committed. The only option left with BOb is to
contact DBA.

DBA will copy the data from previous backup (that was taken last night for example) and
then applies all changes made upto 10:09 AM using Redo log file . This process will keep
rows in PRODUCTS table in the database. But, as the result we lost all changes made by all
users from 10:10 AM to 11:00 AM. And these changes are to be made manually.

The following are the disadvantages with the above process.

 You lose changes made since 10:10 Am.

 Database should be shutdown making it unavailable to users
The above problem can be solved by user himself without consulting DBA with the help of
flashback query.

The following are the steps to be taken for Flashback query.

 Find out the point in time in the past to which you have to go
 Use ENABLE_AT_TIME procedure of DBMS_FLASHBACK package to enable flashback
query.
 Then execute the requried SELECT command to retrieve the data.
 Disable flashback query using DISABLE method of DBMS_FALSHBACK package.

The following example will demonstrate how you can get the rate of the product with id 102
on 1st March,2002 (inspite of serveral changes to rate since then).

EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME ('01-Mar-2002');

SELECT rate FROM products WHERE prodid = 102;
EXECUTE DBMS_FLASHBACK.DISABLE;

First line takes database to 01-mar-2002. Then SELECT command retrieves RATE from
PRODUCTS table where PRODID is 102. And finally we disable flashback query so that
current data is used.

The following is another example where we take the data from the table into cursor and
then uses cursor to insert rows into another table.

-- enable flashback query

EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME('01-mar-2002');

-- retrieve data into a cursor

OPEN c FOR 'SELECT * FROM employees WHERE ...';

-- disable flashback query.

DBMS_FLASHBACK.DISABLE;

-- use data in the cursor

LOOP
FETCH ...;
EXIT WHEN c%NOTFOUND;
INSERT ...;
END LOOP;

Note: You must disable flashback query before you execute any DML commands as DML
commands cannot be executed while flashback query is enabled.

Before users can use flashback query, DBA has to configure the database for flashback
query. It is beyond the scope of this article to get into those details. Please read
Administrator's Guide for more details and system configuration.

Flashback query is going to make system outages less as users can themself recover from
human errors.
What do we need to use Flashback query?
The following steps are to be performed by DBA to enable a user to use flashback query.

 Set the UNDO_RETENTION initialization parameter to a value that represents how far
in the past you might want to query. This is generally set to 10800, which is 3 hours.
 Set the initialization parameter UNDO_MANAGEMENT=AUTO. It is also set by default.
 Grant EXECUTE privilege on the DBMS_FLASHBACK package to users who want to use
it.
For this login into Oracle as sysdba as follows:
 sql>connect sys as sysdba
 Enter password: .....

Then execute the following command to grant ExECUTE privilege to user SRI

Grant execute on dbms_flashback to sri;

ANSI Join

Oracle9i provided standard ANSI join syntax. This is different from the syntax that we use in
current version of Oracle to join tables. Oracle9i has also provided full support for outer-join
and with new syntax of ANSI join.

ANSI Join added some new keywords to Oracle. The following is an example of join in
Oracle8i and then in Oracle9i using ANSI join.

The following command joins STUDENTS table with COURSES table using Oracle8i join
syntax.

SELECT rollno,sname, coursename, duration

from students s, courses c
where s.coursecode = c.coursecode;

The following command joins STUDENTS table with COURSES table using ANSI join syntax.

SELECT rollno,sname,coursename,duration
FROM students s INNER JOIN courses c
ON c.coursecode = s.coursecode;

The biggest advantages with this new syntax is it doesn't allow you to forget to give join
conidition. As you know that is one of the common mistakes among beginners of Oracle. If
you ommit join condition then it results in Cartesian Product.

ANSI join syntax makes condition after ON clause mandatory.

USING clause
If you are dealing with equi-join then it is possible to further simplify the join syntax with
USING clause as follows:
 SELECT rollno,sname,coursename,duration
FROM students s INNER JOIN courses c
USING (coursecode);

USING clause in the above command specifies that tables STUDENTS and COURSES are to
be joined when they have the common values in the column COURSECODE.

While you are dealing with ON clause COURSECODE column from both STUDENTS and
COURSES is available. So you can select the column as follows:

SELECT rollno,sname,c.coursecode , coursename,duration

FROM students s INNER JOIN courses c
on c.coursecode = s.coursecode;
But if we are using USING clause then only one copy of the columns that are used in USING
caluse will be available. That means you do not get two versions of the column in the query.
So if you use USING clause for the above query referring to COURSECODE as
C.COURSECODE is invalid as there is only one copy of COURSECODE column in the query.
The following query is a rewrite of the previous query.
SELECT rollno,sname,coursecode , coursename,duration
FROM students s INNER JOIN courses c
USING (coursecode);

Outer Join
ANSI join also added support for outer join.

Assume that we want to display the details of courses that do not have any students. The
following query uses Oracle's proprietry syntax for outer join.

SELECT c.coursecode, coursename, duration, rollno,sname

FROM courses c, students s
where c.coursecode = s.coursecode(+);

The following is the new ANSI outer-join syntax:

SELECT rollno,sname,coursename,duration
FROM courses c LEFT OUTER JOIN students s
ON c.coursecode = s.coursecode;

The same query can be written using RIGHT OUTER JOIN also if you change the order in
which tables are used. LEFT OUTER JOIN and RIGHT OUTER JOIN are funtionally same except
the order in which tables are given. For LEFT OUTER JOIN parent table is given on left and for
RIGHT OUTER JOIN child table is given on the left.

Outer-join using RIGHT OUTER JOIN.

SELECT rollno,sname,coursename,duration
FROM students s RIGHT OUTER JOIN courses c
ON c.coursecode = s.coursecode;
It may take some time to get used to this ANSI join syntaxes, but it is worth as it will your
code more protable. And moreover once you get used to this new syntax, you will find it
more easier and less prone to errors.

New Functions
The following are new functions that are added in Oracle9i.

NULLIF
Compares given two values and if they are same then returns null otherwise returns first
expression.
NULLIF(expr1, expr2)
he following command displays employee number, name , old job if it is different from
current job and current job.

SQL> select empno, ename, nullif(oldjob,job) oldjob , job from emp2;

EMPNO ENAME OLDJOB JOB

---------- ---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES CLERK MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT PROGRAMMER ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK

EMPNO ENAME OLDJOB JOB

---------- ---------- ---------- ---------
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK

BIN_TO_NUM
Convert the given bit pattern to a number.

The following example returns 15 as binary 1111 is equivalent to 15.

SQL> select bin_to_num(1,1,1,1) from dual;

BIN_TO_NUM(1,1,1,1)
-------------------
15
COALESCE
Returns the first not null value in the list of values. At least one expression out of the given
expressions must not be null. The function is extended version of NVL function.

COALESCE(expr1, expr2,... , exprn)

The following query returns the selling price of the product. If dicount is not null then
remove discount from the price, otherwise if any offer price is available give at the offer
price, otherwise sell the product at the original price.

select prodid,name, coalesce( price - discount, offerprice, price) "Selling

Price"
from products;

EXTRACT
Extracts and returns the value of the specified datetime field from a datatime value.
EXTRACT ( datatimefield FROM datetime)
The following example returns year from the current date.

SQL> select extract( year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
2002

TREAT
Changes the declared type of an expression. This is used with object types to change the
type of an object to its subtype.
TREAT ( expr1 AS type)
The following example converts an object of PERSON_TYPE to an object of STUDENT_TYPE
and takes course name. It is assumed that table PERSONS contains a collection of objects of
type PERSON_TYPE. But object that are stored in the table are objects of STUDENT_TYPE.
SELECT name, TREAT(VALUE(p) AS student_type).course course
FROM persons p;

External Table
External table is a table whose data is not stored in the database and stored outside
database in the form of files. If data is stored in the form of a DAT file then Oracle can
access that file using external table feature of Oracle9i.

External table provides read-only access to external data.

External tables are defined using CREATE TABLE command and with ORGANIZATION
EXTERNAL option.

The data accessed by external table can be in any format for which an access driver is
provided.
Oracle exposes the data in the external table as if it were data residing in a regular database
table. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and no
indexes can be created.

Assume that there is a file called EMPLOYEES.DAT in C:\DATA directory as follows:

7369,SMITH
7499,ALLEN
7521,WARD
7566,JONES
7654,MARTIN
7698,BLAKE
7782,CLARK

Before external table is created, we have to create a directory that is an alias to the physical
directory and then grant READ permission to the required users.

CREATE OR REPLACE DIRECTORY dat_dir AS 'c:\data';

GRANT READ ON DIRECTORY dat_dir TO scott;
The following SQL statement is used to create external table called EMPLOYEES to access
that data in EMPLOYEES.DAT file.
CREATE TABLE employees
(empno NUMBER(4),
ename VARCHAR2(20)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY dat_dir
ACCESS PARAMETERS
(
records delimited by newline
fields terminated by ',' ( empno, ename)
)
LOCATION ('employees.dat')
)
REJECT LIMIT UNLIMITED;

TYPE ORACLE LOADER is the driver to be used to process the external file. DEFAULT
DIRECTORY option specifies the directory from where the file specified in LOCATION clause
should be taken.

ACCESS PARAMETERS specifies that records in the file are separated by newline(one record
per line), and fields are separated by comma.

REJECT LIMIT UNLIMITED specifies that any number of records in the source file can be
rejected if they do not comply with the required structure.

And finally retrieve data from EXPLOYEES table as follows:

select * from empxt;

Table Function
A table function is a function that produces a collection of rows that can be queried just like
a table.

The collection of rows is either a nested table or a VARRAY.

It eliminates the need to store intermediate data into temporary table as the data is directly
passed to next stage.

The following is an example of table function that returns the list of authors of the given
book. The names of authors are stored in AUTHORS column of BOOKS table. Author names
are separated by comma (,). Table function returns the list of author names in the form of a
table, which can be used just like a table in SELECT.

First let us create required types and table.

create or replace type authors_table as table of varchar2(30);

The above command creates AUTHORS_TABLE, which is a collection of strings.

create table books

( title varchar2(30),
authors varchar2(500)
);

BOOKS table contains title and list of authors of the book. Author names are separated by
comma.

The following is sample data of BOOKS table.

insert into books values ('uml user guide','grady booch, james runbaugh, ivar
jacobson');
insert into books values ('core java','horstmann,cornell');
insert into books values ('oracle8i comp. ref.','kevin loney, george koch');

The following table function takes title and returns the names of author in the form of
AUTHORS_TABLE.

create or replace function getauthors(p_title varchar2) return authors_table

is
atab authors_table;
al varchar2(500);
p integer;
a varchar2(30);
begin
atab := authors_table();
select authors into al
from books where title = p_title;
p := instr(al,',');
while p <> 0
loop
a := substr(al,1,p -1);
 atab.extend;
atab(atab.last) := a;
al := substr( al, p+1);
p := instr(al,',');
end loop;
atab.extend;
atab( atab.last) := al;
return atab;
end;
/

Once function is created then it can be called using TABLE keyword as following in SELECT
command.

select b.title, a.* from books b,

table(getauthors(b.title)) a;

TITLE COLUMN_VALUE
------------------------------ ------------------------------
UML User Guide Grady Booch
UML User Guide James Runbaugh
UML User Guide Ivar Jacobson
Core Java Horstmann
Core Java Cornell
Oracle8i Comp. Ref. Kevin Loney
Oracle8i Comp. Ref. George Koch