| Lesson 5 | What is different about Oracle? |
| Objective | Standard SQL versus Oracle's SQL extensions |
What makes Oracle SQL different?
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:
- Use the
SET command. Simply type the following command at the SQL> prompt and press Enter:
SET FLAGGER FULL
- 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
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.