This discussion is archived
4 Replies Latest reply: Jan 23, 2012 12:05 PM by Aketi Jyuuzou RSS

To use "analytic function" at "recursive with clause"

Aketi Jyuuzou Oracle ACE
Currently Being Moderated

The recursive member cannot contain any of the following elements:
・An aggregate function. However, analytic functions are permitted in the select list.

OK I will use analytic function at The recursive member :-)
SQL> select * from v$version;

Oracle Database 11g Release - Production
PL/SQL Release - Production
CORE      Production
TNS for 32-bit Windows: Version - Production
NLSRTL Version - Production

SQL> with rec(Val,TotalRecCnt) as(
  2  select 1,1 from dual
  3  union all
  4  select Val+1,count(*) over()
  5    from rec
  6   where Val+1 <= 5)
  7  select * from rec;
select * from rec
ERROR at line 7:
ORA-32486: unsupported operation in recursive branch of recursive WITH clause
Why ORA-32486 happen ?:|
  • 1. Re: To use "analytic function" at "recursive with clause"
    BobLilly Expert
    Currently Being Moderated
    Hi Aketi,

    It works in, so it is probably a bug:
    select * from v$version
    Oracle Database 11g Enterprise Edition Release - 64bit Production     
    PL/SQL Release - Production                                           
    CORE     Production                                                         
    TNS for IBM/AIX RISC System/6000: Version - Production                
    NLSRTL Version - Production                                           
    with rec(Val,TotalRecCnt) as(
    select 1,1 from dual
    union all
    select Val+1,count(*) over()
    from rec
    where Val+1 <= 5)
    select * from rec
    VAL                    TOTALRECCNT            
    ---------------------- ---------------------- 
    1                      1                      
    2                      1                      
    3                      1                      
    4                      1                      
    5                      1                      
  • 2. Re: To use "analytic function" at "recursive with clause"
    Frank Kulash Guru
    Currently Being Moderated

    It looks like a bug. I tried several other analytic fucntions, and various combinations of PARTITION BY and ORDER BY clauses, and got the same ORA-32486 as you did every time.
    Like you, I used Oracle
  • 3. Re: To use "analytic function" at "recursive with clause"
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    Thanks for replys.

    When I will get,
    I will use below "recursive with clause" which is stopping at NodeCnt.
    create table Edakiri(ID) as
    select RowNum from dict where RowNum <= 10;
    with rec(RootID,ID,Path,NodeCnt) as(
    select ID,ID,to_char(ID),count(*) over()
      from Edakiri
     where ID <= 3
    union all
    select a.RootID,b.ID,a.Path || to_char(b.ID),
    a.nodeSum+count(*) over()
      from rec a,Edakiri b
     where a.ID+1=b.ID
       and a.NodeCnt <= 10)
    select * from rec;
  • 4. Re: To use "analytic function" at "recursive with clause"
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    I have more doubt.
    So I have created new thread at Documentation forum.
    "analytic functions" can be used at "recursive with clause" or not


  • Correct Answers - 10 points
  • Helpful Answers - 5 points