Monday, April 16, 2007

Interactive Reports part 3

The next step to having all these great reports in xml files would be to organize them. This one is the simplest by far. The name of the folder is split on the / and sub folders are made. This is very handy when a report gets into 100s of lines of xml since the reports can now be stored in multiple files but foldered together in the tool. Like in sqldev the "Data Dictionary Reports -> Database Administration -> Sessions -> Session Reports" report which allows kill/trace sessions is over 500 lines by itself and in a separate file.


<folder>
<name><![CDATA[my/great/report]]></name>
<display>.....</display>
</folder>



Wednesday, April 11, 2007

Interactive Reports part 2

In this post, I'll show how to add a context menu to a report. The same thing can be done to a tab which has been added which is described in Question #1 here.

For more on the <display> tag see this post. For more on the <item> tag see this post.

Here's an xml file of the 2 combined. The things to look at are that the <item> is inside the <display> . Then look at the bind variables :TABLE_NAME which will be taken from the row clicked on in the grid. Lastly in the sql and confirmation there's #TABLE_NAME# which will also come from the row clicked on and do a replacement of that text.
<?xml version="1.0" encoding="UTF-8"?>
<displays>
<display id="" type="" style="Table" enable="true">
<name><![CDATA[Good Stuff Report]]></name>
<description><![CDATA[my great report]]></description>
<tooltip><![CDATA[This is the best report ever]]></tooltip>
<query>
<sql><![CDATA[select * from user_tables]]></sql>
</query>
<item>
<title>Logging</title>
<prompt>
<label>Logging:</label>
<value><![CDATA[STATIC:LOGGING:NOLOGGING]]></value>
<default><![CDATA[select logging from user_tables where table_name = :TABLE_NAME]]></default>
</prompt>
<prompt type="confirm">
<label>Change logging status of selected table?</label>
</prompt>
<sql><![CDATA[alter table "#TABLE_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 "#TABLE_NAME#" set to #0#</prompt>
</confirmation>
</item>
</display>
</displays>


Here's the menu choice:



Here's the dialog:



Here's the confirmation:


Monday, April 09, 2007

Extending the Navigator with children

I started to answer this question with a reply to the comment but thought I'd just put a post together since this is should make it easier to find.

Question #3: Do you have the xsd file ?
There is an XSD for the navigator in sqldev 1.1 and it's located here. In the xsd you'll notice there's quite a bit that I didn't mention before.

Question #2: How to add multi-level folder like "Storage" > "Datafiles" > "Files" ?
The full xml from the first post is here. The part that will add the children is the <node/> tag which quite empty in the example. All that has to be done to add children is change the <node /> tag to something like this with PARENT_NAME being the bind for the immediate parent in the tree.
<node>
<childType id="COLUMN"">
<icon RSKEY="/path/to/my/icon.png"/>
<queries >
<query>
<sql>
<![CDATA[SELECT COLUMN_NAME
FROM SYS.ALL_TAB_COLUMNS
WHERE OWNER = :SCHEMA
AND TABLE_NAME = :PARENT_NAME
ORDER BY COLUMN_ID]]>
</sql>
</query>
</queries>
</childType>
</node>


Question #1: How to link navigator "id" with editor "node" in the case I want to add say "SQL*Profiles" ?

The posts I did a while back explain how to get tabs added with Forgotten tabs however this has changed quite a lot in 1.1. The simpliest way now to add a tab to the broswing is to goto into the reports section and define your report exactly how you'd like to see it. Be sure to use :OBJECT_NAME , :OBJECT_TYPE , :OBJECT_OWNER as the bind variables. Then once everything is setup just right click on the report and choose Export. Export this file to some location and open it in your favorite xml editor. You'll get something that looks like this. Now in the <display tag add objectType="TABLE" Oracle will be the default for any tabs added to change to something else add connType="TimesTen".

The resulting display tag will look something like this:

<display id="" style="Table" enable="true" connType="Oracle" objectType="TABLE">

Now to add this file, it just like adding a file for the navigator. Go into the preferences and click add row , choose EDITOR.