Can SQL Developer help me finish my SQL or code?

Probably.

Here’s a quick showing of what it’s capable of.

Start typing a table name...
Start typing a table name…
Get a list of columns...
Get a list of columns…
Get a list of joins...
Get a list of joins…
Columns available in a predicate...
Columns available in a predicate…
What does this procedure need to be invoked?
What does this procedure need to be invoked?
What about this procedure?
What about this procedure?
OK, just print it out for me already!
OK, just print it out for me already!

Note: we enhanced this feature to be a lot more user friendly, so make sure you upgrade to the latest offering and you can do stuff like this. 

Mouse over the grammar squiggle for the help
Mouse over the grammar squiggle for the help
Let's do that again...
Let’s do that again…

Need more help on the syntax? Click on the keyword in the last two examples, and you’ll be taken to Oracle DOCS syntax pages for that command.

Update: I Found One More!

plsql variables and datatype labels
plsql variables and datatype labels

Not working?

A few things to remember:

  • mind the preferences – delay timers, automatic off, limiting the help based on # of hits
  • if the insight query takes too long, the help won’t get printed
  • the helper is invoked with ctrl+spacebar (Mac & Windows)
  • the better grammar you use, the more luck our parser will have in figuring out what you’re doing or what you’re talking about

Just watch the movie

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.

14 Comments

  1. Hi Jeff,
    If I type dbms_utility.get_hash_value( and then ctrl+space I get the completion insight ‘name=>n/*varchar2*/,base=>b/*number*/,hash_size=>h/*number*/’.
    If I try similar for a function that I have built it only seems to return ‘dummy’, instead of the list of arguments. Can I configure anything to get my argument list?
    Thanks
    Steven

    • I just tried it with HR.ADD_JOB_HISTORY

      begin
      add_job_history(–ctrl+spacebar

      this is what i get in the editor


      begin
      add_job_history(
      P_EMP_ID=>P/*NUMBER*/,
      P_START_DATE=>P/*DATE*/,
      P_END_DATE=>P/*DATE*/,
      P_JOB_ID=>P/*VARCHAR2*/,
      P_DEPARTMENT_ID=>P/*NUMBER*/
      );

  2. Andras Fejes Reply

    Hi, my question is about schema names. If I type in “select * from my_schema.my_table” in the code editor and hit enter, then my Developer changes the query to “select * from my_table”, so it clears the schema name that I typed in. I would like to write codes with schema names visible, even if the tables are in my own schema. I cannot find in which menu can I change this behaviour in SQL Developer 4.2.

    • This was a bug – not sure when it was fixed, but it’s working as you want in version 18.1 – which is the current release.

    • Andras Fejes

      Thank you for your quick answer! I will upgrade to the new version.

  3. I have a problem that did not exist until a week ago. When I try to run scripts in SQL Developer that contain an ‘&’ sign, I’m asked to enter a parameter value when it is not a parameter. This does not happen with PL/SQL Developer. Please Help:
    AND (N.PRGRM_NAME LIKE ‘AHK – Medicaid CN Foster Care & Adoption Support Children’
    OR N.PRGRM_NAME LIKE ‘Medicaid MN Others (Pregnant Women & Children)’

  4. For some reason Completion Insight on a SQL Worksheet works in two of our environments, but not our other two. It does not work with Auto-Popup or using Ctrl-Space. I understand there are two connections. One that is used for Insight, but don’t see where I can check or verify. I have tried enabling and disabling Auto-Popup. Nothing seems to work. If I get frustrated and press Ctrl-Space many times, then when I try to run a SQL statement, it can take a very long time. Any ideas on what I can do to get it working for the two environments?

    • So…what’s different between the two environments?

      In newer versions of SQLDev, you can see us fire off the insight queries and see the execution times in View > Log > Statements.

    • I am not seeing that option. I have Version 4.1.5.21. I only see “Messages – Log”.

      I was able to resolve the issue by “resetting” SQLDev.
      Exited SQLDev
      Go to C:\Users\eric\AppData\Roaming. Replace “eric” with your userid (for Win7)
      Rename/backup directories “SQL Developer” and “sqldeveloper”.
      Restart SQLDev

      You will need to redo your connections, preferences, …, but it addressed the problem for me.

      Here is a link to your reset SQLDev page:
      http://www.thatjeffsmith.com/archive/2015/08/how-to-reset-your-sql-developer-preferencessettings/

      Thanks for your help AND this site!!

  5. miguel celin Reply

    Hi, Jeff, is possible to change order the result list in prompt for Completion Insight?, because when I use this, the order is alphabetical ascending and I need that it shows like command DESCRIBE. The real order in the table and not alphabetic.

  6. Will Geiger Reply

    Jeff, does SQL Developer have the ability to autocomplete function/procedure names when referencing another package? For example, in package “a”, I’m calling a procedure named “helloWorld” from package “b”. When I type “b.”, I would love for a list of the procedures/functions available in that package. Is this possible?

Write A Comment