New Features Of Oracle9i Database

New Features of Oracle9i Database Last updated on 28th May, 2002 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]...

1

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

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

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.

4

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.

5

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

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.

ANSI Join

7

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

8

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)

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

OLDJOB

JOB

9

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

10

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

11

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

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

13

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

14