What Makes Oracle SQL Different?

Oracle fully supports the current ANSI/ISO SQL standard, and you can run standards-compliant SQL through SQL*Plus or Enterprise Manager's SQL Worksheet without any special setup. On top of that standard, Oracle layers a large set of proprietary extensions — the kind covered throughout this course. In everyday work, you use both seamlessly in the same query, without ever needing to think about which parts are "standard" and which are "Oracle." Most of the time, that's exactly how it should be.

Occasionally, though, it matters. If you're writing SQL that needs to run unmodified on a non-Oracle database someday, you need a way to know, in advance, which parts of your code are quietly relying on Oracle-only behavior. That's what the rest of this lesson is about.

SQL*Plus includes a built-in compliance checker called FLAGGER. Turn it on, and every SQL statement you enter gets parsed against the ANSI/ISO standard before it runs — if it finds anything that depends on an Oracle-specific extension, it flags it as an error instead of silently letting it through.

SET FLAGGER {OFF | ENTRY | INTERMEDIATE | FULL}

The four levels give you increasingly strict checking, with FULL being the strictest. You can set it two ways: directly at the SQL> prompt with the SET command, or through ALTER SESSION SET FLAGGER = ..., which is the SQL-language equivalent of the same setting. A GUI SQL worksheet will typically expose the same option somewhere in its session or preferences settings, though the exact location varies by tool and version.

To turn on the strictest level from the command line:

SET FLAGGER FULL

One detail worth knowing before you rely on this: FLAGGER doesn't check your SQL against the modern standard — it checks against SQL92, a standard from 1992. That's not a bug or an oversight; it's simply what the feature was built to do and has always done. If you're writing code that leans on SQL features standardized well after 1992 — window functions, recursive `WITH` clauses, and plenty else — FLAGGER won't catch those as non-standard even though they postdate the baseline it checks against. Treat a clean FLAGGER pass as "compatible with 1992's SQL," not "using only modern portable SQL."

Here's what happens once FLAGGER is on and it hits Oracle-specific syntax:

ORA-00097: use of Oracle SQL feature not in SQL92
Full Level

For example, the DECODE function is perfectly valid, everyday Oracle SQL — and also exactly the kind of thing FLAGGER exists to catch, since it's an Oracle extension with no SQL92 equivalent.

A few behavioral notes worth knowing: you can run SET FLAGGER even without an active database connection. Once enabled, it stays in effect for the rest of your SQL*Plus session — across statements, even across a disconnect/reconnect — until you explicitly turn it off with SET FLAGGER OFF (or ALTER SESSION SET FLAGGER = OFF) or exit SQL*Plus entirely. While it's enabled, SQL*Plus will even show a warning on the CONNECT, DISCONNECT, and ALTER SESSION SET FLAGGER commands themselves, whether or not they succeed.

FLAGGER is just one of roughly fifty SQL*Plus environment options you can set with the SET command. The command line is the way to go whenever you're scripting something that needs to run unattended, or when you're working in the command-line version of SQL*Plus rather than a GUI worksheet in the first place. A few of the ones you'll use most:

To see every current setting for your session at once, including FLAGGER, run:

SHOW ALL

Two SQL*Plus errors specifically relate to FLAGGER misuse:

SP2-0575	Use of Oracle SQL feature not in SQL92 Entry/Intermediate/Full Level. Cause: a SQL statement (or an Oracle-specific SQL*Plus feature such as SET AUTOTRACE) was attempted while FIPS flagging was active. Action: turn off FIPS compliance checking with SET FLAGGER OFF, or rewrite the statement to avoid the extension.
SP2-0577	Invalid usage — an unrecognized option was passed to SET FLAGGER. Correct syntax is SET FLAGGER {OFF | ENTRY | INTERMEDIATE | FULL}. Action: specify one of the four valid values.

FLAGGER is set via ALTER SESSION alongside a number of other session-level settings. Table 2-5 lists the full set of clause names available to ALTER SESSION, and what each one accepts:

Notice that FLAGGER shares this table with settings like CONTAINER and EDITION — both squarely modern, multitenant-era features. FLAGGER isn't some abandoned corner of Oracle; it's still a live, maintained part of the same session-configuration surface as Oracle's current architecture.

Standard SQL is what makes your code portable. Oracle's extensions are what make Oracle powerful — and by 26ai, that extension layer is substantial: hierarchical CONNECT BY queries, the (+) outer-join syntax, `DECODE`/`NVL`, and increasingly, newer additions like native AI Vector Search syntax, JSON Relational Duality Views, and a genuine BOOLEAN data type. Almost every real Oracle application, and most of what makes 26ai distinctive as a platform, depends on this extension layer somewhere.

FLAGGER exists for the narrow but real case where portability actually matters more than Oracle-specific power — and now you know how to use it, and just as importantly, what its limits are.

The next lesson concludes this module.