Oracle SQL: "missing keyword" when using SELECT INTO with a recursive CTE

Question

Inside a stored procedure, I am trying to set value of a declared varchar(100) variable 'my_path' based on a virtual dataset created via a WITH and recursive CTE statement.

First I tried "SELECT INTO", but got "missing keyword" error.

I can alternatively think of updating a table row with value of 'path' from WITH statement, but then I get "missing select keyword" error and I have understood that I cannot UPDATE with CTE in Oracle SQL server. But is there any way to access the output of recursive CTE then? I need it later in my stored procedure.

declare mypath varchar(100);
begin
with CTE (toN,path,done)
as
(
-- anchor
select
cap.toN,
concat(concat(CAST(cap.fromN as varchar(10)) , ',') , CAST(cap.toN as varchar(10))),
case when cap.toN = 10000 then 1 else 0 end
from cap 
where
(fromN = 1) and (cap.cap_up > 0)

union all
-- recursive
select
cap.toN, 
concat(concat(path,','), CAST(cap.toN as varchar(10)) ),
case when cap.toN=10000 then 1 else 0 end
from cap join cte on 
cap.fromN = cte.toN
where
(cap.cap_up > 0) and (cte.done = 0)
)
select path into mypath from cte where done=1
);
end;

Answer 1

I think your code should work. It does have a lingering closing paren, which is suspicious.

Perhaps some simplification will help:

with CTE(toN, path, done) as (
-- anchor
      select cap.toN, cap.fromN || ',' || cap.toN 
             (case when cap.toN = 10000 then 1 else 0 end)
     from cap 
     where fromN = 1 and cap.cap_up > 0
     union all
     -- recursive
     select cap.toN, path || ',' || cap.toN,
            (case when cap.to = 10000 then 1 else 0 end)
     from cap join
          cte 
          on cap.fromN = cte.toN
     where cap.cap_up > 0 and cte.done = 0
    )
select path into my_path
from cte
where done = 1;