Oracle Pro Tips, Trends & Technology eNewsletter
Pinnacle Publishing http://www.pinnaclepublishing.com
Issue 2.24
November 27, 2001

TABLE OF CONTENTS

1) Pop Quiz (answer at the end)
2) Did You Know...
3) Oracle9i Desupported Features
4) Light-Hearted Industry News
5) Answer to the Pop Quiz

---------------------------------------------------------
1) POP QUIZ (answer at the end)
---------------------------------------------------------
Does Oracle support ANSI-compliant syntax for joins?

---------------------------------------------------------
2) DID YOU KNOW...
---------------------------------------------------------

* that Oracle has provided the Oracle9iAS Migration Kit
for ASP (Active Server Pages)? It allows migration of
Microsoft IIS/ASP applications to the J2EE (Java 2
Enterprise Edition) platform.

Have a look at the following URL:

http://technet.oracle.com/tech/migration/asp/migration_kit_asp_fov.html.

---------------------------------------------------------
3) ORACLE9i DESUPPORTED FEATURES
---------------------------------------------------------
Note that a number of Oracle8 features will be or have
been desupported in Oracle9i.

For instance, the ANALYZE command may be desupported in a
future release of Oracle. It is recommended that we use
the dbms_stats supplied package. To obtain a description
of the package specifications, issue the following
command in SQL*Plus:

SQL> desc dbms_stats


Further, the CONNECT INTERNAL command is no longer
supported in Oracle9i. In other words, you can no longer
issue the following database connection commands:

svrmgr> CONNECT INTERNAL

or

svrmgr> CONNECT INTERNAL/PASSWORD

Use the following command instead:

svrmgr> CONNECT SYS/XXX as SYSDBA

where XXX is the password stored in the operating system
password file (PWD<SID>.ora).

---------------------------------------------------------
4) LIGHT-HEARTED INDUSTRY NEWS
---------------------------------------------------------
There is an article in the WIRED magazine that explores
the appeal of a unisex perfume produced by Coty that
attempts to "transcend digital and human."

Find out more on how you can smell like a computer! :)

http://www.wired.com/news/holidays/0,1882,48395,00.html

---------------------------------------------------------
5) ANSWER TO THE POP QUIZ
---------------------------------------------------------
Oracle9i supports ANSI syntax for joins.

Syntax:

SELECT ...
FROM table_name table_alias
[INNER] JOIN table_name alias
ON conditions | USING (column_name, ...,column_name)
...
[INNER] JOIN table_name alias
ON conditions | USING (column_name, ...,column_name)
WHERE ...


Legend:
- The INNER JOIN clause replaces the single in Oracle's
original join syntax, and is used to indicate that a join
is being performed
- The INNER keyword in the INNER JOIN clause is optional
as it is the default join type
- The ON clause defines the join conditions
- The USING clause specifies an equi-join of columns --
that is, joining of columns that have the same name in
both tables
- Note that the INNER JOIN syntax can be used to join
more than two tables. After the first two tables are
joined, the result set is simply joined to the next
table, and so on. The original Oracle syntax only joins
two tables at a time

Example:
We wish to obtain the address information of employees
working in the SALES department of a company:

SELECT s.emp_id, a.city
FROM employees s
  INNER JOIN departments c
    ON s.dept_id = c.dept_id
  INNER JOIN employee_addresses a
    ON a.emp_id = s.emp_id
WHERE c.dept_name = 'SALES';

The above statement can be re-written as follows:

SELECT s.emp_id, a.city
FROM employees s
  INNER JOIN departments c
    USING (dept_id)
  INNER JOIN employee_addresses a
    USING (emp_id)
WHERE c.dept_name = 'SALES';


In addition, Oracle9i supports ANSI-compliant syntax for
outer joins. In fact, Oracle recommends the use of the
ANSI syntax rather than the Oracle-specific syntax.

Syntax:
SELECT ...
FROM table_name table_alias
LEFT | RIGHT | FULL [OUTER] JOIN table_name table_alias
ON conditions | USING (column_name, ...,column_name)
...
LEFT | RIGHT | FULL [OUTER] JOIN table_name table_alias
ON conditions | USING (column_name, ...,column_name)
WHERE ...


Legend:
- The LEFT, RIGHT, and FULL keywords indicate an outer
join
- The OUTER keyword is optional
- A LEFT JOIN B creates nULL rows on B to provide matches
for A rows. This is the same as using (+) on all columns
of B
- A RIGHT JOIN B creates nULL rows on A to provide
matches for B rows. This is the same as using (+) on all
columns of A
- A FULL JOIN B returns all rows from A and B. This
feature is not supported using the (+) syntax

---------------------------------------------------------
Well, that's it for this week. I welcome your feedback,
input, tips, suggestions, Web sites, and other Oracle-
related news. If you send me something, please let me
know whether I can use your name with your comments. I
apologize in advance if I don't respond personally to
each of your questions or suggestions, but I'll get to as
many as I can in the eNewsletter if not personally.

Garry Chan, Editor
Database Architect
mailto:GChan@ProcaseConsulting.com

This eNewsletter is brought to you compliments of
Pinnacle Publishing, Inc.
Copyright(c) 2001
http://www.pinnaclepublishing.com
All rights reserved.