SQL Developer Query & Grid Tricks

thatjeffsmith SQL Developer 37 Comments

Tell Others About This Story:

It helps to know the developers. They can tell you things that are not documented in the help or release notes. They mostly don’t hide things on purpose, mostly. They often build things into the application to aid themselves, but usually it’s for the end user’s benefit.

@krisrice sent me over a few nuggets to share with you folks. These are truly ‘tricks.’ Cool things that you would not know about unless someone showed them to you. As that goes, if you have a problem getting these to work, don’t expect Oracle Support to be on call. Feel free to go to the Forums or reply here and I’ll do my best.

So let’s start with hyperlinked objects in a data grid for a SQL query resultset.

Create links to objects in the Object Tree

You’ve seen reports and screens in SQL Developer where you can double-click on the cell and it auto-navigates you to that object in the Connections panel. Here’s an example:

Hyperlinked Objects - double clicking takes you to the object

You can code these links into your own queries!

Here’s an example:

SELECT 'SQLDEV:LINK:'
     ||ao.owner
     ||':'
     ||ao.object_type
     ||':'
     ||ao.object_name
     ||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' ObjectsBigger100000Bytes,
     ao.owner,
     ao.object_name,
     ao.object_type,
     c.bytes
FROM all_objects ao,
     dba_segments c
WHERE ao.owner NOT  IN ('SYS', 'SYSTEM', 'SYSAUX')
 AND ao.object_name IN
     (SELECT segment_name FROM dba_segments b WHERE b.bytes > 100000
     )
 AND ao.object_name      = c.segment_name
 AND ao.object_type NOT IN ('TABLE PARTITION', 'SYNONYM', 'INDEX')
ORDER BY c.bytes DESC;

Please don’t pay too much attention to the code, it’s just meant to be an example. The key piece is the first part of the select. We’re concatenating the schema, object type, and object name into a string that SQL Developer recognizes and can dynamically interpret and display as an internal link. Everything after that is up to you. In my example I created a lame report of objects that are using a lot of space.

Here’s how that looks when I run it:

Stupid pet tricks in SQL Developer!

Yes, the blue text is pretty, but don’t forget to double-click on it!

Adding Color Gauges to Display Value Ranges

This can currently be seen in the ‘Manage Database’ screen. To activate this report, mouse-right-click on your connection and choose ‘Manage Database.’

Pictures are pretty, math is hard!

And here’s the code:

SELECT 'SQLDEV:GAUGE:0:20000:1000:5000:'
     || peeps.salary "WhatIsItYoudSayYouDoHere",
     peeps.salary,
     peeps.first_name
     || ' '
     || peeps.last_name,
     peeps.job_id
FROM hr.employees peeps;

SQLDEV:GAUGE:0:200:50:150 equates to min:max:low threshold:upper threshold:value to graph

Which gives us something that looks like this:

Who is making too much money? I mean, who is a future-member-to-be of the upper class?

Want to make it dynamic?

OK, try this –

WITH q AS
     (SELECT owner,
          TABLE_NAME,
          sysdate - NVL(last_analyzed,sysdate-1000) last_analyzed
     FROM all_tables
     WHERE owner IN (USER, 'HR', 'SCOTT', 'SH')
     )
SELECT 'SQLDEV:GAUGE:0:'
     || MAX(last_analyzed) OVER ()
     ||':100:400:'
     || last_analyzed last_stats_chart,
     owner,
     TABLE_NAME,
     ROUND(last_analyzed) days_since_last_stats
FROM q;

We’re looking for tables that have stale (or missing!) statistics.

Dynamic Ranges for the Gauge

Navigate to a specific line of PLSQL code

Same thing with the hyperlinks. Instead of just linking to the object, this will auto-navigate you to a specific line of code. Yesterday I was talking about GOTOs in PLSQL. Want to generate an index of your GOTO code? Try this:

