Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Archive for the ‘APEX’ Category

mod_rewrite and APEX part 2

Posted by Tyler Muth on August 13, 2008

Here’s another Apache mod_rewrite example that I would love to get some feedback on from the community.

RewriteCond %{REQUEST_URI}%{QUERY_STRING} !/pls/(apex|builder)/f?p=4155:.*
RewriteCond %{REQUEST_URI}%{QUERY_STRING} /pls/(apex|builder)/f?p=(4[0-9]{3}:.*)
RewriteCond %{REMOTE_ADDR}        !^(127\.0\.0\.1|192\.168\.1\.[0-9]{1,3})$
RewriteRule /pls/(apex|builder)/ - [F]
  1. Any request for a 4000 series application (the APEX dev environment) on either the "apex" or "builder" DAD...
  2. ...Not coming from either localhost or a 192.168.1.x address...
  3. Return a 403 "Forbidden" page.

This is a simple way to protect your development environment from people that don't belong there.  The runtime-only installation option introduced in APEX 3.1 is even more secure, but there may be users on previous versions or that do not want to use the runtime only option.  Thoughts?

Posted in APEX, Security | 4 Comments »

mod_rewrite and APEX

Posted by Tyler Muth on August 12, 2008

I’m working on a publication and one concept I’d like to include is the use of mod_rewrite and APEX as it helps simplify another concept.  Here’s the problem, while I’ve used mod_rewrite in many production environments, I’ve never used the exact concept I want to write about.  Essentially, I want to force all requests for the APEX development environment through one Database Access Descriptor (DAD), and all requests for other applications to a different DAD.

I’ve written and tested the rules for this, but I’m asking for some help from the community to check my work and offer suggestions. Sorry, I’m not offering money, or even recognition in the publication (it’s something I cant commit to), but I will definitely acknowledge it here if someone catches an error of mine or makes a suggestion that I use.

So, my goal is to force all requests for “apex”, “apex_admin”, and f?p=4xxx to the “builder” DAD, while forcing all requests to f?p= (anything other than 4000-4999) to the “apex” DAD.

RewriteEngine on
Options +FollowSymLinks
#RewriteLog "/tmp/rewrite.log"
#RewriteLogLevel 3

# Change all requests for "apex" shortcuts to the builder DAD
RewriteRule /pls/apex/apex /pls/builder/apex [R]
RewriteRule /pls/apex/apex_admin /pls/builder/apex_admin [R]

# Change all 4000s to builder DAD
RewriteCond %{REQUEST_URI}%{QUERY_STRING} /pls/apex/f?p=(4[0-9]{3}:.*)
RewriteRule /pls/apex/ /pls/builder/f?p=%1 [R,L]

# Change 4, 40-49, 400-499, or any number starting with 4 greater than 40000 to apex DAD
RewriteCond %{REQUEST_URI}%{QUERY_STRING} /pls/builder/f?p=((4[0-9]{0,2}:)|(4[0-9]{4,}:))
RewriteRule /pls/builder/ /pls/apex/f?p=%1 [R]

# Change all numbers not starting with 4 to apex DAD
RewriteCond %{REQUEST_URI}%{QUERY_STRING} /pls/builder/f?p=(([1-3]|[5-9])[0-9]*:.*)

Posted in APEX, Uncategorized | 2 Comments »

New APEX Blogger Christopher Beck

Posted by Tyler Muth on August 10, 2008

I’d like to welcome Christopher Beck to the APEX / Oracle / PL/SQL blogging community.  I met Chris when I interviewed at Oracle over 8 years ago and we’ve been friends ever since.  He worked with Mike Hichwa and Raj Matamal as one of the original authors of WebDB, as well as writing the first Apache module to serve PL/SQL (neither of us know if this became mod_plsql or not).  Chris worked for Tom Kyte on his “Special Projects Team” (for lack of a better description) for many years.  He co-authored “Beginning Oracle Programming” with Tom Kyte and Sean Dillon.  Funny story about that, his face made the cover of the original Wrox Press version, but apparently his shoulders weren’t broad enough to fill the space so they used someone else’s shoulders:

Beginning_Oracle_Programming

Chris is one of the best PL/SQL programmers I know.  Whenever I get stuck, he’s the first person I turn to.  He’s also VERY good at breaking other people’s code, which is one of the most valuable yet annoying traits you can imagine.  I’ve learned to embrace this quality, as I know he will find bugs in my code that would take others months to find. 

He already has a couple of very cool posts including:

I’m sure we’ll see lot more useful examples in the future, including some cool object-oriented PL/SQL, offset by some procedural Java ;)

Posted in APEX | 3 Comments »

jQuery Datepicker and APEX

Posted by Tyler Muth on July 16, 2008

APEX includes a great datepicker, but it is a popup window and there are a number of newer, better looking options out there.  One of my favorites is from the jQuery UI project (direct link to datepicker section).  If you haven’t worked with jQuery yet, it’s well worth a look as it’s very well documented and there are a ton of plugins.  As always, I STRONGLY recommend you use the Firebug plugin for Firefox for any JavaScript and CSS work as it will save you many, many hours, particularly in a hosted environment like APEX.

