Speech to Text to APEX

One of my favorite online services is Jott. Think of Jott as an online list manager (task or todo lists) that you can call from your phone. Jott does an amazing job of recognizing what you say, then translating that to text and delivering it as an email and text message as well as storing it on jott.com. I use it all the time in the car when I have an idea or need to remember something. My wife and I have even used it occasionally for jotting a short grocery list to each other (don’t worry, she knew she was marrying a geek pretty early in the relationship). I was playing around with some of the partner applications called “Jott Links” such as 30 Boxes, Google Calendar, and Twitter. I also noticed a custom link, allowing developers to create their own Jott partner applications (more info on the Jott Developer page). Hmmm, I’m a developer…I also think voice input is a VERY cool concept…

So, with a little help from Mark at Jott, I put together a sample of integrating Jott with APEX. This allows you to input data into a database table while on the go with nothing but a phone.

Overview

• Sign up for an account on Jott. Note: don’t use your bank password as they are storing your password in clear text, not a hash of it, which they will hopefully change soon.
• Create a new Custom Jott Link called APEX. The 2 URLs you will use are to PL/SQL procedures that are exposed on the Internet.
• You’ll be redirected to an APEX application where you can enter any username. When you hit register, this username will be mapped to your userkey from jott. This just allows you to identify who the jotts belong to.
• Now, when you call Jott, you can perform the following:
  • (Jott) Who would like to Jott?
  • (You) APEX
  • (Jott) Beep (and some other stuff)
  • (You) Remember to blog about integration between Jott and APEX
• Jott will translate your voice message to text, then post it back to a PL/SQL package on your server.

Here are some screenshots from this process:

Demo

If you want to try out my sample application with out downloading it…

1. Sign up for an account on jott.com
2. Configure a custom link
   1. Link Name: APEX
   2. Setup UTL: http://apex.oracle.com/pls/otn/tyler.jott.register
   3. Link URL: http://apex.oracle.com/pls/otn/tyler.jott.message
3. When you save the link, you’ll be redirected to my APEX application
4. Enter any username, just remember it, as you’ll need it later to see your jotts. This application uses open door authentication, so you can login with any username and no password. The key here is that your jotts will show up under your username.
5. Call Jott and Jott a message to APEX
6. Access your message at the following URL:
   http://apex.oracle.com/pls/otn/f?p=32041:1

Code

The following is just the package body, not the spec or the DDL, which are included in the APEX application as “supporting objects” (download link at bottom of this post):

create or replace package body jott as
    function check_ip(
        p_ip in varchar2)
    return boolean
    is
    begin
        -- check that the IP of the host calling this package is in the range 69.12.107.200-204
        if regexp_instr(p_ip,'^69\.12\.107\.(200|201|202|203|204)$' ) = 1 then
            return true;
        else
            return false;
        end if;
    end check_ip;

    function sanitize(
        p_input varchar2)
        return varchar2
    is
    begin
        -- Replace characters that could be used for SQL Injection and Cross Site Scripting with spaces
        return regexp_replace(p_input,'[;|''|-|<|>|/]',' ');
    end sanitize;

    procedure message(
        UserKey in varchar2 default null,
        Message in varchar2 default null,
        listen in varchar2 default null,
        CreationDate in varchar2 default null,
        creationdateutc  in varchar2 default null,
        confidence in varchar2 default null,
        audio in varchar2 default null)
    is
        l_username varchar2(255);
        l_message varchar2(1000);
    begin
        if not check_ip(owa_util.get_cgi_env('REMOTE_ADDR')) then
            htp.p('This package only accepts posts from jott.com');
            return;
        end if;

        for c1 in (select local_username from jott_user_mapping where jott_userkey = UserKey)
        loop
            l_username := c1.local_username;
        end loop;

        if l_username is null then
            htp.p('Error! This Jott account is not registered yet.');
        else
            l_message := sanitize(message);

            insert into jotts(username,UserKey,Message,listen,CreationDate,creationdateutc,audio,confidence)
                   values
                   (l_username,UserKey,l_message,listen,CreationDate,creationdateutc,audio,confidence);
            htp.p(l_message||chr(13)||
            'APEX Link: '||g_url||g_app_number||':1');
        end if;

    end message;

    procedure register(
        UserKey in varchar2 default null,
        userid in varchar2 default null,
        endpointid in varchar2 default null,
        postbackurl in varchar2 default null)
    is
        l_new_id varchar2(32);
    begin
        if g_app_number = 12345 then
            htp.p('Error! Application Number (g_app_number) must be set in the JOTT package.');
            return;
        end if;

        delete from jott_temp where created_on < sysdate - interval '1' hour;

        insert into jott_temp
               (jott_userkey,jott_userid,jott_postbackurl)
        values
               (UserKey,userid,postbackurl) returning id into l_new_id;

        owa_util.status_line(301, null,FALSE);
        owa_util.redirect_url('f?p='||g_app_number||':200:::::P200_ID:'||l_new_id, TRUE);
    end register;
end jott;
/

Make sure you edit the package spec after installation and change the g_app_number and g_url globals.

You download either the APEX Application (which includes all of the DDL) or just the table and package DDL here.

Call BI Publisher Web Services from APEX

