In yesterday’s post I talked about you could use SQL Developer’s Describe (SHIFT+F4) to open a PL/SQL Package at your cursor.

You might get an error if you try to describe this…

If you actually try to describe the package as you see it in the above screenshot, you’ll get an error:

Doh!

I neglected to say in yesterday’s post that I was highlighting the package name before I hit SHIFT+F4. This works just fine, but it will work even better in our next release as we’ve fixed this issue.

Until then, you can also try the Ctrl+Hover with your mouse. For PL/SQL calls you can open the source immediately based on what you’re hovering over with your mouse cursor.

You could try this with “dbms_output.put_line(” too

Ctrl+Click, It’s not just for PL/SQL

If you don’t like the floating describe windows you get when you do a SHIFT+F4 on a database object, the ctrl+click will work too. Instead of opening a normal ‘hover’ panel, you’ll be taken directly to the object editor for that table, view, etc.

Go ahead and try it right now.

Paste this into your worksheet, then ctrl+click with your mouse over the table name:

SELECT * FROM
scott.emp

And now you know, the rest of the story.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

45 Comments

  1. kaushik nayak Reply

    Jeff, what is the shortcut to describe for Mac? I tried fn+shift+f4 but it did not work.

    • kaushik Nayak

      Ok. I got it from preferences ->shortcut keys -> (search) describe popup

  2. Hello.
    Sorry for my poor English.

    When I use Ctrl+Click on table name “Q_200” in sql worksheet,
    It opens the describe table of “QM200”.
    I think the sqldeveloper understands the underscore sign of table name as wildcard.

    My sqldeveloper version is 18.1.0.095 build 095.1630.

    Thank you.

  3. Frank Feng Reply

    This used to work until before I updated Sql Developer to 4.2 version. Now it seems NOT working if there is NO owner/schema prefix in front of the code object name. The error message is: “PL/SQL unit *** does not exist”. If there is schema prefix, then it is fine. If it is system object, e.g. DBMS_OUTPUT.PUT_LINE, then the source from SYS schema gets opened.

    I am using Sql Developer Version 4.2.0.17.089 on Mac.

    • I just tried it in a sql worksheet, seems good to me.

      BEGIN
      proc(…

      put mouse over ‘proc’ – it hyperlinked, opened PROC in a code editor. Connection schema = PROC owner.

    • Frank Feng

      Thanks for the quick reply.

      I tried again. It is working on sql worksheet, but not working on pl/sql package code editor window. When I held Command key (Mac), the blue hyperlink did show. But it errors when I clicked it.

    • Again, this is working for me.

      In a procedure, that calls another procedure…I’m able to ctrl-click into that procedure no problem.

    • Frank Feng

      Thanks for the reply. I tried them again – in sql work sheet, procedure, and package body. They are all working now. Weird. I did not change any setting.

  4. Pascal Glauser Reply

    This post is al little bit outdated, but it meets my problem.
    I am on SQL Dev 4.1.1.19 for such al long time that I am even not able to change my habits: I am used to Popup Describe Objects with Shift-F4 without highlighting them first, and it always worked. Since three days, the “No Object Selected” message shows up. When I hightlight the object name (and perhaps the owner) before pressing Shift-F4, the popup describe shows up as desired.
    I browsed through the preferences several times, but didn’t find any match.

    Any help ?

    Pascal

    • on version 4.2 – I don’t have 4.1 installed anymore – i a worksheet:

      SELECT * FROM sh.sales

      With my cursor on sales, Shift+F4 => I get the DESC popup window for the SALES table.

      We run SYS.DBMS_UTILITY.NAME_RESOLVE(‘SH.SALES’, 0) first to see if we can find the object to be described. You can see what we’re doing in View > Log > Statements.

      The first thing I would do is upgrade – we patched 4.1.1 to 4.1.2, 4.1.3, and 4.1.5 – and we just released version 4.2. So you’re more than a few versions out of date.

  5. Frank Feng Reply

    There was a comment from Chris:

    2. If I ctl-click on a procedure/function in a different package it will just open that package up but not navigate to that procedure/function.

    Is there any improvement for this?

  6. Matthew Sultana Reply

    Chris, is there a way for this it work on an object name in a comment. Very often we have table names in comments and it is a nuisance that this feature does not work in such a case

    • once it’s in a comment, our parser treats it as raw text

      but, try alt-G with your cursor on the object name, I think you might like it 🙂

    • Matthew Sultana

      Tried Alt-G. Unfortunately, it does not work on commented text.

  7. When i click Shift+F4 on package name, window will pop up with message No Object Found can you please assist how to open a package in Sql developer

  8. Something is quite weird and broken with that open declaration. I opened some other packages that are in excess of 10,000 lines of plsql code. None, and I mean NONE, of the attempts to use open declaration work. Not on local or remote procedures and functions.

    • Hi Jeff.

      Thanks for following up on this.

      I’ve had 4.1 installed for the past week and have been testing it out.

      Here’s what I’ve found:

      Packages opened within the database.
      1. If I ctl-click on a procedure/function within the same package, it will take me there. So that works.

      2. If I ctl-click on a procedure/function in a different package it will just open that package up but not navigate to that procedure/function.

      It gets interesting when I try to do the above with packages stored in the filesystem. Initially, it didn’t work for 1 and 2 above for our normal packages. The Open Declaration would not even be enabled and pressing the control key would not underline in blue. Then I noticed when testing with bare bones simple test packages, it worked. That was puzzling. What I found out is that for our normal packages, we have comments that come before the CREATE OR REPLACE PACKAGE declaration whereas my test procedures just start out with CREATE OR REPLACE PACKAGE.

      Occasionally I notice that my normal packages would not be recognized as plsql but as a normal script. So in the toolbar there would be no gear icon for compile/compile for debug. It would just have the run icon. I would have to shut down sqldeveloper and reopen it to fix this problem. I think this is related to the problems above.

      I’ve come to the conclusion that if a package starts out with anything other that CREATE, it will cause sqldeveloper to do inconsistent things. Unfortunately, I’m just a developer so I cannot simply go and remove those comments before the CREATE declaration so I’m stuck on version 4.0 and lower.

      I’m not sure what sqldeveloper is doing, is it looking in the file to see if there is a CREATE OR REPLACE declaration?

    • I need to amend my last post. For some reason now, any package opened from the filesystem will have the open declaration disabled no matter if CREATE… is the first line in the file or not. I am at a loss as to why it is not working for me.

    • that’s exactly what it’s doing – check this post out from Vadim on the forums

      Next, if less than 10 words are recognized then no editor association is made. Otherwise, we assume it is PL/SQL or SQL source. Finally, if first 4 words contain PACKAGE, PROCEDURE, or other pl/sql module type, then it is opened with pl/sql editor. Otherwise it is opened with SQL worksheet. For example, if you open file “myfile.s12” which contains “select * from dual where 1=2;”, then it would be opened in worksheet.

    • Okay. that’s what I’m experiencing and I agree. If the extension of the file is defined in the file types preferences, then it should use whatever is defined as regardless of what is actually in the file.

      Thanks.

  9. Yes. I can live with the quick outline docked for now.

    About the open declaration issue… I think it doesn’t work with functions. I have tried it on procedures and it works but I have yet to get it to work with functions…
    Ie. A := package.get_something(param);

    I will test some more tomorrow.

    Thanks.

    • That’s it. Tried it with a function and it’s broken. Will log and make sure it’s taken care of in our next patch. Thanks for sticking with me here.

    • Chris, I’m testing this in our latest dev build. I can see a package with a function call – ctrl+click is NOT working in 4.0.1, but IS working in today’s code. I’ll make sure this gets included in our next patch.

  10. I have the same problem as Leo. When I ctrl+click on a package.function it pops up telling me that the PLSQL unit does not exist. For local functions, it works fine, but not for functions in other packages. All the packages are under the same user and I log in as the user so it is not an access issue.

    I should also mention that the previous version 3 of Sqldeveloper never had this problem.

    Other problems I’ve noticed is that the quick outline pane does not work the same as version 3. For example, in version 3, when I open a plsql file, I usually have the quick outline tab on the left and it immediately shows the procedures and functions of that package. When I open another plsql file, it will update automatically. Switching between these opened files will also update the outline so that it reflects the items of the current package I’m looking at. Now, in version 4, it no longer updates. I have to go and manually right click and select/reselect quick outline for it to update.

    That said, I’m really liking version 4. I especially like the “breadcrumb” type thing at the bottom that shows you the hierarchy of where your cursor is at (ie. package>function>if>if>if) and the ability to move up levels instantly. Some of the code is so long that it takes several seconds of scrolling to navigate to a higher level and this is a godsend. The other feature is when searching for a specific word, the right gutter has markers showing you all the occurrences of that word so a quick glance can tell you how many and where it occurs. This is also invaluable to me.

    Sqldeveloper is great product and is much more intuitive to use than what my other colleagues are using (Toad), but what irritates me the most is that every new version introduces new and bizarre bugs such as the ones I’ve outline above. What was once working no longer works which was why I was stuck on version 3 for the longest time. Thus, if I can’t find a resolution to the open declaration bug, then I may have to revert back to version 3 as that is a pretty important feature for me.

    If you have any workarounds, please let me know.

    Thanks.

    • UPDATE:

      The quick outline pane will not update if it is minimized. If it is permantly docked, it will update to whatever file you switch to. If it is minimized, it will lose this auto-update functionality. Again, this is different from version 3 where it would update regardless of docked/minimized status.

      I usually have it minimized to maximize screen space and only refer to it when I need to navigate to different area in the file.

    • Hmmm, I have a package body, in a procedure, made a reference to a procedure in another package (in the same schema) and it’s working for me.

      I’ll look into the Quick Outline behavior. But here is what I see – as soon as I click into a new package, the outline updates (that is, after I have restored it from being minimized.)

      Are you saying if you click into the plsql object, it doesn’t update?

    • Chris, so in 3.2 the Quick Outline updates, even when minimized, but in 4.0, doesn’t until you click into the Editor. I can log this, but will the work-around suffice? If not, I have another option, to save space, instead of minimizing the outline, ‘Float’ it. It will stay on top of your SQL Developer Desktop, and update as expected.

    • that’s correct. Here is a common scenario, if the Quick Outline is minimized and is showing the contents of the current file, if I open a new file and click anywhere in it, then look at the Quick Outline, it’s still showing the contents of the previous file.

      However, if I perform the same actions, but with the Quick Outline docked, it will update as expected.

    • Right, but if after that, you click in the editor, the Quick Outline updates to the current file, yes?

  11. So this is what I have
    begin package
    procedure main
    package.p1;
    package.p2;
    package.p3;
    package.p4;
    end main
    procedure p1
    …………
    end p1
    procedure p2
    …………
    end p2
    procedure p3
    …………
    end p3
    procedure p4
    …………
    end p4
    end package

    By ctrl+click on package.p1 it should take me to the procedure p1, but it does not. Can you try it this way?

  12. I’m having issues in SQLdeveloper 4.0.1 with “open declaration”/Ctrl+Click inside package. I’m getting PL/SQL unit does not exist. Any idea? Thanks

    • Does your logon user have access to that unit?

      I just tried this in 4.0.1 and it’s working for me. I tried for a local function.

    • I modify and compile packages just fine. It’s when I want to jump from one function inside the package to another. In 3.2 I could of hold CTRL key and mouse over the function then click on it and it would take me to that function even it would be in a different package. I can see the link is being created with blue underline, but when click I would get error msgbox.

    • That’s working for me too, in a local package body. I can ctrl+click to a procedure in the same package…

    • I’m having the same issue (using 4.0.1 at the moment).

      Ctrl+Click seems to work from within the same screen or entity (albeit freezing the entire app when doing this in large packages), but not as a “schema-wide” code search, e.g. navigating from a worksheet to the package method definition.

      Say I am writing a PL/SQL block in a worksheet connected to schema A

      declare
      myVar number;
      begin
      myVar := PCK_MY_PACKAGE.DoStuff();
      end;

      If I Ctrl+Hover over the package function call, it gets transformed as blue, underlined text, but when I click on it, I get the error described above, where I expected to be transported to the method definition (and preferably to the body instead of the spec).

      PCK_MY_PACKAGE.DoStuff() would be a non-ambiguous qualifier in this situation.

      Thanks!

    • This problems happen with me with most SQL Developer 4.0 ( EA1 and plus )
      and some laters version of 3.2 (64bit), my machine is a Lenovo Thinkpad E430. If i work with a previous version before 3.1 (32 bit) it wont freezes.
      Thank you

Write A Comment