Report Hierarchical Data With APEX

September 5, 2008 12 comments

Anyone familiar with Oracle and hearing the word hierarchical immediately thinks of the sql CONNECT BY clause.  And I would bet that when they think about displaying this data, they would use some sort of tree widget.  APEX has a built-in tree widget and for many applications, it works fine.  

The drawback to the built-in tree widget is that it brings back the entire dataset on the initial page render.  Good and Bad.  Saves round trips to the server, but could take a lot of initial time depending on the size of the data.  Showing/hiding branches of the tree with many elements also can make the application feel sluggish.  

On my current project, I needed to build an interface against just such a dataset.  So I needed an alternative to the built-in tree.  I looked at either writing my own tree using javascript and AJAX or using an already developed one.  Neither one appealed to me.  They both added development complexity and maintaining them weighed on my final decision.

What I chose to do was build a breadcrumb-like report displaying the path traversed and a second report displaying the elements at the current level.  All default APEX functionality.  Simple clean interface.  Easily developed and easily maintained.  You can check it out here.

Let me quickly walk you through how it was built:

1. Create a report on the base table constrained by the hidden item.  The query should look something like:

select *
  from emp
 where nvl(mgr,-1) = :p5_empno

I then linked the ENAME column back to the same page passing the EMPNO value into :P5_EMPNO.
2. Create a hidden Item to hold the manager’s id.  I put it in the step 1’s report region and called it P5_EMPNO and set its default value to -1.

3. Create a PATH report to manage the traversing the data.  This is where the magic happens.  I make use of the SYS_CONNECT_BY_PATH() function in conjunction with the START WITH…CONNECT BY clause. The query I used was:

select '<a href="f?p=' || :app_id || ':5:' ||
         :app_session || 
         '::::p5_empno:-1">Top</a> >>> ' || 
             '<a href="f?p=' || :app_id || ':5:' ||
             :app_session || '::::p5_empno:' || empno ||
             '">' || ename || '</a>', ' : ' ), 4 ) path 
  from emp 
 where empno = :p5_empno 
 start with mgr is null 
connect by prior empno = mgr

Some other tweeks to this region. No Pagination. Report Template of Value Attribute Pairs. Layout above the first report region. No region template.

4. ???

5. Profit!

Now you can use the main report to drill into the children of the row you selected, all the while maintaining the context of where you are in the hierarchy with the path.

It’s a quick simple technique, and 100% APEX. No additional javascript libraries or custom coding to accomplish a clean and simple interface. And it will be easily maintained, either by you, or by the developer that comes after you.

Let me know what you think.

Categories: APEX, SQL Tags: , , , ,

Google Chrome – The Newest Thing In Browsers

September 2, 2008 4 comments

Google entered the browser world today with their own, built from the ground up, open sourced browser called Chrome.  You can download it here.  It’s currently in Beta ( but what of Google’s is not a beta ) and only available on XP and Vista ( but the Mac and Linux versions are in the works ).  I have not had time to do anything more than install it and post this using chrome.   Google released a comicbook of sorts to describe all the new features and functions.  It all sounds impressive.  I really like the idea that each tab is its own process and not a thread in a monster browser process.  

Should help with the ever growing memory hogs like Firefox and IE.  Anyway, check it out and let me know what you find out.  Next stop for me is to put it through its paces with APEX.

Categories: Uncategorized Tags: , ,

APEX 3.1.2 Is Available

August 29, 2008 6 comments

In case you were not aware, Oracle has released a new version of Application Express. From the APEX site on OTN:

Aug 28

I just finished upgrading my local apex instance. I chose to apply the patch instead of a full install and it worked fantastic. Took less than 5 minutes to patch on my dell 620 laptop  running Ubuntu 7.10.  4 minutes to apply the patch and another minute to load the new images.

I read the install notes and there are more than 80 bug fixes and enhancements.

Has anyone else installed it yet? What do you all think? I’ll blog my impressions once I kick the tires for a bit.

Categories: APEX Tags: , , ,

