Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Don.

Asked: April 28, 2021 - 3:40 pm UTC

Last updated: April 30, 2021 - 9:54 am UTC

Version: 19

Viewed 100+ times

You Asked

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.

with LiveSQL Test Case:

and we said...

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.

Rating

  (1 rating)

Comments

Don Simpson, April 29, 2021 - 5:17 pm UTC

Thank you for the response. Is there a mechanism for getting notified if and when the bug is fixed?

... reasons... is right. Fundamentally, it makes the dba happy to run the code from any of the four databases we use for this product. So if he's logged into A, he can move data from B to C without having to create a new connection.

Instead of staging tables, I'm trying views, but it's really the same principle. In the mean time, living with running the code from the target database is really sufficient.

The problem with nocycle is that it stops there and the cte will push through and continue.
Chris Saxon
April 30, 2021 - 9:54 am UTC

There is no bug logged - I'm not sure exactly what's happening here, so if you want this resolved speak to support to get this raised.

The problem with nocycle is that it stops there and the cte will push through and continue.

I'm not sure what you mean here - both stop processing that row when detecting a cycle, but can process the rest of the data set.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database