Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

[4.0n & 4.1EA] control-clicking tablename or alias leads to "too many objects" warning

B.DelméeMay 30 2014 — edited Mar 17 2015

when I control-click on a tablename or alias I am getting a small popup titled "too many objects" with the following text before the details tab opens

Only 2 records will be displayed.

Please refine your search criteria.

Comments

B.Delmée

Actually it seems this is happening when the system has other objects by the same name,

and SqlDeveloper does not always open the correct one when Ctrl-clicking.

e.g. I reference table "TBL1" in a select, but when ctrl-clicking it from the worksheet,

it might be SOMEOTHERUSR.TBL1 that gets displayed (similar things may happen

with Shift-F4 but there I see no easy way of knowing the schema of the displayed object)

My limited testing suggests that Ctrl-clicking appears to end up displaying the details of the object

that lives in the schema that comes first alphabetically - picking the object from the connexion's

schema (if one exists) would seem like a better default...

unknown-7404

What is your full sql developer version and platform?

Can you post a sample query and tell us which of the objects in it you are clicking on?


B.Delmée

I am on win7 32bits, sqldeveloper  Version 4.0.2.15

to avoid posting actual object names, I'll keep the description close to what I already explained:

Select *

from TBL1

order by created_at desc;

I am connected as user 'Zxyz', the query above returns content from the table TBL1 in the current schema (as it should, the db sql engine picks the correct table), but ctrl-clicking "TBL1" opens Axyz.TBL1, as user Axyz also has a table by the same name, on which I have grants.

If I prefix TBL1 with its owner, the correct one is displayed, but it is cumbersome to have to do that for all table references, and one can easily get confused by the current behaviour where sqldeveloper actually shows the details of a table which may not be the one effectively used at runtime.

Note that there are several schemas with identically-named tables (more than 2), hence I suppose the original symptom mentioned in the subject, but some more investigation led me to notice the wrong object sometimes gets displayed

vipe5

A have the same issue when CTRL + left clicking on a table name.

Is there no workaround? Is this getting fixed?

There was no problem with it in 3.0.04 I was using before...

I logged a bug for this - and we should have the same logic in place for doing the popup-desc (SHIFT+F4) and the ctrl-click stuff going forward, which should make this go away.

B.Delmée

I definitely see this behaviour on 3.1 as well, FWIW. It does not even pick the same schema as under 4.x, so perhaps an "order by" was added or something. BTW the <Shift><F4> description does not actually show which schema it uses; this would be a welcome addition. And please do not make <Ctrl><Click> invoke the popup, as this does not allow viewing data nor pinning the table description tab. Thanks.

vipe5

A new version (4.03.16) was released half a month ago, but the problem persists.

B.Delmée

I am sorry to report that this bug is still present in  Version 4.1.0.17. Hope the fix will be included in the release 4.1 version.

thatJeffSmith-Oracle

So ctrl+click gives you the warning, but DESC in 4.1 does not?

B.Delmée

Yes, exact same behaviour as in original post.And DESC worked in 4.0x, already.

Bugged as 20224822

B.Delmée

Thanks vadim - does a fix have a fighting chance of making it into 4.1 ?

Vadim Tropashko-Oracle

It is fixed and is expected to be in 4.1EA2.

B.Delmée

fixed ? excellent ! expected ? don't tease me, Vadim!

B.Delmée

The problem is stil present in 4.1EA2. I'd argue it is even worse, as there is no more initial popup warning, but a table from a wrong schema gets displayed. <Shift><F4> still seems to pick the correct one, though (or I was lucky; i only have one test case this time). This is really the kind of behaviour that makes one loose trust in a tool!

Vadim Tropashko-Oracle

Can you please give more info? In the statement log you would notice the query like this

select NULL ENTRY, inner.* from (

select null name, -1 LINE, -1 COL, null USAGE,

case when obj.object_type like 'JAVA%' then 'JAVA' else obj.object_type end  TYPE,

...

which is run with bind variables

1="EMPLOYEES", 2="TABLE", 3="EMPLOYEES", 4="VIEW"

Can you please run it in the worksheet and exhibit query result here (and, perhaps, explain why the first record in the result set is not what you want)?

P.S. My test don't show anything wrong, that's why I'm asking. QA closed the bug, which means they confirmed the fix as well.

B.Delmée

Hello Vadim,

thanks for pursuing this and pointing me to that query in the log.

This case (I had forgotten when I posted yesterday) is a little different in the sense that the current schema is altered,

so the object I expect does not belong to USER. Sorry for not making this clear.

I see from the query log that the list of candidate objects is ordered by (CASE WHEN OWNER = USER THEN ' ' ELSE OWNER END)

If you where using SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') instead of plain USER,

that'd solve the issue and work identically when the schema has not been switched.

Note that . <Shift><F4> somehow appears to be doing this already.

Thank you for identifying the problem. The USER is now replaced with SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') -- this amendment should be available in next release (4.1 RC1?).

B.Delmée

hey, great news, thanks !

thatJeffSmith-Oracle

So we have your trust back?

B.Delmée

I sure am happy this one got fixed !

New features are cool, but eliminating bugs and annoyances matters, too.

1 - 21
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 14 2015
Added on May 30 2014
21 comments
4,950 views