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

Learn something new everyday

I was testing various things in sql developer and you'd think I'd know what's in there but not so. As most people know sql developer uses swing for the UI. Swing components allow you to render the content as html. This is really easy for example a JLabel.setLabel('<html><b>hi') will bold the text hi. The limitation is it's html 3.2 in java 1.5.

So I tried this:

select '<html><b>'||dummy from dual;
or
select '<html><i>'||dummy from dual;
or maybe more useful:
select '<html><i>'|| object_name object_name,
     '<html><b>'|| object_id object_if,
    decode(status,'VALID',null,'<html><font color="red"><u>')||status status
from user_objects

And here's what the last one looks like: