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:
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:
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.’
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:
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.
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.
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.