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.

Add the ability to make a connection read only

To show how simple it is to add functionality to the context menus in sqldeveloper without writing any java like I posted before about. I'll take a case which was on the forums today about how to make a connection read only. As most people probably know there's a set transaction read only; command which can be issued which prevents the session from issuing updates. To make it easy to toggle this in sqldeveloper, here's what I did:

The format of the xml is the same as the post before. Save this xml to a file:

<?xml version="1.0" encoding="UTF-8"?>
<items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="dialogs.xsd">
   <item type="CONNECTION" reload="true">      
     <title>Set RO</title>     
     <prompt type="confirm">
        <label>Confirm Setting Connection to Read Only.</label>
     </prompt>
     <sql>
        <![CDATA[begin dbms_transaction.read_only; end;]]>
     </sql><help>Issues dbms_transaction.read_only; </help>      
     <confirmation>
     ><title>Confirmation</title>
        <prompt>Success</prompt>
     </confirmation>
   </item>
   <item type="CONNECTION" reload="true">
     <title>Set RW</title>
     <prompt type="confirm">
        <label>Confirm Setting Connection to Read Write.</label>
     </prompt>
     <sql>
       <![CDATA[begin dbms_transaction.read_write; end;]]>
     </sql>
     <help>Issues dbms_transaction.read_write; </help>
     <confirmation>
       <title>Confirmation</title>
       <prompt>Success</prompt>
     </confirmation>
  </item>
</items>

Now go into the Preferences->User Defined Extensions. Prior to 1.1, this would have needed some java coding but now it's very easy to load this xml file. While this shows the file on the file system, it could just as easily use referenced a URL such as http://myhost.mycompany.com/some/path/to/somewhere/ro.xml

Now when after I restart the tool, I'll see the menu option and here's what happens when I try to update the data in a table:


Then If I want to edit again, I choose the other menu which sets things back to read write.

Better APEX urls

I was cleaning up a webserver and found this code I wrote a few years ago to help make HTMLDB now Application Express entry points nicer. Hopefully everyone knows Application Express and uses it everyday, if not you should go sign up and kick the tires at apex.oracle.com. Prior to working on SQL Developer, I did some work in Application Express and built some internal systems with Carl and others. One issue I had was an easy way to map a url to an application in order to make the entry point nice and easy like http://myapp.mycompany.com which really point to http://apex.oracle.com/pls/otn/f?p=MYAPP:MYPAGE

Now you may think just a simple Apache re-write would solve this and you'd be correct but I had to add multiple re-write and didn't want to bounce Apache in order to add new ones or change existing ones. So the answer was in mod_perl. What I did was make a mapping file as follows:

  mycompany.com;/pls/apex/f?p=3701:1
  internal.mycompany.com;/pls/apex/f?p=3701:1
  yourcompany.com;/pls/apex/f?p=111:1
  /path;/pls/apex/f?p=1:1

You can see from the mapping file that it's fairly flexible. You can map /myapp or myapp.mycompany.com. The best part about this solution is that the perl code below re-reads the file every 5 minutes in case you added more mappings and no bounce of apache.

These 2 lines will have to be placed early in the httpd.conf so it can have the opportunity to look at all the incoming request. Here's the 2 lines needed to change in the httpd.conf file

PerlModule HTMLDB::VirtualServer
PerlTransHandler HTMLDB::VirtualServer

This code will have to be adjusted for location of the config file and maybe you'll adjust the reloading of the file but it should mostly work. This file will have to be placed into the perl library path with something like this:

SetEnv PERL5LIB  "/mypath/to/the/path"

package HTMLDB::VirtualServer;
#
#
# Apache httpd.conf entry
# PerlModule HTMLDB::VirtualServer
# PerlTransHandler HTMLDB::VirtualServer
#
#
# Sample config file
#mycompany.com;/pls/apex/f?p=3701:1
#internal.mycompany.com;/pls/apex/f?p=3701:1
#yourcompany.com;/pls/apex/f?p=111:1
#/path;/pls/apex/f?p=1:1
#
#
  use Apache::Constants qw(REDIRECT DECLINED); 
  use strict;
  # path to config file
  my $configfile = "/export/home/oracle/HTMLDB/htmldbvirtual.conf";
  my $debug = 0;
  my $lastLoad;
  my %names = ();


# simple routine to see what's going on
sub logMe{
   my $logLine = shift;
   if ( $debug eq 1 ) {

  # log file if $debug = 1

     open(L,'>>/tmp/htmldbvirtual.log');
     print L $logLine;
     close L;
  }
}
  
# load the config file  
sub loadConfigFile{
  my $now = time;apex/home
  # cache the config for 5 minutes then reload
  if ( ( $now - $lastLoad ) > 600 ) {
    open CONF,$configfile;
    my $name;
    my $uri;
    while (){
      chomp;
        ($name,$uri) = split /;/;
          $names{$name} = $uri;
    }
    $lastLoad = time;
      if ( $debug) {
        while ( my ($key, $value) = each(%names) ) { logMe( "L:$key => $value\n"); }
       }
  }
   %names;
 }


 sub handler {
      my $r = shift;
      my %names = loadConfigFile();
      logMe("Request:" . $r->uri . "\n");

     if  (  $r->uri eq "/" 
            ||  $r->uri eq "/index.html" 
            ||  ( length($names{$r->uri}) > 0 ||  length($names{$r->uri."/index.html"} ) > 0 )  ) {
            	
      # grab vars for use.
      my $s = $r->server();
      my $hostname = $r->hostname();
      my %args = $r->args;
      my $base =  $s->port() == 443 ? "https://" : "http://";

      if ( length($hostname) > 0 ) {
        $base = $base . $hostname;
      } else {
        $base = $base . $s->server_hostname();
      }

      if ( $s->port() != 80 && $s->port() != 443 )  {
       $base = $base . ":" . $s->port();
      }
      logMe($base . "\n");

      my $key = length($names{$r->uri} ) > 0 ? $r->uri : $hostname ;

      if (  length($names{$key} ) > 0
          && ! $args {"p"} ) {
           #$r->header_out(Location => $base . $hostname );
           $r->header_out(Location => $base . $names{$key} );

         logMe("HTMLDB::VirtualServer:". $base .  $names{$key} . "\n");
         return REDIRECT; # means we did a redirect
      }
   }
         return DECLINED; # means we did not handle the request
  }
1;

Sql Worksheet Tips

in a sql worksheet try some of these:

ctrl-shift-0..9

This will pop the 0-9th sql form the history and replace the current sql worksheet contents.

The rest need to be run as if they are scripts ( F5 ):

Want to change the tab in the worksheet while a script runs to see an easy status.

   set worksheetname hello

Working on APEX or mod_plsql routines?

   set owacgienv on
   set getpage on
   begin
      htp.p('hi');
   end;
   /

This will make the owa.init_cgi_env before a plsql block and owa.get_page after and print in the script output.


Lastly, if you have any issue you can turn debug on with this and then take it to the forums to post and get answers:

setloglevel oracle INFO