Regular Expression Searching With APEX

August 26, 2008 1 comment

Ever since Oracle introduced regular expression functions in the database, I have been a big fan of them.  They really make certain tasks much easier and give you added functionality.  One place I always used them is in my APEX apps where I supply the user with a search box to constrain the results of a query.

The old way I would have written the query constraint:

where upper(COL1) like ‘%’ || upper( :P1_SEARCH_TEXT ) || ‘%’

Now using REGEXP_LIKE() you can achieve the same functionality while simplifying the constraint.  And you get the added bonus of advance searching for the power user.

where regexp_like( COL1, nvl(:P1_REGEXP,COL1), ‘ix’ )

The first thing you will probably notice is that there is no conditional operator in that expression.   None is needed.  The regexp_like function is a boolean function and is used as such.

So now, any basic search will work just as before.  But now, you can issue regular expression searches as well.  If you want all the entries that start with S, search for ^s

End with R, use r$

Start with S and end with H, use ^s.*h$

Start with S or end with R, use ^s|r$

All 4 letter names, use ^….$

Contains B, C, D or K, use [b-d,k]

How about all names with double letters, use (.)\1

There are many more things you can do.  If you want to read up about regular expressions in oracle, docs can be found here.

Using regexp_like() opens up the advanced searching for all the regular expression junkies without compromising the simple searching and it add NO complexity to your applications.

I have staged a simple demo on, here

More Google Geocoding

August 11, 2008 7 comments

Ok, for those who liked the prior post on Google geocoding via HTTP, you will enjoy this as well.  It builds on what was done in that example but retrieves even more information from google.

Before we used an output type of CSV and that only gave us back the LATITUDE and LONGITUDE.  But there is more information that we can derive from the geocoding service if we use a different output type.  If we request the result in XML, then we are given the complete address as well as the longitude and latitude.  But we have to do a little extra work to parse out the results.

I have written a PL/SQL object type to do just that.  Why an object type and not a Package?  Personal preference.  In this case, it just seemed to feel right to make it an object.  With some simple code factoring, it could easily be converted into a package.  But since most PL/SQL developers don’t code object types, thought it might be interesting.

Let’s take a look at the object’s specification.

