msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Go ahead, turn your FIPS flagging on

Since my last post about the undocumented pragmas in Oracle, I have found more information about the FIPSFLAG pragma directive and more specifically about the FIPS part of it. Jens commented about the existence of a session parameter FLAGGER and its possible relationship with FIPSFLAG and FIPS. After a bit of searching, here is what I found:

Identifying Extensions to SQL92 (FIPS Flagging)

From chapter 5 of Oracle Database Application Developer’s Guide Fundamentals – 10g Release 1 (10.1) (I could not find it in 10gR2):

The Federal Information Processing Standard for SQL (FIPS 127-2) requires a way to identify SQL statements that use vendor-supplied extensions. Oracle Database provides a FIPS flagger to help you write portable applications.

When FIPS flagging is active, your SQL statements are checked to see whether they include extensions that go beyond the ANSI/ISO SQL92 standard. If any non-standard constructs are found, then Oracle Database flags them as errors and displays the violating syntax.

The FIPS flagging feature supports flagging through interactive SQL statements submitted using Oracle Enterprise Manager or SQLPlus. The Oracle precompilers and SQLModule also support FIPS flagging of embedded and module language SQL.

When flagging is on and non-standard SQL is encountered, the following message is returned:

ORA-00097: Use of Oracle SQL feature not in SQL92 level Level

Where level can be either ENTRY, INTERMEDIATE, or FULL.

Indeed, there is the ALTER SESSION SET FLAGGER statement that lets you specify the FIPS flagging.

ALTER SESSION SET FLAGGER

From the Oracle Database SQL Reference 10g Release 2 (10.2) – ALTER SESSION:

FLAGGER can be equal to ENTRY, INTERMEDIATE, FULL or OFF.

Turing FIPS flagging on causes an error message to be generated when a SQL statement issued is an extension of ANSI SQL92. FLAGGER is a session parameter only, not an initialization parameter.

In Oracle Database, there is currently no difference between ENTRY, INTERMEDIATE, or FULL level flagging. After flagging is set in a session, a subsequent ALTER SESSION SET FLAGGER statement will work, but generates the error message ORA-00097. This allows FIPS flagging to be altered without disconnecting the session. OFF turns off flagging.

See it in action

James Koopmann from Ittoolbox demonstrated Oracle’s behavior when FIPS flagging is turned on:

SQL> CREATE TABLE t (
  2  c CHAR(1),
  3  n NUMBER)
  4  /

Table created.

SQL> INSERT INTO t VALUES ('1',1)
  2  /

1 row created.

SQL> SELECT * FROM t WHERE c = '1'
  2  /

C         N
- ---------
1         1

SQL> SELECT * FROM t WHERE n = '1'
  2  /

C         N
- ---------
1         1

SQL> ALTER SESSION SET FLAGGER=FULL
  2  /

Session altered.

SQL> SELECT * FROM t WHERE c = '1'
  2  /
SELECT * FROM t WHERE c = '1'
                            *
ERROR at line 1:
ORA-00097: use of Oracle SQL feature not in SQL92 Full Level
ORA-06550: line 2, column 25:
PLS-01454: No operator may be used with values of data type CHAR


SQL> SELECT * FROM t WHERE n = '1'
  2  /
SELECT * FROM t WHERE n = '1'
                            *
ERROR at line 1:
ORA-00097: use of Oracle SQL feature not in SQL92 Full Level
ORA-06550: line 2, column 27:
PLS-01451: The data types of these <value expressions> must be comparable

Now the question is, who dares to turn FIPS flagging on?


Filed in Oracle on 29 Jun 06 | Tags: , ,


Comments are closed.