Hello
Using a 21c DB and I'm trying to build a procedure which use a cursor to run over the rows of a query which uses two tables, one local and the other one on a remote database linked using a dblink. For the remote database table employees_time@DEV_FE I have already created the synonym employees_time_FE on the local DB
If the query is wrote using the Full outer join statement I'm getting an ORA-00942 Table or view does not exists, but if the same cursor is declared using the (+) notation it works as expected.
CASE 1: Fails
cursor commitment is
select a.id_project,a.id_owner,nvl(a.owner_month_commitment,0)-nvl(sum(b.time_consumed),0) owner_month_commitmen,nvl(sum(b.time_consumed),0)
from projects a
full outer join employees_time_FE b
on a.id_owner=b.id_employee and
a.id_project=b.id_project and
b.date_report between add_months(trunc(sysdate,'MM'),-1) and trunc(sysdate,'MM')-1
where a.end_date>add_months(trunc(sysdate,'MM'),-1) and
nvl(a.owner_month_commitment,0)>0
having nvl(sum(b.time_consumed),0)<nvl(a.owner_month_commitment,0)
group by a.id_project,a.id_owner,nvl(a.owner_month_commitment,0);
CASE 2: Works ok
cursor commitment is
select a.id_project,a.id_owner,nvl(a.owner_month_commitment,0)-nvl(sum(b.time_consumed),0) owner_month_commitmen,nvl(sum(b.time_consumed),0)
from btr_projects a,btr_employees_time_FE b
where a.end_date>add_months(trunc(sysdate,'MM'),-1) and
nvl(a.owner_month_commitment,0)>0 and
a.id_owner=b.id_employee (+) and
a.id_project=b.id_project (+) and
b.date_report (+) between add_months(trunc(sysdate,'MM'),-1) and trunc(sysdate,'MM')-1
having nvl(sum(b.time_consumed),0)<nvl(a.owner_month_commitment,0)
group by a.id_project,a.id_owner,nvl(a.owner_month_commitment,0);
I've found that if instead of using the synonym for the table, the declarative external object is used, the procedure is created without any issue
CASE 3: Work around
cursor commitment is
select a.id_project,a.id_owner,nvl(a.owner_month_commitment,0)-nvl(sum(b.time_consumed),0) owner_month_commitmen,nvl(sum(b.time_consumed),0)
from btr_projects a
full outer join btr_employees_time@dev_fe b
on a.id_owner=b.id_employee and
a.id_project=b.id_project and
b.date_report between add_months(trunc(sysdate,'MM'),-1) and trunc(sysdate,'MM')-1
where a.end_date>add_months(trunc(sysdate,'MM'),-1) and
nvl(a.owner_month_commitment,0)>0
having nvl(sum(b.time_consumed),0)<nvl(a.owner_month_commitment,0)
group by a.id_project,a.id_owner,nvl(a.owner_month_commitment,0);
This post is just to check if this is a DB release issue or is something that is common for the full outer join statement
Thanks