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'

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.

111 Comments

  1. Hi Jeff,

    I am using Oracle Developer 17.2.0.188 and completion insight doesn’t work in the PACKAGE it self.
    Create some PL/SQL package and then open it. Try to use completion insight, it won’t work.

    Is it bug or feature?

    Thank you!

  2. Thomas Whelan Reply

    Hi Jeff
    Just tested code completion in 17.2.0.188 Build 188.1159
    It has unfortunately gotten worse. Now when I do the ‘Select * from pers’ [CTRL-Space] , I am getting packages and functions and even sequences, but no tables. I would guess that most people would just like to see the tables in their own schema when they hit CTRL-Space after writing ‘Select * from xxx’. I know that I don’t care about data in other schemas. And if I do need to see data in other schemas, then I log into that schema also. So, the solution is definitely not, “Just put the schema name first”. Perhaps there are some hidden parameters: ‘After Select … From, then show me tables’ and ‘Only show my schema objects’?
    Going back to 4.1.5 for now 🙁

    • I believe it’s always worked that way. I’ll create a PERS% table and test in a local schema.

    • Thomas Whelan

      Hi Jeff
      Just tested again. When I start a new worksheet (CTRL-Shift-N), then it is working as in 4.1.5. That is, it will show me the tables, but not the tables in my schema…only in other schemas. The popup list is so long that there isn’t enough room for the tables in my schema. This is why I think it should always show the connected schema’s objects first. 🙂
      Now I know why code completion gives me Packages, Functions, Sequences. Something is confusing SQL Developer there. That is, if I have an invalid SQL query(with a ‘;’ at the end of the query) above the line that I am trying to do Code Completion on, then I get Packages, Functions, Sequences. So, if I have
      ‘select msn, actype
      from acreg a, flightleg f
      where a.msn = f.msn and;’
      then code completion doesn’t quite work. This is not a big problem and something I can live with. It’s the ‘polution’ of table names from other schemas that is the real problem 🙂

    • That’s exactly how it’s supposed to work though – objects in connected schema shown first. That was a bug in earlier versions, it’s working (for me at least) in version 17.2.

      Broken SQL above the query can and will confuse the parser and will make things like code completion unreliable. So try to finish them off with a ‘;’ or comment them out.

    • Thomas Whelan

      Hmmm….It works in some schemas, but not others. There doesn’t seem to be an order by.

    • It’s ordered client side in the code I’m sure. I’ve shown you an example that works. I need you to SHOW ME an example that doesn’t work. Only then can I help you. Which I’m happy to do. I hate bugs.

    • Thomas Whelan

      Hi Jeff
      You can see the screendump here:
      https://postimg.org/image/ykt6ivvc5/

      And here is what I have from the log:
      select ‘TABLE’ type, ”||o.owner# owner, name object_name, null column_name, null column_id, null data_type FROM sys.obj$ o
      where o.name not like ‘BIN%’
      and o.type# = 2
      and rownum <=50
      and name like ?
      union all
      select 'VIEW' type, ''||o.owner# owner, name object_name, null column_name, null column_id, null data_type FROM sys.obj$ o
      where o.name not like 'BIN%'
      and o.type# = 4
      and rownum <=50
      and name like ?
      union all
      select 'TABLE' type, owner, object_name, null column_name, null column_id, null data_type
      from dba_objects
      where object_name like ? and object_type = 'SYNONYM'
      and rownum <=50
      and owner in (SYS_CONTEXT('USERENV','CURRENT_SCHEMA') ,'PUBLIC') 1="PERS%", 2="PERS%", 3="PERS%"

    • I can’t see any of the local schema objects, assuming the schema name matches your connection name?

    • Thomas Whelan

      Yes, the connection name and schema name are the same. I get to see that schema’s objects if I scroll to the bottom of the list and double-click the ‘. . .’
      This behavior doesn’t happen in all of my schemas.

    • >>This behavior doesn’t happen in all of my schemas.
      What can you tell me about the schemas it does not work on as opposed to the ones where it does? Proxy connections? Different roles/privs?

    • Thomas Whelan

      The schemas are identical. No proxies. They get created, then they get the DBA role.

    • Something must be different…it could even be the name. End of the day, I need a reproducible test case.

    • Thomas Whelan

      Hi Jeff, I can give you a test case. You don’t see this behaviour because
      1) You only have 2 tables that start with ‘pers’ and
      2) You don’t have many schemas with tables that start with ‘pers’
      Please have a look at this screenshot from schema grl201504:
      https://postimg.org/image/qnld5n90l/

      Now look what happens when I add an ‘F’ to that select:
      https://postimg.org/image/8ytme0x9h/

      So, the problem has to do with too many hits I think. There must be some ordering done after the select (the one from sys.obj$), because the select itself has no order by. When I run that select in SQL Developer, then the results come in owner# order, which is definitely not the order that I want them to be in. However they are ordered correctly when there are not that many tables that have to be returned. Most schemas I have ever worked with have well over 500 tables. How about adding a
      select ‘TABLE’ type, ”||o.owner# owner, name object_name, null column_name, null column_id, null data_type
      FROM sys.obj$ o
      where o.name not like ‘BIN%’
      and o.type# = 2
      and rownum <=50
      and name like 'PERS%'
      and owner# in (SYS_CONTEXT('USERENV','SESSION_USERID'))
      union all
      at the top of the select that SQL Developer is running?

    • Wasn’t ignoring this, it just got stuck in my spam box. Will take a look on Monday, and thanks for the extra digging!

    • Thomas Whelan

      Thanks Jeff. Were you able to make any headway?
      I find that the SQL formatting is different in 17.2. There is no ‘Number of Commas per Line’ option. Obviously this is another topic, but where should I post this?

    • No, I got distracted by this little event called Oracle Open World. I’ll give it a look see today. No promises.

      We deprecated that formatting preference as we weren’t seeing anyone use it.

      You can set this basically by turning off line breaks on commas and setting max line size in the formatter to force a line break.

    • Thomas Whelan

      Great. Looking forward to it. I’ll move the formatting question to another thread.

  3. 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);

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

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

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

  6. 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)

  7. Bernhard Döbler Reply

    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.

    • 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?

    • Thomas Whelan

      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?

    • 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?

    • 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

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

    • Thomas Whelan

      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?

    • Thomas Whelan

      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%';

  8. boris tyukin Reply

    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.

  9. 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…

    • boris tyukin

      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 🙂

    • the default is ’10’ – but that only applies when doing the automatic pop-ups…ctrl+space will invoke the auto-complete no matter the number of matches

    • boris tyukin

      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

    • no plans – you’re the first to ask for that…could be a more expensive data dictionary query, but it’s an interesting idea

  10. >>By that time I could have just typed it out.
    I would normally just call shenanigans on this, but I’ll just see if we can’t make TAB work 🙂

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

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

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

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

    • Who are you logged in as?

      ‘Works for me’ – this in in 4.1 EA2.

      In this screenshot, I’m logged in as user ‘HR’ – so I’m seeing HR’s tables and views.

    • srinivas

      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.

  14. I upgraded my sql developer to the version 4.0.3.16, but the completion insight is not working for me.. 🙁

    • Srinivas

      Sometimes it is working and sometimes it’s not..!!

    • Boris Tyukin

      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.

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

    • Boris Tyukin

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

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

  16. 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…

    • 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

    • Need small help,
      Why I need to give schema_name(HR) for popup.
      Even I have logged in with HR.

      Working:select * from HR.EMP………..HR.EMPLOYEE
      Not Working:select * from EMP…… no popup.

      Please reply

    • yeah Jeff…I am ready to upgrade.
      please let me know the version to be installed.

  17. Hi Jeff !
    it doesn’t work when input a package name.
    by Mac 4.0.3.16 version
    can you help me?

    • Hmmm, it’s working for me.

      Try this in a worksheet:

      begin
      sys.dbms+

      I get a list of dbms packages, do you?

    • not necessarily, but the parser needs help recognizing that you’re typing valid PL/SQL identifiers…

  18. Thomas Whelan Reply

    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

    • 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?

    • Thomas Whelan

      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.

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

    • Thomas Whelan

      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.

    • Thomas Whelan

      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

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

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

    • Thomas Whelan

      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.

    • 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…

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

    • we changed it from 4.0.2 to 4.0.3 – so go get that and let me know what you like and don’t like

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

    • If this is really a bug, I’ll open a bug/request on the Oracle SQL Developer Exchange.

    • It’s not a bug, and even if it was, the Exchange is for enhancement requests and new features, not for reporting bugs.

    • Thomas Whelan

      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

    • Go get 4.1. Have a go with it. Send your feedback to the forums. We’re at code freeze already for 4.1, so it’s bug fixes only at this point.

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

    • Thomas Whelan

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

    • Thomas Whelan

      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.

    • Thomas Whelan

      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.

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

    • You’re logged in as USER_NAME and you’re seeing USER_NAME prepending the local schema objects?

      What version of SQL Developer are you running?

    • Version 4.0.2.15

      Yeah, you are right. Can this be restricted?

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

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

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

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

    • JeffS

      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!

Reply To JeffS Cancel Reply