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.

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

2. 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.

You saw how to set the FLAGGER option using the command line in SQL*Plus. All of the SQL*Plus environment options can be set this way. You may find you need to use the SET command rather than the window menu for setting these options when:

1. You are creating a script that must run in the background
2. You are working in the command line version of SQL*Plus rather than the GUI version

Here are some examples of commonly used environment options and how to set them on the command line. Modify the number of characters per line to 132: SET LINESIZE 132

1. Set the number of lines per page of output. The default is 24: SET PAGESIZE 66
2. Specify zero to get no page breaks in your output: SET PAGESIZE 0
3. Tell SQL*Plus to display the command before executing it: SET ECHO ON
4. Suppress printing of a record count at the end of a query result: SET FEEDBACK OFF
5. Tell SQL*Plus to stop after each page of output until you press Enter: SET PAUSE ON
6. There are about fifty options that can be set. To view all the settings of your current SQL*Plus session, type this command at the SQL> prompt: SHOW ALL

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.