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>

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:

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.

Interactive Reports part 1

The reports in sqldev 1.1 change a lot. Among the changes are different types of reports, master details , context menus and drill downs. Alot of these features can be created/changed by the Create Report dialog but a number of them can not. I'll try and explain how to do things not in the dialog in the next few blogs working up to a interactive report which will show how to add these to your own reports.

In this post I'll talk about about the basic changes to the xml. In 1.0, the report looked like this:

<report type="report" 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 ......]]></sql>
  </query>
</report>

If you had some reports in 1.0 and have since start 1.1, you can look at the file in $HOME/.sqldeveloper/UserReports.xml and see the file was migrated. This same report in 1.1 will look like:

<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 dummy from dual]]></sql>
  </query>
</display>

While this mostly looks the same, the noticeable addition is the id, type, style attributes. The id attribute is what shows up in the LOV of the Advanced Tab of the dialog when the Add Report is pressed. The type attribute isn't used in this example yet. The style is what kind of report it is and the default styles are

• Table - normal grid of data
• Code - A big text field of the data concatenated together
• Chart - renders a chart from the data
• Script - runs the contents of the sql tag as a sql script
• plsql-dbms_output - This will grab the dbms_output from the plsql and render it as html
• More later on how to create your own

To make a master detail report all that's needed is to add another display tag inside the existing one. The binds for the child will be looked up into the row which is selected in the parent.

<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 dummy from dual]]></sql>
  </query>

  <!-- child -->
  <display id="" type="" style="Table" enable="true">
    <name><![CDATA[Details of stuff ]]></name>
    <description><![CDATA[my details ]]></description>
    <tooltip><![CDATA[This is very interesting stuff]]></tooltip>
    <query>
      <sql><![CDATA[select :DUMMY parent_data from dual]]></sql>
    </query>
  </display>
</display>

Now I've shown nothing more than what the Report dialog can do to create a master/detail report. The first thing you can do which is not in the dialog is take these display tags and nest them as deep as you'd like. Such as this which adds a grandchild to this example:

<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 dummy from dual]]></sql>
  </query>

    <!--  child -->
    <display id="" type="" style="Table" enable="true">
       <name><![CDATA[Details of stuff ]]></name>
       <description><![CDATA[my details ]]></description>
       <tooltip><![CDATA[This is very interesting stuff]]></tooltip>
       <query>
         <sql><![CDATA[select :DUMMY parent_data from dual]]></sql>
       </query>
      
        <!-- grandchild -->
        <display id="" type="" style="Table" enable="true">
           <name><![CDATA[Details of stuff ]]></name>
           <description><![CDATA[my details ]]></description>
           <tooltip><![CDATA[This is very interesting stuff]]></tooltip>
           <query>
              <sql><![CDATA[select :PARENT_DATA parent_data from dual]]></sql>
           </query>
        </display>
    </display>
</display>

Targeted Job Ads?

While checking my gmail, I noticed this sponsored link. Targeted job ads based on history seems like a very easy use of the massive data google compiles.