Integration between Application Express and BI Publisher is primarily focused on delivering high fidelity reports to the browser, such as clicking a “Print” link on an APEX report and getting back a PDF version of that report in your browser. The configuration and architecture of this integration is documented here. However, there have been a number of questions on the APEX forum and from customers I’ve presented to that are not answered by this solution. So, with the knowledge that BI Publisher 10.1.3.3.2 introduced Web Service APIs to run reports (documented here), and Application Express 3.0 introduced the ability to call Web Services, I thought there might be another integration point between these two products.

This technique is completely different from the traditional integration, since the database where the report resides can be completely different from the database where APEX is installed. We’re going to configure a database connection in BI Publisher then build a report using that connection. APEX is essentially going to pass some parameters to that report and doesn’t need access to the data at all. Since we’re defining the data source in BI Publisher, the report data can actually come from any JDBC compliant database (Oracle, SQL Server, MySQL, etc), a Web Service, or even File data sources.

In summary:

• Select zero or more parameters on an APEX Page
• Press Submit
• This calls an APEX Process of type “Web Service Reference”
• The Web Service Call instructs BI Publisher to run a report with the given parameters
• Then deliver it to one or more email address and an FTP server (could also be WebDav, Fax, or a Network Printer)

Software used in the post:

Please make sure all of the following software is installed and configured before starting. You can obviously omit either the Email server or the FTP server if you only care about 1 delivery end point. Make sure you test the Email and / or FTP servers with their respective clients before you try delivering reports to them with BI Publisher.

• Oracle Database 11g (will also work with 10g)
• Oracle Application Express 3.1 (should also work with 3.0.1)
• Oracle BI Publisher 10.1.3.3.2
• soapUI (free, non-pro version)
• Mercury Mail Transport System (IMAP and SMTP server, only uses ~2mb RAM!)
• FileZilla FTP Server and Client (from Mozilla)

Create a Report in BI Publisher

If you are not familiar with BI Publisher, please take the time to go through this Oracle By Example (OBE). The OBE will walk you through all of the steps necessary to build the report in BI Publisher. Once you are familiar with BIP:

1. Create a report based on HR.DEPARTMENTS and HR.EMPLOYEES. This report has a parameter that allows you to select which department you want to view and uses an RTF template to format the output.
2. Configure your SMTP and FTP servers in the Admin interface
3. Click on the “Schedule” link for the report and test FTP delivery first as its less prone to errors. Then test email delivery. Make sure these work from the schedule interface before proceeding. If they don’t work here, there’s no chance they’ll work when called from a Web Service API.
4. Make note of the URL of your report as you’ll use this later in the Web Service API.

Here are 2 screen-shots of my report:

Test The Web Service Call in soapUI

Now that we know we can schedule a report to be delivered via FTP and Email, lets test the BI Publisher Web Service Call to this report. Start by creating a new WSDL Project in soapUI. From the BI Publisher Web Service API Documentation, use the following URL for the WSDL:

http://<host>:<port>/xmlpserver/services/PublicReportService?wsdl

Create a New Request under PublicReportService > scheduleReport. Lets start with a simple request to deliver our report to FTP with no parameters:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <pub:scheduleReport xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService">
         <scheduleRequest>
            <deliveryRequest>
               <ftpOption>
                  <ftpServerName>tmuth-ftp</ftpServerName>
                  <ftpUserName>tyler</ftpUserName>
                  <ftpUserPassword>oracle1</ftpUserPassword>
                  <remoteFile>/employees.pdf</remoteFile>
               </ftpOption>
            </deliveryRequest>
            <notificationTo>tyler@tmuth-lap</notificationTo>
            <notifyWhenFailed>true</notifyWhenFailed>
            <reportRequest>
               <attributeFormat>pdf</attributeFormat>
               <reportAbsolutePath>/~tmuth/employees/employees.xdo</reportAbsolutePath>
            </reportRequest>
            <userJobName>employees</userJobName>
         </scheduleRequest>
         <userID>tmuth</userID>
         <password>oracle1</password>
      </pub:scheduleReport>
   </soapenv:Body>
</soapenv:Envelope>

Some things to note about the code above:

• Line 7: “tmuth-ftp” isn’t the actual server name, it’s the name I gave it in the BIP Admin interface when I added the FTP server.
• Line 10: Don’t forget the leading slash
• Line 17: This is the URL I noted when I ran my report. The BIP interface will often escape the tilde with a percent seven E, but you can just use the tilde here.
• Lines 21 and 22: This is the username and password I used to login to the BIP Web Interface.

Once that is working, you can try adding a parameter to narrow it to just the sales department. Add the following code at the end of the “reportRequest” section between lines 17 and 18:

<parameterNameValues>
  <item>
     <name>DEPARTMENT</name>
     <multiValuesAllowed>false</multiValuesAllowed>
     <values>
        <!--  80 = SALES, -1 = All -->
        <item>80</item>
     </values>
  </item>
</parameterNameValues>

Now, lets add email as a second delivery end-point. Add the following code to the “deliveryRequest” section between lines 11 and 12:

<emailOption>
  <emailBody>webservice test</emailBody>
  <emailFrom>bip@localhost</emailFrom>
  <emailSubject>test</emailSubject>
  <emailTo>tyler@tmuth-lap</emailTo>
