Database Components   «Prev  Next»

Lesson 5 What is different about Oracle?
Objective Standard SQL versus Oracle's SQL extensions

What is different about Oracle?

Distinguish between standard SQL and Oracle's SQL extensions and describe when to use each.
Oracle supports the current international standard for SQL as defined by ANSI/ISO. You can run ANSI/ISO SQL commands in SQL*Plus and also in Enterprise Manager's SQL*Plus Worksheet and the Server Manager (an older command line interface to the Oracle database).
In addition to the standard SQL, Oracle has extended the language to add more functionality for the Oracle programmer.
This course describes many of these extensions. When you write a query, you can use the Oracle extensions seamlessly with standard SQL. Ordinarily, it is not necessary to identify which portions of your code are extensions and which parts are standard. Occasionally, however, you must write SQL code that is portable to non-Oracle platforms. In these cases, you can adjust the environment settings in SQL*Plus to accept only ANSI/ISO SQL.

Accepting ANSI/ISO commands

To adjust the SQL*Plus environment to allow only ANSI/ISO-compliant SQL commands, set the FLAGGER on.
There are two ways to set FLAGGER on:
  1. Use the SET command. Simply type the following command at the SQL> prompt and press Enter:
SET FLAGGER FULL
  1. Use the Windows menu in SQL*Plus to adjust the FLAGGER environment option. See the simulation below to walk through a demonstration.

Once the FLAGGER is turned on (with either method), any SQL command you enter is parsed and if an Oracle extension is found, the parser returns this error:
ORA-00097: use of Oracle SQL feature not in SQL92
Full Level

You can also set the SQL*Plus environment options in a command line.
Set Flagger Option Menu
The FLAGGER remains on during your current SQL*Plus session until you either log off or specifically turn off the FLAGGER. In addition, the FLAGGER can be turned on as a parameter for Oracle's pre-compilers.
The next lesson concludes this module.