When using the code completion feature, SQL Developer will look at what you’ve typed so far, and will offer to ‘auto complete’ what you have at your cursor.

I’ve tried to cover this exhaustively here, but apparently I failed to include THIS bit of the feature.

You have some PL/SQL, and you want some help…

BEGIN
 PACKAGE. -- oh what was that procedure name?;
 PACKAGE.PROCEDURE( -- oh, what were the required parameters, or even all of them?
END;
/

This is where we can help. Of course we can help you complete the package name. And you probably already know about the procedure and function name completion.

But if not…ctrl+space is still the kb activator here…

Insert, update, or delete – note the functions and procedures have different icons.

But really it’s this next part you REALLY want help with.

After typing the ( – activate the helper AGAIN:

What’s this FULL vs MIN sig?

The full sig will bring back the FULL list of parameters.

Note we even add the closing paren and semicolon for you, );

The min sig will return the parameters whose defaults are NOT NULL

Just the absolute, required parameters please.

I would have bet money I had already covered this topic, but I guess not!

Well, now I have at least. And I’ll update the ‘comprehensive’ insight post to include a link here as well.

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.

17 Comments

  1. Hi Jef,
    i don’t foud any help from sqldeveloper when i use TYPE XXX IS RECORD … in the package spec.
    when i use in body with varible of this type sqldeveloper don’t complete my code when i type VARIABLE. he don’t propose the liste of type members. Also when declare varible, i type package_name. i get in choice scalar variables, functions and procedures but not types. These very important to work with collections in pl/sql

  2. What I don’t find is how to select an entry from the list without the mouse. I can select an entry with up/down keys, but pressing return just inserts a new line.

  3. Hi Jeff,
    my database is big, network connection is slow.
    My experience: 2-3 seconds after pressing CTRL-space there is following message in log: “ORA-01013: user requested cancel of current operation”

    I have checked what SQL commands are in Statements log.
    They looks OK. But it takes 20 seconds to run them.
    Is there any way to tell completion insight not to cancel queries after 2-3 seconds(in case CTRL-space was pressed)?
    I’m willing to wait 20 seconds till queries are completed.

    Thank You,
    Andrej

    • You’re an Oracle employee, just hit me up on Slack.

      For everyone else out there reading this – 99.9% of users aren’t this patient, they’d never find waiting 20 seconds for insight advice while typing SQL acceptable, so we bork/abort the attempt.

  4. anibal lopez Reply

    Hello Jeff,

    Is there any documentation where i can see the difference in new features between SQL Developer version 18.4 and SQL Developer 19.1 and mostly related to REST DATA SERVICES (ORDS) ?

    Regards,

    Anibal

    • The 19.1 release notes will mention new features and bug fixes…those are available on the download page.

  5. Hi Jeff,

    I’m using the code completion a lot. I wonder how people don’t want to use it, but anyways…
    Since a few Versions now (I’m on 19.1) the code completion dropdown is simply too narrow. I couldn’t find anything on the web how to make it wider.
    The problem is, that I’m getting a list of tables for example with our long schema names first and then just a few characters a left for the table names. Since we use naming conventions to organize our tables in a schema sometimes I get a list of 10 entries which are the same.

    Is there a way to widen this dropdown? Right now I have only 19 characters.

    Cheers
    Pawel

    • I just hand counted a pop up list for SYS.A – and got up to like 33 or 34…can you upload an image somewhere and post the link here so i can see what you see?

    • Get ready for some math, from the developer:

      “The dialog width is the minimum of screen width divided by 4 and 250 pixels. In my environment (4K monitor) the quarter of screen width is way larger than 250 pixels. When setting font ridiculously large *and* artificially limiting the width to 250 pixels I can kind of reproduce the problem”

    • So I just need to get a 4K display, then? 🙂

    • No..I mean, I have two rigs, one with a 4k, and one without, both without this issue.

      Try experimenting with the size of your window and your editor font size.

    • I changed the window size: didn’t have any effect
      I changed the font size: This one worked.

      But only if I have a very small font size (12-14) I see enough to work with it, but then I will most likely need thicker glasses soon. I’m usually at font size 22.

      Is there any chance to fix this? I’m also pretty sure, that it wasn’t an issue in the 4.x versions, not so sure which one, though.

    • Can you share your exact screen resolution, application window size/dimensions, font, and font size?

      ‘Cause on my ‘normal’ Windows 10 rig, it’s not exhibiting what you’re showing.

  6. Hi Jeff,

    I’m using SQL Developer version 18.4 and gave the following scenarios a test to see if code-completion worked:-

    Scenario 1: SUCCESS

    begin
    schema_name.package_name.procedure_name( — code-completion worked!
    end;

    Scenario 2: FAILED

    begin
    schema_name.procedure_name( — code-completion did not pop-up!
    OR
    schema_name.function_name( — code-completion did not pop-up!
    end;

    It looks like the code-completion ONLY works with packages.procedures/functions but not simply packages or function calls. Not everyone uses packages all the time.

    Thanks!

    • I see the same bug…which is annoying because that was JUST working not that long ago. Will log it for you and push to have fixed for 19.2

  7. This will save me some much needed time in the future !
    Tnx for the always very useful SQL Developer tips.

Reply To anibal lopez Cancel Reply