create or replace
type google_geocode_type as object (

  -- Attributes
  response xmlType,
  search varchar2(32767),

  -- Constructor Methods
  constructor function google_geocode_type(
    p_search varchar2 default null ) return self as result,

  -- Execute Geocode
  member procedure execute(
    p_search varchar2 default null ),

  -- Getter Routines
  member function get_result_count return number,

  member function get_geometry(
    p_sequence number default 1 ) return sdo_geometry,

  member function get_latitude(
    p_sequence number default 1 ) return number,

  member function get_longitude(
    p_sequence number default 1 ) return number,

  member function get_address(
    p_sequence number default 1 ) return varchar2,

  member function get_street(
    p_sequence number default 1 ) return varchar2,

  member function get_city(
    p_sequence number default 1 ) return varchar2,

  member function get_state(
    p_sequence number default 1 ) return varchar2,

  member function get_zipcode(
    p_sequence number default 1 ) return varchar2,

  member function get_county(
    p_sequence number default 1 ) return varchar2,

  member function get_country(
    p_sequence number default 1 ) return varchar2


The object’s body is a bit long and I will not post all of it here but will make it all available for download.  But I will post a few of the member functions and talk about what they are doing.

constructor function google_geocode_type(
  p_search varchar2 default null ) return self as result is
begin := p_search;

For those who are not familiar with PL/SQL object types, a constructor function is the method called when you create a new instance of the object (just like java).  Every object has a default constructor, but you can define your own so to manage the object’s creation as well as defaulting certain object attributes.  For me, I did not want the use to have to supply a value for the attribute RESPONSE so I defined my own.

The EXECUTE() method is very similar to the the prior example.  I made a minor change to handle the case where we get back more than 32K of data.

member procedure execute( p_search varchar2 default null ) is
  l_http_req  utl_http.req;
  l_http_resp utl_http.resp;
  l_response long;
  l_clob clob;
  if p_search is not null then := p_search;
  end if;
  l_http_req := utl_http.begin_request(
                  url => '' ||
                  '?q=' || utl_url.escape( ) ||  -- address to geocode
                  '&output=xml' ||  -- XML return type
                  '&key=abcdef' );  -- site key
  l_http_resp := utl_http.get_response( l_http_req );
      utl_http.read_text( l_http_resp, l_response );
      l_clob := l_clob || l_response;
    end loop;
    when utl_http.end_of_body then
  utl_http.end_response( l_http_resp );
  self.response := sys.xmlType.createXML( l_clob );
end execute;

Now depending on how specific the address you supply, you may get more than one answer.  I sent in a value of “1600 Pennsylvania Ave” and it returned 10 results.  If I made that search more specific, say “1600 Pennsylvania Ave, DC”, then I get just a single result.  But since we don’t know how many results we will get, we need to parse out all the results.  So the first thing we need to know is how many results came back.

member function get_result_count return number is
  l_count number;
  select count(*)
    into l_count
    from table( XMLSequence( extract(
                 'xmlns=""' ) ) );
  return l_count;
end get_result_count;

This method calculates how many results are in the XML returned by finding each <Placemark> section,  and then counting them using the XMLSequence and Table functions in SQL.

Now we know how many results, we can begin to ask for the individual values.

member function get_zipcode(
  p_sequence number default 1 ) return varchar2 is
  return self.response.extract(
           '//Placemark[@id="p' || p_sequence || '"]',
           'xmlns=""' ).extract(
             'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"' ).getStringVal();
end get_zipcode;

Here in the GET_ZIPCODE() method, we use the EXTRACT() method on the XMLType variable RESPONSE and XPATH searching to retrieve the proper zipcode.  I had to do and extract() of an extract() because the relavant peices of data were in different namespaces in the XML.  (My XPATH searching ability is not that strong so there may be an easier way?  If you know of one, let me know.)

All the other get methods are similiar to this example.

Now let’s see how we would use this.

  1  declare
  2    g google_geocode_type := new google_geocode_type();
  3  begin
  4    g.execute( '1910 Oracle way, reston' );
  5      dbms_output.put_line( 'lat/lon: ' ||
  6                            g.get_latitude()||'/'||
  7                            g.get_longitude() );
  8      dbms_output.put_line( 'Address: ' || g.get_address() );
  9      dbms_output.put_line( 'County: ' || g.get_county() );
 10* end;
SQL> /
lat/lon: -77.351976/38.954872
Address: 1910 Oracle Way, Reston, VA 20190, USA
County: Fairfax County

PL/SQL procedure successfully completed.

Given just the street and the city, google was able to determine the full address and the latitude and longitude.

Now let’s see what happens when we supply even less information

  1  declare
  2    g google_geocode_type := new google_geocode_type();
  3  begin
  4    g.execute( '1600 Pennsylvania Ave' );
  5    for i in 1 .. g.get_result_count() loop
  6      dbms_output.put_line( 'lat/lon: ' ||
  7                            g.get_latitude(i)||'/'||
  8                            g.get_longitude(i) );
  9      dbms_output.put_line( 'Address: ' || g.get_address(i) );
 10    end loop;
 11* end;
SQL> /
lat/lon: -90.229033/38.617594
Address: 1600 Pennsylvania Ave, St Louis, MO 63104, USA
lat/lon: -76.880242/42.031789
Address: 1600 Pennsylvania Ave, Pine City, NY 14871, USA
lat/lon: -82.984848/42.36331
Address: 1600 Pennsylvania St, Detroit, MI 48214, USA
lat/lon: -76.634388/39.30307
Address: 1600 Pennsylvania Ave, Baltimore, MD 21217, USA
lat/lon: -96.77534/32.759033
Address: 1600 Pennsylvania Ave, Dallas, TX 75215, USA
lat/lon: -81.620803/38.360844
Address: 1600 Pennsylvania Ave, Charleston, WV 25302, USA
lat/lon: -80.27183/40.687529
Address: 1600 Pennsylvania Ave, Monaca, PA 15061, USA
lat/lon: -79.8573/40.362383
Address: 1600 Pennsylvania Ave, West Mifflin, PA 15122, USA
lat/lon: -117.32709/34.084866
Address: 1600 Pennsylvania Ave, Colton, CA 92324, USA
lat/lon: -75.185584/40.121061
Address: 1600 Pennsylvania Ave, Oreland, PA 19075, USA

PL/SQL procedure successfully completed.

There sure are a lot of 1600 Pennsylvania Ave’s.  But if you notice, the most famous one is not even in the list?  Why?  I have NO idea???  But if we make our search a bit more specific…

  1  declare
  2    g google_geocode_type := new google_geocode_type();
  3  begin
  4    g.execute( '1600 Pennsylvania Ave, dc' );
  5    for i in 1 .. g.get_result_count() loop
  6      dbms_output.put_line( 'lat/lon: ' ||
  7                            g.get_latitude(i)||'/'||
  8                            g.get_longitude(i) );
  9      dbms_output.put_line( 'Address: ' || g.get_address(i) );
 10    end loop;
 11* end;
SQL> /
lat/lon: -77.036698/38.897102
Address: 1600 Pennsylvania Ave NW, Washington, DC 20006, USA

PL/SQL procedure successfully completed.

… we find that famous one that will be getting a new family moving in this January.

I hope this was interesting and helpful.  Again, if you want to get the code and try it out yourself, its right here.  And as always, if you have any questions, just ask.

Categories: PL/SQL Tags: , , , ,

Quick Geocoding Using Google

August 5, 2008 10 comments

Location based services are all the rage these days.  Everyone is including it into their applications and there are many ways to accomplish this.  Oracle supports geocoding in the database but you need to own the geocoding dataset.  To get that dataset, you would probably need to purchase it from some provider like NAVTEQ.

But there are services out there, like Google, that allows us common folk to use its data, but you have to know how to ask for it.  Google offers a ton of Javascript APIs for mapping and geocoding, but that does nothing for us working in the database with PL/SQL.  I routinely want to geocode the addresses in a table on insert and update via a trigger.  Javascript is not going to help you here.

Luckily, Google supports geocoding via HTTP and I have written a simple function to access it.  ( Oracle also supports a geocoding solution via HTTP and has a hosted site to support it.  I will blog about it in the future. )

create or replace
function google_geocode( p_address varchar2 ) return sdo_geometry is
 l_http_req  utl_http.req;
 l_http_resp utl_http.resp;
 l_response long;
 l_latlon long;

 l_http_req := utl_http.begin_request(
   url => '' ||
          '?q=' || utl_url.escape( p_address ) ||  -- address to geocode
          '&output=csv' ||                         -- simplest return type
          '&key=abcdef' );                         -- Google API site key

 l_http_resp := utl_http.get_response( l_http_req );
 utl_http.read_text( l_http_resp, l_response );
 utl_http.end_response( l_http_resp );
 l_latlon := substr( l_response, instr( l_response, ',', 1, 2 ) + 1 );

 return sdo_geometry(
          2001, 8307,
          sdo_point_type( to_number( substr( l_latlon, instr( l_latlon, ',' )+1 )),
                          to_number( substr( l_latlon, 1, instr( l_latlon, ',' )-1 )),
                          null ),
          null, null );
end google_geocode;

That’s it.  It’s not that complicated.  You just need to send via HTTP the address and you will get back a comma separated string which we parse out the latitude and longitude and stuff into a sdo_geometry type.

I’ll quickly dissect the code.

Line 9 – Initializing the HTTP call.  The URL includes 3 parameters ( q, output and key )

  • q is the address string to be geocoded.
  • output is the format that we want the out returned in.  I chose csv.  It’s the simplest for this example.
  • key is the your Google API key.  ( Read more about getting a Google API key ).

Lines 15-17 – Sending the request and fetching the result via HTTP.  The result that the comes back is in the form of:

  • HTTP_return_code,accuracy,latitude,longitude

Line 18 – Substring the latitude and longitude.

Line 20 – Creating and returning the SDO_GEOMETRY

Now you can call this from your pl/sql code in the database, or code in your ApEx app ( since that too is stored and run in the database ) and geocode any address.  Just like this:

SQL> select google_geocode( '1910 Oracle Way, Reston' ) g
  2    from dual;
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-77.351976, 38.954872, NULL), NULL, NULL)

