ThatJeffSmith

SQL Developer Query & Grid Tricks

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.