Code Completion Insight with Oracle SQL Developer

thatjeffsmith SQL Developer 83 Comments

Tell Others About This Story:

Most third generation language (3GL) IDEs have trained hackers like me to expect the tool to write some of the code for them. Regular internet folks are already used to having search terms and URLs auto-completed in their browsers. I don’t want to get into a debate over whether this makes us lazier AND dumber, but I DO want to give you a quick overview of what SQL Developer can do in this area.

If you fall in the NOTEPAD – ‘do not put anything into the editor which I do not type myself’ camp, then I feel sorry for you. But also, rest assured that you can disable this feature in SQL Developer.

Turn it off, turn it off!

SQLDev can ‘think’ for you…

Love how it offers JOINS for me too!

…or SQLDev can ‘remember’ for you

auto-complete based on previous work

This is a REALLY nice touch and isn’t something I’ve seen in other database IDEs before. Let’s pretend that you have a database that is ‘missing’ foreign keys – crazy, I know. Instead of having to type out your joins manually, it’s nice that SQLDev can complete those for you now. So you type the join once and only once. The developer wins!

It’s all in the wrist

This is very subtle, and it took me a while to figure it out. What you see will depend on when you rest the keyboard and where. If I type ‘SELECT…WHERE’ and stop, I’ll see history items. If I type the same thing and add a SPACE after the WHERE, I’ll get the regular parsing and guessing what you want next items. If you can’t get the popup to ‘pop up’ when you want it to, you can always ask for it manually with the keyboard shortcut.

Ctrl + Space to get your insight to popup 'on demand'

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

