Event Alerts
Event alerts immediately notify you of activity in your database as it happens. You define what a database event is – an insert or an update to a table – and Oracle Alert informs you when it happens. You can modify our precoded alert conditions or simply create your own, and Oracle Alert will send messages or perform predefined actions in
an action set when important events occur.
Periodic Alerts
Periodic alerts periodically report key information according to a schedule you define.
You can modify our precoded alerts or simply create your own, and Oracle Alert will send messages or perform predefined actions from an action set according to the schedule you set.
You can define periodic alerts on any Oracle Financials, Oracle Manufacturing, Oracle Human Resources, or Oracle Public Sector Financials application as well as any custom Oracle application.
Periodic alerts can be set to run as often as you need during a 24-hour period, or they can be set to run once a month – the frequency is up to you. Used over time, periodic alerts can provide a regular and reliable measure of performance.
For example, you can define a periodic alert for Oracle Purchasing that sends a message to the Purchasing Manager listing the number of approved requisition lines that each purchasing agent placed on purchase orders. You can define this alert to run weekly, and provide performance measurement on a consistent and timely basis.

Easy Alert Definition

Oracle Alert can load the SQL statement for your alert definition from an operating system file, allowing you to automatically perform the functions you currently do by hand. Oracle Alert will also transfer your entire alert definition across databases. You can instantly leverage the work done in one area to all your systems.
Customizable Alert Frequency
With Oracle Alert, you can choose the frequency of each periodic alert. You may want to check some alerts every day, some only once a month, still others only when you explicitly request them. You have the flexibility to monitor critical exceptions as frequently as necessary, even multiple times during a 24-hour period. You can also check less significant exceptions on a more infrequent schedule; for example, once a month.
Customizable Alert Actions
You can define a variety of actions for Oracle Alert to perform based on the exceptions it finds in your database. Oracle Alert can send an electronic mail message, run a SQL script or an operating system script, or submit a concurrent request, or any combination of the above. You can create your own message, SQL script, or operating system script
actions in Oracle Alert, or have Oracle Alert send messages or perform scripts that reside in external files. Each action is fully customizable to the exceptions found in your database, so you have complete flexibility in your exception management.
Detail or Summary Actions
You can choose to have Oracle Alert perform actions based on a single exception or a combination of exceptions found in your database. You can define a detail action such that Oracle Alert performs that action for each individual exception found. You can also define a summary action such that Oracle Alert performs that action once for each
unique combination of exceptions found. You decide which exceptions you want Oracle Alert to consider as a unique combination. You can format a detail or summary message action to display the exception(s) in an easy-to-read message.
No Exception Actions
Oracle Alert can perform actions if it finds no exceptions in your database. You can define Oracle Alert to send electronic mail messages, run SQL scripts or operating system scripts, or submit concurrent requests, or any combination of the above.
Alert History
Oracle Alert can keep a record of the actions it takes and the exceptions it finds in your database, for as many days as you specify. When you ask Oracle Alert to reconstruct alert history you see a complete record of alert activity exactly as it was performed. You can even review all responses Oracle Alert received to your messages and the actions
they invoked. Oracle Alert also lets you decide which information you want to review.
You can narrow your review criteria so you see only the history you specifically want to examine, without sorting through all the history information available for an alert.
Duplicate Checking
Oracle Alert can search for exceptions that remain in your database over time, and can take certain actions based on the presence of those “duplicate exceptions.” You can track exceptions in your database for the length of time that you save history for your alerts.
Action Escalation
You can define a sequence of actions and have Oracle Alert perform the next action in that sequence each time it finds the same exception or exceptions in your database. For example, you can have Oracle Alert send messages of increasing severity if it finds the same exceptions over a period of time. Using action escalation, you can make sure that exceptions needing attention don’t languish unattended in your database.
Summary Threshold
Oracle Alert can automatically determine whether to perform a detail or a summary action based on the number of exceptions it finds in your database. If your alert locates few exceptions, it can simply perform detail actions-one for each exception. If your alert locates many exceptions, it can perform a summary action on all of those exceptions.
Oracle Alert automatically determines when it should perform a detail or a summary action.
Response Processing
Oracle Alert can take certain predefined actions based on a user’s response to an alert message. The response can cause Oracle Alert to send another alert message, run a SQL script or an operating system script, or submit a concurrent request, or any combination of the above. Because Oracle Alert performs response actions automatically, you can delegate routine user transactions to Oracle Alert and thereby increase your organization’s efficiency.
Self-Referencing Alerts
You can create an alert that checks for exceptions that are new in your database since the last time the alert was checked. The alert uses its own DATE_LAST_CHECKED value as the start time for checking for new exceptions.
Customizable Options and User Profile
You can specify exactly how you want your Oracle Alert user interface to look and behave. From choosing a printer to specifying the header text in your Oracle Alert messages.
Electronic Mail Integration
Oracle Alert allows you to send alert e-mail messages through your mail system using the Simple Mail Transfer Protocol (SMTP) for outbound messages and the Internet Message Access Protocol (IMAP) for inbound messages.
NVL
The NVL() function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value. It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server.
The syntax for the NVL function is:
    NVL( string1, replace_with ) string1 is the string to test for a null value.
                                           replace_with is the value returned if string1 is null.

    NVL( column1, replace_with )