</emailOption>

Create an APEX Application to Call The Report

1. Create a new APEX application with one blank page (Page 1).
2. Create a new “Web Service Reference” (Application > Shared Components > Logic)
   1. Do not search a UDDI repository.
   2. Create the reference manually (link on the right side of the page).
   3. Enter the WSDL you used for soapUI
   4. Enter the SOAP envelope at the bottom of this section (make sure you change the values to match your configuration).
3. Create an Item on Page 1 of type “Select List”. The LOV should be a query of department_name,department_id from hr.departments. The NULL value is -1.
4. Create a Button labeled “Deliver Report”. Also have the page branch to itself.
5. Create a Process (on-submit):
   1. Type: Web Service
   2. Select the Web Service Reference you created earlier.
   3. Only fire this process when you press the Deliver Report button.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <pub:scheduleReport xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService">
         <scheduleRequest>
            <deliveryRequest>
               <ftpOption>
                  <ftpServerName>tmuth-ftp</ftpServerName>
                  <ftpUserName>tyler</ftpUserName>
                  <ftpUserPassword>oracle1</ftpUserPassword>
                  <remoteFile>/employees.pdf</remoteFile>
               </ftpOption>
               <emailOption>
                  <emailBody>webservice test</emailBody>
                  <emailFrom>bip@localhost</emailFrom>
                  <emailSubject>test</emailSubject>
                  <emailTo>tyler@tmuth-lap</emailTo>
               </emailOption>
            </deliveryRequest>
            <notificationTo>tyler@tmuth-lap</notificationTo>
            <notifyWhenFailed>true</notifyWhenFailed>
            <reportRequest>
               <attributeFormat>pdf</attributeFormat>
               <reportAbsolutePath>/~tmuth/employees/employees.xdo</reportAbsolutePath>
               <parameterNameValues>
                  <item>
                     <name>DEPARTMENT</name>
                     <multiValuesAllowed>false</multiValuesAllowed>
                     <values>
                        <item>#P1_DEPARTMENT#</item>
                     </values>
                  </item>
               </parameterNameValues>
            </reportRequest>
            <userJobName>employees</userJobName>
         </scheduleRequest>
         <userID>tmuth</userID>
         <password>oracle1</password>
      </pub:scheduleReport>
   </soapenv:Body>
</soapenv:Envelope>

Notice that we reference APEX Items in Web Service Requests using #ITEM# notation.You can now test your application by selecting a department then submitting the page.

Use Cases

• Long running reports, asynchronously delivered via email
• Print bar codes via network attached printer
• Publishing a report in HTML or PDF format to a web site via FTP

Downloads

Click here to download the APEX Application, BI Publisher Report, and PDF output of the BIP Report. Note that in the APEX application, the P1_DEPARTMENT item queries HR.DEPARTMENTS. employees.zip is the BIP Report which you simply want to upload to BI Publisher “as-is” without unzipping.

APEX 3.1 Released!

For everyone waiting on the release of APEX 3.1, your wait is over.  You can download it from apex.oracle.com.  You can check out the new features here.  There’s also an online demo app linked off the new features page you can use to “kick the tires”.

PL/SQL Associative Arrays

I’ve known of PL/SQL Associative Arrays for quite a while (doc for 11.1, 10.2, 10.1, 9.2), but never really used them before. Wow, I had no idea what I was missing! They’re particularly useful for name-value pair type arrays where you want to look up the value of a particular element without looping over the entire array. To quote the 11g documentation “It is like a simple version of a SQL table where you can retrieve values based on the primary key”.  As with any array in PL/SQL, they are most efficient when used with a small number of rows, such as simple lookup tables.  If you find yourself loading tens of thousands of rows into an array, you’re probably doing something wrong.

I always learn best from examples, but I didn’t really find a lot of sample code for Associative Arrays, which is why I wanted to include a few here based on some data from the Apollo Space Program. Lets start with a classic name-value pair example:

declare
    type assoc_arr is table of varchar2(255) index by varchar2(255);
    apollo_commanders assoc_arr;
