SQL Developer and a 12c Magic Trick

thatjeffsmith SQL Developer 3 Comments

Tell Others About This Story:

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…
Nothing fancy or magical so far...

Nothing fancy or magical so far…

Should just take a second or so…

Whoa...

Whoa!

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.

If you change your mind, you can always UNDO the expand back to the original SQL statement.

If you change your mind, you can always UNDO the expand back to the original SQL statement.

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.

How it normally looks...

How it normally looks…

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.

Is this practical? Not really. But is it fun? Definitely. The 'invisible squiggle' to fool your co-workers thing, that is.

Is this practical? Not really. But is it fun? Definitely. The ‘invisible squiggle’ to fool your co-workers thing, that is.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 3

  1. 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 ?

    1. 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

Leave a Reply

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