Oracle Database 11g was released on July 11 ,2007. Though they released Linux version first eventually Windows version was also released. In this article, I want to show the new features that Oracle developers would find interesting. You can download Oracle Database 11g by going to www.oracle.com.
REGEXP_COUNT function
PL/SQL Sequence
Continue in PL/SQL
Triggers Firing Order
Mixed Notation
Virtual Columns
Fine Grained Dependency Tracking
Compound Trigger
Other Misselaneous Features
REGEXP_COUNT Function
New function, REGEXP_COUNT takes a string and regular expression and returns the number of times the expression is present in the string.
SQL> select regexp_count('How do you do?','do') from dual;
REGEXP_COUNT('HOWDOYOUDO?','DO')
--------------------------------
2
SQL> select regexp_count('Oracle9i Oracle10g and Oracle11g','[0-9]+') as Count from dual;
COUNT
----------
3
PL/SQL Sequence
Oracle Database 11g has now provided support for Sequence in PL/SQL. Earlier to get a number from a sequence in PL/SQL we had to use SELECT command with DUAL table as follows:
declare
rno number;
begin
select rollno.nextval into rno from dual;
-- use rno which contains next number from sequence
end;
Now, we can refer to a sequence without having to use any SELECT command as follows:
declare
rno number;
begin
rno := rollno.nextval; -- access sequence directly from PL/SQL
-- use rno
end;
Continue Statement
Continue statement is introduced in PL/SQL loops to skip rest of the statements in an iteration and restart the next iteration. Of course its functionality is same as continue statement of C language. Remember EXIT statement is same as break statement of C language.
begin
for i in 1..100
loop
if mod(i,3) = 0 then /* ignore numbers divisible by 3 */
continue;
end if;
-- process other numbers
dbms_output.put_line(i);
end loop;
end;
Trigger Firing Order
When two or more triggers are defined for the same event, in the past it was not possible to define the order in which those triggers are fired when the event occurs. Starting from 11g Oracle allows you to specify that a trigger must be fired only after another trigger for the same event is fired. It is done using FOLLOWS keyword followed by trigger name after which current trigger is to be invoked. Assume we have TEST and TESTLOG tables as follows.
create table test( n varchar(10));
create table testlog (log varchar(50));
Create the following two triggers that are to be fired before INSERT command of TEST table.
create or replace trigger test_trg1 before insert on test
for each row
begin
insert into testlog values ('From test_trig1');
end;
create or replace trigger test_trg2 before insert on test
for each row
follows test_trg1
begin
insert into testlog values ('From test_trig2');
end;
In the above example, both TEST_TRG1 and TEST_TRG2 are associated with the same event (before insert of test table). As we specified at the time of creating TEST_TRG2 that it must be called only after TEST_TRG1 is called using FOLLOWS keyword, it is always invoked after TEST_TRG1. To test the order, insert a row into TEST table and see what rows are placed in TESTLOG table.
SQL> insert into test values(1);
1 row created.
SQL> select * from testlog;
LOG
--------------------------------------------------
From test_trig1
From test_trig2
Mixed Notation
Oracle 11g allows mixed notation for function calls. It was possible to use either positional parameters or named parameters in the previous versions. Now, Oracle supports mixing named and positional notations. Create a procedure that takes three parameter that are optional as follows:
create or replace procedure p1(n1 number:=1, n2 number :=2, n3 number := 3)
is
begin
dbms_output.put_line(n1);
dbms_output.put_line(n2);
dbms_output.put_line(n3);
end;
Now, call this procedure using positional, named and mixed parameter notations as follows and see the output given after the block.
begin
p1(10,20,30); -- positional
p1( n2=> 20, n3 =>30 ); -- named
p1( 10, n3 =>10 ); -- MIXED is new
end;
10
20
30
1
20
30
10
2
10
Virtual Columns
Virtual column is a column whose value is derived from an expression. Oracle doesn't store any data related to virtual column, only expression given at the time of creating virtual column is stored in data dictionary. The following example creates virtual column called DIFFERENCE, which returns the difference between MAX_SALARY and MIN_SALARY columns in JOBS table.
ALTER TABLE JOBS ADD (DIFFERENCE AS ( MAX_SALARY - MIN_SALARY))
Select job_title, difference from jobs;
Fine Grained Dependency Tracking
Prior to Oracle11g, modifying the structure of a table would make dependent views invalid even thought the change to table has nothing to do with view. As we have a table called T1 and view V1 as follows.
create table t1 (n1 number(5), n2 number(5));
create view v1 as select n1, n2 from t1;
In Oracle10g, any change to table T1 would mark view V1 invalid whether or not the change effects view logically. For example the following change to table T1 would make view V1 invalid.
SQL>alter table t1 add( n3 number(5));
SQL>select status from user_objects where object_name = 'V1';
STATUS
-------
INVALID
However, the change made to table T1 has nothing to do with view and logically doesn't effect view.
That is why starting from Oracle11g a change to table that does not effect view logically, will NOT make view invalid. So, if you add a column to table T1 then because of fine grained dependency tracking system applied by Oracle 11g, it doesn't make view invalid.
Compound Trigger
A compound trigger allows different blocks within a trigger to be executed at different timing points. It has a declaration section and a section for each of its timing points. All of these sections can access a common PL/SQL state.
Assume we need to ensure the total amount of increase in prices must not cross 1000 for all products put together in a single UPDATE statement. A compound statement can achieve this by adding the difference between new price and old price to total and checking whether total crosses 1000 at the end of the statement.
To demonstrate compound trigger, create a table and insert some data as follows:
SQL> create table products (name varchar(10), price number(5));
Table created.
SQL> insert into products values('a',1000);
1 row created.
SQL> insert into products values('b',3000);
1 row created.
SQL> insert into products values('c',2300);
1 row created.
SQL> commit;
Now, create a compound trigger to ensure the total increase in all prices is not more than 1000 in a single UPDATE statement.
CREATE OR REPLACE TRIGGER total_price_check
FOR UPDATE ON PRODUCTS COMPOUND TRIGGER
total number(5);
BEFORE STATEMENT IS
BEGIN
total := 0;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
null;
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
total := total + :new.price - :old.price;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
if total > 1000 then
raise_application_error(-20100,'Total increase in price cannot cross 1000');
end if;
END AFTER STATEMENT;
END;
Now make the following changes. First change is accepted as it doesn't increase price by more than 1000 for all products put together. But, second change is not allowed as it causes the total increase to cross 1000.
SQL> update products set price = price + 100;
3 rows updated.
SQL> update products set price = price + 500;
update products set price = price + 500
*
ERROR at line 1:
ORA-20100: Total increase in price cannot cross 1000
ORA-06512: at "HR.TOTAL_PRICE_CHECK", line 18
ORA-04088: error during execution of trigger 'HR.TOTAL_PRICE_CHECK'
Miscellaneous Features
The following are other interesting features added to Oracle Database 11g.
SIMPLE_INTEGER data type will improve performance especially when PL/SQL code is compiled to native code.
SUPER reference can be used to access methods of super type that are overridden in sub type
You can cache results of queries and results of PL/SQL functions
Password has become case sensitive.
The above list of new features by no means is complete. I have written about the features that i personally found interesting and useful. For complete list of more than 400 new features, please read Oracle documentation.
Wednesday, July 29, 2009
Getting Started With Oracle Database 10g
Today I received a mail seeking my help regarding using Oracle Database 10g. That made me feel I should write about how to install and get started with Oracle Database 10g.
Getting Software and Installing It
The first step is obviously to download oracle software from oracle.com. When you try to download Oracle Database 10g express edition, it will ask you to register (if not already registered). Don't worry! Registration process is very simple and just do it.
Download Oracle Database 10g express edition, which is a download of 156MB. With a lot of people using broadband of 256kbps or above, it doesn't take any time to download 156MB.
You downloaded a simple file oraclexe.exe. Just double click on that and follow the instructions. At one point it asks you to provide password for system account. Provide a password and make sure you remember it. Here, I have a suggest. Try to simplify your password. Give you name or something that you can easily remember. After all you are not going to put any confidential data in your database and moreover in many cases only you access the database and no one else.
Unlocking HR account
Until Oracle9i we had an account called SCOTT with password TIGER. Many try to use that to log in but fail. Oracle Database 10g doesn't come with SCOTT account any more. It instead provides HR account HR password.
But, HR account is locked and cannot be used unless you unlock it. So here are the steps to unlock HR account.
Start SQL Plus using Oracle Database 10g Express Edition->Run SQL Command Line.
At SQL> prompt, connect to Oracle using SYSTEM account with password given at the time of installation. Oracle password are NOT case sensitive.
CONNECT SYSTEM/password
Unlock HR account with the following command.
ALTER USER HR ACCOUNT UNLOCK;
> Now, HR account is unlock and ready to be used.
So, try to connect to HR account using the following command.
CONNECT HR/HR
It will prompt you to enter new password and current password is expired. This is because of the fact, HR account's password is expired by default. Don't worry enter new password for HR. To make life easy, you better enter the same HR as password.
That's all you have to do to access HR account and enjoy all the sample table that come with HR account.
If you want to know more about HR account tables their relationships and queries related to those tables, please visit Human Resource Schema Of Oracle10g.
Getting Software and Installing It
The first step is obviously to download oracle software from oracle.com. When you try to download Oracle Database 10g express edition, it will ask you to register (if not already registered). Don't worry! Registration process is very simple and just do it.
Download Oracle Database 10g express edition, which is a download of 156MB. With a lot of people using broadband of 256kbps or above, it doesn't take any time to download 156MB.
You downloaded a simple file oraclexe.exe. Just double click on that and follow the instructions. At one point it asks you to provide password for system account. Provide a password and make sure you remember it. Here, I have a suggest. Try to simplify your password. Give you name or something that you can easily remember. After all you are not going to put any confidential data in your database and moreover in many cases only you access the database and no one else.
Unlocking HR account
Until Oracle9i we had an account called SCOTT with password TIGER. Many try to use that to log in but fail. Oracle Database 10g doesn't come with SCOTT account any more. It instead provides HR account HR password.
But, HR account is locked and cannot be used unless you unlock it. So here are the steps to unlock HR account.
Start SQL Plus using Oracle Database 10g Express Edition->Run SQL Command Line.
At SQL> prompt, connect to Oracle using SYSTEM account with password given at the time of installation. Oracle password are NOT case sensitive.
CONNECT SYSTEM/password
Unlock HR account with the following command.
ALTER USER HR ACCOUNT UNLOCK;
> Now, HR account is unlock and ready to be used.
So, try to connect to HR account using the following command.
CONNECT HR/HR
It will prompt you to enter new password and current password is expired. This is because of the fact, HR account's password is expired by default. Don't worry enter new password for HR. To make life easy, you better enter the same HR as password.
That's all you have to do to access HR account and enjoy all the sample table that come with HR account.
If you want to know more about HR account tables their relationships and queries related to those tables, please visit Human Resource Schema Of Oracle10g.
Regular Expressions in Oracle Database 10g
Oracle Database 10g supports regular expression in search and replace. It provides a set of new functions that provides regular expression capability. Oracle10g's implementation of regular expression complies with Portable Operating System for Unix (POSIX) standards.
Regular expression is a pattern that contains special characters, which specify what exactly we are looking for. Let us now understand some of the limitations we have with LIKE operator and INSTR function.
Let us assume we have to search for a name that starts with either N, M or P. The following LIKE operator, though lengthy, can do it.
select * from employees where first_name like 'N%' or first_name like 'M%' or first_name like 'P%';
But what if we want to get all names that start with any uppercase letter and contain only 5 letters. It is possible but it is not an easy condition (not at least for beginners). This is the purpose regular expressions can serve. They allow you to make your searching more precise. The following example searches for employees whose first name is starting with uppercase letter and contain only 5 letters.
select first_name from employees where regexp_like ( first_name, '^[:aplha:]{4}$');
Special characters in regular expression
The above example used a few special characters to specify what exactly we search for. Here is the list of special characters in regular expression.
Character Description
* Zero or more occurrences of the previous character.
+ One or more occurrences of the previous character.
? Zero or one occurrences of the previous character.
. Any character matches this position.
[] Character must be any character in the brackets. Even a range of characters can be given. For Example, [A-Z] means any uppercase letter is a match.
[^] Any character other than the characters given in the brackets is a match. Reverse of [].
^ Matches beginning of the string. For example, ^A means the string must start with A.
$ Matches end of the string. For example, A$ means string must end with A.
| Alternate operator. Either of the expressions must be a match. expr1|expr2 means either expr1 or expr2 must be a match.
{n} Previous expression must be repeated for exactly n times. For example, [0-9]{3} means three digits.
{n,m} Previous expression must be repeated from n to m times. For example, [0-9]{3,5} means we may have 3 to 5 digits.
{n,} Previous expression must be repeated for at least n times.
() Groups the expression. The entire expression enclosed is treated as one expression.
The character class [:alpha:] matches any alphabet. The other characters classes available are given below:
Character Class Description
[:alnum:] Alphanumeric characters
[:alpha:] Any alphabet either upper or lower case.
[:cntrl:] Any control character. A non-printable character is called as control character.
[:digit:] Any digit.
[:lower:] Any lower case letter.
[:print:] Any printable character.
[:punct:] Any punctuation character.
[:space:] All space characters.
[:upper:] Any upper case letter.
New Functions
The following are the new functions and what they do. All these function extend the functionality of some existing function/operator with the capability of supporting regular expression.
Function Description
REG_LIKE Same as LIKE operator but supports regular expression. Allows search based on regular expression.
REG_INSTR Same as INSTR function but supports regular expression. Allows you to locate a string in another string more precisely using regular expression.
REG_SUBSTR Same as SUBSTR function but supports regular expression. Allows you to extract a part of another string using regular expression.
REG_REPLACE Same as REPLACE function but supports regular expression. Allows you to search for source string using regular expression and replaces it with target string
Examples
Let us now explore these functions and see how they can be used. We start with REGEXP_LIKE function, which is extended version of LIKE operator.
select job_title from jobs where regexp_like ( job_title, '^S.*er$');
The above example displays job titles that start with S and end with er. It may contain anything in between these two.
If you want to modify the way REGEXP_LIKE compares characters then third parameter, which contains either 'c' for case sensitive or 'i' for ignore case, can be given as shown below.
select job_title from jobs where regexp_like ( job_title, '^S.*er$','i')
Now let us see how REGEXP_SUBSTR is used to extract a substring based on regular expression.
select regexp_substr('Oracle Database 10g is first grid aware database','[0-9]+') version from dual;
The above query displays 10 as it is consisting of one or more digits. The following example looks for a number followed by a character - displays 10g.
select regexp_substr('Oracle Database 10g is first grid aware database','[0-9]+[a-z]') version from dual;
The following query displays the starting position of one or more digits.
select regexp_instr('Oracle Database 10g is first grid aware database','[0-9]+') position from dual;
The following query returns the positon of first non-alphabet in the given string.
select regexp_instr('Abc123 xyz123','[^[:alpha:]]') from dual;
The following query places a space between Oracle its version using REGEXP_REPLACE function. For example, Oracle9i will become Oracle 9i, Oracle10g will become Oracle 10g. We are looking for a series of alphabets and take them as group 1. Then we are looking for a group of digits followed by any character and treat it as group 2. Then we replace the original with \1 (group 1) a space and \2 (group 2).
select regexp_replace('Oracle10g','([[:alpha:]]+)([[:digit:]]+.)','\1 \2') from dual;
Regular expressions in Oracle10g will make searching and replacing more precise. Certain searches that were not possible with simple LIKE operator are now possible with the introduction of regular expressions.
Regular expression is a pattern that contains special characters, which specify what exactly we are looking for. Let us now understand some of the limitations we have with LIKE operator and INSTR function.
Let us assume we have to search for a name that starts with either N, M or P. The following LIKE operator, though lengthy, can do it.
select * from employees where first_name like 'N%' or first_name like 'M%' or first_name like 'P%';
But what if we want to get all names that start with any uppercase letter and contain only 5 letters. It is possible but it is not an easy condition (not at least for beginners). This is the purpose regular expressions can serve. They allow you to make your searching more precise. The following example searches for employees whose first name is starting with uppercase letter and contain only 5 letters.
select first_name from employees where regexp_like ( first_name, '^[:aplha:]{4}$');
Special characters in regular expression
The above example used a few special characters to specify what exactly we search for. Here is the list of special characters in regular expression.
Character Description
* Zero or more occurrences of the previous character.
+ One or more occurrences of the previous character.
? Zero or one occurrences of the previous character.
. Any character matches this position.
[] Character must be any character in the brackets. Even a range of characters can be given. For Example, [A-Z] means any uppercase letter is a match.
[^] Any character other than the characters given in the brackets is a match. Reverse of [].
^ Matches beginning of the string. For example, ^A means the string must start with A.
$ Matches end of the string. For example, A$ means string must end with A.
| Alternate operator. Either of the expressions must be a match. expr1|expr2 means either expr1 or expr2 must be a match.
{n} Previous expression must be repeated for exactly n times. For example, [0-9]{3} means three digits.
{n,m} Previous expression must be repeated from n to m times. For example, [0-9]{3,5} means we may have 3 to 5 digits.
{n,} Previous expression must be repeated for at least n times.
() Groups the expression. The entire expression enclosed is treated as one expression.
The character class [:alpha:] matches any alphabet. The other characters classes available are given below:
Character Class Description
[:alnum:] Alphanumeric characters
[:alpha:] Any alphabet either upper or lower case.
[:cntrl:] Any control character. A non-printable character is called as control character.
[:digit:] Any digit.
[:lower:] Any lower case letter.
[:print:] Any printable character.
[:punct:] Any punctuation character.
[:space:] All space characters.
[:upper:] Any upper case letter.
New Functions
The following are the new functions and what they do. All these function extend the functionality of some existing function/operator with the capability of supporting regular expression.
Function Description
REG_LIKE Same as LIKE operator but supports regular expression. Allows search based on regular expression.
REG_INSTR Same as INSTR function but supports regular expression. Allows you to locate a string in another string more precisely using regular expression.
REG_SUBSTR Same as SUBSTR function but supports regular expression. Allows you to extract a part of another string using regular expression.
REG_REPLACE Same as REPLACE function but supports regular expression. Allows you to search for source string using regular expression and replaces it with target string
Examples
Let us now explore these functions and see how they can be used. We start with REGEXP_LIKE function, which is extended version of LIKE operator.
select job_title from jobs where regexp_like ( job_title, '^S.*er$');
The above example displays job titles that start with S and end with er. It may contain anything in between these two.
If you want to modify the way REGEXP_LIKE compares characters then third parameter, which contains either 'c' for case sensitive or 'i' for ignore case, can be given as shown below.
select job_title from jobs where regexp_like ( job_title, '^S.*er$','i')
Now let us see how REGEXP_SUBSTR is used to extract a substring based on regular expression.
select regexp_substr('Oracle Database 10g is first grid aware database','[0-9]+') version from dual;
The above query displays 10 as it is consisting of one or more digits. The following example looks for a number followed by a character - displays 10g.
select regexp_substr('Oracle Database 10g is first grid aware database','[0-9]+[a-z]') version from dual;
The following query displays the starting position of one or more digits.
select regexp_instr('Oracle Database 10g is first grid aware database','[0-9]+') position from dual;
The following query returns the positon of first non-alphabet in the given string.
select regexp_instr('Abc123 xyz123','[^[:alpha:]]') from dual;
The following query places a space between Oracle its version using REGEXP_REPLACE function. For example, Oracle9i will become Oracle 9i, Oracle10g will become Oracle 10g. We are looking for a series of alphabets and take them as group 1. Then we are looking for a group of digits followed by any character and treat it as group 2. Then we replace the original with \1 (group 1) a space and \2 (group 2).
select regexp_replace('Oracle10g','([[:alpha:]]+)([[:digit:]]+.)','\1 \2') from dual;
Regular expressions in Oracle10g will make searching and replacing more precise. Certain searches that were not possible with simple LIKE operator are now possible with the introduction of regular expressions.
Using Oracle Database 10g Express Edition with Java
This article shows how to connect to Oracle Database 10g Express Edition using JDBC.
I use J2SE 5.0 (c:\jdk1.5.0) and Oracle Database 10g Express Edition on Windows XP (f:\oraclexe) for this article.
The following is the batch file used to set PATH and CLASSPATH.
path c:\jdk1.5.0\bin;f:\oraclexe\app\oracle\product\10.2.0\server\BIN
set classpath=.;f:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar
Connecting using OCI driver
The following program shows how to connect to Oracle using OCI driver.
import java.sql.*;
public class OracleOCIConnection
{
public static void main(String args[])
{
try
{
// load oracle driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// connect using Native-API (OCI) driver
Connection con = DriverManager.getConnection("jdbc:oracle:oci8:@","hr","hr" );
System.out.println("Connected Successfully To Oracle using OCI driver");
con.close();
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
}
Connecting using Thin driver
The following program uses thin driver of Oracle to connect to Oracle. The default service name in Oracle Database 10g Express Edition is xe and port number for listener is 1521.
import java.sql.*;
public class OracleThinConnection
{
public static void main(String args[])
{
try
{
// load oracle driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// connect using Thin driver
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","hr","hr");
System.out.println("Connected Successfully To Oracle");
con.close();
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
}
Connecting using Data Source - OracleDataSource
Connecting to Oracle using Driver Manager is deprecated in the new version. Instead it is recommended to use OrcleDataSource to get connection. The following program shows how to connect to Oracle using OracleDataSource.
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
class DSConnection
{
public static void main (String args[]) throws SQLException
{
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:hr/hr@localhost:1521/XE");
Connection con = ods.getConnection();
System.out.println("Connected");
con.close();
}
}
I use J2SE 5.0 (c:\jdk1.5.0) and Oracle Database 10g Express Edition on Windows XP (f:\oraclexe) for this article.
The following is the batch file used to set PATH and CLASSPATH.
path c:\jdk1.5.0\bin;f:\oraclexe\app\oracle\product\10.2.0\server\BIN
set classpath=.;f:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar
Connecting using OCI driver
The following program shows how to connect to Oracle using OCI driver.
import java.sql.*;
public class OracleOCIConnection
{
public static void main(String args[])
{
try
{
// load oracle driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// connect using Native-API (OCI) driver
Connection con = DriverManager.getConnection("jdbc:oracle:oci8:@","hr","hr" );
System.out.println("Connected Successfully To Oracle using OCI driver");
con.close();
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
}
Connecting using Thin driver
The following program uses thin driver of Oracle to connect to Oracle. The default service name in Oracle Database 10g Express Edition is xe and port number for listener is 1521.
import java.sql.*;
public class OracleThinConnection
{
public static void main(String args[])
{
try
{
// load oracle driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// connect using Thin driver
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","hr","hr");
System.out.println("Connected Successfully To Oracle");
con.close();
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
}
Connecting using Data Source - OracleDataSource
Connecting to Oracle using Driver Manager is deprecated in the new version. Instead it is recommended to use OrcleDataSource to get connection. The following program shows how to connect to Oracle using OracleDataSource.
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
class DSConnection
{
public static void main (String args[]) throws SQLException
{
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:hr/hr@localhost:1521/XE");
Connection con = ods.getConnection();
System.out.println("Connected");
con.close();
}
}
History Of Oracle Database
Here is the list of important events that took place in the last 30 years of Oracle history. Oracle is celebrating its 30 wonderful years in the industry (from 1977 to 2007).
This article was updated on 6th August, 2007.
In 1977, Larry Ellison, Bob Miner and Ed Oates founded Software Development Laboratories to undertake development work
After reading a paper by Codd in IBM Journal of Research and Development, they created Oracle. The first version was never released. It was written in Assembly language of PDP which ran in 128kb of RAM.
Version 2.0 of Oracle was released in 1979 and it became first commercial relational database and first SQL database. The company changed its name to Relational Software Inc. (RSI).
In 1981, RSI started developing tools for Oracle.
In 1982, RSI was renamed to Oracle Corporation. It held its first user conference in San Francisco.
In 1983, Oracle released version 3.0, which was rewritten in C language and ran on multiple platforms.
In 1984, Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency etc.
By 1985, Oracle released version 5.0 and became the first relational database that worked in client/server environment.
In 1986, Oracle goes public on the NASDAQ exchange.
In 1987, Oracle wanted to create enterprise applications that take advantage of their database.
In 1988, Oracle version 6.0 was released. It provided row-level locking, hot backup and PL/SQL as main features.
By 1989, Oracle moved to new headquarters in Redwood Shores, California.
In 1990, they released Oracle Applications Release 8, which included account software for client/server
In 1992, they released Oracle 7.0. It provided better performance, administrative utilities, application development tools, security features, stored procedures, triggers and declarative integrity.
In 1995, Oracle became the first major company to announce a comprehensive internet strategy.
In 1997, Oracle released Oracle 8.0 and Oracle Applications 10.7. It started embracing Java. Partitioning, support for different types of data like images, large text, external data etc.(lobs) are provided. It also started providing support for Object in database becoming an Object-Relational DBMS
By 1999, Oracle realized that "Internet Changes Everything". Oracle released Oracle 8i and Oracle Applications 11i. They supported open standards like XML. Oracle8i provided Java Virtual Machine (JVM) to run Java program in Oracle Database and also scalability, which was required for internet databases.
To read more about what is new in Oracle8i, read What's new in Oracle8i and PL/SQL Enhancements articles.
In 2000, Oracle9iAS was released. Oracle corporation is no longer a company providing only database management system and instead started providing all that it takes to develop and deploy a complete application. AS(Application Server) runs on middle tier in 3-tier Client/Server architecture boosting the performance.
In 2001, Oracle9i was released. It allows Oracle to run on RAC (Real Application Cluster), which is a collection of low-cost servers. It also allowed XML documents to be stored and queried in Oracle Database.
You can get information about new features that were introduced in Oracle9i using New features of Oracle9i Database
In 2003, Oracle10g was released, where g stands for Grid computing, which servers computing power across the enterprise as a utility, automatically shifting processing load based on demand. Oracle10g also made a lot of administrative tasks automatic.
In 2007, Oracle has released Oracle11g. The new version focused on better partitioning, easy migration etc.
As of now, Oracle has 68,000 employees, 2,75,000 customer with US$14 billion revenue. It is run by Larry Ellison (CEO), Charles Phillips(president),Safra Catz (president and CFO).
Oracle's philosophy, according to Ken Jacobs, vice president of product strategy is, "Nothing is impossible. Impossible just takes a little longer".
This article was updated on 6th August, 2007.
In 1977, Larry Ellison, Bob Miner and Ed Oates founded Software Development Laboratories to undertake development work
After reading a paper by Codd in IBM Journal of Research and Development, they created Oracle. The first version was never released. It was written in Assembly language of PDP which ran in 128kb of RAM.
Version 2.0 of Oracle was released in 1979 and it became first commercial relational database and first SQL database. The company changed its name to Relational Software Inc. (RSI).
In 1981, RSI started developing tools for Oracle.
In 1982, RSI was renamed to Oracle Corporation. It held its first user conference in San Francisco.
In 1983, Oracle released version 3.0, which was rewritten in C language and ran on multiple platforms.
In 1984, Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency etc.
By 1985, Oracle released version 5.0 and became the first relational database that worked in client/server environment.
In 1986, Oracle goes public on the NASDAQ exchange.
In 1987, Oracle wanted to create enterprise applications that take advantage of their database.
In 1988, Oracle version 6.0 was released. It provided row-level locking, hot backup and PL/SQL as main features.
By 1989, Oracle moved to new headquarters in Redwood Shores, California.
In 1990, they released Oracle Applications Release 8, which included account software for client/server
In 1992, they released Oracle 7.0. It provided better performance, administrative utilities, application development tools, security features, stored procedures, triggers and declarative integrity.
In 1995, Oracle became the first major company to announce a comprehensive internet strategy.
In 1997, Oracle released Oracle 8.0 and Oracle Applications 10.7. It started embracing Java. Partitioning, support for different types of data like images, large text, external data etc.(lobs) are provided. It also started providing support for Object in database becoming an Object-Relational DBMS
By 1999, Oracle realized that "Internet Changes Everything". Oracle released Oracle 8i and Oracle Applications 11i. They supported open standards like XML. Oracle8i provided Java Virtual Machine (JVM) to run Java program in Oracle Database and also scalability, which was required for internet databases.
To read more about what is new in Oracle8i, read What's new in Oracle8i and PL/SQL Enhancements articles.
In 2000, Oracle9iAS was released. Oracle corporation is no longer a company providing only database management system and instead started providing all that it takes to develop and deploy a complete application. AS(Application Server) runs on middle tier in 3-tier Client/Server architecture boosting the performance.
In 2001, Oracle9i was released. It allows Oracle to run on RAC (Real Application Cluster), which is a collection of low-cost servers. It also allowed XML documents to be stored and queried in Oracle Database.
You can get information about new features that were introduced in Oracle9i using New features of Oracle9i Database
In 2003, Oracle10g was released, where g stands for Grid computing, which servers computing power across the enterprise as a utility, automatically shifting processing load based on demand. Oracle10g also made a lot of administrative tasks automatic.
In 2007, Oracle has released Oracle11g. The new version focused on better partitioning, easy migration etc.
As of now, Oracle has 68,000 employees, 2,75,000 customer with US$14 billion revenue. It is run by Larry Ellison (CEO), Charles Phillips(president),Safra Catz (president and CFO).
Oracle's philosophy, according to Ken Jacobs, vice president of product strategy is, "Nothing is impossible. Impossible just takes a little longer".
New Features of Oracle9i Database
ast 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]...
[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
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
Oracle9i Database Certification
The following are the various levels of certification available for Oracle9i Database.
Oracle9i Certified Database Associate
The Oracle Certification Program begins with the Associate level. At this apprentice skill level, Oracle Associates have a foundation of knowledge that will allow them to act as a junior team member working with database administrators or application developers. The Oracle Associate certification gives a beginning IT professional recognition for their abilities and can facilitate building a career with Oracle technologies.
To become an Oracle Associate, you must pass the exams required for the Oracle Associate level of your selected job role. Typically, two exams are required; the first one can be taken via the Internet, while subsequent exams must be taken in a proctored environment. By completing your Oracle Associate, you are half-way toward achieving the Oracle Certified Professional credential.
Exams
Exam-1 Introduction to Oracle9i: SQL Exam number: 1Z1-007
Exam-2 Oracle9i Database: Fundamentals I Exam number: 1Z1-031
Oracle9i Certified Database Administrator - Oracle Certified Professional
The Oracle Certified Professional credential is the next step toward gaining 9i Database expertise. After achieving your Oracle Certified Associate (OCA) credential, you will be ready to proceed forward toward earning the highly respected OCP status.
Oracle Certified Professionals have proven skills managing a large-scale database or developing robust applications that are deployed enterprise-wide. To become an Oracle Certified Professional, you must pass all required exams in your selected job role, including those at the Associate level. When you obtain this credential, you will have a valuable asset as you apply for more senior level opportunities and gain more credibility and support from your employer.
Exams
Exam-3 Oracle9i Database: Fundamentals II Exam number: 1Z1-032
Exam-4 Oracle9i Database: Performance Tuning Exam number: 1Z1-033
Oracle Certified Master
The Oracle Certified Master is the highest credential you can earn in the Oracle Certification Program. Oracle Certified Masters are senior members of IT departments, responsible for handling mission critical database systems and applications. They have proven Oracle skills and are looked upon as the experts within their organization. Not surprisingly, they command the highest salaries in the industry and offer the experience and skill level few others can deliver
To become an Oracle Certified Master, you must first be an Oracle Certified Professional.
Oracle9i Certified Database Associate
The Oracle Certification Program begins with the Associate level. At this apprentice skill level, Oracle Associates have a foundation of knowledge that will allow them to act as a junior team member working with database administrators or application developers. The Oracle Associate certification gives a beginning IT professional recognition for their abilities and can facilitate building a career with Oracle technologies.
To become an Oracle Associate, you must pass the exams required for the Oracle Associate level of your selected job role. Typically, two exams are required; the first one can be taken via the Internet, while subsequent exams must be taken in a proctored environment. By completing your Oracle Associate, you are half-way toward achieving the Oracle Certified Professional credential.
Exams
Exam-1 Introduction to Oracle9i: SQL Exam number: 1Z1-007
Exam-2 Oracle9i Database: Fundamentals I Exam number: 1Z1-031
Oracle9i Certified Database Administrator - Oracle Certified Professional
The Oracle Certified Professional credential is the next step toward gaining 9i Database expertise. After achieving your Oracle Certified Associate (OCA) credential, you will be ready to proceed forward toward earning the highly respected OCP status.
Oracle Certified Professionals have proven skills managing a large-scale database or developing robust applications that are deployed enterprise-wide. To become an Oracle Certified Professional, you must pass all required exams in your selected job role, including those at the Associate level. When you obtain this credential, you will have a valuable asset as you apply for more senior level opportunities and gain more credibility and support from your employer.
Exams
Exam-3 Oracle9i Database: Fundamentals II Exam number: 1Z1-032
Exam-4 Oracle9i Database: Performance Tuning Exam number: 1Z1-033
Oracle Certified Master
The Oracle Certified Master is the highest credential you can earn in the Oracle Certification Program. Oracle Certified Masters are senior members of IT departments, responsible for handling mission critical database systems and applications. They have proven Oracle skills and are looked upon as the experts within their organization. Not surprisingly, they command the highest salaries in the industry and offer the experience and skill level few others can deliver
To become an Oracle Certified Master, you must first be an Oracle Certified Professional.
Subscribe to:
Posts (Atom)