Example #1:
    select NVL(supplier_city, ‘n/a’)
    from suppliers;
The SQL statement above would return ‘n/a’ if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.
Example #2:
    select supplier_id,
    NVL(supplier_desc, supplier_name)
    from suppliers;
This SQL statement would return the supplier_name field if the supplier_desc contained a null value. Otherwise, it would return the supplier_desc.

The decode() Function

The decode() function works on the same principle as the if-then-else statement does in many common programming languages, including PL/SQL. You can pass a variable number of values into the call to the decode() function, which will appear in the column clause of your select statement. Your first item will always be the name of the column you want to decode. Next, you identify the first specific value Oracle should look for in that column. After that, you pass in the substitute you want Oracle to return if the first specific value is encountered. From there, you can then define as many specific value-substitute pairs as you would like. Once all value-substitute pairs have been defined, you can optionally specify a default value that Oracle will return if the column value doesn’t match a specified value. Take a look at the following code block to get a better idea of how this works:
SELECT decode(column_name,
              value1, substitute1,
              value2, substitute2,
              … ,
              return_default)
FROM … ;
 

Example:
Select
decode(AUTHORIZATION_STATUS,
       ‘APPROVED’, ‘APPRED’,
       ‘IN PROCESS’, ‘JUST STARTED’,
       ‘Others’)
From PO_HEADERS_ALL
SYSDATE
In Oracle/PLSQL, the sysdate function returns the current system date and time on your local database.
The syntax for the sysdate function is:    sysdate
Example #1:
Select SYSDATE FROM DUAL;
OUTPUT: 3/3/2009 2:22:27 AM

Example #2:
Select TRUNC(SYSDATE) FROM DUAL;
OUTPUT: 3/3/2009

Example #3:

Select TO_CHAR(SYSDATE,’DD-MM-YYYY’) FROM DUAL;
OUTPUT: 03-03-2009
Example #4:
select sysdate into v_date from dual;
OUTPUT: The variable called v_date will now contain the current date and time value.

Example #5:

You could also use the sysdate function in any SQL statement. For example:
    select supplier_id, sysdate
    from suppliers
    where supplier_id > 5000;
Arithmetic Functions
Other functions are designed to perform specialized mathematical functions, such as those used in scientific applications such as sine and logarithms. These operations are commonly referred to as arithmetic or number operations. The functions falling into this category are listed next. These functions are not all that is available in Oracle, but rather they are the most commonly used ones that will likely appear on OCP Exam 1:abs(x) Obtains the absolute value for a number. For example, the absolute value of -1 is 1, whereas the absolute value of 6 is 6.
round(x,y) Rounds x to the decimal precision of y. If y is negative, it rounds to the precision of y places to the left of the decimal point. For example, round(134.345,1) = 134.3, round(134.345,0) = 134, round(134.345,-1) = 130. This can also be used on DATE columns.
ceil(x) Similar to executing round on an integer (for example, round(x,0)), except ceil always rounds up. For example, ceil(1.4) = 2. Note that rounding up on negative numbers produces a value closer to zero (for example, ceil(-1.6) = -1, not -2).
floor(x) Similar to ceil, except floor always rounds down. For example, floor(1.6) = 1. Note that rounding down on negative numbers produces a value further away from zero (for example, floor (-1.6) = -2, not -1).
mod(x,y) The modulus of x, defined in long division as the integer remainder when x is divided by y until no further whole number can be produced. For example, mod(10,3) = 1, and mod(10,2) = 0.
Sign(x) Displays an integer value corresponding to the sign of x: 1 if x is positive, – 1 if x is negative.

