Following query fails on ORA-00904
. Why?
with t (id, parent_id) as (
select 1, 2 from dual
)
, wrapper (id, parent_id) as (
select * from t where parent_id = 2
)
, wrapper_with_elements (id, elements) as (
select u.id, sys.odcinumberlist(1) as elements
from wrapper u
)
select (
select cast(collect(cast(ru.id as number)) as sys.odcinumberlist)
from wrapper_with_elements ru
) as agg1
, (
select cast(collect(cast(ru.id as number)) as sys.odcinumberlist)
from wrapper_with_elements ru
) as agg2
from wrapper w
ORA-00904: "PARENT_ID": invalid identifier
Db fiddle for Oracle 11g, however it works same way even on newest Oracle 21c version.
The query comes from more complex practical query and after minimization it became somewhat nonsense. However any attempt of further simplification makes error disappear, namely:
from wrapper
for from t
as it is same datasys.odcinumberlist(1)
for null
or any scalar valuewrapper_with_elements
into from
clause in correlated subqueries of main queryagg1
, agg2
columnsDoes someone has an idea what is going wrong there? Although the query is silly I would expect it to return value.
I suspect this is some Oracle bug related to collection usage (because once I managed to make some workaround run without bind variables but with bind variables it failed with ORA-600: Internal Error Code, Arguments: [qcsfbdnp:1]
which disappeared only after collection handling removal) but I don't want it to be treated as misleading conclusion. In real case I use custom nested table type but the effect is the same, I used sys.odcinumberlist
just to make example easily reproducible.
The problem could be declaring the same column names lists twice in two cte definitions.
t (id, parent_id) as
and
wrapper (id, parent_id) as
If you change the way of naming of columns in just your first cte - so that you give the names of columns within the Select statement instead as a list of columns outside then there is no error.
with t as (
select 1 as id, 2 as parent_id from dual
)
... it is unknown to me how Oracle treats those column names lists declared outside Select statement and how they are (the column names) assigned to a particular CTE. That is problematic in case of multiple CTEs.
Regards...