OK, on to the good stuff.  You can try out the examples here, or download the example application here.  There are several JavaScript and one CSS file inlcuded in the page template, as well as some customized CSS classes.  The header section of page 1 is where the following JavaScript is declared:

function disable_kinda(pThis){
	$x_Style(pThis,'color','#ccc');
	pThis.onfocus=function(){this.blur();}
} 

$(document).ready(function(){
	disable_kinda($x('P1_DATE_ONE'));

	$('#P1_DATE_ONE').datepicker({
		showOn: "both",
		buttonImage: "http://ui.jquery.com/repository/demos_templates/images/calendar.gif",
		buttonImageOnly: true});

	$('#P1_DATE_TWO').datepicker({
		showOn: "both",
		buttonImage: "http://ui.jquery.com/repository/demos_templates/images/calendar.gif",
		buttonImageOnly: true});

	$('#P1_DATE_THREE').datepicker({
		dateFormat: "D, M dd, yy",
		showOn: "both",
		buttonImage: "http://ui.jquery.com/repository/demos_templates/images/calendar.gif",
		buttonImageOnly: true});

	$('#P1_DATE_FOUR').datepicker({
		onSelect: function(date) { doSubmit("DATE_CHANGE");},
		showOn: "both",
		buttonImage: "http://ui.jquery.com/repository/demos_templates/images/calendar.gif",
		buttonImageOnly: true});

	$('#P1_DATE_FIVE').datepicker({
		onClose: function(date) { doSubmit("DATE_CHANGE");},
		rangeSelect: true,
		numberOfMonths: 2,
		minDate: new Date(2008, 5 -1, 1),
		maxDate: new Date(2008, 7 -1, 30),
		showOn: "both",
		buttonImage: "http://ui.jquery.com/repository/demos_templates/images/calendar.gif",
		buttonImageOnly: true});
});

Thanks to Carl Backstrom for the "disable_kinda" function, as well as suggesting I look at jQuery in the first place.

MatjazC posted a good link in the comments to this site, which is the development page for this speciific comments.  The key point there is the direct link on that page to the CSS for this control.  I used the flora.css that ships with jQuery, then modified a few of the classes.  It would be better to start with the this one instead.

Posted in APEX, JavaScript / AJAX | Tagged: | 19 Comments »

Suggest a Session for OOW: APEX Auth Schemes

Posted by Tyler Muth on June 23, 2008

Raj Mattamal, an old colleague, friend, and former APEX Team Member is trying to get his session voted into one of the few remaining slots for Oracle Open World via Oracle Mix. The title is “Security: Writing Custom Authentication Schemes for Application Express” and you can go here to vote for it.

Top 5 reasons you should vote for Raj vs someone else?

  1. This is actually a pretty interesting topic that comes up with almost every application.
  2. If you check out the mix link you’ll see he has endorsements from Tom Kyte, Mike Hichwa, and Carl Backstrom.
  3. Raj is entertaining. He could compare and contrast the Dewey Decimal System and the Library of Congress Classification and make it interesting.
  4. Raj is animated… more so than I can effectively describe here. You know the bouncing doc icons on a Mac? The ones that bounce up and down continuously to tell you something important? Now imagine one of them had arms and the important thing it was trying to tell you was “Security: Writing Custom Authentication Schemes for Application Express”… but not in an annoying Jack Russell Terrier sort of way, but more of dance… though not quite as good as these guys (warning!!! video with SOUND).
  5. He’s a dog person. Well more of a dog stalker since he doesn’t actually own one, but he likes dogs. In exchange for this endorsement, Raj has agreed to dog-sit for our dog the 3rd week of August. Oh, Raj, are you free the 3rd week of August? Don’t worry, just put her on the treadmill, kick back and relax.

Posted in APEX, Oracle | 6 Comments »

Speech to Text to APEX

Posted by Tyler Muth on May 11, 2008

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 Matt 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:

jott_screen1

jott_screen2

apex_screen1

apex_screen2

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.

Posted in APEX, Oracle | Tagged: | 16 Comments »

Call BI Publisher Web Services from APEX

Posted by Tyler Muth on March 31, 2008

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.

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:

report edit

report view

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.apex_app.png

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.

Posted in APEX, BI Publisher | Tagged: , , | 44 Comments »

APEX 3.1 Released!

Posted by Tyler Muth on February 29, 2008

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”.

Posted in APEX | Leave a Comment »

Image Caching in PL/SQL Applications

Posted by Tyler Muth on February 4, 2008

‘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.

Posted in APEX, Oracle | Tagged: | 24 Comments »

Producing RSS from PL/SQL

Posted by Tyler Muth on January 22, 2008

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:SQL Developer OWA Ouput

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).

Posted in APEX, Oracle | Tagged: | 49 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 89 other followers