Forgotten tabs part 2

Now that's I know there's some interest in people writing, I'll expand on my previous post and show how to have shared queries, master/detail , 2 queries and the bottom query being text.

When there's sql which has to used on multiple object types like tables and view, the sql can be defined once and shared to simplify the xml file.


<item type="sharedQuery" id="grants">
	    <query>
	       <sql><![CDATA[Select PRIVILEGE, GRANTEE, GRANTABLE, GRANTOR, COLUMN_NAME object_name from all_col_privs where table_schema = :OBJECT_OWNER and TABLE_NAME =  :OBJECT_NAME union all Select PRIVILEGE, GRANTEE, GRANTABLE, GRANTOR, table_NAME object_name from all_tab_privs where table_schema = :OBJECT_OWNER and TABLE_NAME = :OBJECT_NAME]]></sql>
        </query>
  </item>

Since this item's type is sharedQuery it's now available to be used. This is very easily done as such


 <item type="editor" node="TableNode">
    <title><![CDATA[Grants]]></title>
	    <query id="grants" />
 </item>

From here out I'll just use sharedQueries so the xml is easier to read but anywhere there's a query tag it can be a tag that includes the sql.

So now you say you need 2 independent grid of data for a given node. This is easy as well.


 <item type="editor" node="TableNode">
   <title>Dependencies</title>
	<query id="grants" />
	<bottomquery>
	    <title>References</title>
		<query id="someothersql" />
	</bottomquery>  >
 </item>

Ok now 2 grid of stuff is good but now you want master detail. This will re-run the bottom code with the row which is double clicked on in the top grid ( master ). The thing to notice in the following examples is the use of bind variables. The subquery can reference and column in the master as a bind. In this first example there's a column names TABLE_NAME in the first sql statement. The second sql has :TABLE_NAME.


  <item type="editor" node="MViewNode">
    <title><![CDATA[Indexes]]></title>
	      
        <subquery>
           <query>
	        <sql><![CDATA[ SELECT :TABLE_NAME FROM SYS.DUAL]]></sql>
          </query>
        </subquery>
 </item>

Lastly, master detail again but the detail is text like the triggers tab on table. The only difference here is that the subquery has a type of "code". This keys SQL Developer to get the first column of the resulting sql statement and dump it into a text area.


<item type="editor" node="TableNode">
    <title><![CDATA[Triggers]]></title> 
	    <query id="triggerSql" />
        <subquery type="code">
	      <query>
	        <sql><![CDATA[ SELECT DBMS_METADATA.GET_DDL('TRIGGER',:TRIGGER_NAME,:OBJECT_OWNER) SQL FROM SYS.DUAL]]></sql>
          </query>
        </subquery>
 </item>