I tried to set up a quick demo of this on here, but I need to get the access to run UTL_HTTP via setting up an ACL. I am trying to see if I can get the admins to allow that for me.

If you want to read more about the options Google offers for geocoding via HTTP, you can read about it here.

As always, if you have any questions, just ask.

Categories: PL/SQL Tags: , , ,

It’s Official – I Hate Traveling

August 4, 2008 2 comments

Early on in my career, I traveled a lot.  I was proud of my premier exec status on United which you archived by flying over 50,000 miles in a year.   Maintained that status for many years.   It was a badge that us travel warriors wore proudly.   I was also a savvy traveler.  I made sure to take flights on planes that had to most upgradable seats to maximize my chances of sitting in business class or first class.  I planned my hotel stays at the same hotel chain to acquire even more points.  It was a lot of planning, but at the time, I thought it was fun.  Boy how things have changed.

Over the last few years, I have lost all my status on United and I really don’t mind.  I think I made one trip all of last year and that is just fine with me.

Unfortunately, I have recently become involved on a project ( using ApEx ) that has required me to again join the traveling world.  Twice in the last two weeks I had to make a day trip from DC to Boston.  Not the worst trip, but still not fun.  Getting up at 4-4:30AM to make the 7AM from IAD to BOS and to get the 6:30PM back home is not my idea of a good time.

