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

Question

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

Use double quotes. Databases are finicky about identifiers that start with numbers -- or better yet, fix the names so they don't need to be escaped. — Gordon Linoff, Dec 31 '20 at 14:03
This solves it, I had tried unsuccessfully with single quotes. If you make it an answer, I will select it as the solution. — Giuseppe, Dec 31 '20 at 16:23
When PIVOT generates the column names it encloses them in double quotes. You can see the generated SQL by using `dbms_utility.expand_sql_text`. — William Robertson, Jan 02 '21 at 11:02

score 2 · Accepted Answer · answered Dec 31 '20 at 22:40

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.

...

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;

score 1 · Answer 2 · answered Dec 31 '20 at 15:01

One option is that when you are creating the table, don't create columns that start with numbers. When doing a PIVOT, you can alias the values in the IN clause so that your columns produced from the pivot are more user friendly. Try to create your table by using a statement like this one:

  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 AS year_2017, 2018 AS year_2018, 2019 AS year_2019))
   WHERE ordine > 0
ORDER BY ordine;

Thanks but I was not seeking a turnaround or practical solution, but rather to understand the principle by which I am allowed to create a variable starting with a number and then not allowed to select it. — Giuseppe, Dec 31 '20 at 16:21

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

2 Answers2

Database Object Naming Rules