begin
    apollo_commanders('Apollo 11'  := 'Neil Armstrong';
    apollo_commanders('Apollo 12'  := 'Pete Conrad';
    apollo_commanders('Apollo 13'  := 'James Lovell';
    apollo_commanders('Apollo 14'  := 'Alan Shepard';
    apollo_commanders('Apollo 15'  := 'David Scott';
    apollo_commanders('Apollo 16'  := 'John W. Young';
    apollo_commanders('Apollo 17'  := 'Eugene A. Cernan';

    dbms_output.put_line(apollo_commanders('Apollo 11'));
    dbms_output.put_line(apollo_commanders('Apollo 14'));
end;
/

– Results:
– Neil Armstrong
– Alan Shepard

Notice that I can simply access an element by name, without looping. The next example demonstrates how to loop over an Associative Array. This may seem like it’s contrary to the purpose of Associative Arrays, but I had a need for this and had a hard time finding examples of it:

declare
    type assoc_arr is table of varchar2(255) index by varchar2(255);
    apollo_commanders assoc_arr;
    l_current_mission   varchar2(255);
begin
    apollo_commanders('Apollo 11'  := 'Neil Armstrong';
    apollo_commanders('Apollo 12'  := 'Pete Conrad';
    apollo_commanders('Apollo 13'  := 'James Lovell';
    apollo_commanders('Apollo 14'  := 'Alan Shepard';
    apollo_commanders('Apollo 15'  := 'David Scott';
    apollo_commanders('Apollo 16'  := 'John W. Young';
    apollo_commanders('Apollo 17'  := 'Eugene A. Cernan';

    l_current_mission := apollo_commanders.first;
    loop
        exit when l_current_mission is null;
        dbms_output.put_line('Mission: '||l_current_mission||', Commander: '||apollo_commanders(l_current_mission));
        l_current_mission := apollo_commanders.next(l_current_mission);
    end loop;
end;
/

– Results:
– Mission: Apollo 11, Commander: Neil Armstrong
– Mission: Apollo 12, Commander: Pete Conrad
– Mission: Apollo 13, Commander: James Lovell
– Mission: Apollo 14, Commander: Alan Shepard
– Mission: Apollo 15, Commander: David Scott
– Mission: Apollo 16, Commander: John W. Young
– Mission: Apollo 17, Commander: Eugene A. Cernan

Finally, a more complex example using a record that I’ve used a lot on a recent project:

declare
    type apollo_rec is record(
        commander   varchar2(100),
        launch      date);
    type apollo_type_arr is table of apollo_rec index by varchar2(100);
    apollo_arr apollo_type_arr;
begin
    apollo_arr('Apollo 11').commander := 'Neil Armstrong';
    apollo_arr('Apollo 11').launch := 	to_date('July 16, 1969','Month dd, yyyy');
    apollo_arr('Apollo 12').commander := 'Pete Conrad';
    apollo_arr('Apollo 12').launch := 	to_date('November 14, 1969','Month dd, yyyy');
    apollo_arr('Apollo 13').commander := 'James Lovell';
    apollo_arr('Apollo 13').launch := 	to_date('April 11, 1970','Month dd, yyyy');
    apollo_arr('Apollo 14').commander := 'Alan Shepard';
    apollo_arr('Apollo 14').launch := 	to_date('January 31, 1971','Month dd, yyyy');  

    dbms_output.put_line(apollo_arr('Apollo 11').commander);
    dbms_output.put_line(apollo_arr('Apollo 11').launch);
end;
/

-- Results:
-- Neil Armstrong
-- 16-JUL-69

Image Caching in PL/SQL Applications

‘Apollo, we have a solution’. I’ve been working with PL/SQL based web applications for many years now, and one of the more annoying aspects of mod_plsql is that when you serve images out of the database, your browser never seems to cache them. I’ve played around with the Cache-Control, Expires, and Last-Modified headers, but nothing seems to work. Every time I reload the page, the browser reloads all images coming from mod_plsql, while it gets images that come from the file system from cache. This results in the page feeling slow to the end user when it has a lot of images that could have been cached.

One of the projects I’m currently working on is going to use a lot of images stored in the database. Faced with the caching issue, I was considering using Java to write the images out to the file system so Apache could serve them in a way that would be cached. Before I went down that path, I decided to do some tracing with Firebug and Wireshark. A few things to note about these tools. When tracing image caching with Firebug > Net > Images, a light gray bar indicates a cached image and a dark gray bar indicates a non-cached image. When using wireshark, right-click on a relevant captured packet, such as a Get of an image, and select “Follow TCP Stream”. This will show you the packet stream in order.

I started by tracing a static page served by Apache that contained some images served from the file system. What I discovered was that the browser was making the same “Get” requests every time I reloaded a page, whether I pressed shift+refresh or simply refresh, returning a non-cached or cached page respectively. One caveat to this was that the “If-Modified-Since” and “If-None-Match” headers were only there on cached pages, but I’ll get into this in a bit. So, the browser was requesting the image (or file) every time, which meant it was not caching the image. So, something was happening server-side that I was missing.

After a little more tracing, then some reading of the HTTP spec, I figured out how the request-response process worked. The following example is an imaginary conversation between Firefox and Apache:

Firefox: Get logo.gif
Apache: HTTP 200 “OK”, ETag: ABC123, Content-Length: 35, contents of gif file

Firefox: Get logo.gif, If-None-Match: ABC123
Apache: HTTP 304 “Not Modified”, ETag: ABC123, Content-Length: 35, headers closed, no file returned

On the first request for logo.gif, Apache returned an ETag header as well as the contents of the file. On the second request, Firefox sent the value of ETag as the If-None-Match header. Apache compared the values and simply returned a 304 status, not the image since the values were the same.

What is ETag? It’s simply a unique identifier for a file that should change any time the file changes. It’s corresponding request header is “If-None-Match”. The same comparison can be made between the “Last-Modified” response header and the “If-Modified-Since” request header. The HTTP spec considers ETag a “strong” attribute and “Last-Modified” a weak attribute, so I will focus on ETag from here on out.

So, all this time we were expecting Apache or mod_plsql to cache our images, we (the PL/SQL programmers) were actually the ones responsible for implementing this caching. Fortunately it’s very simple, especially now that you understand the problem.

To make this work, you need to do 3 things:

1. Make the If-None-Match CGI environment variable available to mod_plsq or the embedded gateway.
2. Send the ETag header back with any image request.
3. When an image is requested, compare the ETag and If-None-Match headers and either return a 304 Not Modified header or return the image.

If you’re using the Embedded PL/SQL Gateway, execute the following code as SYS to add 2 CGI environment variables:

begin
DBMS_EPG.SET_DAD_ATTRIBUTE (
   dad_name    => 'APEX',
   attr_name   => 'cgi-environment-list',
   attr_value  => 'HTTP_IF_NONE_MATCH');

DBMS_EPG.SET_DAD_ATTRIBUTE (
   dad_name    => 'APEX',
   attr_name   => 'cgi-environment-list',
   attr_value  => 'IF_MODIFIED_SINCE');
end;
/

If you’re using Oracle HTTP Server (Apache + mod_plsql), add the following lines to your Database Access Descriptor (DAD), then bounce OHS to apply your changes:
PlsqlCGIEnvironmentList HTTP_IF_NONE_MATCH
PlsqlCGIEnvironmentList IF_MODIFIED_SINCE

OK, now for the code:

create or replace procedure image_test(
    p_name      in varchar2)
is
    l_mime          varchar2(255);
    l_length        number;
    l_file_name     varchar2(2000);
    l_id            varchar2(32);
    l_created_on    date;
    l_file_etag     varchar2(100);
    l_header_etag   varchar2(100);
    l_file_blob     blob;

begin
    for c1 in (select id,name,filename,title,mime_type,
                      doc_size,created_on,blob_content
                 from my_files a
                where a.filename = p_name)
    loop
        l_id            := c1.id;
        l_created_on    := c1.created_on;
        l_file_name     := c1.filename;
        l_mime          := c1.mime_type;
        l_length        := c1.doc_size;
        l_file_blob     := c1.blob_content;
    end loop; --c1

    l_header_etag   := owa_util.get_cgi_env('HTTP_IF_NONE_MATCH');
    l_file_etag     := l_id||to_char(l_created_on,'JHH24MISS');

    if l_file_etag = l_header_etag then
        owa_util.status_line(
            NSTATUS         => 304,
            CREASON         => 'Not Modified',
            BCLOSE_HEADER   => true);
        return;
    else
        owa_util.mime_header(nvl(l_mime,'application/octet'), FALSE );

        htp.p('Date: ' ||to_char(l_created_on,'Dy, DD Mon RRRR hh24:mi:ss')||' GMT');
        htp.p('Content-length: ' || l_length);
        htp.p('Content-Disposition: filename="' || l_file_name || '"');
        htp.p('ETag: ' || l_file_etag);
        htp.p('Expires: ' ||to_char(l_created_on+1,'Dy, DD Mon RRRR hh24:mi:ss')||' GMT');
        htp.p('Cache-Control: max-age=300');
        owa_util.http_header_close;
        wpg_docload.download_file(l_file_blob);
    end if;
end image_test;
/

Lines 27-35 are the important ones where we decide whether the image / file has changed and simply return a 304 status code, NOT the image itself. Note that I’m constructing the ETag header by concatenating the primary key with last-updated / created_on date (formatted as a Julian date with time). This means that if the image or file is updated, the date will change, thus changing the ETag. This, in turn will allow your browser to download the updated version.

Producing RSS from PL/SQL

First things first. The idea and the bulk of the code for this post are not mine, they are Sean Dillon’s. Sean is a good friend of mine and originally wrote this code for AskTom. You can actually still find Sean’s code here. It’s a very cool idea that he came up with and it still works flawlessly today after at least 3 years.

I needed to incorporate RSS into a project I’m working, so I grabbed Sean’s code. The problem is, it’s based on the AskTom table structure which means it won’t run on your database without immediately re-writing the query. Additionally, the nature of this code relies on a pretty lengthy query to generate the XML. I’ll admit, when I first looked at it, I thought “Wow, this is going to be more complex than I thought.” After looking at it for a little while longer, I realized it was actually very simple. Sean also included support for several versions of RSS, improving the functionality, but again, adding to the complexity.

So, I created an example table and simplified the code as much as possible to make it easier for everyone to understand. The table, “PLSQL_PACKAGES”, stores information about some of the built-in PL/SQL packages I use on a regular basis. The links in this table point back to the online Oracle Documentation.

This block of code is just the DDL for the sample table and the insert statements to populate it:

create table plsql_packages(
    id          varchar2(32),
    title       varchar2(255),
    description varchar2(4000),
    link        varchar2(1000),
    updated_by  varchar2(100),
    updated_on  date)
/

create or replace trigger  biu_plsql_packages before insert or update on plsql_packages
for each row
begin
	if inserting then
		:new.id := sys_guid();
	end if;
        :new.updated_by := nvl(v('APP_USER'),user);
        :new.updated_on := sysdate;
end;
/

insert into plsql_packages(title,description,link)
     values ('DBMS_CRYPTO','DBMS_CRYPTO provides an interface to encrypt and decrypt stored data, and can be used in conjunction with PL/SQL programs running network communications. It provides support for several industry-standard encryption and hashing algorithms, including the Advanced Encryption Standard (AES) encryption algorithm. AES has been approved by the National Institute of Standards and Technology (NIST) to replace the Data Encryption Standard (DES).','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_crypto.htm#i1005082' 
/

insert into plsql_packages(title,description,link)
     values ('DBMS_EPG','The DBMS_EPG package implements the embedded PL/SQL gateway that enables a web browser to invoke a PL/SQL stored procedure through an HTTP listener.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_epg.htm#sthref3481' 
/

insert into plsql_packages(title,description,link)
     values (' OWA_UTIL','The OWA_UTIL package contains utility subprograms for performing operations such as getting the value of CGI environment variables, printing the data that is returned to the client, and printing the results of a query in an HTML table.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/w_util.htm#sthref17019' 
/

insert into plsql_packages(title,description,link)
     values ('UTL_MAIL','The UTL_MAIL package is a utility for managing email which includes commonly used email features, such as attachments, CC, BCC, and return receipt.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/u_mail.htm#i1001258' 
/

insert into plsql_packages(title,description,link)
     values ('DBMS_STATS','With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_stats.htm#sthref9773' 
/

insert into plsql_packages(title,description,link)
     values ('DBMS_LDAP_UTL','The DBMS_LDAP_UTL package contains the Oracle Extension utility functions.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_ldaputl.htm#i997505' 
/

insert into plsql_packages(title,description,link)
     values ('DBMS_FLASHBACK','Using DBMS_FLASHBACK, you can flash back to a version of the database at a specified wall-clock time or a specified system change number (SCN).','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_flashb.htm#i998870' 
/

insert into plsql_packages(title,description,link)
     values ('DBMS_FGA','The DBMS_FGA package provides fine-grained security functions.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_fga.htm#i1001938' 
/

insert into plsql_packages(title,description,link)
     values ('APEX_UTIL','The APEX_UTIL package provides utilities for getting and setting session state, getting files, checking authorizations for users, resetting different states for users, and also getting and setting preferences for users.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/a_hdbutl.htm#sthref40' 
/

insert into plsql_packages(title,description,link)
     values ('DBMS_RLS','The DBMS_RLS package contains the fine-grained access control administrative interface, which is used to implement Virtual Private Database (VPD). DBMS_RLS is available with the Enterprise Edition only.','http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_rls.htm#i1000830' 
/

This is the important block of code as it creates the RSS procedure. Don’t be intimidated by it though as you only need to modify a few lines. The only lines you need to customize to make it work against your own table are 4-7 and 41-43!

create or replace procedure rss
is
    -- customizable parameters
    l_title         varchar2(255) := 'Oracle PL/SQL Packages';
    l_link          varchar2(255) := 'http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/toc.htm';
    l_description   varchar2(255) := 'This is a feed of changes to PL/SQL Package Documentation';
    l_language      varchar2(255) := 'en-us';
    -- end customizable parameters
    l_version       varchar2(10)  := '2.0';
    l_clob          clob;
    l_idx           pls_integer := 1;
    l_len           pls_integer := 255;
    l_defrows       pls_integer := 10;
    l_maxrows       pls_integer := 30;
    l_desclen       pls_integer := 250;
begin
    for i in (
      select xmlelement( "rss",
               -- Begin XML Header Block
               xmlattributes( l_version as "version"),
                 xmlelement( "channel",
                   xmlforest( l_title as "title",
                              l_link as "link",
                              l_description as "description",
                              l_language as "language"),
                 -- End XML Header Block
                 -- Begin List of Individual Articles or Items
                 xmlagg(
                     xmlelement( "item",
                       xmlelement("title", x.title),
                       xmlelement("link", x.link),
                       xmlelement("description", x.description),
                       xmlelement("pubDate", to_char(x.updated_on,'Dy, DD Mon RRRR hh24:mi:ss')),
                       xmlelement("guid", XMLATTRIBUTES('false' as "isPermaLink"),x.id||to_char(x.updated_on,'JHH24MISS'))
                     )
                   )
                   -- End List of Individual Articles or Items
                 )
             ) as result
        from (  -- Actual Database Query that populates the list of Items
                select id,title,link,description,updated_on
                  from plsql_packages
                 where rownum < (l_maxrows+1)) x)
    loop
        l_clob := xmltype.extract(i.result,'/').getclobval;
        exit;
    end loop; --i

    --- OUTPUT RESULTS
    owa_util.mime_header('application/xml', false);
    owa_util.http_header_close;
    for i in 1..ceil(dbms_lob.getlength(l_clob)/l_len) loop
        htp.prn(substr(l_clob,l_idx,l_len));
        l_idx := l_idx + l_len;
    end loop; --i
end rss;
/

Note on line 34 the “guid” element. This is an optional element (documented here) that an aggregator can use to uniquely identify the item. I’m concatenating the ID column from the table with Julian date concatenated with hours, minutes, and seconds - to_char(sysdate,‘JHH24MISS’). This means that when you update a row, the date will change causing your aggregator to see a new guid and display a new item for the changed row.The easiest way to test this procedure is using the “OWA Output” tab in SQL Developer:

If you’re running XE or 11g and you want to call this procedure directly through the APEX DAD, you’ll need to edit the FLOWS_XXXXXX.wwv_flow_epg_include_mod_local function and comment-out the first line as well as your procedure to the IN list. Dietmar Aust has a nice post on this.

Once you have your feed displayed in a reader, try updating one of the rows so the updated_on changes. Now refresh the feed in your feed reader and you should see a new item for row you updated. Here’s a link to the code in action on the beta site of apex.oracle.com (this is an RSS feed, so you might want to test it in a reader such as Thunderbird, NetVibes, or Google Reader).

11g SQL Performance Analyzer Rocks!

There are a lot of myths and rules of thumb floating around about Oracle tuning:

• Should I partition this table or not?
• What partitioning scheme should I use?
• What values should I use for OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING?
• Should I regularly rebuild my indexes?

…and the list goes on. Some of the most respected experts in the field, including Tom Kyte and Jonathan Lewis, promote the methodology of “Prove It”, which I support 100%. The problem is that the time required to construct test cases and “proof” can be a bit prohibitive.

Enter SQL Performance Analyzer (SPA). This is one of my favorite 11g new features. SQL Performance Analyzer allows you to capture a SQL Tuning set, play it back multiple times with different options, then get the results of the changes.

Lets start with a quick overview of the process:

1. Capture a SQL Tuning set. This is a pretty straight forward step from Database Console. For my example later in this post, I told it to capture all SQL executed as user FLOWS_030000 for the next 2 minutes. I then ran the Application Express builder to generate the SQL.
2. Run the SQL Performance Analyzer “Guided Workflow” wizard:
   1. Replay the SQL Tuning set the First Time.
   2. Change some parameters or data structures.
   3. Replay the SQL Tuning set the Second Time.
   4. Compare the 2 Tuning Sets and store the results
   5. View the results, including SQL that improved or regressed, and any SQL Plans that changed.

Example

As an example, lets take a look the impact of changing OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING. As I mentioned before, the Tuning Set we’re going to use is just the SQL executed as the user FLOWS_030000, so this is NOT testing the full impact of this change on my database, but you could capture a tuning set for the whole db to test this. I’m not going to walk through all 5 steps of the wizard, because there isn’t much to see. I will say that this whole process, including capturing the SQL Tuning Set, required only 5 minutes.

To get to the SQL Performance Analyzer from the 11g Database Console, click the Performance tab, then click on SQL Performance Analyzer on the lower right, then click Guided Workflow. Here’s a screenshot of the Guided Workflow wizard:

Before starting Step 2, I ran the following code:

alter system set optimizer_index_cost_adj=100 scope=both;
alter system set optimizer_index_caching=0 scope=both;
alter system flush shared_pool;
alter system flush buffer_cache;

Before starting Step 3, I ran the following code:

alter system set optimizer_index_cost_adj=45 scope=both;
alter system set optimizer_index_caching=90 scope=both;
alter system flush shared_pool;
alter system flush buffer_cache;

Below is a screenshot of the results screen. Note that of the 379 SQL statements, 147 had errors. These were due to DML operations in APEX and therefore not an issue here, but this is a great feature to be aware of. Also note that there was a 78% Improvement Impact and a 0% Regression Impact.

Let’s drill into the details of one of the SQL statements so we can see the details of the change on this particular statement:

Finally, the next two cropped screenshots are from the details of a SQL plan that changed, showing us the old and new plans. I cropped the screen into 2 parts so it would fit on this web page.
Old Plan:

New Plan:

As you can see, the new plan uses an index that the old plan did not.

Traditionally, testing these changes against 1 SQL Statement would be relatively simple, but testing the impact on 300+ statements, or for that matter every SQL Statement in the whole database, would be next to impossible.  This is one of those features that DBA’s and developers alike should embrace.   As a developer, you can test any performance change that you want your DBA to make before you make the request.  As a DBA, you can review the impact of any potential changes before you put them in production, drastically reducing the risk.

Conditional Column Formatting in APEX

I wanted to share a little trick I’ve used in APEX for a while now to conditionally format report columns based on their values. I’m sure there are plenty of alternatives to this trick, including the 4 built-in conditional alternatives for a row when using a named column template.

At a high level, this technique uses the following components:

1. A hidden column in the query that returns the formatting attributes for a column. I’m going to return the color or padding-left in my examples later in this post.
2. Edit the Report Attributes > Column Attributes > Column Formatting > HTML Expression of a visible column and use the hidden column to change it’s formatting. This is the same section you apply a date or number format.

Example 1 - Color Code Salary

Query

select empno,ename,sal,
	   case when sal < 1000 then 'red'
		    when sal between 1000 and 2000 then 'purple'
		    when sal > 2000 then 'green'
	   end the_color
  from emp

Report Attributes > Column Attributes for “SAL” > Column Formatting > HTML Expression

<span style="color:#THE_COLOR#;font-weight:bold;">#SAL#</span>

Result

Example 2 - Employee Hierarchy

Query

select (level*20)-20 the_level,
        empno,ename,mgr
   from emp
connect by prior empno = mgr
  start with mgr is null

Report Attributes > Column Attributes for “ENAME” > Column Formatting > HTML Expression

<span style="padding-left:#THE_LEVEL#px;">#ENAME#</span>

Result

APEX + Flashback + SQL Developer = Undo

Application Express doesn’t really have an undo button. Fortunately, since it runs in the database it can leverage one of my favorite database features: Flashback. I strongly urge every developer and DBA to get more familiar with flashback (OTN overview here). Some things to consider before you start working with flashback:

• The default value for UNDO_RETENTION parameter is 900 seconds (15 minutes). This is WAY TOO LOW in my opinion. If you have plenty of disk space, I’d aim for something more like 48 hours.
• You need to grant execute on dbms_flashback to schemas that need to use that package. APEX has the grant, but for the SQL Developer demos of this post, I had to grant execute on that package to the schema I was using in SQL Developer.
• The SQL Developer integration requires SQL Developer 1.2.1 and APEX 3.0.1.

The basic process of using flashback with APEX to recover from mistakes is:

1. Export a page or application “as of” some time in the past.
2. Import that component, either as a new application number, or overwriting your existing application.

Here’s a screenshot of the web interface to export an application “as of” a previous time:

The problem with the web interface is that if you delete an application, there’s no longer an interface to export that application. This is where SQL Developer 1.2.1 comes in. Below is a screenshot of SQL Developer after I’ve deleted my APEX application 104. As you can see there are no application in the list:

Now we can flashback our whole APEX session using the following code:
exec dbms_flashback.enable_at_time(SYSTIMESTAMP - INTERVAL ‘10′ minute);

Notice that my deleted APEX application has magically reappeared since it’s now 10 minutes ago (and we didn’t need 1.21 gigawatts to do it).

I can then right-click on it and export it:

Just for fun, I tested this concept in 11g with Flashback Data Archive (Total Recall). I wrote a block of PL/SQL to loop over every table in APEX and add it to a Flashback Archive. Sure enough, it worked like a charm! Keep in mind this NOT supported (yet), but I did demo it to the APEX team, so we’ll see what happens in the future. This would allow you to view / export any application as of, say, a year ago! Here’s the code I used:

-- connect as sys
grant flashback archive administer to flows_030000
/

create tablespace apex_fb_db_arch1
datafile 'apex_fb_db_arch1.dbf'
size 100m reuse autoextend on next 1m maxsize 500m
nologging extent management local segment space management auto
/

alter user flows_030000 quota unlimited on apex_fb_db_arch1
/

alter session set current_schema=FLOWS_030000
/

create flashback archive apex_fb1
tablespace apex_fb_db_arch1
quota 500m
retention 1 year
/

declare
  fb_enabled        exception;
  pragma 			exception_init (fb_enabled, -55600);
begin
  for c1 in (select table_name from all_tables where owner='FLOWS_030000' and table_name like 'WWV_%' 
  loop
    begin
      dbms_output.put_line(c1.table_name);
      execute immediate 'alter table '||c1.table_name||' flashback archive apex_fb1';
      exception
        — ignore errors from any tables already in an archive, allow all other errors to raise
        when fb_enabled then null;
      end;
  end loop;
end;
/

SQL*Plus Tricks

I work with SQL*Plus on a daily basis and I thought I’d pass on a few tricks to make it easier to use. These tricks are for SQL*Plus on Windows, started from a dos prompt, not the gui SQL*Plus. I’ve never been a fan of the gui version, and I believe it’s gone in 11g anyway.

99% of the time when start SQL*Plus, here’s the routine I go through:

1. Open a dos prompt
2. Change to a directory that contains the sql scripts I’m working on
3. Login to SQL*Plus with the username and password of one of my databases

I wanted to automate this process as it’s a bit tedious when you repeat it every day.

For the first two steps, I recommend using a tool that allows you to store commands and execute them using keyboard shortcuts. About a year ago, a friend got me hooked on a great tool called SlickRun. It lets you define keyboard shortcuts to start one or more programs without moving your hand to the mouse, clicking Start… Now that I’ve moved from XP to Vista, I use a tool called Start++ that performs a similar function (better in some ways, not as good in others).

An example of the command you need to store is:

cmd.exe /k cd \sqlscripts\hr_demos\ && title hr@11g && sqlplus

That command (which you can also test from Start > Run):

• Starts a dos prompt. The /k tells dos not to terminate after executing any commands that follow it.
• Changes to the \sqlscripts\hr_demos\ directory
• Changes the title of the dos window to hr@11g (we’ll use this later)
• Starts sqlplus

For step three, I wanted a secure way to store the database connect information. I’ve been using KeePass to store account information for a while now, and it works well for database accounts as well. The thing I love about KeePass is it’s “Auto-Type” feature. This enables it to send a customizable username / password string to a window based on it’s title (remember that we set the title of the dos prompt with the “title” command). For instance, when I open Yahoo mail, I simply hit ctrl+alt+a and KeePass sends {username}{TAB}{password}{ENTER}. I defined this sequence, and told it to look for windows with the title Yahoo!*. I haven’t typed in a password for an account in months!

The string sequence I used for this database connection is the following:

{USERNAME}@11g{ENTER}{PASSWORD}{ENTER}

So, to login to this account I simply:

• Press the Windows key
• Type hr + [Enter] (Start++ executes the dos command described earlier)
• Press [Ctrl]+[Alt]+a (KeePass sends the correct db account info based on the title of the window)

This may sound a bit complicated, but I wanted to err on the side of being too detailed so people could adapt this to other combinations of utilities. It’s really very simple to set up and will save you a lot of time and clicking.