Database Components   «Prev  Next»

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

Setting 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

Setting Environment Options

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


Set FLAGGER using Option Menu selection

  1. Begin in the SQL*Plus window by choosing the Options menu.
  2. Click Environment on the pop-up menu.
  3. You now see the Environment window. The list on the left is all the environment settings available to you while you work in SQL*Plus. Click the down arrow on the scroll bar and scroll down until you see "FLAGGER" in the window.
  4. Select "FLAGGER" to enable the setting boxes for this environment option.
  5. The default setting of FLAGGER is OFF. Click the radio button labeled Custom in the Value box.
  6. Now, type FULL in the text box inside the Value box and click the OK button.
  7. You have returned to the main SQL*Plus window. Let's assume that you have just typed the query you see in the window. Press Enter to execute the query.
  8. You see the resulting error messages caused by the FLAGGER. Even though the query is valid in Oracle, it includes the DECODE function which is an Oracle extension to SQL. 

SET FLAGGER {OFF | ENTRY | INTERMEDIATE | FULL}
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard. 

SET FLAGGER {OFF | ENTRY | INTERMED[IATE] | FULL}

Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard. If any non-standard constructs are found, the Oracle Database Server flags them as errors and displays the violating syntax. This is the equivalent of the SQL language ALTER SESSION SET FLAGGER command.
You may execute SET FLAGGER even if you are not connected to a database. FIPS flagging will remain in effect across SQL*Plus sessions until a SET FLAGGER OFF (or ALTER SESSION SET FLAGGER = OFF) command is successful or you exit SQL*Plus. When FIPS flagging is enabled, SQL*Plus displays a warning for the CONNECT, DISCONNECT, and ALTER SESSION SET FLAGGER commands, even if they are successful.
SP2-0575 Use of Oracle SQL feature not in SQL92 Entry |Intermediate|Full Level
Cause: A SQL statement was attempted that is not FIPS compliant. May also occur if a SQL*Plus feature, for example SET AUTOTRACE, that uses Oracle-specific SQL was turned on when you are using FIPS flagging. Action: Use SET FLAGGER, and turn FIPS compliance checking OFF, or rewrite the statement.
SP2-0577 Usage: 
SET FLAGGER {OFF | ENTRY | INTERMEDIATE | FULL}
Cause: An invalid option was specified in the SET FLAGGER command. Action: Specify a valid option.

Table 2-5 describes the ALTER SESSION command rule settings.
Table 2-5 ALTER SESSION Command Rule Settings
Table 2-5: ALTER SESSION Command Rule Settings

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.

SEMrush Software Target 5SEMrush Software Banner 5