CODEWITHSUNDEEP
oracle-databaseconnectiondblink
Someone112
Someone112

Reputation: 1

ORA-02019:connection description for remote database not found - left join in a view

I have 3 tables:

table1: id, person_code
table2: id, address, person_code_foreing(same with that one from table 1), admission_date_1
table3: id, id_table2, admission_date_2, something

(the tables are fictive)

I'm trying to make a view who takes infos from this 3 tables using left join, i'm doing like this because in the first table i have some record who don't have the person_code in the others tables but I want also this info to be returned by the view:

CREATE OR REPLACE VIEW schema.my_view
   SELECT t1.name, t2.adress, t3.something
   from schema.table1@ambient1 t1
   left join schema.table2@ambient1 t2
    on t1.person_code = t2.person_code_foreing
   left join schema.table3@ambient1 t3
    on t3.id_table2 = t2.id
   and t1.admission_date_1=t2.admission_date_2;

This view needs to be created in another ambient (ambient2).

I tried using a subquery, there I need also a left join to use, and this thing is very confusing because I don't get it, the subquery and the left join are the big no-no?! Or just de left-join?!

Has this happened to anyone? How did you risolved it?

Thanks a lot.

Upvotes: 0

Views: 8289

Answers (2)

Someone112
Someone112

Reputation: 1

I managed to make it work, but apparently ambient2 doesn't like my "left-join", and i used only a subquery and the operator (+), this is how it worked:

    CREATE OR REPLACE VIEW schema.my_view
       SELECT t1.name, all.adress, all.something
       from schema.table1@ambient1 t1,(select * from
    schema.table3@ambient1 t3, schema.table2@ambient1 t2
        where t3.id_table2 = t2.id(+)
       and (t1.admission_date_1=t2.admission_date_2 or t1.admission_date is null)) 
all
        where t1.person_code = t2.person_code_foreing(+);

I tried to test if a query in ambient2 using a right-join works (with 2 tables created there) and it does. I thought there is a problem with that ambient..

For me, there is no sense why in my case this kind of join retrieves that error.

The versions are different?! I don't know, and I don't find any official documentation about that.

Maybe some of you guys have any clue..

There is a mistery for me :))

Thanks.

Upvotes: 0

pmdba
pmdba

Reputation: 7033

ORA-2019 indicates that your database link (@ambient1) does not exist, or is not visible to the current user. You can confirm by checking the ALL_DB_LINKS view, which should list all links to which the user has access:

select owner, db_link from all_db_links;

Also keep in mind that Oracle will perform the joins in the database making the call, not the remote database, so you will almost certainly have to pull the entire contents of all three tables over the network to be written into TEMP for the join and then thrown away, every time you run a query. You will also lose the benefit of any indexes on the data and most likely wind up with full table scans on the temp tables within your local database.

I don't know if this is an option for you, but from a performance perspective and given that it isn't joining with anything in the local database, it would make much more sense to create the view in the remote database and just query that through the database link. That way all of the joins are performed efficiently where the data lives, only the result set is pushed over the network, and your client database SQL becomes much simpler.

Upvotes: 1

Related Questions