recursive query with select * raises ORA-01789

Question

This is a minimized version of complex recursive query. The query works when columns in recursive member (second part of union all) of recursive CTE are listed explicitly:

with t (c,p) as (
  select 2,1 from dual
), rec (c,p) as (
  select c,p from t
  union all
  select t.c,t.p from rec join t on rec.c = t.p
)
select * from rec

I don't get why error ORA-01789: query block has incorrect number of result columns is raised when specified t.* instead.

with t (c,p) as (
  select 2,1 from dual
), rec (c,p) as (
  select c,p from t
  union all
  select t.* from rec join t on rec.c = t.p
)
select * from rec

Why t.* is not equivalent to t.c,t.p here? Could you please point me to documentation for any reasoning?

UPDATE: reproducible on 11g and 18 (dbfiddle).

Tom&#225;š Z&#225;lusk&#253; · Accepted Answer

I finally asked on AskTom forum and according to response from Oracle expert Connor McDonald, this behavior is in compliance with documentation, namely the sentence The number of column aliases following WITH query_name and the number of columns in the SELECT lists of the anchor and recursive query blocks must be the same which can be found in this paragraph.

The point is, the expansion of star expression is done after checking whether the numbers of columns are same. Hence one must list columns explicitly, shortening to star is not possible.

recursive query with select * raises ORA-01789

Answers (2)

Related Questions