Invalid identifier error ORA-00904 when trying to use && substitution variable

Question

I used the following code to define a couple substitution variables

Select '&&my_name' "my name", '&&my_birthday' "date born" from dual;

I want to use the date_born variable in subsequent calls in the select statement but keep getting the ORA-00904 error. Below is the code I'm trying:

Select '&&my_name' "my name", '&&my_birthday' "date born", TO_DATE("date born") from dual;

or

Select '&&my_name' "my name", '&&my_birthday' "date born", TO_DATE(my_birthday) from dual;

Any ideas? I must be defining or calling the variable or column incorrectly.

Answer 1

DUAL doesn't have a "date born" column.

Try this:

with demo (my_name, date_born) as
     ( select '&&my_name', '&&my_birthday' from dual )
select my_name, to_date(date_born)
from   demo;

or this

select my_name, to_date(date_born)
from   ( select '&&my_name' as my_name, '&&my_birthday' as date_born from dual );

I have used standard naming rather than double-quoting everything.

to_date(date_born) is a bit of a gamble on the date format and language happening to match your session defaults. I would prefer to specify a date format, for example by ensuring that '&&my_birthday' is formatted as YYYY-MM-DD and then

select '&&my_name' as my_name, date '&&my_birthday' as date_born from dual;

Edit: alternatively, as lad2025 pointed out, since you are using && you can use the same variable multiple times in the same statement.