sqrt(x) The square root of x.
  
trunc(x,y)
Truncates x to the decimal precision of y. If y is negative, it truncates to y number of places to the left of the decimal point. This can also be used on DATE columns.
vsize(x) The storage size in bytes for value x.
Text Functions
Several functions in Oracle manipulate text strings. These functions are similar in concept to nvl() and decode() in that they can perform a change on a piece of data, but the functions in this family can change only VARCHAR2 and CHAR data. Here are some examples:   
lpad(x,y[,z]) and rpad(x,y[,z]) Return data in string or column x padded on the left or right side, respectively, to width y. The optional value z indicates the character(s) that lpad() or rpad() use to pad the column data. If no character z is specified, a space is used.
    
lower(x), upper(x), and initcap(x) Return data in string or column x in lowercase or uppercase characters, respectively, or change the initial letter in the data from column x to a capital letter.
     
length(x)
Returns the number of characters in string or column x.
     
substr(x,y[,z]) Returns a substring of string or column x, starting at the character in position number y to the end, which is optionally defined by the character appearing in position z of the string. For example, substr(‘ABCDEFG’,3,4) returns CDEF.
    
instr(x,y)
Determines whether a substring y given can be found in string x. For example, instr(‘CORPORATE FLOOR’,’OR’) returns 2.
The trim() Function A single-row function called trim() behaves like a combination of ltrim() and rtrim(). The trim() function accepts a string describing the data you would like to trim from a column value using the following syntax: trim([[keyword ]’x’ from] column). Here keyword is replaced by leading, trailing, or both, or it’s omitted. Also, x is replaced with the character to be trimmed, or it’s omitted. If x is omitted, Oracle assumes it must trim whitespace. Finally, column is the name of the column in the table to be trimmed. Note that trim() only removes trailing or leading instances of the character specified. If that character appears somewhere in the string, trim() will not remove it.
Conversion Functions
Conversion functions are designed to convert data from one datatype format to another. These functions do not actually modify the stored data in the table itself; they just return the converted values to the SQL*Plus session. Figure 2-1 displays how information can get converted from one datatype to another using various functions. Several different conversion functions are available in the Oracle database, as listed here:
to_char(x) Converts the value x to a character or converts a date to a character string using formatting conventions (see “Date-Formatting Conventions” subtopic below).
to_number(x) Converts nonnumeric value x to a number.
to_date(x[,y]) Converts the nondate value x to a date using the format specified by y.
to_multi_byte(x) Converts the single-byte character string x to multibyte characters according to national language standards.

to_single_byte(x)
Converts the multibyte character string x to single-byte characters according to national language standards.
chartorowid(x) Converts the string of characters x into an Oracle ROWID.
rowidtochar(x) Converts the ROWID value into the string of characters x of VARCHAR2 datatype.
hextoraw(x) Converts the hexadecimal (base-16) value x into a raw (binary) format.
rawtohex(x) Converts the raw (binary) value x into a hexadecimal (base-16) format.
convert(x[,y[,z]]) Executes a conversion of alphanumeric string x from the current character set (optionally specified as z) to the one specified by y.
translate(x,y,z) Executes a simple value conversion for character or numeric string x into something else based on the conversion factors y and z.

UNION Query
The UNION query allows you to combine the result sets of 2 or more “select” queries.

  • It removes duplicate rows between the various “select” statements.
  • Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types.

The syntax for a UNION query is:
Select field1, field2, . field_n   from tables
UNION
Select field1, field2, . field_n   from tables;

Example #1

The following is an example of a UNION query:
Select supplier_id from suppliers
UNION
Select supplier_id from orders;
In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear once in your result set. The UNION removes duplicates.

Example #2 – With ORDER BY Clause

The following is a UNION query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id > 2000
UNION
Select company_id, company_name from companies where company_id > 1000
ORDER BY 2;
Since the column names are different between the two “select” statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we’ve sorted the results by supplier_name / company_name in ascending order, as denoted by the “ORDER BY 2”.

UNION ALL Query

The UNION ALL query allows you to combine the result sets of 2 or more “select” queries. It returns all rows (even if the row exists in more than one of the “select” statements).
Each SQL statement within the UNION ALL query must have the same number of fields in the result sets with similar data types.
The syntax for a UNION ALL query is:
Select field1, field2, . field_n from tables
UNION ALL
Select field1, field2, . field_n from tables;

