What's Up With OPEN DATA and Government?

Intro:  Code for America has a chapter here in Hawaii.  Although it is based on Oahu, I still manage to keep up with their activity from here on Maui.

Watching Over State Legislator and Employee Spending

Many of you database developers may wonder in an existential sense if there is anything out there beyond the systems we support each day.  Yes, the government sector alone has a myriad of information that is of the public domain that is a vital link to citizen awareness of their own governance.

The self-description of the CfA Brigade in Hawaii introduces them as:

This group [sic. Hawaii CfA Brigade] is for all those interested in open data, data access, apps, big data, data visualization, APIs and anything related to civic engagement and collaborative technologies.

In the news:  The personal allowance budgets of lawmakers (and their staff) who represent Hawaii are now available for public review.  More details on the committee decisions that led to this is detailed in a Civil Beat article on lawmaker allowance spending reviews.

A look at how you can query and download some of these figures for your own review is explained below in the next section.

How to Export Data from the Legislator Allowance Tool

Legislative Allowance Tool @khon2

The site that provides this information is hosted at:

http://khon2.com/legislative-allowance-tool/

1. The first step is to select the initial query parameters to collect the larger set of information you are querying.


2. Look for the "Download" link next to the output report grid and select it to initiate the generation of a comma-separated-value file for downloading to your local computer.

3. The .csv text file downloaded from the tool can be ingested into any relational database or aggregation system for further research and analysis.

An example comma separated value (.csv) file format opened using spreadsheet software:

What the Data Looks Like from the Web App Tool

Give it a try.  Even if you aren't super savvy at SQL or database technology, even a spreadsheet formula or two transforms this raw data into a source of accountability as the Hawaii State Ethics committee probably also believes in as well.  Check it out.

Parsing a Comma Delimited Input String with PL/SQL

A colleague recently introduced me to an Oracle 11g PL/SQL feature of syntax called "comma_to_table".  It is a part of the DBMS_UTILITY built in package.  She used it as a method to break apart a string input that had components delimited by a comma ",".

How Complicated is Delimited String Parsing?

The more commonly known function: LISTAGG converts a single column output of many rows into a string with a custom delimiter.  There originally was no real alternative to do the reverse except through a procedural path in our code:



        for i in 1 .. l_count
            loop
              select regexp_substr(string_to_parse,'[^,]+',1,i)
                into l_value from dual;
                dbms_output.put_line(l_value);
            end loop; ...

[Sourced from an example on: Oradev.com]

This example loops through the input string "string_to_parse" and breaks the string into pieces based on a self-declared delimiter.  A fancier approach is to use the CONNECT BY syntax (a hierarchical SQL syntax concept) and make an artificial cartesian product to force an iteration over multiple delimited elements:

[Ask Tom has an example] of this:

      select trim( substr (txt, instr (txt, ',', 1, level ) + 1,
             instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 ) )
        from dual 
     connect by level <= length(txt) - length(replace(txt,',','')) -1)

this one is clever and elegant but it takes a while to interpret.  The use of "connect by...level" is not readily obvious and looks like something is missing from the clause when I look at it.

Hidden Constraints:  The Trouble with Misapplied PL/SQL Syntax

The summary of the comma_to_table function is that it was created with a specific purpose in mind and does not have a fully generic application for all cases.  With a single command, this function allows coders to convert a comma-separated series of string values such as:

     '1,2,3,four,fifteen,orange,shack'

to a row-wise output:

        1
        2
        3
        four
        fifteen
        orange
        shack

Ask-Tom also warns that the original intent of this function derives from the expression: NAME TOKENIZE. [1]  This function was made to assist with internal database ETL processes where the task was to parse valid object names (such as tables) from comma delimited string inputs.  This limits the types of values that can be contained within the string:

1. Each delimited element must be no longer than about 30 characters.
2. The string values must represent valid database object names.
3. Objects are defined as:
   "[schema].[object_name].[procedure|function]@[database link]"
4. Certain reserved words that do not make valid object names will cause an error when applied.

In general, the comma-to-table function (Oracle APEX also has a similar command within its built-in package library) has enough limitations to make it a clear recommendation to avoid.  General parsing capability is not available because of its exceptions and the output can defy a coder's original intent.

Source Citations:

1. DBMS_UTILITY.comma_to_table Limitations from "Ask Tom" at:  https://asktom.oracle.com/pls/asktom ...

A Reminder: UTL_SMTP or UTL_MAIL for Sending Mail?

I thought this was worth mentioning because it is often confused by old-school Oracle developers who have survived more than one or two major releases of this RDBMS.

From the docs at Oracle [1]:

The UTL_SMTP package is designed for sending electronic mails (emails) over Simple Mail Transfer Protocol (SMTP) as specified by RFC821.

The folks at PSOUG[2] have also noted that historically, this built-in package has been around since Oracle 8i:

UTL_SMTP Definition[3]

The UTL_MAIL package was similarly created to replace UTL_SMTP. Both work slightly differently from one another.  Oracle announced the eventual replacement of the newer mail utility around Release of the Oracle 10g RDBMS product release.  It's definition:

UTL_MAIL Definition [4]

I have noticed and used UTL_MAIL since at least the 10g RDBMS Release.  Anyways, it would be wise to stick with the new replacement to add longevity to your PL/SQL procedural code which uses email features supplied by the database system.

1. Oracle PL/SQL Documentation: UTL_SMTP
   http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_smtp.htm#i1002798
2. (PSOUG) Puget Sound Oracle User Group
3. UTL_SMTP History
   http://psoug.org/reference/utl_smtp.html
4. UTL_MAIL History
   http://psoug.org/reference/utl_mail.html