sqloracle-databaseinvalid-charactersquoted-identifierora-00911

Oracle SQL, column name gives ORA-00911: invalid character


I created a table through a pivot statement, which automatically creates some variable names that start with a number.

create table MYTAB as
select *
from (select x, anno, v, delta from tab_a13_2 where anno in(2017,2018,2019))
pivot(sum(v)  as v, sum(delta) as d for anno in (2017,2018,2019)) 
where ordine > 0
order by ordine;

select * from MYTAB;
x 2017_V    2017_D  2018_V  2018_D  2019_V  2019_D
1   1.01    -3.18     1.04   11.18    0.96   -6.87
2   1.28     0.09     1.28    7.33    1.25   -1.49
...

However, if I try to specify the column names in a select, I get this error:

select x,
       2017_V, 2018_V, 2019_V,
       2017_D, 2018_D, 2019_D 
from MYTAB;

Error at line 5:
ORA-00911: invalid character
           2017_V, 2018_V, 2019_V,
               ^
1 statement failed.

I don't get it. Either I am not allowed to create column names that start with a number, and therefore the table creation should fail, or I should be able to use them. I checked that the column names are not quoted, i.e. '2017_V'.


Solution

  • From the Database Object Names and Qualifiers documentation:

    Database Object Naming Rules

    Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.

    • A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.
    • A nonquoted identifier is not surrounded by any punctuation.

    ...

    1. Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.

    You have an identifier that begins with a number. This tells you that the solution is to use a quoted identifier and surround the column name with double quotes:

    select x,
           "2017_V",
           "2018_V",
           "2019_V",
           "2017_D",
           "2018_D",
           "2019_D" 
    from   MYTAB;