Today is the day! We have new releases of both SQL Developer and SQL Developer Data Modeler.

click here for SQLDev 4.2

I’ve been talking about v4.2 tweaks and enhancements for awhile now, and here’s the full list if you want to go back and review.

I’d like to share a few highlights though.

But first, FULL STOP. Go click that big button to the top and left.

Ok, let’s continue.

Formatter

I’d like to thanks the entire community for all the feedback, especially those of you who provided test code to exercise the new formatter. We made a few changes since the Early Adopter 2 update – tons of bug fixes, and tweaked the options a bit more based on your feedback.

With 4.2 now live in production, if our parser (and accordingly our formatter) doesn’t understand your code, we just won’t format it. In the EA’s we printed comments that showed the code bits we didn’t understand. For v4.2 production you should just see the parser ‘squiggle’ indicating where the code isn’t being interpreted correctly. Please share your code samples with MOS and the community if you think that’s a bug.

Make your code, pretty.

Some things of note:

  • The preferences have moved AND changed from version 4.1. They’re now under the Code Editor page, and should also be simpler to understand now.
  • There’s a ‘simple’ mode, and advanced mode, and an uber-expert mode.
  • In advanced mode, you can format your code by hand, paste it into the preferences, and we’ll auto-detect how you want your code to look going forward.
  • In uber-expert mode, you can tell the formatter exactly what to do to your code. Our developer Vadim has a couple of blog posts showing how to do this with examples.
formatter preferences in oracle sql developer
Type code, as the formatter sees ‘stuff’ it changes the formatting options – these are highlighted in Green so you can see what’s changed.

Instance Viewer

Not a new feature – we first introduced the Instance Viewer in version 4.1. But we now offer a ‘Top SQL’ report. See your expensive queries, and drill down into them to see what’s going on or fix them.

sql developer top sql animated gif
Pretty colors, plus some interesting bits of data.

The licensing prompts on the drill down report let’s you know that the SQL Tuning Advisor and Run time history reports require the tuning and diagnostic packs, respectively. If you don’t click on those reports, then there’s nothing to worry about.

Everything the Instance Viewer hits on the main panel is of the ‘no additional cost’ data dictionary views category.

Easy Peasy Password Resets

No more requiring an Oracle Client to change your database passwords when you’re not already connected. Maybe the most compelling reason to upgrade your end users.

More details here.

right click connection to reset password in oracle sql developer
Yup, this one.

12c Release Two Enhancements

We support big-ole object names now.

You CAN do this. Not sure you SHOULD do this though.

And you can create Analytic Views too!

And the Multitenant option got a lot of upgrades. Among them, Application Containers. We support those too.

Go to the DBA panel for your CDB.

New to 12cR2? Did you know our VirtualBox VM was upgraded to 12.2? Also has APEX 5.1 and ORDS 309 for ya.

Speaking of ORDS…

You can now manage your RESTful Services directly in the database connection tree! This is much easier than setting up your ORDS Dev user connection. You’ll see the new REST item in the tree, and I talk about this here.

oracle rest data services modules
Note the ‘REST Data Services’ item on the tree.

And the Modeler!

I think the biggest two changes of note are the ability to include your diagrams in your HTML and PDF reports AND the ability to now store your repository in GIT.

On the diagrams, you can ask to include them in your reports AND you can just directly print them to HTML/SVG.

File..Print Digram…

And we get..

sql developer data modeler diagram html
WYSIWYG!

Et cetera and potpourii

This marks the end of the SQL Developer version 4.X releases. We’re moving towards a quarterly release schedule to synchronize up with our Database Cloud Services updates. So our next version will start with a 17 – for 2017, then quarter (1,2,3,4), then week.

This release also ships with the fewest known number of bugs…ever…for a SQL Developer release. We hate bugs. So we tried to kill ’em all. I’m sure you’ll find some more. Let us know. We’ll be able to release updates more frequently, getting you bug fixes that much faster.

Enjoy!

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.

