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>

XML Based right click menus

See what I miss by going on vacation. I just happen to be looking at old blogs and ran across this one. So this one will explain how to add to the right click menu using nothing but xml. Hopefully most of the xml is obvious. As an example, I'll use the right for table to change logging vs. nologging.

<items>
<item type="TABLE">
   	<title>Logging</title>
  	<prompt>
	      <label>Logging:</label>	      <value><![CDATA[STATIC:LOGGING:NOLOGGING]]></value>
	      <default><![CDATA[select logging from all_tables where owner = :OBJECT_OWNER and table_name = :OBJECT_NAME]]></default>
  	</prompt>  	
  	<prompt type="confirm">
	       <label>Change logging status of selected table?</label>
  	</prompt>  	
  	<sql><![CDATA[alter table "#OBJECT_OWNER#"."#OBJECT_NAME#" #0#]]></sql>
  	<help>Change the logging status of a table.  LOGGING will log changes to the redo log file.<br/>
  NOLOGGING will not log changes to the redo log file.</help>
  	<confirmation>
  	       <title>Confirmation</title>
  	       <prompt>Table "#OBJECT_NAME#" set to #0#</prompt>
                </confirmation>
  </item>
  </items>

While there's a lot more xml, hopefully the simplicity of this is not lost. Here's what it looks like in the tool:



Hopefully most of the xml is obvious. Here's a break down of the tags:

Prompts:
prompt - This is a prompt for user input. The user's input can be referenced when submitted as #0# for direct substituition.
label - This is the label of the prompt
value - This has a few choices.
- If omitted , the user will be presented a text field for free-form input
- To have a static list of values STATIC:A:B:B
- To have a select base list <value><![CDATA[select :OBJECT_OWNER from dual ]]></value> ( more on binds later )
default - This can be a single value or if a select SQL Developer will grab the first column of the first row

The next prompt is a confirm, a prompt of this type is just text to the user. There is no input.

Now for the actual code to be executed against the DB. The contents of the sql tag will have #0# ... #N# replaced with the values from the user prompts. Also available are:
#OBJECT_NAME# - name of the object
#OBJECT_TYPE# - type of the object
#OBJECT_OWNER# - owner of the object
#COLUMN_NAME# - name of the column ( if a column was right clicked
#OBJECT_ID# - object_id from all_objects
#USER# - user which is connected to the database

The rest should be easy if your eyes have not glazed over by now the rest should be easy. The help is just what is shown when the help button is clicked.
The confirmation is what is presented to the user once the sql has been executed successfully.

Finally pass the command like -Draptor.user.contextmenu=/path/to/file:/path/to/file2

Now that you've made it this far. You can see how the values entered by the user are passed to the sql to be executed. This mean the code in the sql block has to handle all the inputs and deal with them. If you really want to see this, check out the code generated by Table->Column->Normalize.


----- Updated 5/9------
I should have said to pass the new menus, these are the choices
sqldeveloper -J-Draptor.user.contextmenu=/path/to/file:/path/to/file2
or edit sqldeveloper.conf
AddVMOption -Draptor.user.contextmenu=/path/to/file:/path/to/file2

Forgotten tabs

So, you say we missed adding a partition tab or a MV column stats. I'd say it's an opportunity for you to add it exactly how you'd like it. This can all be done via xml and a command line flag


It's really easy to add tabs to an object when clicked in the tree.

<?xml version="1.0" encoding="UTF-8"?>
<items>
 <item type="editor" node="TableNode"  vertical="true">
    <title><![CDATA[Modifications]]></title>
      <query>
         <sql><![CDATA[select * from ALL_TAB_MODIFICATIONS where  table_owner = :OBJECT_OWNER and table_name = :OBJECT_NAME]]></sql>
        </query>
 </item>
</items>

Hopefully most of this is pretty straight forward. Here's a few thing explained:

First the item tag has a couple attibutes

type="editor"This is required right now and the only value is "editor"

node="TableNode" This is the node which the tab will show up on. See the list below for all supported values.

vertical="true"This will make the report vertical since I know this sql will return only 1 row. It makes it much easier to read. Leave this attribute out or no value and the report will be a normal data grid of the results

Here's the list of valid values for the node attribute:
IndexNode
TriggerNode
PlSqlBaseNode
MViewLogNode
DatabaseLinkNode
RecycledObjectNode
SequenceNode
SynonymNode
MViewNode
ViewNode

The thing that probably stands out is the binds. Here's the list of binds that are supported:
:OBJECT_NAME name of the object
:OBJECT_TYPE type of the object
:OBJECT_OWNER owner of the object
:COLUMN_NAME name of the column ( if a column was right clicked
:OBJECT_ID object_id from all_objects
:USER user which is connected to the database

Now all that's needed is to tell SQL Developer to load the file. This is very easy:

-Draptor.user.editors=/home/klrice/editors.xml

There can only be one file right now. In a future release, I'll get a registerEditors call added as well as the ability to have multiple files passed on the command line just like the snippets and reports have now.

If people find this usefull , well I'll do it anyway. I'll follow this up with how to add master/detail ( index columns ) and top results with bottom code ( triggers ).

SQL Developer Extensions and Eclipse

Want to write an extension to SQL Developer but you only use Eclipse? There's a post on the forum about how to use Eclipse to write an extension to sql developer so I figured I'd put it here so it's not lost into the of the forum.


This is actually quite easy to setup. This can be checked by running the shell script with a -x, so bash -x sqldeveloper. This will show what is being executed

First make a run/debug configuration.

1. Main Tab
   
   • Main Class: oracle.ideimpl.Main
     
   • Check the Include libraries when searching for a main class checkbox
     
   
   
2. Arguments Tab
   
   • The paths in the VM arguments should be adjusted to your specifics. Also if there is a space in the paths they will have to quoted like "C:\Document and Settings\sqldeveloper...."
     
     
   • VM Arguments: -Dide.product=oracle.sqldeveloper -Dide.splash.screen=raptor-splash.gif -Xverify:none -Xmx512M -XX:MaxPermSize=128M -Dice.browser.forcegc=false -Dice.pilots.html4.tileOptThreshold=0 -DEDITOR_J2SE_VERSION=1.5 -DINSIGHT_OMIT_HIDDEN=true -Doracle.xdkjava.compatibility.version=9.0.4 -Dide.debug.controller.stack=false -Dceditor.java.parse.small=300 -Dceditor.java.parse.large=1500 -Dide.extension.cpmissing.nowarn=true -Dcompiler.vmargs=-mx512m -Djbo.debugoutput=silent -Djbo.showdebugwarningbanner=false -Dide.config_pathname=/home/klrice/sqldeveloper/jdev/bin/sqldeveloper.conf -Dide.startingcwd=/home/klrice/sqldeveloper/jdev/bin -Dide.user.dir=/tmp
     
   • Working directory: choose Other . Then enter the path to /jdev/bin
     
   
   
3. Classpath Tab
   
   • Add the following ( all paths are relative to )
     
   • /ide/lib/ide.jar
     
   • /ide/lib/javatools.jar
     
   • /ide/lib/xmlparserv2.jar
     
   • /ide/lib/help4.jar
     
   • /ide/lib/share.jar
     
   • /ide/lib/javax-ide.jar
     
   
   

Now the run configuration is setup and ready to use. You should now be able to run and debug an extension.