When you start working with 12c, if you want to freak out (sorry, GRAB the attention of) your co-workers or folks attending your presentation, try this:
- Open a worksheet
- Write a simple query against a view, or a data dictionary view
- Mouse over the SELECT and wait…
Should just take a second or so…
So we’re now seeing the SQL ‘behind the SQL’ – an MTV spinoff I pitched that that never really took off.
This is a combo of SQL Developer version 4.1 and Oracle Database 12c, and the feature is SQL Text Expansion [DOCs]. Tom Kyte talks about it here.
You don’t have to expand it, you can just ‘peak’ into the SQL to see what it’s doing. If you do click on the hyperlinked text, it will replace your query in the worksheet.
And now my 2 line query is almost 400 lines.
So the Fun or Magic Part
In your preferences, switch the SQL Expand indicator to ‘white’ – so it looks like you can just cast your magic spell on demand.
It’s the ‘Disconnected Join Graph’ item, which is apparently translates mathematically to some sort of SQL thing that some of our code insight features key in on, specifically when we add the GROUP BYs for you.
3 Comments
Excellent stuff.
Jeff,
instead of all_source if you put all_objects in SQL Developer 4.1 connected with 12c, why don’t we see the expanded sql statement ?
Jeff,
Never mind, I think I got it. its because for privilege issues. ( when did this from console I realized this )
rajesh@PDB1> variable x clob
rajesh@PDB1> exec dbms_utility.EXPAND_SQL_TEXT(‘select * from all_objects’,:x);
BEGIN dbms_utility.EXPAND_SQL_TEXT(‘select * from all_objects’,:x); END;
*
ERROR at line 1:
ORA-24256: EXPAND_SQL_TEXT failed with ORA-01039: insufficient privileges on underlying objects of the view
ORA-06512: at “SYS.DBMS_UTILITY”, line 1525
ORA-06512: at line 1