Comments 83

  1. It would be nice that when I type the name of a package and a dot, Insight could show the names of the public procedures/functions of the package.
    For example:
    pkg ABC has procedures ABC1, ABC2, & ABC3.

    In the editor or the worksheet I will type ABC. (or ABC. + Ctrl+SPACE)
    And a popup will show ABC1, ABC2 & ABC3.

    If the procedures has parameters, it will simply add the stub of the parameters.
    For example, after Insight a procedure ABC4 with 2 parameters (pParam1 varchar2(10), pParam2 varchar2(10)) will display:

    ABC.ABC4 (pParam1, pParam2);

  2. Code completion works fine as long as you have an open connection to the data base. While coding there is no need to keep connections open. So, wouldn’t it be nice to have code completion cached? And just update when connected and (meta) data have changed.

    1. thatjeffsmith Post
      Author

      I guess I’d argue with your assertion that there’s no need to be connected while you code – I’m always browsing objects, writing queries, etc.

      Also, we’d have to cache the entire data dictionary to make it available when not connected. I don’t think that would work very well.

  3. Hi, Jeff!
    may be i missed smth, but in one comment you had wrote: “Add schema prefix to your query and then invoke – only the local objects will come up, and when you select one, the schema prefix will be removed.”
    and here is my 2 questions:
    1. I need this prefix. How could I leave it?
    2. When I use another schema prefix and then select table name from popup, schema prefix becomes UPPERcase, but I would like to see it in lowercase. Is there any solution for it?

    P.S. Version 4.1.1.19

    1. thatjeffsmith Post
      Author
  4. Thanks guys.
    Autocomplete behavior (matches from multiple schemas instead of current connection / not putting current connection on top of the list..) is a real pain. Looking forward for this bug fix ( +representing many of my colleagues)

  5. Someone commented above:”. If I have typed enough of the table name, so that the list doesn’t have a scrollbar, then the current schema table is at the top of the list.” This seems to be the case in 4.0.3 and 4.1.1 . It’s actually very annoying. Tables from current schema should under every circumstance be listed on top. I have a couple identic schemas with tables named `leistung_…`Even if I have typed the full name of a table the list is still not short enough that it lists the table from current schema on top. You write “If you want to see less, type more.” – doesn’t work — unless I start with typing the schema: but why would I? I’m logged in as a certain user with a certain default schema.

    1. thatjeffsmith Post
      Author

      Sorry, your description is a bit confusing…you want local schema objects up top, and they appear up top, but they don’t?

      Can you share a screenshot of what you’re talking about?

      1. I follow you Bernhard. It’s what Yannick and I have mentioned earlier. When the user is logged onto a schema, he wants to see that schema’s tables first. Isn’t that it?

        1. thatjeffsmith Post
          Author

          Ok, so I am logged in as ‘sqldevdemo’

          I have an employees table all over my database, including in my login schema.

          I type select * from em

          How is this different than what you are seeing and wanting?

          1. thatjeffsmith Post
            Author
          2. thatjeffsmith Post
            Author

            show me select * from user_tables where table_name like ‘PERSONNEL%’

            note that that is not what we run, but i just want to see for myself that you are logged in as ICE and that ICE actually owns tables that start with that string

            if you open, view > log > statements, you can see what we actually run when you invoke the help

          3. thatjeffsmith Post
            Author

            Ok, so something weird is going on. Assuming you don’t have a login trigger which switches your schema context…I would log a bug. But be ready to share complete details to reproduce, or expect questions like you’ve seen here.

          4. No, there is no login trigger. I can run the statement from ‘Log-statements’ and my current schema doesn’t get selected.

            Here is what is run:
            SELECT ‘SCHEMA’ type, username owner, username object_name, null column_name, null column_id, null data_type
            FROM all_users WHERE rownum <=50
            and username like 'PERSONNEL%'
            union all
            SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type
            FROM all_objects
            WHERE object_type ='TABLE' and object_name not like 'BIN$%' and rownum <=50
            and object_name like 'PERSONNEL%'
            union all
            SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type
            FROM all_objects
            WHERE object_type ='VIEW' and object_name not like 'BIN$%' and rownum <=50
            and object_name like 'PERSONNEL%';

            I'm not sure why it is trying to match username with a table name in the first part of the select(from all_users). Any ideas?

          5. If it did more like this, then it would work:
            SELECT ‘SCHEMA’ type, username owner, username object_name, null column_name, null column_id, null data_type
            FROM all_users WHERE rownum <=50
            and username like 'PERSONNEL%'
            union all
            SELECT object_type type, null, object_name, null column_name, null column_id, null data_type
            FROM user_objects
            WHERE object_type ='TABLE' and object_name not like 'BIN$%' and rownum <=50
            and object_name like 'PERSONNEL%'
            UNION ALL
            SELECT object_type type, null, object_name, null column_name, null column_id, null data_type
            FROM user_objects
            WHERE object_type ='VIEW' and object_name not like 'BIN$%' and rownum <=50
            and object_name like 'PERSONNEL%'
            UNION ALL
            SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type
            FROM all_objects
            WHERE object_type ='TABLE' and object_name not like 'BIN$%' and rownum <=50
            and object_name like 'PERSONNEL%'
            union all
            SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type
            FROM all_objects
            WHERE object_type ='VIEW' and object_name not like 'BIN$%' and rownum <=50
            and object_name like 'PERSONNEL%';

  6. yep and they disable that option by default but i always used to enable it since it was very very handy – a lot of times you remember one of these keywords. Same in healthcare space – we have thousands of tables but normally tables have one of the keywords that many people know by memory.

  7. Thank you for your article.
    Can SQL Developer autocomplete by first letters? For example, I input the letter j and SQL Developer shows tables job and job_history.
    At this moment i’m trying to input a first letter, but SQL Developer doesn’t want to autocomplete…

    1. thatjeffsmith Post
      Author
      1. as I remember there is a setting in properties that would suppress pop-up window if more than 50 matches are found – i think 50 is a default. One of my databases has over 500 tables starting with e 🙂

        1. thatjeffsmith Post
          Author
          1. thanks for clarification, Jeff. good to know.

            Btw any plans to use partial keywords? I really miss that feature from SQL Server SQL Assistant add-on. Basically if you type a partial keyword like order it would suggest all tables with “order” keyword regardless if order keyword is at the beginning, middle or end of the table name, so it will suggest:

            order
            exception_order
            work_order_progress

          2. thatjeffsmith Post
            Author
  8. thatjeffsmith Post
    Author
    1. Thanks Jeff, having TAB select and populate the first drop down option would be a big time saver.

      Especially with snippets as they are generally the only option anyway.

  9. Sorry ended too soon,

    For example if I type

    sel

    I see the SELECT * FROM pop up, but to get the complete I have to hit DOWN then ENTER. By that time I could have just typed it out. Would be cool if we could do

    sel[TAB]

    and have it do the completion and then be able to just keep typing.

  10. It would be nice if we could get an option to use tab to autocomplete the first option instead of ENTER, I think that is something more people are used to doing.

  11. While writing a query like suppose “select * from ” and as per the completion insight if I hit Ctrl+space it should show me the table names right, but Its not showing me anything even on repeated trails.

    1. thatjeffsmith Post
      Author
      1. Hi jeff,

        Even I logged in as the user ‘HR’ but am not getting the HR related tables or views when I press ctrl+space.

        BTW, when I write ssf, I can see the ‘select * from Table’ statement as a suggestion similarly for create table/function/procedure. But it’s not showing me only the tables/views.

      1. thatjeffsmith Post
        Author
        1. not really happy with IntelliSense as well and i had high hopes for 4.1.

          first, it is very very slow even after changing delay to 0.1 sec.

          Second, why ctrl-enter not tab like in all other tools?

          third, and this bugs me most, when you have more than 1-2 queries and these queries are complicated, it just stops working. Sometimes it works sometimes it does not.

          this is the number 1 thing i was looking forward to get improved from 4.1 and unfortunately it is still pretty bad.

          1. thatjeffsmith Post
            Author

            If it’s very very slow – have you considered your db might be far far away or it might be slow slow slow to respond to our queries we use to populate the insight lists?

            We use ctrl-enter…because? That’s just what we use.

            If you don’t like the keyboard shortcut, change it. It’s ‘completion insight’ in Tools – Preferences – Keyboard Shortcuts. Then map ‘insert tab’ to ctrl-enter and you’re good to go.

            ‘Queries are complicated’ – no idea. Give me a code sample. It’s very dependent on our parser. If there’s invalid sql in there, or you’re not using statement delimiters to separate them, it probably gets confused.

            Give me examples using HR schema objects, and I can log a bug, if there is one.

            We can’t improve it based on your experience if you never share your experience with us. So I really appreciate your feedback here and hope to get it right for you in our 4.1 patch we’re working on now.

          2. thanks for your reply, Jeff. sorry if my message did not come across well – i am a big fan of your blog and we all really appreciate your team’s work improving SqlDev. In fact our other team was going to purchase TOAD and I begged them to try SQLDev explaining that it is very different from version 1 which everyone hated and that it works great now and became a really mature tool.

            yes, I work with very very large and complex EHR database, thousands of tables and it is on remote server but on very fast network. I think we have over 50Tb of data if it matters..

            Your question makes me wonder now – SQLDev does not cache the metadata and would query target database for every code insight request? that does not seem like a good idea – all third-party tools I’ve worked before actually would cache metadata upfront or in the background. I guess this could be a reason why it does not always work for me if it waits every time for request to get back.

            I set code insight pop-up time to minimum 0.1 sec and a lot of times pop-up would not even show up so i have to press ctrl-space. I just changed it to 0.6 sec and pop-up now pops up more consistently. That was another annoyance for me to press ctrl-space almost always and i guess i just need to wait a bit more.

            As for the query example, it is tough to replicate my queries to our EHR, since demo schemas are very simple. But I just tried the queries below on Oracle Dev Day VM and while most of the time code insight would work (after I set delay to 0.5 sec), it started to misbehave when i started to type where clause – just try to start typing t. and do it 10 times back and forth – in my case it would work 8 times out of 10. The other 2 times popup would not show-up even on ctrl-space. This is very simple query – normally i have to work with much complicated queries, with 15-20 joins, analytical functions, CTEs and subqueries. At some point code insight would just stop working. sometimes it helps to delete the last typed keyword and type it again and hit ctrl-space but sometimes nothing helps and i had to disconnect and connect to make code insight work again.

            SELECT * FROM SCOTT.EMP;

            select * from HR.COUNTRIES, HR.REGIONS;

            SELECT * FROM SH.SALES f
            inner join SH.CUSTOMERS cust
            on f.CUST_ID = cust.CUST_ID
            inner join SH.PRODUCTS prod
            on prod.PROD_ID = f.PROD_ID
            inner join SH.CHANNELS ch
            on f.CHANNEL_ID = ch.CHANNEL_ID
            inner join SH.PROMOTIONS promo
            on promo.PROMO_ID = f.PROMO_ID
            inner join sh.times t
            on f.TIME_ID = t.time_id
            WHERE t.
            ;

  12. Hi Jeff. When the table I want is highlighted in the completion insight I have to hit Enter to select it. How do I change this to the Tab key?

    1. thatjeffsmith Post
      Author
  13. Is there a keyboard shortcut to select the first (or only…) item in the list when it pops up? Other (unnamed…ahem….) apps allow me to hit the tab or enter key to select the first item in the popup so I can just continue typing without having to go to the mouse to click to select…is really nice not to have to click or arrow down the list then enter…lazy? Maybe, but just one of those nice-to-haves…

    1. thatjeffsmith Post
      Author
      1. i watch this gif several times and try, but why i cannot reproduce your scenario?i enter ‘ctrl+space’, it pops up, but then i enter ‘tab’,nothing happens?! this is exactly what we want,please solve my problem

        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  14. I am seeing all of the schemas in the DB. And we have so many schemas that I don’t see the schema that I am currently connected to. Well I do, if I type 95% of the tablename(then the pop-up list is small enough). How do I get SQL Developer to only show those tables that are in my schema? I don’t care about the other schemas

    1. thatjeffsmith Post
      Author

      You should be only seeing the objects in your schema…I’m assuming you have something like

      > select * from

      when you try to invoke the helper?

      Who are you logged in as, the schema owner, or someone like SYSTEM/SYS?

      1. Hi Jeff. Thanks for the quick response.
        That’s correct: I type ‘select * from personnel’ and press ‘CTRL-Enter’. Then I get all tables that start with ‘personnel’ for every schema in the DB. The list is so long, that I don’t see all the schemas or even my own tables. If I type a bit more, ‘personnelce’, then the list gets narrowed down enough so that my own schema tables show up at the top. However, all other schemas are there as well.
        Yes, I’m logged on as the schema owner and not as SYS. The user I’m logged on as has the DBA role.

        1. thatjeffsmith Post
          Author
          1. That will work, but I don’t like that solution at all. I don’t remember it being like this in the 3.x version of SQL Developer. If you ask me, it’s a bug. When I’m in a schema, I don’t care about the tables in the other schemas. And the few times that I do, then I will prefix those. There must be a way to turn this ‘feature’ off.

          2. thatjeffsmith Post
            Author
          3. Hi Jeff. Any chance that you guys can add a ‘Show objects in connected schema only’ checkbox to the ‘Completion Insight’ preferences? It would make it soooooo much nicer.
            Thanks

          4. I would also really appreciate an option to only show (or at least prioritize) the objects of the current schema in the code completion.

            Most of our development users see all schemas of the dev. database, thus typing a table name without the current schema prefix yields a long list of the same object in other users schemas. (we use SQLDev Version 4.0.2.15)

            Apart from this little annoyance, Completion Insight is a great feature. Thanks a lot.

          5. thatjeffsmith Post
            Author

            The more you type, the less you’ll see in terms of ‘hits’ – you can also change the filter to kick in if there are more than X suggestions. I think in 4.0.3 we default to 10.

          6. So, there are other people who hate that other schemas are shown. If I want to find data in another schema, then I have an extra tab open in SQL Developer with that schema. What is the point of having code completion if I have to type over 90% of the word? I just don’t understand why this was introduced in 4.x. Jeff, you guys must be able to fix this. Pretty please with sugar on top.

          7. thatjeffsmith Post
            Author

            I’m listening.

            So I’m playing with this in v4.1.

            If I type
            SELECT * FROM – I only see local objects.
            SELECT * FROM F
            – I see local schema objects that start with F, followed by other schemas’ objects that start with F.

            Is that what you’re seeing? And you just don’t want to see the other schema objects that match the search term?

            I could turn this off, but then the other half of users would probably be upset…

          8. Hi Jeff. Thanks for the quick reply.

            Jeff wrote: “SELECT * FROM F – I see local schema objects that start with F, followed by other schemas’ objects that start with F. ”

            This is exactly what i would expect to happen, but it doesn’t in my case.
            When I type SELECT * FROM BLA_ i get only objects that are in other schemas starting with BLA_. There are too many results to suggest them all, so it shortens the list with a […] entry at the bottom of the list as expected. But non of the displayed matches are from my own schema.

            If i type almost 90% of the table name and there are less than the maximum of results, it displays the object in my table first in the list and only then the remaining objects from other schemas. Just as expected.

            It seems that this might be a bug in related to when there are more than the maximum of results that can be displayed in the list. It seems to prioritizes objects of other schemas when creating the subset of matches from the total.

            I hope this analysis helps.

          9. thatjeffsmith Post
            Author
          10. I just tested it with SQLDev Version 4.0.3.16 (Build MAIN-16.84). Once with the imported properties from 4.0.2, and once with a fresh configuration.

            The same problem with Code Completion Insight still occures.
            As soon as there are more than 50 possible suggestions, the results no longer get sorted properly.

            Instead of sorting all possible results by [objects of current schema > objects of other schemas > alphabetical order] it almost looks like SQLDev takes the first 50 suggestions of the unsorted result and only then sorts that subset alphabetically.
            Unfortunately the chosen 50 suggestions are all from an other schema and not the current schema.

          11. thatjeffsmith Post
            Author
          12. Bug or no bug, will it be ‘fixed’ in a coming release? My schema names are a lot longer than ‘hr’, so typing them is out of the question. I have 20 schemas that all have the same tables, so my list can be quite long.
            Thanks, Thomas

          13. thatjeffsmith Post
            Author
          14. I checked it with 4.1 just now and it still behaves the same way.
            I’ll post my bug report on the forums. Thanks for the hint.

          15. Well, that’s a drag. Thanks for posting the bug report Yannick. Hopefully, there will be a fix in a few months!

        2. Always so fast at responding you are! When I type
          Select * from per
          then I get all the schemas in alphabetical order that have a table starting with per. Many times, the table I want is so far down I have to scroll.
          I will have to double-check on Monday(when I’m back in the office) if I am running the absolute latest version of SQL Developer.
          If it always listed current schema tables first, then I would be happy. Or, if there was a checkbox in the Code Completion options, ‘Only show connected schemas objects’ (or something similar), then the user could choose to have them or not.
          Thanks for listening.

          1. I think Yannick has found the problem. It’s only when the list is too long that it puts other schemas first(at the top). If I have typed enough of the table name, so that the list doesn’t have a scrollbar, then the current schema table is at the top of the list.

            I also just checked the version I am running. 4.0.3.16.84

            Hope this helps.

  15. Whenever I press Ctrl + Space I got the table names along with list of usernames which are not required. How to remove the username from the code completion sight?
    For e.g., USER_NAME.TABLE_NAME

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author

          You should only see the schema names if you’re selecting objects from a schema different than your login. If you’re not seeing this, please send me a screenshot.

          SELECT * FROM USER_TABLES and then where SQLDev is adding your schema to the table list in the completion popup list.

  16. I love Completion Insight but I am trying to figure out how to get regexp_like(, ”, ‘i’) appear in the list when I am entering a where clause. Can you please help me out with this?

    1. thatjeffsmith Post
      Author

      I don’t think you can…that’s not actually a pl/sql object in the database I don’t think, it’s hard-coded just as ‘LIKE’ is.

      So if you created a code snippet instead, you should be able to get to it via Code Completion.

  17. It gets nicer each release… but the code completion doesn’t work for MySQL unfortunately. Oh well.. you get what you pay for 🙂

    1. JeffS Post
      Author

      It’s nice to have a quick-n-dirty scratchpad to pull data back from MySQL or DB2, but we’re not intending for SQL Developer to be a generic, one-size-fits-all RDBMS IDE.

      Feel free to raise your bar, I prefer to exceed the end-user’s expectations!

Leave a Reply

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