Example #1
The following is an example of a UNION ALL query:
Select supplier_id from suppliers
UNION ALL
Select supplier_id from orders;
If a supplier_id appeared in both the suppliers and orders table, it would appear multiple times in your result set. The UNION ALL does not remove duplicates.
Example #2 – With ORDER BY Clause
The following is a UNION query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id > 2000
UNION ALL
Select company_id, company_name from companies where company_id > 1000
ORDER BY 2;
Since the column names are different between the two “select” statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we’ve sorted the results by supplier_name / company_name in ascending order, as denoted by the “ORDER BY 2”.
INTERSECT Query
The INTERSECT query allows you to return the results of 2 or more “select” queries. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
Each SQL statement within the INTERSECT query must have the same number of fields in the result sets with similar data types.
The syntax for an INTERSECT query is:
Select field1, field2, . field_n from tables
INTERSECT
Select field1, field2, . field_n from tables;

Example #1
The following is an example of an INTERSECT query:
Select supplier_id from suppliers
INTERSECT
Select supplier_id from orders;
In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear in your result set.
Example #2 – With ORDER BY Clause
The following is an INTERSECT query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id > 2000
INTERSECT
Select company_id, company_name from companies where company_id > 1000
ORDER BY 2;
Since the column names are different between the two “select” statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we’ve sorted the results by supplier_name / company_name in ascending order, as denoted by the “ORDER BY 2”.
MINUS Query
The MINUS query returns all rows in the first query that are not returned in the second query.
Each SQL statement within the MINUS query must have the same number of fields in the result sets with similar data types.
The syntax for an MINUS query is:
Select field1, field2, . field_n from tables
MINUS
Select field1, field2, . field_n from tables;

Example #1
The following is an example of an MINUS query:
Select supplier_id from suppliers
MINUS
Select supplier_id from orders;
In this example, the SQL would return all supplier_id values that are in the suppliers table and not in the orders table. What this means is that if a supplier_id value existed in the suppliers table and also existed in the orders table, the supplier_id value would not appear in this result set.
Example #2 – With ORDER BY Clause
The following is an MINUS query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id > 2000
MINUS
Select company_id, company_name from companies where company_id > 1000
ORDER BY 2;
Since the column names are different between the two “select” statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we’ve sorted the results by supplier_name / company_name in ascending order, as denoted by the “ORDER BY 2”.

So far, we have seen how to get data out of a table using SELECT and WHERE commands. Often, however, we need to list the output in a particular order. This could be in ascending order, in descending order, or could be based on either numerical value or text value. In such cases, we can use the ORDER BY keyword to achieve our goal.
The syntax for an ORDER BY statement is as follows:

SELECT “column_name”
FROM “table_name”
[WHERE “condition”]
ORDER BY “column_name” [ASC, DESC]

The [] means that the WHERE statement is optional. However, if a WHERE clause exists, it comes before the ORDER BY clause. ASC means that the results will be shown in ascending order, and DESC means that the results will be shown in descending order. If neither is specified, the default is ASC.
It is possible to order by more than one column. In this case, the ORDER BY clause above becomes ORDER BY “column_name1” [ASC, DESC], “column_name2” [ASC, DESC]
The typical database contains many tables. Some smaller databases may have only a dozen or so tables, whereas other databases may have hundreds or even thousands. The common factor, however, is that few databases have just one table containing everything you need. Therefore, you usually have to draw data from multiple tables together in a meaningful way. To show data from multiple tables in one query, Oracle allows you to perform table joins.
Here are the two rules you need to remember for table joins. Data from two (or more) tables can be joined, if the same column (under the same or a different name) appears in both tables, and the column is the primary key (or part of that key) in one of the tables. Having a common column in two tables implies a relationship between the two tables. The nature of that relationship is determined by which table uses the column as a primary key. This begs the question, what is a primary key? A primary key is a column in a table used for identifying the uniqueness of each row in a table. The table in which the column appears as a primary key is referred to as the parent table in this relationship (sometimes also called the master table), whereas the column that references the other table in the relationship is often called the child table (sometimes also called the detail table). The common column appearing in the child table is referred to as a foreign key.

Join Syntax
Let’s look at an example of a join statement using the Oracle traditional syntax, where we join the contents of the EMP and DEPT tables together to obtain a listing of all employees, along with the names of the departments they work for:

SQL> select e.ename, e.deptno, d.dname
    2  from emp e, dept d
    3  where e.deptno = d.deptno;

