Is "INTEGER" a reserved word? 30 December quiz (1824)

In the 30 December 2010 quiz on valid identifiers, we scored the following choice as correct:

DECLARE
   INTEGER   VARCHAR2 (100) := 'Everything''s Fine!';
BEGIN
   DBMS_OUTPUT.put_line (INTEGER);
END;

In our explanation, we wrote: "INTEGER" is a valid PL/SQL identifier and it is not a reserved word, so you can use it as the name of your variable. It is a datatype declared in the STANDARD package, one of the default packages of PL/SQL. You can, therefore, use that same identifier in your own code.

One player wrote the following:

"When reading your answer on the Quiz of Thursday 30 December 2010 I think it is not (completely) correct. 1) You're saying that INTEGER is 'not' a reserved word, but it is (select * from v$reserved_words). 2) I agree with the answer because it works, but I do not agree with your explanation, because why does the following not work? (on a 10.2 database.) -- This works: declare integer varchar2(100); begin null; end; -- This works to: declare varchar2 number; begin null; end; -- But together it does not work: --ORA-06550: line5, column 11: --PLS-00320: the declaration of the type of this expression is incomplete or malformed declare integer varchar2(100); varchar2 number; begin null; end; -- Also when giving the variable the same name as the (sub)type) does not work either; declare integer integer; begin null; end; declare number number; begin null; end; I hope you have an explanation for this strange behaviour."

I believe that I can provide an explanation.

First, INTEGER is a reserved word in SQL, but not in PL/SQL.

This means, for example, that I cannot create a table named INTEGER or a column in a table named INTEGER.

But in a PL/SQL block, I can name a variable INTEGER. That's because all of the base datatypes in PL/SQL are defined in the STANDARD package, one of two default packages of PL/SQL. The defining of a datatype like INTEGER in STANDARD means that:

1. I can declare a variable to be an integer in either of these two ways:

DECLARE
   l_int INTEGER;
   l_int2 STANDARD.INTEGER;
BEGIN
   NULL;
END;

2. "INTEGER" is "just" a type defined in a package. It is not a reserved word in the PL/SQL language itself. I can "override" the use of that name as a type for something else in my own code, like:

DECLARE
   INTEGER   VARCHAR2 (100) := 'Everything''s Fine!';
BEGIN
   DBMS_OUTPUT.put_line (INTEGER);
END;

When this block is compiled, PL/SQL resolves the reference to "INTEGER" as a variable of type VARCHAR2, and not as the STANDARD.INTEGER type.

As for the usages of seemingly reserved words that cause errors, consider the blocks below, each of which will cause compile errors:

DECLARE
   integer    VARCHAR2 (100);
   varchar2   NUMBER;
BEGIN
   NULL;
END;
/

DECLARE
   integer   INTEGER;
BEGIN
   NULL;
END;

DECLARE
   number   NUMBER;
BEGIN
   NULL;
END;
/

The reason Oracle complains about this code is that it cannot resolve the references to VARCHAR2, INTEGER and NUMBER, respectively in the three blocks. Sure, you can "override" VARCHAR2 in your own block of code, but then you have to use it in a way that is consistent with your override. If I use VARCHAR2 as a datatype, I cannot also use it as a variable name.

The most important thing to remember about the ability to "override" identifiers defined in STANDARD (and DBMS_STANDARD) is that YOU SHOULD NOT DO THIS. Oracle even has a warning defined for this:

PLW-05004: identifier string is also declared in STANDARD or is a SQL builtin

Cause: The indicated identifier was also either: o) declared in package STANDARD, or o) a SQL builtin function, or o) a pseudo-column. This situation can result in name resolution issues since the STANDARD/builtin declaration will be chosen over the local declaration in SQL statements; however the local declaration will be chosen over the STANDARD/builtin declaration outside SQL scope. (See the documentation on PL/SQL name resolution.)

Action: Either: o) rename the given identifier, o) qualify any references to the identifier with the containing scope's name, or o) make sure the intended resolution occurs if using the identifier in SQL scope.