41 Comments

  1. What is the number of the current/latest version for Oracle Developer ?
    What comes after version 4.0 ? version 4.2 ? etc

  2. Copying an object name from the Connections panel becomes unavailable in 4.2.
    Not sure it is an intended change or a bug. I have a screen snapshot but I cannot paste it here, so let me describe it. In an example, after I made the database connection, I expand Tables, and a list of table names displayed in the Connections panel. In the old version of SQL developer, if I click a table name and Ctrl-C to copy it, I then can paste this table name to anywhere. In new version 4.2 that we have just upgraded to, this copy/paste stops working. Instead, I have to right click it and open the Edit window and copy the table name from there. Is there an easier way to copy object name in 4.2 ?

  3. I’ve been using version 4.2 for a few weeks now and I keep getting an error message saying the database connection has been reset and any pending transactions have been lost.

    This occurs mainly when I attempt to look at any object in the object viewer.

    This doesn’t happen with v4.1.5

    Searches on the web came up with various causes including malware and a suggestion to add AddVMOption -Doracle.net.disableOob=true
    to the sqldeveloper_nodebug.conf file. The theory being that this is caused by and Out of Bands error in the jdbc driver or on the network. Doing this made no difference.

    I’m running this on Windows 7 64 bit against a local pluggable database (12.1).

  4. Tony Bouttell Reply

    Hi, I’m using 4.1.5.21.68-x64 and installed version 4.2.
    I put sysWOW64 into the path so that MSVCR100.dll could be found and … version 4.2 still failed to run.
    Has anyone seen this behavior before?

    • Tony Bouttell

      My version is: 4.2.0.17.089.1709
      This is my path : Path=C:\Windows;C:\Windows\sysWOW64;C:\Windows\system32;C:\Windows\System32\Wbem;

    • anthony bouttell

      The OS I’m using is Windows 7 64bit.
      Interestingly, This works on my Windows 10 system

  5. Tania Dosenberger Reply

    Is it just me or does anyone else have issues with Developer queries crashing when it hits a ‘SELECT’ statement that is commented out with the double dashes –? Removing ‘SELECT’ it runs cleanly. Surround the ‘SELECT’ with /*..*/ also runs cleanly. This is annoying if this is how it works now. It was nice to just do the double dashes that you could drop in or out of the front of the SELECT statement that you wanted for testing.

    • Tania Dosenberger

      with Vari AS
      (
      SELECT
      to_date(‘2017-04-30’, ‘YYYY-MM-DD’) v_end
      FROM dual
      ),
      /*Get all active clients as of month end*/
      actref AS
      (
      SELECT
      cli_per_id AS paris_id
      ,inp_cli_id
      ,inp_association AS ref_id
      ,trim(fnc_get_gen_desc@parq(‘TREATREAS’,refe.ref_treat_reason)) AS ref_reason
      ,trim(inp_team) AS team_code
      ,team_name
      ,team_area
      ,FNC_GET_STAFF_NAME_FROM_CODE@parq(inp_staff) as primary_provider
      ,inp_from
      ,inp_to
      –,monthly_report_team
      FROM paris.vws_mpi_involved_prof@parq
      INNER JOIN paris.vws_mpi_client@parq ON cli_id = inp_cli_id
      INNER JOIN clgry_sr_rpt.lup_team ON trim(inp_team) = team_code
      LEFT JOIN paris.vws_ref_referral@parq refe ON inp_association = refe.ref_id
      CROSS JOIN Vari
      WHERE team_category IN (‘HC’,’SWCM’) — HC teams – Abram confirmed via email saying SWCM should not be counted in HC counts Dec . There is only 1 active record now. Gets separated out below
      AND inp_alloc_type = ‘MN’ –Primary provider
      AND (inp_from IS NULL OR inp_from = v_end)
      ) –SELECT * FROM actref;
      ,
      /*Get the most recent client grouping that occurred from the period onwards*/
      — this gets all client groupings as some active clients may not have a client group
      Client_group AS
      (
      SELECT DISTINCT
      grp_per_id
      ,TRIM(grp_sub_group) as subgroup
      ,grp_from
      ,grp_to
      FROM vws_mpi_grouping@parq
      WHERE (grp_id, grp_per_id) IN
      (
      SELECT
      MAX(grp_id) max_grp_id,
      grp_per_id
      FROM vws_mpi_grouping@parq
      CROSS JOIN Vari
      WHERE grp_group = ‘CG’
      AND grp_from = v_end
      AND (grp_per_id, grp_from) IN
      (
      SELECT
      grp_per_id,
      MAX(grp_from) max_grp_from
      FROM vws_mpi_grouping@parq
      CROSS JOIN Vari
      WHERE grp_group = ‘CG’
      AND grp_from = v_end
      group by grp_per_id
      )
      GROUP BY grp_per_id
      )
      ),
      /* Join the the two subqueries together*/
      HC_active_referral AS
      (
      SELECT
      v_end AS report_month
      ,actref.paris_id
      ,trim(nam.nam_fore1) as client_fname
      ,trim(nam.nam_surname) as client_lname
      ,actref.ref_id
      ,actref.ref_reason
      ,actref.team_code
      ,actref.team_name
      ,actref.team_area
      –,actref.monthly_report_team
      ,actref.primary_provider
      ,actref.inp_from
      ,actref.inp_to
      ,cg.subgroup
      ,trim(clgrp_lup.lcc_desc) as client_group
      ,cg.grp_from
      ,cg.grp_to
      FROM actref
      CROSS JOIN vari
      LEFT JOIN client_Group cg ON actref.paris_id = cg.grp_per_id
      LEFT JOIN paris.vws_lup_client_cat@parq clgrp_lup ON cg.subgroup = trim(clgrp_lup.lcc_code)
      INNER JOIN paris.vws_mpi_name_current@parq nam ON actref.paris_id = nam.nam_per_id
      ) –SELECT * FROM HC_active_referral;
      /*if the details are required run this lines of code below*/
      –select * FROM HC_active_referral where team_name in (‘HCRT – GENERALIST’,’HOME CARE RESPONSE TEAM’) ;
      –SELECT * FROM HC_active_referral where paris_id = 10130360;
      ,
      /*compile the client counts for each team*/
      totals AS
      (
      SELECT
      team_area
      ,team_code
      ,count(*) as total
      FROM HC_active_referral
      GROUP BY
      team_area
      ,team_code
      ) –SELECT * FROM totals;

      /*Final result count*/
      /*Only show the results for the teams that are current teams in the monthly report (‘MR’) */
      SELECT
      mths.fy_yr_nam_txt_1
      ,to_char(v_end,’YYYY-MM’) AS event_ym
      ,v_end as report_month
      ,monthly_report_team
      ,ta.team_area
      ,team_name
      ,ta.team_code
      ,NVL(total, 0) as total
      FROM clgry_sr_rpt.lup_team ta
      CROSS JOIN vari
      LEFT JOIN totals t ON t.team_code = ta.team_code
      LEFT OUTER JOIN mrt_analysis.DIM_PERIODS_DT_months mths on mths.mn_nam_yr_num = to_char(v_end,’YYYY-MM’)

      WHERE monthly_report_team =’MR’;

      First error is:
      ORA-00928: missing SELECT keyword
      00928. 00000 – “missing SELECT keyword”
      *Cause:
      *Action:
      Error at Line: 48 Column: 26
      which is at: ) –SELECT * FROM actref;

    • >>–SELECT * FROM actref;

      That’s the BUG. Having the ‘;’ in a comment is tripping up the parser. We’ll have this fixed in a patch/update.

  6. Thanks for the new release.
    Hovering on star in order to expand all query columns in the projection does not work any more.
    Bug or feature?

    • I just tried it, works for me.

      select * from sh.sales, the * is squiggly underlined, mouse over, i get the expand * to: prod_id, … hover bubble.

    • Strahinja

      Thanks for double checking.
      It works for me in 4.1.2.20.64, but not in 4.2.0.15.260, nor the 4.2.0.17.079 … strange …

    • use the view > Log > Statements panel to debug – you’ll see bg parser queries we run when we ‘see’ your queries and we go out to get the columns.

  7. Is there a way of turning of comparing subprograms. In 4.2 on large packages this seems to be running a lot and stopping me from editing code. Thanks

    • Preferences – Code Editor – Supported Gutter Navigation…and maybe also Supported Sync Spec and Body

      There were no changes in this area from 4.1.5 to 4.2 though, that I know of.

    • Thanks Jeff, this is a huge help – I have to use OSD over a very laggy VPN and this is a great optimization!

  8. Daniel Nyeste Reply

    Hey Jeff,

    Thanks for the new release, I really like the new Formatter options!
    On the other hand,are there any chance you will implement a vim-like keybindings for the Code Editor in the funture? Something similar to the vim plugin for IntelliJ editors.

    Regards,
    Daniel Nyeste

    • Daniel Nyeste

      Thanks for the quick reply! Yes, I’ve set up and use the external editor function already (thanks to your post), but it’s just … cumbersome.

      Regards,
      Daniel

    • we’re never going to be better than your favorite editor, so if you’re doing some serious type-y stuff, alt-tab

    • Daniel Nyeste

      Oh don’t worry, your tool is also doing great work 🙂

  9. Great to see SQL Developer 4.2. It appears not to include sqlcl. Did I miss something?

  10. Mette Juel Reply

    Love the new versions … .apart from the DBA frontpage. I am simply not able to read the page properly ….. with the grey and light blue ? (age 56). Is it possible to custmize the font colors ?

    Almost same problem with Apex 5 …. new and modern responsive …. but not user friendly

    regards
    Mette

  11. Brett Hodel Reply

    Thanks for the write up! Is there any walk through on how to use the data modeler with GIT? I have been doing this manually for a while outside of sql developer, and after playing with the new release been unsuccessful in using git inside sql developer.

    Thanks!

    • Not yet, but it’s on my list. You should be able to set the svn server details to your git repo. I’ll get better instructions out pronto.

    • Anyone experiencing bug with linesize?

      show linesize command outputs 1763 but the result displays only 72

    • Laszlo J. Gal

      Jeff, Are you sure about Modeler and Git? I’ve heard that it is not supported yet as SVN is supported i.e. from within the Modeler’s object comparison dialogues and it is not on the list yet. Are you saying that there is a workaround presenting the Git repository as SVN for the modeler? The only workaround I know is using https://subgit.com/ where the local check out is SVN and the remote repository is Git. If you know a more direct, better workaround, it would be great to hear about the steps to set up.

Write A Comment