ENAME     DEPTNO    DNAME
———-      ———     ————–
SMITH         20          RESEARCH
ALLEN         30          SALES
WARD         30          SALES
JONES         20          RESEARCH
Note the many important components in this table join. Listing two tables in the from clause clearly indicates that a table join is taking place. Note also that each table name is followed by a letter: E for EMP or D for DEPT. This demonstrates an interesting concept—just as columns can have aliases, so too can tables. The aliases serve an important purpose—they prevent Oracle from getting confused about which table to use when listing the data in the DEPTNO column. Remember, EMP and DEPT both have a column named DEPTNO
You can also avoid ambiguity in table joins by prefixing references to the columns with the table names, but this often requires extra coding. You can also give the column two different names, but then you might forget that the relationship exists between the two tables. It’s just better to use aliases! Notice something else, though. Neither the alias nor the full table name needs to be specified for columns appearing in only one table. Take a look at another example:
SQL> select ename, emp.deptno, dname
  2  from emp, dept
  3  where emp.deptno = dept.deptno;
How Many Comparisons Do You Need?
When using Oracle syntax for table joins, a query on data from more than two tables must contain the right number of equality operations to avoid a Cartesian product. To avoid confusion, use this simple rule: If the number of tables to be joined equals N, include at least N-1 equality conditions in the select statement so that each common column is referenced at least once. Similarly, if you are using the ANSI/ISO syntax for table joins, you need to use N-1 join tablename on join_condition clauses for every N tables being joined.

For N joined tables using Oracle or ANSI/ISO syntax for table joins, you need at least N-1 equijoin conditions in the where clause of your select statement or N-1 join tablename on join_condition clauses in order to avoid a Cartesian product, respectively.

Cartesian Products
Notice also that our where clause includes a comparison on DEPTNO linking data in EMP to that of DEPT. Without this link, the output would have included all data from EMP and DEPT, jumbled together in a mess called a Cartesian product. Cartesian products are big, meaningless listings of output that are nearly never what you want. They are formed when you omit a join condition in your SQL statement, which causes Oracle to join all rows in the first table to all rows in the second table. Let’s look at a simple example in which we attempt to join two tables, each with three rows, using a select statement with no where clause, resulting in output with nine rows:

SQL> select a.col1, b.col_2
  2  from example_1 a, example_2 b;

     COL1 COL_2
——— ——————————
        1 one
        2 one
        3 one
        1 two
        2 two
        3 two
You must always remember to include join conditions in join queries to avoid Cartesian products. But take note of another important fact. Although we know that where clauses can contain comparison operations other than equality, to avoid Cartesian products, you must always use equality operations in a comparison joining data from two tables. If you want to use another comparison operation, you must first join the information using an equality comparison and then perform the other comparison somewhere else in the where clause. This is why table join operations are also sometimes referred to as equijoins. Take a look at the following example that shows proper construction of a table join, where the information being joined is compared further using a nonequality operation to eliminate the employees from accounting:

SQL> select ename, emp.deptno, dname
  2  from emp, dept
  3  where emp.deptno = dept.deptno
  4  and dept.deptno > 10;

ANSI/ISO Join Syntax (Oracle9i and higher)
In Oracle9i, Oracle introduces strengthened support for ANSI/ISO join syntax. To join the contents of two tables together in a single result according to that syntax, we must include a join tablename on join_condition in our SQL statement. If we wanted to perform the same table join as before using this new syntax, our SQL statement would look like the following:
Select ename, emp.deptno, dname
from emp join dept
on emp.deptno = dept.deptno;

ENAME         DEPTNO    DNAME
———-          ———      ————–
SMITH             20         RESEARCH
ALLEN             30         SALES
WARD             30         SALES
JONES             20         RESEARCH

Note how different this is from Oracle syntax. First, ANSI/ISO syntax separates join comparisons from all other comparisons by using a special keyword, on, to indicate what the join comparison is. You can still include a where clause in your ANSI/ISO-compliant join query, the only difference is that the where clause will contain only those additional conditions you want to use for filtering your data. You also do not list all your tables being queried in one from clause. Instead, you use the join clause directly after the from clause to identify the table being joined.
Never combine Oracle’s join syntax with ANSI/ISO’s join syntax! Also, there are no performance differences between Oracle join syntax and ANSI/ISO join syntax.
Cartesian Products: An ANSI/ISO Perspective
In some cases, you might actually want to retrieve a Cartesian product, particularly in financial applications where you have a table of numbers that needs to be cross-multiplied with another table of numbers for statistical analysis purposes. ANSI/ISO makes a provision in its syntax for producing Cartesian products through the use of a cross-join. A cross-join is produced when you use the cross keyword in your ANSI/ISO-compliant join query. Recall from a previous example that we produced a Cartesian product by omitting the where clause when joining two sample tables, each containing three rows, to produce nine rows of output. We can produce this same result in ANSI/ISO SQL by using the cross keyword, as shown here in bold:

