New Features of Oracle9i

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

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

The following is the syntax of CASE expression.

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

   [ELSE  elseresult]
 END;

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

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

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')
);

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

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

create index sales_prodid_custid_idx on sales(prodid, custid);

select * from sales where prodid = 100;

select * from sales where custid = 1002;

MERGE Statement

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

Multitable Insert

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;

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

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;

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

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;

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?

• 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

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;

ANSI join syntax makes condition after ON clause mandatory.

USING clause

 SELECT rollno,sname,coursename,duration
 FROM students s INNER JOIN courses c
    USING  (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; 

 SELECT rollno,sname,coursecode , coursename,duration
 FROM students s INNER JOIN courses c
 USING (coursecode);

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;

Outer-join using RIGHT OUTER JOIN.

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

New Functions

NULLIF

 
  NULLIF(expr1, expr2)

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

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

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

select  prodid,name, coalesce( price - discount, offerprice, price) "Selling Price"
from  products;

EXTRACT

   EXTRACT ( datatimefield    FROM   datetime)

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

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

TREAT

   TREAT ( expr1  AS type) 

SELECT name, TREAT(VALUE(p) AS student_type).course course
FROM persons p;

External Table

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; 

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

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

create table books
( title varchar2(30),
  authors varchar2(500)
);

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');

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

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