Unexpected behaviour of Linked Query

[Michael D]

I just found some rather strange behaviour when using the Linked Query function from the SQL window. I found it in PL/SQL Developer 11 but the same thing happens in PL/SQL Developer 10.

Consider this query in Oracle's HR schema:

select dept.*
from   departments         dept
,      employees           emp
where  emp.department_id = dept.department_id;

The Linked Query button shows the parent and child tables of the DEPARTMENTS table, which is what I expect, since that is the table showing in the result grid.

Now take a look at this version of the query:

select dept.*
from   employees           emp
,      departments         dept
where  emp.department_id = dept.department_id;

Notice that the two tables are swapped but the same columns are selected. Since I'm selecting from the same table, I would expect the Linked Query button to show the tables linked to DEPARTMENTS again. In this case however, the parent and child tables of the EMPLOYEES table are shown.

So, instead of showing the tables linked to the selected data, it shows the tables linked to whatever table comes first in the FROM-clause. I did several other tests, with three and four tables in the FROM clause and columns from more than one table in the SELECT list. All tests show the same issue.

Question is, which links to show when there are more tables in the SELECT list You cannot show them all, so maybe for that reason, this is intended behaviour.
In the following case however, this issue results in an error message:

select dept.*
from   locations           loc
,      departments         dept
,      employees           emp
where  emp.department_id = dept.department_id
and    loc.location_id   = dept.location_id;

The linked query button shows the tables linked to LOCATIONS. When I select the parent of LOCATIONS (COUNTRIES), the linked query results in an error:

select * from COUNTRIES t
where t.country_id = :m_country_id

ORA-01008: Not all variables bound.

The error is understandable, of course, since COUNTRY_ID is not in the select list but in my opinion, this linked query should not be possible to select.

Best regards,
Michael Doel
Truston Solutions

 

[Marco Kalter]

We'll enhance this.

 

[Michael D]

Has this issue ever been looked into? It's still present in PL/SQL Developer 15.

Best regards,
Michael Doel
Truston Solutions