I am trying to use a cte in a query that copies data from one database to another. The cte is used because I am unable to handle a cycle with connect by.
In this simple illustration the db link used by the insert causes an error (ORA-00942: table or view does not exist).
insert into don.T2@gstest (RELATE_STRING)
with cte (LVL, I, PARENT_I, RELATE) as (
select 1 as LVL,
I,
PARENT_I,
'+' || RELATE as RELATE
from don.T1@gsdev
where PARENT_I is null
union all
select c.LVL + 1,
t.I,
t.PARENT_I,
c.RELATE || '+' || t.RELATE
from cte c
join T1 t
on t.PARENT_I = c.I)
select RELATE from cte order by LVL, I;
The illustration doesn't have a cycle issue, so connect by can be used to demonstrate.
If I ensure that the code is executed from the target database and I remove the db link, the code works.
insert into don.T2 (RELATE_STRING)
...
I was unable to figure out how to make a db link in liveSql.
It looks like you've hit a bug. Though as it looks like you're querying across two databases, so I have to wonder:
Why not run the insert locally on the target database (gstest)?
If you can't because... reasons... you could always work around this splitting the process in two:
- Load the output of the query into a (local) staging table
- Insert into the remote database from this staging table
The cte is used because I am unable to handle a cycle with connect by.
What exactly is the cycle condition? There may be a way to do what you want with connect by nocycle.