Srikanth Technologies

New Features of Oracle9i

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

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

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]

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.

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

    -- 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

   -- use disrate


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.

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

   [ELSE  elseresult]

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.

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

    -- get quantity purchased into QTY variable

       when qty > 10 then 20
       when qty > 6  then 15
       when qty > 3  then 10
       else 0

    -- use discount rate here


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.


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.

WHEN credit_limit >=50000 THEN
INTO special_customers VALUES(custid,name,credit_limit)
INTO customers
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;

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.

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.

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).

SELECT rate FROM products WHERE prodid = 102;

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

-- retrieve data into a cursor
OPEN c FOR 'SELECT * FROM employees WHERE ...';

-- disable flashback query. 

-- use data in the cursor
   FETCH ...;
   INSERT ...;

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.


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.


Compares given two values and if they are same then returns null otherwise returns first expression.
  NULLIF(expr1, expr2)

The 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;

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

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


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;



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;


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;



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:


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.



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) 
         records delimited by newline 
         fields terminated by ',' ( empno, ename) 
       LOCATION ('employees.dat') 

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

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