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.
38 Comments
Hi there. I would like to have a SQLDEV:LINK similar to the DBADrillDown, but that would open a new report. Yes, it sounds like a child report; but it is not. I want to be able to open a new report.
Child reports limit you to a two-level system. A DrillDown that opens a new report makes levels deeper than two possible. You could easily add support for parameters.
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.
Example please. I spent 15 minutes setting this up – works as expected.
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
as this comment-function did something strange with my sql-statements I put them to http://pastebin.com/tuWPSwav
BR,
Daniel
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!
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.
You can, but SQL*Plus will just see the /*html*/ as a comment.
If you run it via SQLcl, you’ll get just what you want. Have you tried SQLcl yet?
No, I haven’t……
I’ll try it and let you know…
Thanks.
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.
no control over the ‘zebra’ or ‘checkerboard’ affect today, but it’s something we want to make configurable where you can set the color schemes
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,
sorry, they’re hardcoded
if you need more flexibility, try building an APEX App instead
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 ?
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%’);
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 ?
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 ?
I don’t know if you can, it might be limited to grids.
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 !
But of course!
Thank You !
Can we set this background color to entire row based on a value in that row?
No, or not w/o a lot of ugly code including casting EVERYTHING to a string so you could prefix/append the values with the HTML tags.
hi Jeff,
I want use “SQLDEV:LINK” with object like tablespace too.
How to make?
Thank you,
Enrico
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”
Great, Thank you!!!
hi Jeff,
I want use “SQLDEV:LINK”, bau the objects are datafile. How to make?
Thanks
Thank you for the quick reply. It’s a pity that OSD doesn’t offer this.
Hi Jeff,
I like Oracles SQL Developer not at last because its free. But what I’m really missing is a qiuck option for linked queries when I select a row. For example PL/SQL Developer offers this feature (see http://www.allroundautomations.com/download/Manual71.pdf / 6.4). Do you have a hint how to realize something similar in Oracles product?
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.
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?
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.
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! 🙂
It kinda sounds like you’re describing APEX.
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.
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.
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 🙂
It’s still there in 4.1.3.20.
I would love to see it fixed!
Best regards,
Sabine