SELECT NAME,
     owner,
     line+1 line,
     'SQLDEV:LINK:'
     ||owner
     ||':'
     ||TYPE
     ||':'
     ||NAME
     ||':'
     ||(line + 1)
     ||':0:'
     ||SUBSTR(text, 0, 500)
     ||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' drill_to
FROM all_source
WHERE UPPER(text) LIKE '%GOTO%';

The +1 is there to offset the difference where we add a CR/LF after the CREATE OR REPLACE piece of the program.

Double-click on the link to open the code and go to that line #

Ok, there’s 3 tricks. I’m sure many of you out there are way more creative than me. Build stuff. Share it. Let us know what else you’d like to see us build.

Tell Others About This Story:

Comments 37

  1. Script Output comment:

    Hi Jeff, I’ve been really struggling with formatting the Script Output area in SQL Developer. I don’t understand why it’s not modified in Preferences.

    And, the one thing I’m trying to do doesn’t seem to be easy even with SET commands. Why is the column header truncated to four characters for columns with small widths? Why is this not easy to change? I’ve spent an hour searching for it, and apparent;y people have been complaining about this since at least 2003 in SQL*PLUS.

    I somehow got this to work in 4.0.3, or else it was implemented automatically, or something. But I upgraded to 4.1.5 and it’s back to truncation!

    Sorry, I come from SQL Server Management Studio and before that, and this sort of stuff is really basic over there. I don’t mind research but I can’t even find it! It’s making me crazy!

    I’m comparing SHOW ALL dumps from old and new, but nothing jumps out.

    I guess I will have to start using the Grid, even though I really prefer the text output.

    1. thatjeffsmith Post
      Author
  2. Hi Jeff,

    Like others mentioned already before, the colour of the rightmost part of the gauge changes its colour depending on the value used.
    I find this behaviour confusing and annoying.

    Is there no way to change/configure this “traffic-light effect”?

    Try this query in SQL Worksheet as an example:

    select ‘value < 66' value,'right part of gauge is green' description,'SQLDEV:GAUGE:0:15:5:10:65' gauge from dual
    union
    select '66 <= value <= 90' value,'right part of gauge is yellow' description,'SQLDEV:GAUGE:0:600:200:400:66' gauge from dual
    union
    select '66 <= value 90′ value,’right part of gauge is red’ description, ‘SQLDEV:GAUGE:0:500:200:400:91’ gauge from dual;

    Only way to work around this a little bit, is to set the upper threshold to the same value as max, but this way you lose one of the three segments.
    e.g.:

    select ‘value < 66' value,'SQLDEV:GAUGE:0:15:5:15:65' gauge from dual
    union
    select '66 <= value <= 90' value,'SQLDEV:GAUGE:0:600:200:600:66' gauge from dual
    union
    select '66 <= value 90’ value, ‘SQLDEV:GAUGE:0:500:200:500:91’ gauge from dual;

    BR,
    Daniel

  3. Hello Jeff

    Is it possible to link to a select statement instead of an existing object in the navigator tree in an editor type extension – using something like this:

    select ‘SQLDEV:LINK:(select t.a, t.b, t.c from sometable t):oracle.dbtools.raptor.controls.grid.DefaultDrillLink’ from dual “foo”;

    thanks!

  4. Is it possible to run this query through sqlplus:

    select /*html*/ column1,column2 from some_table;

    Or hints only works in sql developer worksheet?

    Thanks.

    1. thatjeffsmith Post
      Author
  5. Hi Jeff, is it possible to have the data grid to display data in alternating colors in different rows? like first row is blue, 2nd row is white, 3rd row is blue etc.

    1. thatjeffsmith Post
      Author
  6. Jeff,

    How can I define the colors for a gauge?

    I’ve created a gauge report to keep an eye on space usage but it appears from left to right as red:yellow:green and I need it to be green:yellow:red.

    Thanks,

    1. thatjeffsmith Post
      Author
  7. Hi,
    It doesn’t seem to work for a package body. The logging pane registers this:
    SEVERE 1134 7791 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1
    No registered action with id if mod(l_alt,2)=0 then at oracle.javatools.ui.RichHintLabel$HyperlinkL.hyperlinkUpdate(RichHintLabel.java:383)
    I am using the latest 4.1 final release, Version 4.1.0.19. Did you have this as well ?

    1. FYI: I used this to test:

      SELECT
      owner,
      NAME,
      type,
      line+1 line,
      ‘SQLDEV:LINK:’||upper(owner)||’:’||upper(TYPE)||’:’||upper(NAME)||’:’||(line + 1)||’:0:’||SUBSTR(text, 0, 100)||’:oracle.dbtools.raptor.controls.grid.DefaultDrillLink’ drill_to
      FROM dba_source
      WHERE
      UPPER(text) LIKE upper(‘%goto%’);

      1. Ok, I just downloaded the 4.0.3 client and used same code to test. It works with that so it must be a 4.1 bug.
        Jeff or others can you confirm this ?

  8. How can i use this in the user-defined-reports section if i have pl/sql dbms output as Style ?
    I tried generating a html A tag with href=”xx” where xx is the link construct above.
    However although sql developer recognizes it as a link(blue underlined clickable) it doesn’t open anything.
    Is this possible and if so what is the format ?

    1. thatjeffsmith Post
      Author
  9. Hello Jeff,

    Is possible to change the background color of some result on SQL DEVELOPER ?

    Something like that ?

    CASE
    WHEN column = ‘valor’ THEN ‘setBackgroundColor = red’
    END

    Thank you very much !

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author

      Turns out you CAN do this 🙂

      Here’s all the object links possible from the DBA panel.

      ‘SQLDEV:LINK:’||USER||’:SCHEDULED_RMAN_ACTION:’||JOB_NAME||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Job Name”,
      ‘SQLDEV:LINK{#;#}’||USER||’#;#BACKUP_JOB#;#’||command_id||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Backup Name”,
      ‘SQLDEV:LINK:’||USER||’:BACKUP_SET:#’||F.BS_KEY||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Key”,
      ‘SQLDEV:LINK:’||USER||’:CONSUMERGROUP:’||consumer_group||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Consumer Group”,
      ‘SQLDEV:LINK:’||USER||’:CONSUMERGROUPMAPPING:’||P.ATTRIBUTE||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’
      ‘SQLDEV:LINK:’||USER||’:CONTAINERRMPLAN:’||p.plan||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Plan”,
      ‘SQLDEV:LINK{#;#}’||USER||’#;#CONTROLFILE#;#’||c.name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “File Path”,
      ‘SQLDEV:LINK{#;#}’||USER||’#;#DATAFILE#;#’||ddf.file_name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “File Name”,
      ‘SQLDEV:LINK{#;#}’||USER||’#;#IMAGE_COPY#;#’||FNAME||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Name”,
      ‘SQLDEV:LINK{#;#}’||USER||’#;#PERFORMANCE_ADDMTASK#;#’||a.task_name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Name”, ‘SQLDEV:LINK{#;#}’||USER||’#;#PERFORMANCE_BASELINE#;#’||b.baseline_name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Name”,
      ‘SQLDEV:LINK{#;#}’||USER||’#;#PERFORMANCE_BASELINE#;#’||b.baseline_name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Name”,
      ‘SQLDEV:LINK{#;#}’||USER||’#;#PERFORMANCE_BASELINETEMPLATE#;#’||t.template_name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Name”,
      ‘SQLDEV:LINK{#;#}’||USER||’#;#PERFORMANCE_BASELINETEMPLATE#;#’||t.template_name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Name”,
      ‘SQLDEV:LINK{#;#}’||USER||’#;#PERFORMANCE_SNAPSHOT#;#’||sn.snap_id||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “ID”,
      ‘SQLDEV:LINK{#;#}’||USER||’#;#PERFORMANCE_SNAPSHOT#;#’||sn.snap_id||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “ID”,
      ‘SQLDEV:LINK{#;#}’||USER||’#;#PERFORMANCE_ADDMTASK#;#’||f.task_name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Task Name”,
      ‘SQLDEV:LINK:’||USER||’:PROFILE:’||p1.profile||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Profile”,
      ‘SQLDEV:LINK:’||USER||’:REDOLOGGROUP:#’||group#||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Group”,
      ‘SQLDEV:LINK:’||USER||’:RESOURCEMANAGERPLAN:’||p.plan||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Plan”,
      ‘SQLDEV:LINK:’||USER||’:RESOURCEMANAGERPLAN:’||p.plan||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Plan”,
      ‘SQLDEV:LINK:’||USER||’:RESTORE_POINT:’||name||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Restore Point Name”,
      ‘SQLDEV:LINK:’||USER||’:RESTORE_POINT:’||name||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Restore Point Name”,
      ‘SQLDEV:LINK:’||USER||’:ROLE:’||role||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Role”,
      ‘SQLDEV:LINK{#;#}’||USER||’#;#ROLLBACK_SEGMENT#;#’||r.segment_name||’#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Name”,
      ‘SQLDEV:LINK:’||USER||’:TABLESPACE:’||a.tablespace_name||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as tablespace_name,
      ‘SQLDEV:LINK:’||USER||’:TABLESPACE:’||h.tablespace_name||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as tablespace_name,
      ‘SQLDEV:LINK:’||USER||’:TABLESPACE:’||g.tablespace_name||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Tablespace” ,
      ‘SQLDEV:LINK:’||USER||’:TT_GROUPS:’||group_name||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “Group”
      ‘SQLDEV:LINK:’||USER||’:USER:’||USERNAME||’:oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink’ as “User Name”

    1. thatjeffsmith Post
      Author

      No way to auto browser master-child records from a query resultset. We’ve talked about building something like into the table editors, but it’s still on the drawing board. We have this, but it’s not really what you’re asking for – useful for reports though.

  10. Jeff, is there any way to store the definition of the reports on a server and have users access them using SQLDeveloper to have reports generated locally?

    1. thatjeffsmith Post
      Author

      Tools – Preferences – Database – User Defined Extensions

      Add a report entry for each of the reports you want to have shared – you can reference the file via http or via directory location.

      1. Jeff, it’s obvious the SQLDeveloper is a tool for developer, but most business customers today would like to have direct access to their data , BI is just icing on the cake. Most of those tools are either too costly, or take to much resources to maintain. If there’s a “light” version that developers can generate the XML files (pre-defined, param-driven reports) and can be executed as a stand-alone web application – just like Oracle XE’s web interface but with the SQLDeveloper report functionality, that would be awesome! 🙂

        1. thatjeffsmith Post
          Author
  11. Hi Jeff,
    Just want to share some finding. I can see that the color of the gauge behind the upper threshold may vary between green, yellow, and red. When the value is below 66, it is green. When the value is 66 or more but less than 90, that’s yellow. Otherwise (above 90) that’s red.
    Probably, you already know about it, and maybe there is a way to change those ‘fixed’ thresholds.
    Thanks.

    1. thatjeffsmith Post
      Author

      For the tablespace report? Yes, that sounds right, but let me see if I can’t get the source and share the code how to build that sort of gauge, versus the gauges I have in my examples.

      1. Hi Jeff – Did you ever get the source code?

        Is there any way to build our own gauge and have it used by SQL Developer?

        I see a feature request to reverse the colours for SQLDEV:GAUGE was rejected back in 2008…

        IMO, a gauge like this shouldn’t have non-configurable thresholds set, not if it’s going to be useful to large numebrs of people…

        And yes, I see that it works well for the Manage Database report 🙂

Leave a Reply

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