Select col1, col_2
from example_1 cross join example_2;

COL1    COL_2
——— ————-
 1        one
 2        one
 3        one
 1        two
 2        two
 3        two
 1        three

Natural Joins

One additional type of join you need to know about for OCP is the natural join. A natural join is a join between two tables where Oracle joins the tables according to the column(s) in the two tables sharing the same name (naturally!). Natural joins are executed whenever the natural keyword is present. Let’s look at an example. Recall our use of the EMP and DEPT tables from our discussion above. Let’s take a quick look at the column listings for both tables:
SQL> describe emp
Name                       Null            Type
————————–    ———       ————
EMPNO                  NOT NULL    NUMBER(4)
ENAME                                    VARCHAR2(10)
JOB                                         VARCHAR2(9)
MGR                                        NUMBER(4)
SQL> describe dept
Name                       Null           Type
————————–   ———       ————
DEPTNO                 NOT NULL  NUMBER(2)
DNAME                                   VARCHAR2(14)
LOC                                       VARCHAR2(13)
As you can see, DEPTNO is the only column in common between these two tables, and appropriately enough, it has the same name in both tables. This combination of facts makes our join query of EMP and DEPT tables a perfect candidate for a natural join. Take a look and see:
Select ename, deptno, dname
from emp natural join dept;

ENAME         DEPTNO    DNAME
———-          ———    ————–
SMITH             20         RESEARCH
ALLEN             30         SALES
WARD             30         SALES
Outer Joins
Outer joins extend the capacity of Oracle queries to include handling of situations where you want to see information from tables even when no corresponding records exist in the common column.  The purpose of an outer join is to include non-matching rows, and the outer join returns these missing columns as NULL values.
Left Outer Join
A left outer join will return all the rows that an inner join returns plus one row for each of the other rows in the first table that did not have a match in the second table.
Suppose you want to find all employees and the projects they are currently responsible for. You want to see those employees that are not currently in charge of a project as well. The following query will return a list of all employees whose names are greater than ‘S’, along with their assigned project numbers.
  SELECT EMPNO, LASTNAME, PROJNO
    FROM CORPDATA.EMPLOYEE LEFT OUTER JOIN CORPDATA.PROJECT
          ON EMPNO = RESPEMP
    WHERE LASTNAME > ‘S’
The result of this query contains some employees that do not have a project number. They are listed in the query, but have the null value returned for their project number.
EMPNO     LASTNAME     PROJNO
000020     THOMPSON     PL2100
000100     SPENSER         OP2010
000170     YOSHIMURA     –
000250     SMITH             AD3112
In oracle we can specify(in Oracle 8i or prior vesrion this was the only option as they were not supporting the ANSI syntex) left outer join by putting a (+) sign on the right of the column which can have NULL data corresponding to non-NULL values in the column values from the other table.
example:  select    last_name,   department_name
from   employees e,   departments d
where  
e.department_id(+) = d.department_id;
Right Outer Join
A right outer join will return all the rows that an inner join returns plus one row for each of the other rows in the second table that did not have a match in the first table. It is the same as a left outer join with the tables specified in the opposite order.
The query that was used as the left outer join example could be rewritten as a right outer join as follows:

SQL> — Earlier version of outer join
SQL> — select e.ename, e.deptno, d.dname
SQL> — from dept d, emp e
SQL> — where d.deptno = e.deptno (+);
SQL>
SQL> — ANSI/ISO version
SQL> select e.ename, e.deptno, d.dname
SQL> from emp e right outer join dept d
SQL> on d.deptno = e.deptno;

Full Outer Joins
Oracle9i and higher
Oracle9i also makes it possible for you to easily execute a full outer join, including all records from the tables that would have been displayed if you had used both the left outer join or right outer join clauses. Let’s take a look at an example:
SQL> select e.ename, e.deptno, d.dname
2 from emp e full outer join dept d
3 on d.deptno = e.deptno;