Not only am I losing out on my beauty sleep, on my most recent trip, I lost my suit coat.  Yup, gone.  Let me explain.  I was on the 6:30PM out of BOS back into IAD.  Delayed, of course, for 45+ minutes.  Not horrible but not how you want to finish up your day which started at 4:30AM.  So anyway, after we landed, all I wanted to do was get the hell off the plane, back to my car ( parked over a mile walk away ) and home.  There was a glass of Merlot with my name on it there.  Well, I’m not sure if I retrieved  the wrong jacket out of the overhead bin first, or the other guy did, but somehow I wound up with the wrong coat.  I carried it off the plane and all the way to the car.  When I tossed it in the back seat, I noticed it looked a bit off.  Then I noticed that it was a single breasted jacket while mine was a double breasted and the dark blue was a bit off from my pants.  This coat had a half eaten pack of Mentos in it and I don’t even like Mentos.

Sign, what to do?   Well I decided to walked back to the ticket counter ( probably another 1/3 mile ) which of course was closed.  It was now 9:25PM, and it closed at 9PM.  I could not get back to the gate since you can’t get through security without a boarding pass.  So I went to the lost luggage department.  After waiting in line for 15 minutes I finally spoke with someone.   She called down to the gate, but nothing was found on the plane.  So I left the jacket I had there with a note on it that if someone came to claim it, they would know how to get in contact with me to return my jacket.  I waited around for 15-20 minutes in the hopes that the other party had discovered the mix up before going home but no luck.

So not only was my latest trip extra long ( I got home about 11:30 or so ), but I’m also out one blue suit.  Anyway, if you happen to be the other person on this flight:

Flight/Equip.: Jetblue Airways Corp 1259  Airbus A320
Depart:  Boston (BOS)/Wednesday July 30 6:30 pm
Arrive:  Washington (IAD)/Wednesday July 30 8:15 pm
Stops:   non-stop Miles:413
Class:   Coach

or know was, contact me and we can each save hundreds of dollars replacing a dark blue suit.

Categories: Personal Tags: ,