SQL Developer: Describe versus Ctrl+Click to Open Database Objects

thatjeffsmith SQL Developer 35 Comments

Tell Others About This Story:

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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 35

  1. 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?

  2. 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

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  3. 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

  4. 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.

    1. thatjeffsmith Post
      Author
      1. 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?

        1. 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.

        2. thatjeffsmith Post
          Author

          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.

          1. 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.

  5. 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.

    1. thatjeffsmith Post
      Author
      1. thatjeffsmith Post
        Author

        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.

          1. thatjeffsmith Post
            Author
  6. 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.

    1. 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.

    2. thatjeffsmith Post
      Author

      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?

      1. thatjeffsmith Post
        Author

        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.

      2. 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.

        1. thatjeffsmith Post
          Author
  7. 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?

  8. 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

    1. thatjeffsmith Post
      Author
      1. 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.

        1. thatjeffsmith Post
          Author
          1. 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!

    1. thatjeffsmith Post
      Author
      1. 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

        1. thatjeffsmith Post
          Author

Leave a Reply

Your email address will not be published. Required fields are marked *