Application Express Nuggets

Three New Web Service Demos Available on the OLL

2011-10-21T10:50:00.001-07:00

One of my colleagues at Oracle, Marcie Young, has created three new excellent demonstrations of consuming web services in Application Express 4.1.

The first demonstration called "Creating and Using a Manual SOAP Web Service in Your Application" takes you step by step through creating a manual web service reference, and then using the Create Form and Report on Web Service wizard to create the page to interact with the service. The ability to use that wizard on a manual web reference was a new feature on 4.0. (This demonstration even exposed a slight branching inconvenience that I am going to file and fix in 4.1.1.)

"Creating and Using a RESTful Web Service with an XML Response" uses the Google Geocoder v3 web service and is a classic example of consuming a RESTful style web service in Application Express.

Finally, "Creating and Using a RESTful Web Service with an XML Response and a Bind Variable" is a great example of consuming a RESTful service where the parameter is passed as part of the URI path (not part of the query string), like http://someservice.com/employees/{id}, where {id} is the employee that you want to retrieve. This can be accomplished by using &ITEM_NAME. syntax when you specify the URL endpoint in the RESTful web service reference.

All of these demonstrations reinforce your need to learn to write XPath expressions to traverse an XML document. If you don't know what I am talking about, there is an excellent XPath tutorial at www.w3schools.com.

So check these demonstrations out (they are nice videos) and give your mad props to Marcie!

Building an Amazon S3 Client with Application Express 4.0 White Paper Available

2011-01-03T07:02:00.001-08:00

If you have seen me present about web services and Oracle Application Express 4.0 in the last year (at APEXposed, OOW, or ODTUG Kaleidoscope), you have probably heard me say that a white paper about building an Amazon S3 client with APEX was forthcoming. I am happy to announce the paper is now available along with a corresponding sample application. The best part is with the AWS Free Usage Tier, you can try everything out for free!

Adding a Context Menu to a Tree Region

2010-09-14T09:47:00.001-07:00

One of the nice new features of Application Express is the new tree region. It is based on jsTree and supports features such as tool-tips. jsTree is the same tree used for the new tree view of the Application Builder Page Definition page. That page supports a right click context menu which is missing from the current version of the Application Express new tree region. With a little help from Patrick, I was able to add a context menu to a tree region I was working on for an update to the Document Library packaged application. The purpose of this article is to describe how to do this for your tree regions. I will use the EMP table as a simple example.

Disclaimer:
Patrick wanted me to mention that we may change the implementation of the tree in the future, so you may have to adjust the JavaScript code listed here in future versions of Application Express.

Let's start by creating a copy of the emp table, emp2, so you don't actually mess with the emp data. Use the SQL Workshop SQL Command Processor and the code in listing 1 to create emp2.

Code Listing 1


create table emp2 as select * from emp
/

alter table emp2 add constraint emp2_pk primary key (empno)
/

alter table emp2 add constraint emp2_fk foreign key (mgr) references emp2(empno)
/

Create Application and Tree

With emp2 in place you are ready to create an application. Simply run the create application wizard and create a form and report on emp2. You will replace the report on page 1 with a tree region. After the application is created, edit page 1. Delete the report region and then create a new tree region specifying the following options:

Display Attributes, Title: Employees

Tree Template: Default

Table/View: EMP2

Confirm that all values are defaulted on the Query step

Tooltip: Database Column

Tooltip Column: HIREDATE

Run the page and confirm that your tree appears. When you right click on a node, you should only see that standard right click options of whatever browser you are using.

Create Context Menu

Now for the magic! By sprinkling a little JavaScript on the page here and there, we will get a nice right click context menu. First edit the tree region and give the region a static ID of EMP2 as in figure 1. We need this static ID to make it easier to write JavaScript and select this region with jQuery.

Figure 1

Now you just need to sprinkle a little JavaScript on the page! Edit the page definition of page one and add the code in code listing 2 into the Function and Global Variable Declaration text area and the code in code listing 3 into the Execute when Page Loads text area.

Code Listing 2


function doAction(pNode,pTree,a){
  var l_action;
  var l_id;

  l_id = pNode.attr("id");

  if (a=="create") { l_action = "f?p="+$v('pFlowId')+":2:"+$v('pInstance')+":::2:P2_MGR:"+l_id }
  if (a=="delete") { deleteEmp(pNode,l_id); }
  if (a=="edit") { l_action = "f?p="+$v('pFlowId')+":2:"+$v('pInstance')+"::::P2_EMPNO:"+l_id }

  if (l_action != null) {document.location.href=l_action; }
}

Code Listing 3


var lTreeContextMenu={
    items:{create:false,rename:false,remove:false,
        contextmenu_create:{
            label:"Add Employee",
            icon: "",
            visible: true,
            action: function(pNode, pTree){doAction(pNode, pTree, "create");}
        },
        contextmenu_delete:{
            label:"Delete",
            icon: "",
            visible: true,
            action: function(pNode, pTree){doAction(pNode, pTree, "delete");}
        },
        contextmenu_edit:{
            label:"Edit",
            icon: "",
            visible: true,
            action: function(pNode, pTree){doAction(pNode, pTree, "edit");}
        }
}};

        // use jsTree to render the tree
        var lTreeSel = apex.jQuery("#EMP2").find("div.tree");
        var lTreeId = lTreeSel.attr("id");
        var lDataId = lTreeId.replace("tree","");
        var lTreeData = eval("l"+lDataId+"Data");
        var lTree = lTreeSel.tree({
          data:{
            type:"json",
            async:true,
            opts:{
              "static":lTreeData,
              isTreeLoaded:false,
              method:"POST",
              url:"wwv_flow.show"
            }
          },
          root:{
            draggable:false,
            valid_children: "folder"
          },
          folder:{
            valid_children: "file"
          },
          file:{
            valid_children: "none",
            max_children: 0,
            max_depth:0
          },
          opened:["7839"],
          plugins:{contextmenu:lTreeContextMenu}
          });

        $.showTooltip = function(pEvent) {
        var lAction = apex.jQuery(pEvent.target).attr("tooltip");
        if (lAction && lAction != "") {
            toolTip_enable(pEvent,this,apex.jQuery(this).attr("tooltip"));
        }
        }; // showTooltip

        // Bind Tooltips for tree nodes
        apex.jQuery('a[tooltip]', lTreeSel).bind("mouseover", $.showTooltip);

        // Hack for right click problem on selected node
        apex.jQuery("#EMP2").find("a").live("mouseup",function() {apex.jQuery("#EMP2").find("a").removeClass()});

Now when you run the page you should see a right click context menu when you right click on a node in your tree, like figure 2. Note that the Add Employee and Edit actions are implemented and working. The delete action is not yet working, that will be added next.

Figure 2

Implement Delete Action

Technically the delete action is already implemented. You could edit the employee and just click delete on the next page. What sounds like more fun is to call an on demand process using AJAX from some JavaScript, and then use jQuery to remove the node from the tree, display a confirmation message, all without doing a page submit. First things first though, create a region that will hold the message specifying the following options:

Region Type: HTML

Title: Message Container

Region Template: No Template

Sequence: 5

Region Source: See Code Listing 4

Code Listing 4


<div class="success" id="success-message" style="display:none;">
  <img src="#IMAGE_PREFIX#delete.gif" onclick="apex.jQuery('#success-message').hide()" style="float:right;" class="remove-message" alt="" />
<div id="theMessage">This is a placeholder for messages</div>
</div>

Now we have a place for messages. Next, create a process on page 1 with the following options:

Process Type: PL/SQL

Name: DELETE_EMPLOYEE

Point: On Demand – Run this process when requested by AJAX

PL/SQL Page Process: See Code Listing 5

Code Listing 5


begin
    delete from emp2 where empno = apex_application.g_x01;
    owa_util.mime_header('text/xml', FALSE );
    htp.p('Cache-Control: no-cache');
    htp.p('Pragma: no-cache');
    owa_util.http_header_close;
    htp.p('{result:"success",message:"Employee successfully deleted"}');
exception when others then
    owa_util.mime_header('text/xml', FALSE );
    htp.p('Cache-Control: no-cache');
    htp.p('Pragma: no-cache');
    owa_util.http_header_close;
    htp.p('{result:"failed",message:"Error deleting employee: '||sqlerrm||'"}');
end;

Finally, you add the JavaScript to do the AJAX call, display the result message, and remove the node from the tree. Edit the page attributes and add the code in code listing 6 in the Function and Global Variable Declaration text area before the existing doAction function.

Code Listing 6


function confirmSubmit(){
var agree=confirm("Are you sure you wish to continue and delete?");
if (agree)
 return true ;
else
 return false ;
}

function deleteEmp(pNode,pId){
  var lTest = confirmSubmit();
  if (lTest) {
    var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=DELETE_EMPLOYEE',$v('pFlowStepId'));
    get.addParam('x01',pId);
    gReturn = get.get();
    var j = eval("("+gReturn+")");
    apex.jQuery("#theMessage").text(j.message);
    apex.jQuery("#success-message").show();
    if (j.result == "success") {
        pNode.remove();
    }

    get = null;
  }
}

Now run the page and unroll Clark. Right click on Miller and choose delete. Confirm the delete and the result should be similar to figure 3.

Figure 3

Application Express 4.0 Web Services Evaluation Guide

2010-07-06T11:53:00.000-07:00

I just returned from ODTUG Kaliedoscope 2010, (awesome event, best one I have been to), and promised during my presentation that the Application Express 4.0 Web Services Evaluation Guide would be posted on the Application Express Web Services Integration page on OTN. Well it now is, so I kept my promise. ;)

If you missed me at ODTUG, you can see me give a very similar presentation this year at Oracle Open World. The session is at 12:30 on Thursday September 23rd, so don't get too crazy at the appreciation event the night before!

Update 7/28/2010: A patch set exception was created for the issue the Roel reported in the comments below. Log on to Metalink and search for 9848562.

Application Express 4.0 Early Adopter Phase II (EA2) Available

2010-02-26T13:06:00.000-08:00

The Application Express 4.0 Early Adopter Phase II (EA2) is now available. Like EA1, this instance is running on Amazon EC2. The beauty of Amazon's elastic IP feature is that we could work on the EA2 instance and get it completely ready, then associate the elastic IP with the new instance, making the switch in less than 60 seconds. You can still access EA1 for the next couple of weeks in case you need anything from that instance. You will need to sign up for a new workspace on EA2.

One nugget I would pass on is that if you run Oracle in the cloud and use Elastic Block Storage for your data files, you should also change the parameter diagnostic_dest to point to your EBS volume instead of the volatile volume that only lives with the instance. The reason is that you could fill up that volume pretty quickly if there is a lot of action on your instance and cause your database to shutdown.

Have fun with EA2 and especially kicking the tires of Websheets!

OTN Developer Days in NYC

2010-01-08T05:52:00.000-08:00

Are you in the NY, NY area? If so you should definitely attend the free OTN Developer Day- Hands-on Oracle Database 11g Applications Development event at the New York Marriot Marquis on January 13, 2010. I will not be there, but much smarter colleagues of mine will like Mike Hichwa (VP of Development Tools, father of Application Express), Marc Sewtz, David Peake and Christina Cho.

Bring your own laptop for the labs.

Application Express 4.0 EA Running on Amazon EC2

2009-12-21T11:30:00.001-08:00

The Application Express 4.0 evaluation instance is running on an Amazon EC2 instance. You can access it at http://tryapexnow.com. You may also be interested in checking out the APEX 4.0 Web Services Evaluation Guide here.

flex_ws_api now on samplecode.oracle.com

2009-11-20T08:00:00.000-08:00

Tyler recently made me aware of http://samplecode.oracle.com/. It is a great place to share code projects, allow others to contribute and comment through discussion forums. I have set up a project for the flex_ws_api and all further development will take place there. I have created discussions for general questions, bug reports, and enhancement requests. Feel free to participate there. All you need is an OTN account to login.

REST Now Supported in flex_ws_api and Other Good Stuff

2009-10-23T13:36:00.001-07:00

I have finally added support for consuming RESTful Web services in the flex_ws_api API. I have also added new globals in the API to keep track of cookies, HTTP headers and the response code returned from a service. There is also a global you can populate prior to calling any of the make_request procedure/functions that will send cookies along with the request to the Web service.

To demonstrate using the new features of the API, I will walk you through creating a new application that will call the RESTful version of Amazon's Product Advertising API. You may recall from a previous post that Amazon now requires that all requests to this API are signed with the developer's secret key. This example will assume that you have compiled the following in your schema: the new flex_ws_api, the java source hmacSHA256, the pl/sql function hmacSHA256, and finally the amazon_signature function.

First create a new application with one blank page called Product Search. Modify the application attributes and add the following substitutions:

AWSACCESSKEY
ASSOCIATETAG
AWSSECRETKEY

Enter the appropriate values with the values that match your AWS credentials. You will need to sign up for an AWS Access Key ID which will also give you your secret code and you also should sign up to be an associate which will give you your associate tag.

Next create an HTML region called Product Search (if it was not already created by the create application wizard) and place the following items in that region:

Name: P1_KEYWORDS, Display As: Text Field
Name: P1_SEARCHINDEX, Display As: Select List, List of Values – Display Extra Values: No, List of values definition: STATIC2:All;All,Apparel;Apparel,Automotive;Automotive,Baby;Baby,Beauty;Beauty,Books;Books,Classical;Classical,DigitalMusic;DigitalMusic,DVD;DVD,Electronics;Electronics,GourmetFood;GourmetFood,HealthPersonalCare;HealthPersonalCare,HomeGarden;HomeGarden,Industrial;Industrial,Jewelry;Jewelry,KindleStore;KindleStore,Kitchen;Kitchen,Magazines;Magazines,Merchants;Merchants,Miscellaneous;Miscellaneous,MP3Downloads;MP3Downloads,Music;Music,MusicalInstruments;MusicalInstruments,MusicTracks;MusicTracks,OfficeProducts;OfficeProducts,OutdoorLiving;OutdoorLiving,PCHardware;PCHardware,PetSupplies;PetSupplies,Photo;Photo,Shoes;Shoes,SilverMerchants;SilverMerchants,Software;Software,SportingGoods;SportingGoods,Tools;Tools,Toys;Toys,UnboxVideo;UnboxVideo,VHS;VHS,Video;Video,VideoGames;VideoGames,Watches;Watches,Wireless;Wireless,WirelessAccessories;WirelessAccessories
Name: P1_TIMESTAMP, Display As: Hidden and Protected

Create a Submit button on the page that submits the page and branches back to page 1.

You must compute the value of P1_TIMESTAMP to a format that Amazon is expecting. The computation includes a GMT offset at the end so your computation will depend on where your database server is relative to GMT. For me it was -08:00. Create a computation on page 1 with the following attributes:

Item Name: P1_TIMESTAMP
Type: PL/SQL Function Body
Computation Point: After Submit
Computation: return to_char(sysdate + 8/24,'YYYY-MM-DD')||'T'||to_char(sysdate,'hh24:mi:ss')||'-08:00';

Now you create the process on that page that uses the flex_ws_api to call the Amazon Product Advertising API REST Web service. Create a process on the page with the following attributes:

Name: Call Amazon Product Search API
Type: PL/SQL anonymous block
Process Point: On Submit – After Computations and Validations
Source: <see code listing 1>
Conditional Processing, When Button Pressed: SUBMIT

Code Listing 1, Call Amazon Product Search API Process


declare
  l_signature     varchar2(4000);
  l_response      clob;
  l_parm_name_tab wwv_flow_global.vc_arr2;
  l_parm_val_tab  wwv_flow_global.vc_arr2;
  i number;
  secure varchar2(1);
begin

  --compute the signature
  l_signature := amazon_signature('GET'||chr(10)||'ecs.amazonaws.com'||chr(10)||'/onca/xml'||chr(10)||'AWSAccessKeyId='||:AWSACCESSKEY||'&AssociateTag='||:ASSOCIATETAG||'&Keywords='||replace(:P1_KEYWORDS,' ','%20')||'&Operation=ItemSearch&ResponseGroup=ItemAttributes%2CImages&SearchIndex='||:P1_SEARCHINDEX||'&Service=AWSECommerceService&Timestamp='||apex_util.url_encode(:P1_TIMESTAMP)||'&Version=2009-03-31',:AWSSECRETKEY);

  --create the tables of parameter names and values
  l_parm_name_tab := apex_util.string_to_table('Service:Version:Operation:Keywords:SearchIndex:AWSAccessKeyId:AssociateTag:ResponseGroup:Timestamp:Signature');
  --need to use ~ instead of : for separator since timestamp will contain :
  l_parm_val_tab := apex_util.string_to_table('AWSECommerceService~2009-03-31~ItemSearch~'||:P1_KEYWORDS||'~'||:P1_SEARCHINDEX||'~'||:AWSACCESSKEY||'~'||:ASSOCIATETAG||'~ItemAttributes,Images~'||:P1_TIMESTAMP,'~');
  --need to add l_signature to the table separately, it may contain any character
  l_parm_val_tab(l_parm_val_tab.count + 1) := l_signature;

  --make the REST request
  l_response := flex_ws_api.make_rest_request(
      p_url => 'http://ecs.amazonaws.com/onca/xml',
      p_http_method => 'GET',
      p_parm_name => l_parm_name_tab,
      p_parm_value => l_parm_val_tab );

  --populate a collection with the response
  apex_collection.create_or_truncate_collection('P1_ITEMSEARCH_RESPONSE');
  apex_collection.add_member(
      p_collection_name   => 'P1_ITEMSEARCH_RESPONSE',
      p_clob001           => l_response );

  --populate a collection for any response http headers returned
  apex_collection.create_or_truncate_collection('P1_RESP_HEADERS');

  for i in 1.. flex_ws_api.g_headers.count loop
    apex_collection.add_member(
      p_collection_name => 'P1_RESP_HEADERS',
      p_c001 => flex_ws_api.g_headers(i).name,
      p_c002 => flex_ws_api.g_headers(i).value,
      p_c003 => flex_ws_api.g_status_code);
  end loop;

end;

Next you create two SQL Report regions. The first one reports on the response from the Web service and shows the products that match the search term and the second shows the HTTP headers that are returned with the response. Create a SQL Report region with the following attributes:

Title: Results
Type: SQL Query
Source: <see code listing 2>
Conditional Display, Condition Type: PL/SQL Expression
Conditional Display, Expression 1: apex_collection.collection_exists('P1_ITEMSEARCH_RESPONSE')

Code Listing 2, Results SQL Query


select extractValue(value(t),'/*/ASIN','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"') ASIN
   , extractValue(value(t),'/*/DetailPageURL','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"') DetailPageURL
   , extractValue(value(t),'/*/ItemAttributes/Title','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"') Title
   , '<img src="'||nvl(extractValue(value(t),'/*/SmallImage/URL','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"'),'http://ec1.images-amazon.com/images/G/01/x-locale/detail/thumb-no-image._V47060337_.gif')||'" width="'||nvl(extractValue(value(t),'/*/SmallImage/Width','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"'),'50')||'" height="'||nvl(extractValue(value(t),'/*/SmallImage/Height','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"'),'60')||'" />' "Image"
   , extractValue(value(t),'/*/ItemLinks/ItemLink[4]/URL','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"') Link
from wwv_flow_collections c,
    table(xmlsequence(extract(xmltype.createxml(c.clob001),'//Item','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"'))) t
where c.collection_name = 'P1_ITEMSEARCH_RESPONSE'

The query above pulls out the ASIN, detail page URL, title, small image and wish list link from the XML document. You want to create links for the title point to the detail page and a link for the wishlist in your report. Make the following changes to the Results report by click on the Report link on the page definition.

Uncheck Show for the column DETAILPAGEURL
Enter the following HTML Expression for TITLE column: <a href="#DETAILPAGEURL#">#TITLE#</a>
Enter the following HTML Expression for LINK column: <a href="#LINK#">[Add to Wishlisth]</a>

Create a SQL Report region for the HTTP headers with the following attributes:

Title: Headers
Type: SQL Query
Source: <see code listing 3>
Conditional Display, Condition Type: PL/SQL Expression
Conditional Display, Expression 1: apex_collection.collection_exists('P1_RESP_HEADERS')

Code Listing 3, Header SQL Query

 select c001 name, c002 value, c003 status_code
from apex_collections
where collection_name = 'P1_RESP_HEADERS'

Now run the page, enter a search term, choose a category and click Submit. You should see a page similar to the one below. That's all there is to it!

HMAC_SHA256 in PL/SQL

2009-07-24T08:34:00.001-07:00

I have been working on an Application Express 4.0 feature to support consuming REST Web services. REST Web services use a simpler architecture than the popular SOAP style Web services. Instead of posting some big XML document wrapped in a SOAP Envelope, REST requests are typically done by passing name/value pairs in the query string of a URL.

To prove that the support for REST I am building in Application Express is useful, I have been working with popular public REST Web services offered by Yahoo, Google and Amazon. I built a similar application to the Amazon Store sample application using the REST APIs. Everything was going great until I received the following email from Amazon.

"Dear Product Advertising API Developer,

We wanted to remind you that all Product Advertising API developers will be required to authenticate all calls to the Product Advertising API by August 15, 2009. We noticed that requests with your AWS Access Key ID are not being signed and, while you have more than 60 days until the date on which authentication is required, we are, as a courtesy, sending you this email to remind you of the new authentication requirement. Please remember that calls to the Product Advertising API that are not signed will not be processed after August 15, 2009."

What do they mean I need to sign my requests? Luckily they provided some links for developer resources on how to construct signed requests. In a nutshell, you must byte order all of your parameters, add a Timestamp parameter and then you create a base64-encoded HMAC_SHA256 signature using your AWS secret key. Should not be a problem. I recently added support for parameters in REST requests to be populated by the result of a function and I am somewhat familiar with the DBMS_CRYPTO package. Unfortunately I quickly found out that DBMS_CYRPTO only supports HMAC_SHA1 and I not so quickly found out that HMAC_SHA1 is not the same as HMAC_SHA256. Am I stuck?

I binged (Google gets enough love and Microsoft can use the help after their most recent earnings results) for HMAC_SHA256 and PL/SQL and found this forum post. This guy wanted to do exactly what I need to do. Someone suggested that he create a Java stored procedure and then he responded with some Java code example with and hmacSHA256 function. Unfortunately no-one could give him an example of exactly how to create that Java stored procedure and then use it in PL/SQL. That's when I remembered that my good friend and colleague Joel Kallman wrote about creating a Java stored procedure to create a zip file of BLOB's.

Well, surely I can follow Joel's example as a cookbook and do the same thing with this hmacSHA256 function, right? The answer is yes, after spending more time than I probably should have (don't tell Joel if you see him), but hey, I had to prove that this REST stuff would actually be useful to customers. If they can't sign their requests, they won't be able to use Application Express to build cool applications that interact with Amazon Web Services. (At least that is my attempt at invoking what another colleague calls The Oracle Justification Server.)

First, start by compiling the following Java source.

Code listing 1, java source hmacSHA256


create or replace and compile java source named hmacSHA256 as

import java.io.*;
import java.net.*;
import java.security.*;
import java.util.*;

public class hmacSHA256 {
    public static String encrypt(
                        String message,
                        String keyStr) {

            //get the bytes of the keyStr
            byte[] key = keyStr.getBytes();
            // Start by getting an object to generate SHA-256 hashes with.
            MessageDigest sha256 = null;
            try {
                sha256 = MessageDigest.getInstance("SHA-256");
            } catch (NoSuchAlgorithmException e) {
                throw new java.lang.AssertionError(".hmacSHA256(): SHA-256 algorithm not found!");
            }
            // Hash the key if necessary to make it fit in a block (see RFC 2104).
            if (key.length > 64) {
               sha256.update(key);
                key = sha256.digest();
                sha256.reset();
            }

            // Pad the key bytes to a block (see RFC 2104).
            byte block[] = new byte[64];
            for (int i = 0; i < key.length; ++i) block[i] = key[i];
            for (int i = key.length; i < block.length; ++i) block[i] = 0;

            // Calculate the inner hash, defined in RFC 2104 as
            // SHA-256(KEY ^ IPAD + MESSAGE)), where IPAD is 64 bytes of 0x36.
            for (int i = 0; i < 64; ++i) block[i] ^= 0x36;
            sha256.update(block);
            try {
                sha256.update(message.getBytes("UTF-8"));
            } catch (UnsupportedEncodingException e) {
                throw new java.lang.AssertionError(
                        "ITunesU.hmacSH256(): UTF-8 encoding not supported!");
            }
            byte[] hash = sha256.digest();
            sha256.reset();

            // Calculate the outer hash, defined in RFC 2104 as
            // SHA-256(KEY ^ OPAD + INNER_HASH), where OPAD is 64 bytes of 0x5c.
            for (int i = 0; i < 64; ++i) block[i] ^= (0x36 ^ 0x5c);
            sha256.update(block);
            sha256.update(hash);
            hash = sha256.digest();

            // The outer hash is the message signature...
            // convert its bytes to hexadecimals.
            char[] hexadecimals = new char[hash.length * 2];
            for (int i = 0; i < hash.length; ++i) {
                for (int j = 0; j < 2; ++j) {
                    int value = (hash[i] >> (4 - 4 * j)) & 0xf;
                    char base = (value < 10) ? ('0') : ('a' - 10);
                    hexadecimals[i * 2 + j] = (char)(base + value);
                }
            }

            // Return a hexadecimal string representation of the message signature.
            return new String(hexadecimals);
    }
}
/

Next you create a standard PL/SQL function that uses the java source as in the following code listing.

Code listing 2, PL/SQL hmacSHA256 function


create or replace function hmacSHA256(
    p_string    in varchar2,
    p_key       in varchar2) return varchar2
as language java
    name 'hmacSHA256.encrypt(
          java.lang.String,
          java.lang.String) return String';
/

Finally, you create a function that takes in the string to sign, your AWS Secret Key, and then creates the HMAC_SHA256 signature.

Code listing 3, amazon_signature function


create or replace function amazon_signature(
    p_string    in varchar2,
    p_key       in varchar2) return varchar2
as
    encrypted_raw   raw(2000);
    output_string   varchar2(32000);
begin

    encrypted_raw := hmacSHA256(p_string,p_key);
    output_string := UTL_I18N.RAW_TO_CHAR (utl_encode.base64_encode(encrypted_raw), 'AL32UTF8');

    return output_string;

end amazon_signature;
/
show errors

Now let's test all this out. Amazon provides a nice self contained Web page utility that uses Javascript to help you create signed requests. I will use 123456 as my AWS Access Key ID and abcdefg as my AWS Secret Access Key. Plug in the following URL in the Unsigned URL text area:

http://ecs.amazonaws.com/onca/xml?Timestamp=2009-07-24T06%3A35%3A14-08%3A00&Service=AWSECommerceService&Version=2009-03-31&Operation=ItemSearch&ResponseGroup=ItemAttributes,Images&Keywords=liberty+and+tryanny&SearchIndex=Books&AWSAccessKeyId=123456&AssociateTag=apex30-20

This will come up with the following string to sign:

GET

ecs.amazonaws.com

/onca/xml

AWSAccessKeyId=123456&AssociateTag=apex30-20&Keywords=liberty%20and%20tryanny&Operation=ItemSearch&ResponseGroup=ItemAttributes%2CImages&SearchIndex=Books&Service=AWSECommerceService&Timestamp=2009-07-24T06%3A35%3A14-08%3A00&Version=2009-03-31

And will produce the following Signature parameter:

uMJX4cN6EXHyTUrC03Ae9hAcGdTnAHI0KqtovwQUHP8%3D

If I take the same string to sign and AWS Secret Key of abcdefg and run it through amazon_signature, I get the following results:

Code listing 4, result of amazon_signature function


jason@APX11W> declare
  2      l_token varchar2(4000);
  3  begin
  4      l_token := amazon_signature('GET
  5  ecs.amazonaws.com
  6  /onca/xml
  7  AWSAccessKeyId=123456&AssociateTag=apex30-20&Keywords=liberty%20and%20tryanny&Operation=ItemSearch&ResponseGroup=It
emAttributes%2CImages&SearchIndex=Books&Service=AWSECommerceService&Timestamp=2009-07-24T06%3A35%3A14-08%3A00&Version=20
09-03-31','abcdefg');
  8      dbms_output.put_line(l_token);
  9  end;
 10  /
uMJX4cN6EXHyTUrC03Ae9hAcGdTnAHI0KqtovwQUHP8=

PL/SQL procedure successfully completed.

The only difference in the result is the very end. Prior to using the signature, it must be URL encoded. If you run the string uMJX4cN6EXHyTUrC03Ae9hAcGdTnAHI0KqtovwQUHP8= through wwv_flow_utilities.url_encode2, you will get uMJX4cN6EXHyTUrC03Ae9hAcGdTnAHI0KqtovwQUHP8%3D.

New Web Services Integration White Paper and Sample Application on OTN

2009-04-14T06:59:00.000-07:00

Late last year I promised a white paper and sample application on integrating Application Express and BI Publisher through Web services. Well five months later (hey, that's less than 1/2 a year), it is now available on the Application Express Web Services Integration page on OTN. The sample application allows you to login to a BI Publisher instance as a BI Publisher defined user, browse the folders and reports available to that user, and provides the ability to download the report all from within the Application Express application. The white paper describes in detail how to build the application using PL/SQL and the flex_ws_api package. I hope you find it useful.

20 cents an hour! Who's got that kind of dough?

2009-03-13T08:45:00.001-07:00

In these trying economic times, I thought it would be prudent to try to save some money on the cost of my Cloud experiment. (Although a colleague of mine pointed out that my actions were "anti-stimulative.") I wanted to downsize my machine to the 10 cent per hour machine described as:

Small Instance (Default) 1.7 GB of memory, 1 EC2 Compute Unit (1 virtual core with 1 EC2 Compute Unit), 160 GB of instance storage, 32-bit platform

I didn't want to have to start from scratch; I wanted to keep the database and all the applications that I installed intact. But how could I do that? I knew full well that once I shut my instance down everything was gone. This is where the beauty of Amazon's Elastic Block Storage (EBS) comes in. It allows you to create persistent volumes that you can then attach, format, and mount on an EC2 instance. If you shutdown the EC2 instance for any reason your EBS volume persists and you can then mount it on another EC2 instance.

EBS has its own charges and I estimated it would cost me about $18 per month for my 10GB volume. But I am saving about $72 month by decreasing my hourly costs by 10 cents, so my net savings will be more than $50 a month. Not bad.

So the idea is that I create an EBS volume, move all the database files necessary to run the database to that volume, and then start a database using those data files on the cheaper machine. With some help from my friends at the Oracle Cloud Computing Center, I was able to do just that and I have detailed the steps below.

First I needed to create my EBS volume. This is very simple using Elasticfox and detailed in the Elasticfox Getting Started Guide. Click on the Volumes and Snapshots tab and then the create icon. Enter the size in GB (I chose 10) and also choose an availability zone. This is important since you can only attach EBS volumes in the same availability zone as your EC2 instance. My instance was in the us-east-1b zone so I chose that.

Now that I have the EBS volume I attached it to my instance by going to the Instances tab in Elasticfox, right clicking on my instance, and choose Attach an EBS volume. I gave it a device name of /dev/sdf1. Next I need to format the volume as a file system before I can use it so I issue (as root):

$ mkfs.ext3 /dev/sdf1

Figure 1, Create an EBS volume

Mount it to a temporary place, like:

$ mount /dev/sdf1 /mnt

My instance had a mount point of /u02 which contained all of the file necessary for my database including the datafiles, control files, redo log files, etc.

$ df -m

Filesystem 1M-blocks Used Available Use% Mounted on
/dev/sda1 9647 7116 2042 78% /
none 871 539 332 62% /dev/shm
/dev/sda2 342668 3000 322262 1% /u02

I copied all those files to the temporary mount point on /mnt. First I shutdown the database or the files will be unusable.

$ su - oracle
$ sqlplus / as sysdba
SQL> shutdown immediate
SQL> exit
$ cp /u02/* /mnt/
$ exit #this will return me to the root user

Finally I un-mounted the current /u02 mount point and remount /dev/sdf1 to /u02. I only did this to prove I can start the database using the EBS volume with these data files. If I can't start the database using this EBS volume mounted as /u02, I don't want to proceed until I can.

$ umount /dev/sda2
$ umount /dev/sdf1
$ mount /dev/sdf1 /u02
$ su - oracle
$ sqlplus / as sysdba
SQL> startup

In my case, the database started up so I did a shutdown immediate. I un-mounted /dev/sdf1 and then detached the EBS volume from this instance. I kept this instance available until I successfully started the database on the new smaller instance.

SQL> shutdown immediate
SQL> exit
$ exit
$ umount /dev/sdf1

With the database shutdown and the EBS volume detached, I created a snapshot of the volume. The snapshot is a point-in-time backup of the EBS volume and it is stored using Amazon's S3. Once I have a snapshot, I can create new EBS volumes based on that snapshot. This gives me the flexibility of shutting down all instances and deleting my EBS volume, and then I only pay S3 storage charges. I can create a new EBS volume based on this snapshot sometime in the future, start an EC2 instance, attach the EBS volume and I am good to go.

Figure 2, Create a snapshot of the EBS volume

Next, I started a new instance using the same 11gR1 32 bit AMI on a smaller instance with Elasticfox. It was important that I chose the same availability zone as my EBS volume, us-east-1b.

Figure 3, Launch the smaller EC2 instance

I connected to the instance using Putty and chose "No" when asked if I wanted to create a database at this time. This gave me an instance with the Oracle software installed in an Oracle home, but without a database. Now for the magic. I created a /u02 mount point under /, attached the EBS volume to this instance as /dev/sdf1, and then mounted it to /u02. To start the database on this instance I also needed to set my ORACLE_SID environment variable and create symlinks in $ORACLE_HOME/dbs to the spfile and password file in the admin directory on /u02.

$ mkdir /u02
$ mount /dev/sdf1 /u02
$ su – oracle
$ export ORACLE_SID=orcl
$ cd $ORACLE_HOME/dbs
$ ln -s /u02/admin/orcl/dbs/spfileorcl.ora spfileorcl.ora
$ ln -s /u02/admin/orcl/dbs/orapworcl orapworcl
$ sqlplus / as sysdba
SQL> startup

And my database started on the new 10 cent/hour machine!

Test Drive Oracle Application Express 3.2 for 60 cents (USD)

2009-03-03T13:38:00.000-08:00

Would you like to kick the tires of Oracle Application Express 3.2 on your own instance but do not have the spare hardware? Do you have 60 cents (USD)? Read on.

Amazon has a service called the Elastic Compute Cloud (EC2) where you can fire up virtual machines in the cloud on a whim. They have partnered with other software vendors to provide images with pre-configured software. Oracle is one of those vendors and you can read about the offerings at the Oracle Cloud Computing Center on OTN. Oracle has an image which contains Enterprise Linux and Oracle Database 11g R1.

The purpose of this post is to describe how I started an 11g R1 instance in the cloud, upgraded it to Application Express 3.2, and then completed the "Converting Your Oracle Forms Applications to Application Express 3.2" and "Utilizing Improved Security Enhancements in Application Express 3.2" Oracle By Example tutorials (OBEs). If you can spare 60 cents you may wish to try this yourself at home.

First, you need to get your AWS account in order. Sign up for AWS at the previous link and then sign up for EC2. Review the EC2 pricing and then establish a payment method. If you already are a customer of Amazon.com you can choose one of the credit cards you have on file or establish a new one. Unfortunately, there is no infrastructure in place for you to insert two quarters and a dime for this test drive.

Now you are going to need to get some software to startup an instance, manage it, and transfer files to it. Get the Elasticfox Firefox plug-in and review the Getting Started Guide. The Getting Started Guide will give detailed instructions on associating your AWS EC2 account with Elasticfox and also creating a KeyPair that you can use to identify yourself when connecting to the machine. You need to download Putty & PuttyGen to connect to your instance via SSH. You should download WinSCP to transfer files to your instance. Finally you need to download Application Express 3.2.

The keypair that you created when reviewing the Elasticfox Getting Started Guide needs to be converted to a format that can be used by Putty and WinSCP. You can use PuttyGen to create this key. Simply start PuttyGen, click load and browse to find the .pem keypair file you created with Elasticfox, then click Save private key to save it as a .ppk file.

Figure 1, Creating a .ppk from your .pem with PuttyGen.

You are ready to view Deploying Oracle Database in the Cloud viewlet that is available on the Oracle Cloud Computing Center page on OTN. The viewlet will show you how to find and start image ami-cecb2fa7 which is 32bit Enterprise Linux with 11g. It will take you through creating and configuring the database. I chose to start a c1.medium instance type which costs 20 cents/hr and is described as:

High-CPU Medium Instance 1.7 GB of memory, 5 EC2 Compute Units (2 virtual cores with 2.5 EC2 Compute Units each), 350 GB of instance storage, 32-bit platform

Figure 2, Instance Details

You manage the instance by connecting via SSH and you can use Putty as the viewlet demonstrated. Before you can connect to the machine on port 22 from your computer you will have to specifically allow it from your IP address. In Elasticfox first identify the Security Group used to start the instance. It is most likely called "default" unless you added another Security Group. Click the Security Group tab, highlight default and then click the green checkmark under the Group Permissions pane to add a permission. You can either allow a specific host or a network range. Allow your host to connect on port 22 and then create another permission to allow anyone to connect on port 8080 wich is the default port that EPG will be listening for HTTP requests. You choose Network and allow 0.0.0.0/0.

Figure 3, Creating a Security Group

Before logging in to Oracle Application Express on this instance, I had to change the password for the Application Express ADMIN account by following these steps (which changed the password to oracle):

su - oracle
sqlplus / as sysdba
SQL> @?/apex/apxxepwd oracle

Figure 4, Create a Workspace

Now for the fun part. Start WinSCP and connect to your instance and copy apex_3.2.zip there.

Figure 5, Copy apex_3.2.zip to your instance

Return to your Putty terminal session connected to your instance and install Oracle Application Express 3.2:

su - oracle
unzip apex_3.2.zip
cd apex
sqlplus / as sysdba
SQL>@apexins SYSAUX SYSAUX TEMP /i/
SQL>@apxldimg /home/oracle

Follow Joel's blog post on making Application Express run faster. You are ready to complete the Converting Your Oracle Forms Applications to Application Express 3.2 and Utilizing Improved Security Enhancements in Application Express 3.2 Oracle by Example (OBE) tutorials. An easy way to get the files for the Forms OBE to your machine is to run the following command in your terminal session:

wget http://www.oracle.com/technology/obe/apex32/files/forms_conversion.zip

Figure 6, Application Express 3.2 in the Cloud!

So there you have it. Test drive your own instance of Oracle Application Express 3.2 for around 60 cents (USD). For a limited time (until my manger finds out), you can try out my cloud instance using the following application linked below. I have configured my cloud instance to use the APEX Listener, which is a Java based HTTP listener that should release with the next version of Oracle Application Express.

RMOUG, Kaleidoscope 2009, Slight Change to flex_ws_api

2009-02-20T11:05:00.000-08:00

I know that I am horribly overdue for a post. I was out at the RMOUG Training Days last week and presented on Integrating Web Services and Application Express. The presentation should be linked from the referenced page soon. Unfortunately I only had 60 minutes to present 90 minutes worth of material. I presented on integrating Web services created from JDeveloper, XDB Native Web Services, BPEL Web services and finally integration with Oracle Content Server (formerly Stellent). The presentation was supposed to end with a discussion of debugging techniques using a variety of tools such as SOAPUI, ProxyTrace and XMLSpy.

Luckily, I will be presenting at the ODTUG Kaleidoscope 2009 in Monterey California in June. I have 90 minutes so I will be able to get to the debugging topics. Please join me if you can swing it.

Lastly, I have made a couple of minor tweeks to flex_ws_api. The changes allow for a more graceful response from parse_xml and parse_xml_clob if the node is not found using the supplied Xpath expression. It now simply returns null.

More Changes to flex_ws_api

2008-12-04T13:46:00.000-08:00

I added three new functions to the flex_ws_api. The most significant is the new make_request function which is just like the procedure with the same name but returns an XML type instead of storing the results in a collection. It became apparent to me that you may want to make a request without storing the results in a collection. In my particular case, I was working on a sample application for BiPublisher.

BiPublisher has a service called the PublicReportService. Tyler Muth has blogged about using the scheduleReport operation to schedule a report to be run and delivered vi email or ftp from an Application Express interface. But what if you want the report right now and allow for downloading it directly from that application? There is a runReport operation of the service to allow just that. It returns the report base64 encoded, and you just have to write a process to convert that to a BLOB and download it. No problem!

I set out to build a sample application that would do just that. I noticed that there was an operation called validateLogin so I thought maybe I could create a custom authentication scheme using flex_ws_api to validate the user by making a call to the PublicReportService. That is when I realized that storing the result in a collection won't do any good if the user does not have a valid session yet. So there was a need for a function to return the results as an XMLType. I also added two functions to parse out the results of the XMLType, parse_xml and parse_xml_clob.

I was successful in building the custom authentication scheme after I added these functions. I was also successful in building the rest of the application. You login using the validateLogin operation, view and traverse folders from the BiPublisher repository using the getFolderContents operation, and click on links to download the report using the runReport operation, all in an Application Express application. I plan to make the sample application available on OTN along with a white paper on how it was built. Stay tuned.

The New flex_ws_api and SOAP 1.2 Example

2008-11-10T11:46:00.000-08:00

Before I make wild claims like the one I made on October 22, where I said "flex_ws_api now supports SOAP 1.2" I probably should have tested the code against a SOAP 1.2 service. I can now say that I have tested it with a SOAP 1.2 service and I learned a couple of things.

Firstly, when setting the content-type header, the action must appear after the charset declaration or you will get an unsupported media type error from the service. Secondly, there may be an occasion where you have base 64 encoded character data and you want to convert that into binary data, for example, providing the ability to download a document.

Based on these two findings, I have updated the flex_ws_api code available below to set the content-type header properly and added a function called clobbase642blob that takes in a base64 encoded clob and returns a blob.

The services that I tested with are the Oracle Beehive web services. Oracle Beehive "is a collaborative environment built on a unique model that combines the various communication and coordination services into a comprehensive platform." You can test these services via an HTTP interface which came in very handy when I needed to know the structure of the SOAP 1.2 envelope that each service expected. The services that I interacted with were the WorkspaceService to get a list of folders in a workspace and the DocumentService to get a list of documents in a particular folder.

The application I built was a simple three page application. The first page showed a list of folders in the workspaces that the logged in user belongs to. Each folder linked to the second page which would then show the contents of that folder. Each document linked to the third page which has a before header process that downloads the document.

Start by creating an Application Express application with one blank page. Make sure you have compiled the new flex_ws_api in the schema associated with this workspace. Call the blank page something like Get Folders. The first thing to do is to create a before header process on the page to use the flex_ws_api and call the WorkspaceService, operation GetWorkspaces based on the currently logged in user. See the code listing.

Code Listing 1, Call GetWorkspaces Before Header Process on Page 1


declare
  l_env clob;
begin
  l_env := '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"><soap:Header><wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:env="http://www.w3.org/2003/05/soap-envelope" soap:mustUnderstand="1"><wsse:UsernameToken xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"><wsse:Username>'||:APP_USER||'</wsse:Username><wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">Welcome1</wsse:Password></wsse:UsernameToken></wsse:Security></soap:Header>
    <soap:Body xmlns:ns1="http://oracle.bee.platform.webservice/">
        <ns1:GetWorkspaces>
            <ns1:uID xmlns:ns2="http://www.w3.org/2001/XMLSchema-instance" ns2:nil="true"/>
            <ns1:wspType>TEAM</ns1:wspType>
            <ns1:wspFilter xmlns:ns3="http://www.w3.org/2001/XMLSchema-instance" ns3:nil="true"/>
        </ns1:GetWorkspaces>
    </soap:Body>
</soap:Envelope>';

  flex_ws_api.make_request(
    p_url => 'http://localhost:7777/ws/WorkspaceService',
    p_version => '1.2',
    p_collection_name => 'GETWORKSPACES_RESPONSE',
    p_envelope => l_env );
end;

One thing to note about the process above is that we are passing the username in the envelope using :APP_USER to reference the currently logged in user. Whatever authentication method your application uses will have to also be a user in the Beehive world. The second thing to note is that the password is hardcoded to Welcome1. You will obviously need to change this to be dynamic and based on the user. Finally, we are storing the response from the Beehive service in an Application Express collection called GETWORKSPACES_RESPONSE.

Now that the page has a process to call the web service you create a report region to show the folders in the workspace for this particular user based on the web service result. You do this by writing a query that first casts the clob001 column in the Application Express collection to an xmltype and then use the table command to shred the document. See the code listing.

Code Listing 2, Report on Result to Show Folders, Page 1


select wwv_flow_utilities.url_encode2(extractValue(value(t),'/*/id','xmlns="http://oracle.bee.platform.webservice/"')) "id"
     , extractValue(value(t),'/*/name','xmlns="http://oracle.bee.platform.webservice/"') "name"
     , extractValue(value(t),'/*/description','xmlns="http://oracle.bee.platform.webservice/"') "description"
 from wwv_flow_collections c,
      table(xmlsequence(extract(xmltype.createxml(c.clob001),'//GetWorkspacesResponse/return/libraryIDList','xmlns="http://oracle.bee.platform.webservice/"'))) t
where c.collection_name = 'GETWORKSPACES_RESPONSE'

One thing to note is that the id column might contain colons and slashes, so the report calls wwv_flow_utilities.url_encode2 to encode these characters. You will modify this report slightly to hide the id column and then link the folder name to page 2 which makes the encoding trick necessary. You first create page two.

Page two of the application calls the DocumentService and the GetDocumentsInFolder operation based on the value of a hidden item to hold the ID of the folder.

First create a new blank page (page 2). Add a before header process that uses the flex_ws_api to call the DocumentService as in the following code listing.

Code Listing 3, Call GetDocumentsInFolder Before Header Process, Page 2


declare
  l_env clob;
begin
  l_env := '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"><soap:Header><wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:env="http://www.w3.org/2003/05/soap-envelope" soap:mustUnderstand="1"><wsse:UsernameToken xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"><wsse:Username>'||:APP_USER||'</wsse:Username><wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">Welcome1</wsse:Password></wsse:UsernameToken></wsse:Security></soap:Header>
    <soap:Body xmlns:ns1="http://oracle.bee.platform.webservice/">
        <ns1:GetDocumentsInFolder>
            <ns1:folderID>
                <ns1:type xmlns:ns2="http://www.w3.org/2001/XMLSchema-instance" ns2:nil="true"/>
                <ns1:description xmlns:ns3="http://www.w3.org/2001/XMLSchema-instance" ns3:nil="true"/>
                <ns1:name xmlns:ns4="http://www.w3.org/2001/XMLSchema-instance" ns4:nil="true"/>
                <ns1:id>'||wwv_flow_utilities.url_decode2(:P2_FOLDER_ID)||'</ns1:id>
            </ns1:folderID>
            <ns1:docFilter xmlns:ns5="http://www.w3.org/2001/XMLSchema-instance" ns5:nil="true"/>
        </ns1:GetDocumentsInFolder>
    </soap:Body>
</soap:Envelope>';

  flex_ws_api.make_request(
    p_url => 'http://localhost:7777/ws/DocumentService',
    p_version => '1.2',
    p_collection_name => 'GETDOCUMENTS_RESPONSE',
    p_envelope => l_env );
end;

Note that the process calls wwv_flow_utilities.url_decode2 to decode the folder id, which we needed to encode on the prior page, because it was being passed as part of the link. The response is stored in a collection called GETDOCUMENTS_RESPONSE.

The next thing to do is the create a report region that reports on the results of the web service much like page one. See the code listing.

Code Listing 4, Report on GetDocumentsInFolder Result to List Documents


select wwv_flow_utilities.url_encode2(extractValue(value(t),'/*/contentID/id','xmlns="http://oracle.bee.platform.webservice/"')) "id"
     , extractValue(value(t),'/*/content/name','xmlns="http://oracle.bee.platform.webservice/"') "name"
     , extractValue(value(t),'/*/mimeMultipartType','xmlns="http://oracle.bee.platform.webservice/"') "type"
 from wwv_flow_collections c,
      table(xmlsequence(extract(xmltype.createxml(c.clob001),'//GetDocumentsInFolderResponse/return','xmlns="http://oracle.bee.platform.webservice/"'))) t
where c.collection_name = 'GETDOCUMENTS_RESPONSE'

Finally, create a hidden item on page two called P2_FOLDER_ID for the folder id.

Now we return to page one and edit the report region to link the folder name to page two, populating the P2_FOLDER_ID item. To accomplish this:

Click the Report link next to the Folders region

Uncheck the Show check box corresponding to the id column

Click the edit icon next to the name column

Scroll down to the Column Link region

Click the [name] quick link below the Link Text field

Choose Page in this Application from the Target list

Enter 2 in the Page field

Choose P2_FOLDER_ID for Item 1 from the pop-up list

Choose #id# for Value from the pop-up list

Click Apply Changes

Figure 1, Column Link Attributes for Folder Name

At this point, you should be able to run the application, see a list of folders, click on a folder, and see the contents of the folder.

Figure 2, List of Folders

Figure 3, List of Contents of Folder

The response from calling the DocumentService GetDocumentsInFolder operation is not only a listing of the documents there but also the data of the documents encoded in base64. Luckily, we can now convert the base64 clob into a blob which allows us to create another page in the application which will download the document.

Create a new blank page (3) in the application. Create an empty HTML region to hold a hidden item for the document ID. Create a hidden item on page 3 called P3_DOCUMENT_ID.

Now create a before header process that will parse the response of GetDocumentsInFolder and determine the size, mime type, name and base64 encoded data. Convert the base64 encoded data to a blob, and then use wpg_docload.download_file procedure to download the file. Use the code in the following listing to create the process.

Code listing 5, Download Document Before Header Process, Page 3


declare
  l_mime      varchar2(48);
  l_name      varchar2(4000);
  l_base64    clob;
  l_blob      blob;
  l_size      varchar2(255);
begin
  l_size := flex_ws_api.parse_response('GETDOCUMENTS_RESPONSE','//ns0:GetDocumentsInFolderResponse/ns0:return[ns0:contentID/ns0:id/text()="'||wwv_flow_utilities.url_decode2(:P3_DOCUMENT_ID)||'"]/ns0:content/ns0:size/text()','xmlns:ns0="http://oracle.bee.platform.webservice/"');

  l_mime := flex_ws_api.parse_response('GETDOCUMENTS_RESPONSE','//ns0:GetDocumentsInFolderResponse/ns0:return[ns0:contentID/ns0:id/text()="'||wwv_flow_utilities.url_decode2(:P3_DOCUMENT_ID)||'"]/ns0:content/ns0:mediaType/text()','xmlns:ns0="http://oracle.bee.platform.webservice/"');

  l_name := flex_ws_api.parse_response('GETDOCUMENTS_RESPONSE','//ns0:GetDocumentsInFolderResponse/ns0:return[ns0:contentID/ns0:id/text()="'||wwv_flow_utilities.url_decode2(:P3_DOCUMENT_ID)||'"]/ns0:content/ns0:name/text()','xmlns:ns0="http://oracle.bee.platform.webservice/"');

  l_base64 := flex_ws_api.parse_response_clob('GETDOCUMENTS_RESPONSE','//ns0:GetDocumentsInFolderResponse/ns0:return[ns0:contentID/ns0:id/text()="'||wwv_flow_utilities.url_decode2(:P3_DOCUMENT_ID)||'"]/ns0:content/ns0:data/text()','xmlns:ns0="http://oracle.bee.platform.webservice/"');

  l_blob := flex_ws_api.clobbase642blob(l_base64);

 
  htp.init;

  owa_util.mime_header( nvl(l_mime,'application/octet'), FALSE );
  htp.p('Content-length: '||l_size);
  htp.p('Content-Disposition:  attachment; filename="'||replace(replace(l_name,chr(10),null),chr(13),null)||'"');
  owa_util.http_header_close;
  wpg_docload.download_file( l_blob );
 
  apex_application.g_unrecoverable_error := true;

end;

The final step is to modify the report attributes of the report region on page 2, hide the id column and link the name column to page 3, populating the P3_DOCUMENT_ID item like you did on page one for the link from the folder name. Once you have completed this step, you will have a simple three page application the shows a list of folders of workspaces that the currently logged in user belongs to, shows the contents of the folders when click on, and then downloads the document when clicked on.

flex_ws_api Now Supports SOAP 1.2

2008-10-22T12:47:00.000-07:00

I recently became aware that not only is the message and envelope format of SOAP 1.2 different from SOAP 1.1, but also the Content-Type HTTP header as well. If you send the Content-Type of a SOAP 1.1 message, text/xml to a SOAP 1.2 document, you will get a message back, formatted in SOAP 1.1 saying that there is a version mismatch. SOAP 1.2 expects a Content-Type HTTP Header like the following (note the SOAPAction is also on this line and shortened to action):

Content-Type: application/soap+xml; action=initiate; charset="utf-8"

The flex_ws_api now accepts a p_version parameter which is defaulted to '1.1', but if '1.2' is passed, the proper Content-Type header will be sent for a SOAP 1.2 message.

APEXposed 2008

2008-09-30T12:23:00.000-07:00

I will be presenting at APEXposed 2008, October 29 - 30, at Chicago O'Hare Wyndham. I am going to present on everything Application Express and Web services. You can view the abstract here:

http://www.odtugapextraining.com/presentations.html#IntegratingWebServices

Register for the event here:

http://www.technicalconferencesolutions.com/ODTUG_OPP_registration.html

Hope you can make it!

NTLM and Application Express Whitepaper

2008-06-13T09:57:00.000-07:00

There is now an official whitepaper on OTN written about NTLM authentication and Application Express by a colleague of mine, Priyanka Sharma. The paper is very good and gives a good introduction and definition of NTLM. The page sentry function is based on the NTLM posting here with some fine contributions by Patrick Wolf.

Flexible Web Service API

2008-06-06T12:12:00.000-07:00

Update 11/20/2009: The flex_ws_api is now managed and updated at https://flex-ws-api.samplecode.oracle.com/.

I have often been hounded by a colleague, Tyler Muth, about creating some type of programmatic support for Web services in Application Express. Tyler's reasons are good ones, for example, being able to call a Web service to populate a temporary table that is used for an LOV in an application. He has had other customers that want to create an authentication scheme based on a Web service. And finally, the reason that actually got me to work on a flexible Web service API, is that you cannot post large base64 encoded binary information to a Web service because you are limited to 32K when you reference an item value in Application Express.

I worked with some other colleagues on creating an Application Express application that can checkin a document to a Stellent repository through Stellent's Web service API's. We wanted to be able to checkin documents whose base64 encoding was larger than 32K bytes. I could not use Application Express's built-in support for Web services because of that requirement. My only option was to hand code a PL/SQL process that does all the necessary things to post a very large SOAP envelope to a service, and parse out the response.

I recently revisited that PL/SQL code and created a flexible Web service API that can be used to call any Web service programmatically with Application Express. You are responsible for building up the request envelope as a CLOB. I use a tool called SOAPUI to help me build a SOAP request envelope. You create a new project based on a WSDL, and the tool will create shell envelopes for all operations that the service supports as defined in the WSDL.

The API supports basic HTTP authentication, a proxy server override (great for debugging), wallets for contacting services with HTTPS, and a colon delimited list of name value pairs that will be sent as HTTP headers with the request. This was added for maximum flexibility to support custom authentication with Web services among other things. See the code listing below. It also contains helper functions to take a BLOB and base64 encode it into a CLOB (very useful if you have a service that you need to pass a document or attachment to) as well as functions to anaylze a response and return the text in varchar or clob format.

--


create or replace package flex_ws_api
as

empty_vc_arr wwv_flow_global.vc_arr2;

g_request_cookies   utl_http.cookie_table;
g_response_cookies  utl_http.cookie_table;

type header is record (name varchar2(256), value varchar2(1024));
type header_table is table of header index by binary_integer;

g_headers           header_table;
g_request_headers   header_table;

g_status_code       pls_integer;


function blob2clobbase64 (
    p_blob in blob ) return clob;

function clobbase642blob (
    p_clob in clob ) return blob;

procedure make_request (
    p_url               in varchar2,
    p_action            in varchar2 default null,
    p_version           in varchar2 default '1.1',
    p_collection_name   in varchar2 default null,
    p_envelope          in clob,
    p_username          in varchar2 default null,
    p_password          in varchar2 default null,
    p_proxy_override    in varchar2 default null,
    p_wallet_path       in varchar2 default null,
    p_wallet_pwd        in varchar2 default null,
    p_extra_headers     in wwv_flow_global.vc_arr2 default empty_vc_arr );

function make_request (
    p_url               in varchar2,
    p_action            in varchar2 default null,
    p_version           in varchar2 default '1.1',
    p_envelope          in clob,
    p_username          in varchar2 default null,
    p_password          in varchar2 default null,
    p_proxy_override    in varchar2 default null,
    p_wallet_path       in varchar2 default null,
    p_wallet_pwd        in varchar2 default null,
    p_extra_headers     in wwv_flow_global.vc_arr2 default empty_vc_arr ) return xmltype;

function make_rest_request(
    p_url               in varchar2,
    p_http_method       in varchar2,
    p_username          in varchar2 default null,
    p_password          in varchar2 default null,
    p_proxy_override    in varchar2 default null,
    p_body              in clob default empty_clob(),
    p_body_blob         in blob default empty_blob(),
    p_parm_name         in wwv_flow_global.vc_arr2 default empty_vc_arr,
    p_parm_value        in wwv_flow_global.vc_arr2 default empty_vc_arr,
    p_http_headers      in wwv_flow_global.vc_arr2 default empty_vc_arr,
    p_http_hdr_values   in wwv_flow_global.vc_arr2 default empty_vc_arr,
    p_wallet_path       in varchar2 default null,
    p_wallet_pwd        in varchar2 default null ) return clob;

function parse_xml (
    p_xml               in xmltype,
    p_xpath             in varchar2,
    p_ns                in varchar2 default null ) return varchar2;

function parse_xml_clob (
    p_xml               in xmltype,
    p_xpath             in varchar2,
    p_ns                in varchar2 default null ) return clob;

function parse_response (
    p_collection_name   in varchar2,
    p_xpath             in varchar2,
    p_ns                in varchar2 default null ) return varchar2;

function parse_response_clob (
    p_collection_name   in varchar2,
    p_xpath             in varchar2,
    p_ns                in varchar2 default null ) return clob;

end flex_ws_api;
/
show errors

create or replace package body flex_ws_api
as

function blob2clobbase64 (
    p_blob in blob ) return clob
is
    pos         pls_integer         := 1;
    buffer      varchar2 (32767);
    res         clob;
    lob_len     integer             := dbms_lob.getlength (p_blob);
    l_width     pls_integer         := (76 / 4 * 3)-9;
begin
    dbms_lob.createtemporary (res, true);
    dbms_lob.open (res, dbms_lob.lob_readwrite);

    while (pos < lob_len) loop
        buffer :=
                utl_raw.cast_to_varchar2
                 (utl_encode.base64_encode (dbms_lob.substr (p_blob, l_width, pos)));

        dbms_lob.writeappend (res, length (buffer), buffer);

        pos := pos + l_width;
    end loop;

    return res;

end blob2clobbase64;

function clobbase642blob (
    p_clob in clob ) return blob
is
    pos         pls_integer         := 1;
    buffer      raw(36);
    res         blob;
    lob_len     integer             := dbms_lob.getlength (p_clob);
    l_width     pls_integer         := (76 / 4 * 3)-9;
begin
    dbms_lob.createtemporary (res, true);
    dbms_lob.open (res, dbms_lob.lob_readwrite);

    while (pos < lob_len) loop
        buffer := utl_encode.base64_decode(utl_raw.cast_to_raw(dbms_lob.substr (p_clob, l_width, pos)));

        dbms_lob.writeappend (res, utl_raw.length(buffer), buffer);

        pos := pos + l_width;
    end loop;

    return res;

end clobbase642blob;

procedure make_request (
    p_url               in varchar2,
    p_action            in varchar2 default null,
    p_version           in varchar2 default '1.1',
    p_collection_name   in varchar2 default null,
    p_envelope          in clob,
    p_username          in varchar2 default null,
    p_password          in varchar2 default null,
    p_proxy_override    in varchar2 default null,
    p_wallet_path       in varchar2 default null,
    p_wallet_pwd        in varchar2 default null,
    p_extra_headers     in wwv_flow_global.vc_arr2 default empty_vc_arr )
is
    l_clob clob;
    l_http_req utl_http.req;
    l_http_resp utl_http.resp;
    l_amount binary_integer := 8000;
    l_offset integer := 1;
    l_buffer varchar2(32000);
    l_db_charset   varchar2(100);
    l_env_lenb integer := 0;
    i integer := 0;
    l_headers wwv_flow_global.vc_arr2;
    l_response varchar2(2000);
    l_name          varchar2(256);
    l_hdr_value     varchar2(1024);
    l_hdr           header;
    l_hdrs          header_table;
begin

    -- determine database characterset, if not AL32UTF8, conversion will be necessary
    select value into l_db_charset from nls_database_parameters where parameter='NLS_CHARACTERSET';

    -- determine length for content-length header
    loop
        exit when wwv_flow_utilities.clob_to_varchar2(p_envelope,i*32767) is null;
        if l_db_charset = 'AL32UTF8' then
            l_env_lenb := l_env_lenb + lengthb(wwv_flow_utilities.clob_to_varchar2(p_envelope,i*32767));
        else
            l_env_lenb := l_env_lenb + utl_raw.length(
                    utl_raw.convert(utl_raw.cast_to_raw(wwv_flow_utilities.clob_to_varchar2(p_envelope,i*32767)),
                        'american_america.al32utf8','american_america.'||l_db_charset));
        end if;
        i := i + 1;
    end loop;

    -- set a proxy if required
    if apex_application.g_proxy_server is not null and p_proxy_override is null then
        utl_http.set_proxy (proxy => apex_application.g_proxy_server);
    elsif p_proxy_override is not null then
        utl_http.set_proxy (proxy => p_proxy_override);
    end if;

    utl_http.set_persistent_conn_support(true);
    utl_http.set_transfer_timeout(600);

    -- set wallet if necessary
    if instr(lower(p_url),'https') = 1 then
        utl_http.set_wallet(p_wallet_path, p_wallet_pwd);
    end if;

    -- set cookies if necessary
    begin
        if g_request_cookies.count > 0 then
            utl_http.clear_cookies;
            utl_http.add_cookies(g_request_cookies);
        end if;
    exception when others then
        raise_application_error(-20001,'The provided cookie is invalid.');
    end;

    -- begin the request
    if wwv_flow_utilities.db_version like '9.%' then
        l_http_req := utl_http.begin_request(p_url, 'POST', 'HTTP/1.0');
    else
        l_http_req := utl_http.begin_request(p_url, 'POST');
    end if;

    -- set basic authentication if required
    if p_username is not null then
        utl_http.set_authentication (
            r => l_http_req,
            username => p_username,
            password => p_password,
            scheme => 'Basic',
            for_proxy => false );
    end if;

    -- set standard HTTP headers for a SOAP request
    utl_http.set_header(l_http_req, 'Proxy-Connection', 'Keep-Alive');
    if p_version = '1.2' then
        utl_http.set_header(l_http_req, 'Content-Type', 'application/soap+xml; charset=UTF-8; action="'||p_action||'";');
    else
        utl_http.set_header(l_http_req, 'SOAPAction', p_action);
        utl_http.set_header(l_http_req, 'Content-Type', 'text/xml; charset=UTF-8');
    end if;
    utl_http.set_header(l_http_req, 'Content-Length', l_env_lenb);

    -- set additional headers if supplied, these are separated by a colon (:) as name/value pairs
    for i in 1.. p_extra_headers.count loop
        l_headers := apex_util.string_to_table(p_extra_headers(i));
        utl_http.set_header(l_http_req, l_headers(1), l_headers(2));
    end loop;

    --set headers from g_request_headers
    for i in 1.. g_request_headers.count loop
        utl_http.set_header(l_http_req, g_request_headers(i).name, g_request_headers(i).value);
    end loop;

    -- read the envelope, convert to UTF8 if necessary, then write it to the HTTP request
    begin
        loop
            dbms_lob.read( p_envelope, l_amount, l_offset, l_buffer );
            if l_db_charset = 'AL32UTF8' then
                utl_http.write_text(l_http_req, l_buffer);
            else
                utl_http.write_raw(l_http_req,utl_raw.convert(utl_raw.cast_to_raw(l_buffer),'american_america.al32utf8','american_america.'||l_db_charset));
            end if;
            l_offset := l_offset + l_amount;
            l_amount := 8000;
        end loop;
    exception
        when no_data_found then
            null;
    end;

    -- get the response
    l_http_resp := utl_http.get_response(l_http_req);

    -- set response code, response http header and response cookies global
    g_status_code := l_http_resp.status_code;
    utl_http.get_cookies(g_response_cookies);
    for i in 1..utl_http.get_header_count(l_http_resp) loop
        utl_http.get_header(l_http_resp, i, l_name, l_hdr_value);
        l_hdr.name := l_name;
        l_hdr.value := l_hdr_value;
        l_hdrs(i) := l_hdr;
    end loop;

    g_headers := l_hdrs;

    -- put the response in a collection if necessary
    if p_collection_name is not null then

        apex_collection.create_or_truncate_collection(p_collection_name);

        dbms_lob.createtemporary( l_clob, FALSE );
        dbms_lob.open( l_clob, dbms_lob.lob_readwrite );
        begin
            loop
                utl_http.read_text(l_http_resp, l_buffer);
                dbms_lob.writeappend( l_clob, length(l_buffer), l_buffer );
            end loop;
        exception
            when others then
                if sqlcode <> -29266 then
                    raise;
                end if;
        end;

        apex_collection.add_member(
            p_collection_name   => p_collection_name,
            p_clob001           => l_clob);
    end if;
    --
    utl_http.end_response(l_http_resp);

end make_request;

function make_request (
    p_url               in varchar2,
    p_action            in varchar2 default null,
    p_version           in varchar2 default '1.1',
    p_envelope          in clob,
    p_username          in varchar2 default null,
    p_password          in varchar2 default null,
    p_proxy_override    in varchar2 default null,
    p_wallet_path       in varchar2 default null,
    p_wallet_pwd        in varchar2 default null,
    p_extra_headers     in wwv_flow_global.vc_arr2 default empty_vc_arr ) return xmltype
is
    l_clob clob;
    l_http_req utl_http.req;
    l_http_resp utl_http.resp;
    l_amount binary_integer := 8000;
    l_offset integer := 1;
    l_buffer varchar2(32000);
    l_db_charset   varchar2(100);
    l_env_lenb integer := 0;
    i integer := 0;
    l_headers wwv_flow_global.vc_arr2;
    l_response varchar2(2000);
    l_name          varchar2(256);
    l_hdr_value     varchar2(1024);
    l_hdr           header;
    l_hdrs          header_table;
begin

    -- determine database characterset, if not AL32UTF8, conversion will be necessary
    select value into l_db_charset from nls_database_parameters where parameter='NLS_CHARACTERSET';

    -- determine length for content-length header
    loop
        exit when wwv_flow_utilities.clob_to_varchar2(p_envelope,i*32767) is null;
        if l_db_charset = 'AL32UTF8' then
            l_env_lenb := l_env_lenb + lengthb(wwv_flow_utilities.clob_to_varchar2(p_envelope,i*32767));
        else
            l_env_lenb := l_env_lenb + utl_raw.length(
                    utl_raw.convert(utl_raw.cast_to_raw(wwv_flow_utilities.clob_to_varchar2(p_envelope,i*32767)),
                        'american_america.al32utf8','american_america.'||l_db_charset));
        end if;
        i := i + 1;
    end loop;

    -- set a proxy if required
    if apex_application.g_proxy_server is not null and p_proxy_override is null then
        utl_http.set_proxy (proxy => apex_application.g_proxy_server);
    elsif p_proxy_override is not null then
        utl_http.set_proxy (proxy => p_proxy_override);
    end if;

    utl_http.set_persistent_conn_support(true);
    utl_http.set_transfer_timeout(600);

    -- set wallet if necessary
    if instr(lower(p_url),'https') = 1 then
        utl_http.set_wallet(p_wallet_path, p_wallet_pwd);
    end if;

    -- set cookies if necessary
    begin
        if g_request_cookies.count > 0 then
            utl_http.clear_cookies;
            utl_http.add_cookies(g_request_cookies);
        end if;
    exception when others then
        raise_application_error(-20001,'The provided cookie is invalid.');
    end;

    -- begin the request
    if wwv_flow_utilities.db_version like '9.%' then
        l_http_req := utl_http.begin_request(p_url, 'POST', 'HTTP/1.0');
    else
        l_http_req := utl_http.begin_request(p_url, 'POST');
    end if;

    -- set basic authentication if required
    if p_username is not null then
        utl_http.set_authentication (
            r => l_http_req,
            username => p_username,
            password => p_password,
            scheme => 'Basic',
            for_proxy => false );
    end if;

    -- set standard HTTP headers for a SOAP request
    utl_http.set_header(l_http_req, 'Proxy-Connection', 'Keep-Alive');
    if p_version = '1.2' then
        utl_http.set_header(l_http_req, 'Content-Type', 'application/soap+xml; charset=UTF-8; action="'||p_action||'";');
    else
        utl_http.set_header(l_http_req, 'SOAPAction', p_action);
        utl_http.set_header(l_http_req, 'Content-Type', 'text/xml; charset=UTF-8');
    end if;
    utl_http.set_header(l_http_req, 'Content-Length', l_env_lenb);

    -- set additional headers if supplied, these are separated by a colon (:) as name/value pairs
    for i in 1.. p_extra_headers.count loop
        l_headers := apex_util.string_to_table(p_extra_headers(i));
        utl_http.set_header(l_http_req, l_headers(1), l_headers(2));
    end loop;

    --set headers from g_request_headers
    for i in 1.. g_request_headers.count loop
        utl_http.set_header(l_http_req, g_request_headers(i).name, g_request_headers(i).value);
    end loop;

    -- read the envelope, convert to UTF8 if necessary, then write it to the HTTP request
    begin
        loop
            dbms_lob.read( p_envelope, l_amount, l_offset, l_buffer );
            if l_db_charset = 'AL32UTF8' then
                utl_http.write_text(l_http_req, l_buffer);
            else
                utl_http.write_raw(l_http_req,utl_raw.convert(utl_raw.cast_to_raw(l_buffer),'american_america.al32utf8','american_america.'||l_db_charset));
            end if;
            l_offset := l_offset + l_amount;
            l_amount := 8000;
        end loop;
    exception
        when no_data_found then
            null;
    end;

    -- get the response
    l_http_resp := utl_http.get_response(l_http_req);

    -- set response code, response http header and response cookies global
    g_status_code := l_http_resp.status_code;
    utl_http.get_cookies(g_response_cookies);
    for i in 1..utl_http.get_header_count(l_http_resp) loop
        utl_http.get_header(l_http_resp, i, l_name, l_hdr_value);
        l_hdr.name := l_name;
        l_hdr.value := l_hdr_value;
        l_hdrs(i) := l_hdr;
    end loop;

    g_headers := l_hdrs;

    -- put the response in a clob
    dbms_lob.createtemporary( l_clob, FALSE );
    dbms_lob.open( l_clob, dbms_lob.lob_readwrite );
    begin
        loop
            utl_http.read_text(l_http_resp, l_buffer);
            dbms_lob.writeappend( l_clob, length(l_buffer), l_buffer );
        end loop;
    exception
        when others then
            if sqlcode <> -29266 then
                raise;
            end if;
    end;

    utl_http.end_response(l_http_resp);

    return xmltype.createxml(l_clob);

exception when others then
    if sqlcode = -31011 then -- its not xml
        return null;
    end if;
end make_request;

function make_rest_request(
    p_url               in varchar2,
    p_http_method       in varchar2,
    p_username          in varchar2 default null,
    p_password          in varchar2 default null,
    p_proxy_override    in varchar2 default null,
    p_body              in clob default empty_clob(),
    p_body_blob         in blob default empty_blob(),
    p_parm_name         in wwv_flow_global.vc_arr2 default empty_vc_arr,
    p_parm_value        in wwv_flow_global.vc_arr2 default empty_vc_arr,
    p_http_headers      in wwv_flow_global.vc_arr2 default empty_vc_arr,
    p_http_hdr_values   in wwv_flow_global.vc_arr2 default empty_vc_arr,
    p_wallet_path       in varchar2 default null,
    p_wallet_pwd        in varchar2 default null )
return clob
is
    l_http_req      utl_http.req;
    l_http_resp     utl_http.resp;
    --
    l_body          clob default empty_clob();
    i               integer;
    l_env_lenb      number  := 0;
    l_db_charset    varchar2(100) := 'AL32UTF8';
    l_buffer        varchar2(32767);
    l_raw           raw(48);
    l_amount        number;
    l_offset        number;
    l_value         clob;
    l_url           varchar2(32767);
    l_parm_value    varchar2(32767);
    l_name          varchar2(256);
    l_hdr_value     varchar2(1024);
    l_hdr           header;
    l_hdrs          header_table;
begin

    -- determine database characterset, if not AL32UTF8, conversion will be necessary
    select value into l_db_charset from nls_database_parameters where parameter='NLS_CHARACTERSET';

    -- set a proxy if required
    if apex_application.g_proxy_server is not null and p_proxy_override is null then
        utl_http.set_proxy (proxy => apex_application.g_proxy_server);
    elsif p_proxy_override is not null then
        utl_http.set_proxy (proxy => p_proxy_override);
    end if;

    utl_http.set_persistent_conn_support(TRUE);
    utl_http.set_transfer_timeout(180);

    if instr(lower(p_url),'https') = 1 then
        utl_http.set_wallet(p_wallet_path, p_wallet_pwd);
    end if;

    if dbms_lob.getlength(p_body) = 0 then
        for i in 1.. p_parm_name.count loop
            if p_http_method = 'GET' then
                l_parm_value := apex_util.url_encode(p_parm_value(i));
            else
                l_parm_value := p_parm_value(i);
            end if;
            if i = 1 then
                l_body := p_parm_name(i)||'='||l_parm_value;
            else
                l_body := l_body||'&'||p_parm_name(i)||'='||l_parm_value;
            end if;
        end loop;
    else
        l_body := p_body;
    end if;

    i := 0;

    l_url := p_url;

    if p_http_method = 'GET' then
        l_url := l_url||'?'||wwv_flow_utilities.clob_to_varchar2(l_body);
    end if;

    -- determine length in bytes of l_body;
    if dbms_lob.getlength(p_body_blob) > 0 then
        l_env_lenb := dbms_lob.getlength(p_body_blob);
    else
        loop
            exit when wwv_flow_utilities.clob_to_varchar2(l_body,i*32767) is null;
            if l_db_charset = 'AL32UTF8' then
                l_env_lenb := l_env_lenb + lengthb(wwv_flow_utilities.clob_to_varchar2(l_body,i*32767));
            else
                l_env_lenb := l_env_lenb + utl_raw.length(
                    utl_raw.convert(utl_raw.cast_to_raw(wwv_flow_utilities.clob_to_varchar2(l_body,i*32767)),
                        'american_america.al32utf8','american_america.' || l_db_charset));
            end if;
            i := i + 1;
        end loop;
    end if;

    -- set cookies if necessary
    begin
        if g_request_cookies.count > 0 then
            utl_http.clear_cookies;
            utl_http.add_cookies(g_request_cookies);
        end if;
    exception when others then
        raise_application_error(-20001,'The provided cookie is invalid.');
    end;

    begin
        l_http_req := utl_http.begin_request(l_url, p_http_method);
        -- set basic authentication if necessary
        if p_username is not null then
             utl_http.set_authentication(l_http_req, p_username, p_password);
        end if;
        utl_http.set_header(l_http_req, 'Proxy-Connection', 'Keep-Alive');
        if p_http_method != 'GET' then
            utl_http.set_header(l_http_req, 'Content-Length', l_env_lenb);
        end if;
        -- set additional headers if supplied, these are separated by a colon (:) as name/value pairs
        for i in 1.. p_http_headers.count loop
            utl_http.set_header(l_http_req, p_http_headers(i), p_http_hdr_values(i));
        end loop;
    exception when others then
        raise_application_error(-20001,'The URL provided is invalid or you need to set a proxy.');
    end;

    --set headers from g_request_headers
    for i in 1.. g_request_headers.count loop
        utl_http.set_header(l_http_req, g_request_headers(i).name, g_request_headers(i).value);
    end loop;

    --
    l_amount := 8000;
    l_offset := 1;
    if p_http_method != 'GET' then
        if dbms_lob.getlength(l_body) > 0 then
            begin
                loop
                    dbms_lob.read( l_body, l_amount, l_offset, l_buffer );
                    if l_db_charset = 'AL32UTF8' then
                        utl_http.write_text(l_http_req, l_buffer);
                    else
                        utl_http.write_raw(l_http_req,
                                           utl_raw.convert(utl_raw.cast_to_raw(l_buffer),
                                                           'american_america.al32utf8',
                                                           'american_america.' || l_db_charset
                                       )
                        );
                    end if;
                    l_offset := l_offset + l_amount;
                    l_amount := 8000;
                end loop;
            exception
                when no_data_found then
                    null;
            end;
        elsif dbms_lob.getlength(p_body_blob) > 0 then
            begin
                l_amount := 48;
                while (l_offset < l_env_lenb) loop
                    dbms_lob.read(p_body_blob, l_amount, l_offset, l_raw);
                    utl_http.write_raw(l_http_req, l_raw);
                    l_offset := l_offset + l_amount;
                end loop;
            exception
                when no_data_found then
                    null;
            end;
        end if;
    end if;
    --
    begin
        l_http_resp := utl_http.get_response(l_http_req);
    exception when others then
        raise_application_error(-20001,'The URL provided is invalid or you need to set a proxy.');
    end;
    --

    -- set response code, response http header and response cookies global
    g_status_code := l_http_resp.status_code;
    utl_http.get_cookies(g_response_cookies);
    for i in 1..utl_http.get_header_count(l_http_resp) loop
        utl_http.get_header(l_http_resp, i, l_name, l_hdr_value);
        l_hdr.name := l_name;
        l_hdr.value := l_hdr_value;
        l_hdrs(i) := l_hdr;
    end loop;

    g_headers := l_hdrs;

    --
    dbms_lob.createtemporary( l_value, FALSE );
    dbms_lob.open( l_value, dbms_lob.lob_readwrite );

    begin
        loop
            utl_http.read_text(l_http_resp, l_buffer);
            dbms_lob.writeappend( l_value, length(l_buffer), l_buffer );
        end loop;
    exception
        when others then
            if sqlcode <> -29266 then
                raise;
            end if;
    end;
    --
    utl_http.end_response(l_http_resp);

    return l_value;

end make_rest_request;

function parse_xml (
    p_xml               in xmltype,
    p_xpath             in varchar2,
    p_ns                in varchar2 default null ) return varchar2
is
    l_response          varchar2(32767);
begin

    l_response := dbms_xmlgen.convert(p_xml.extract(p_xpath,p_ns).getstringval(),1);

    return l_response;

exception when others then
    if sqlcode = -30625 then -- path not found
        return null;
    end if;
end parse_xml;

function parse_xml_clob (
    p_xml               in xmltype,
    p_xpath             in varchar2,
    p_ns                in varchar2 default null ) return clob
is
    l_response          clob;
begin

    l_response := p_xml.extract(p_xpath,p_ns).getclobval();

    return l_response;

exception when others then
    if sqlcode = -30625 then -- path not found
        return null;
    end if;
end parse_xml_clob;

function parse_response (
    p_collection_name   in varchar2,
    p_xpath             in varchar2,
    p_ns                in varchar2 default null ) return varchar2
is
    l_response          varchar2(32767);
    l_xml               xmltype;
begin

    for c1 in (select clob001
                 from apex_collections
                where collection_name = p_collection_name ) loop
        l_xml := xmltype.createxml(c1.clob001);
        exit;
    end loop;

    l_response := parse_xml(l_xml, p_xpath, p_ns);

    return l_response;

exception when others then
    if sqlcode = -31011 then -- its not xml
        return null;
    end if;
end parse_response;

function parse_response_clob (
    p_collection_name   in varchar2,
    p_xpath             in varchar2,
    p_ns                in varchar2 default null ) return clob
is
    l_response          clob;
    l_xml               xmltype;
begin

    for c1 in (select clob001
                 from apex_collections
                where collection_name = p_collection_name ) loop
        l_xml := xmltype.createxml(c1.clob001);
        exit;
    end loop;

    l_response := parse_xml_clob(l_xml, p_xpath, p_ns);

    return l_response;

exception when others then
    if sqlcode = -31011 then -- its not xml
        return null;
    end if;
end parse_response_clob;

end flex_ws_api;
/
show errors

--

Now, with this API, it is very easy to check a document into a Stellent repository. I simply created a page with one HTML region and two items, P1_FILE (of type File Browse) and P1_RES_MSG, and one submit button. Then I created an after submit PL/SQL process that makes calls to the flex_ws_api package.

The process first queries APEX_APPLICATION_FILES to get the BLOB of the file I just uploaded. It calls flex_ws_api.blob2clobbase64 to encode that blob into a base64 encoded CLOB. That clob is used as a parameter in the SOAP envelope that is built up next in a local CLOB in the process. The process then calls flex_ws_api.make_request with the necessary parameters and specifies a collection to store the response in. Finally flex_ws_api.parse_response is called to get the response code from the Stellent server.

--


declare
 l_filename varchar2(255);
 l_BLOB BLOB;
 l_CLOB CLOB;
 l_envelope CLOB;
 l_response_msg varchar2(32767);
BEGIN
 IF :P1_FILE IS NOT NULL THEN
    SELECT filename, BLOB_CONTENT
      INTO l_filename, l_BLOB
      FROM APEX_APPLICATION_FILES
      WHERE name = :P1_FILE;

    l_CLOB := flex_ws_api.blob2clobbase64(l_BLOB);

   l_envelope := q'!<?xml version='1.0' encoding='UTF-8'?>!';
   l_envelope := l_envelope '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:chec="http://www.stellent.com/CheckIn/">
  <soapenv:Header/>
  <soapenv:Body>
     <chec:CheckInUniversal>
        <chec:dDocName>'||l_filename||'</chec:dDocName>
        <chec:dDocTitle>'||l_filename||'</chec:dDocTitle>
        <chec:dDocType>Document</chec:dDocType>
        <chec:dDocAuthor>GM</chec:dDocAuthor>
        <chec:dSecurityGroup>Public</chec:dSecurityGroup>
        <chec:dDocAccount></chec:dDocAccount>
        <chec:CustomDocMetaData>
           <chec:property>
              <chec:name></chec:name>
              <chec:value></chec:value>
           </chec:property>
        </chec:CustomDocMetaData>
        <chec:primaryFile>
           <chec:fileName>'||l_filename||'</chec:fileName>
           <chec:fileContent>'||l_CLOB||'</chec:fileContent>
        </chec:primaryFile>
        <chec:alternateFile>
           <chec:fileName></chec:fileName>
           <chec:fileContent></chec:fileContent>
        </chec:alternateFile>
        <chec:extraProps>
           <chec:property>
              <chec:name></chec:name>
              <chec:value></chec:value>
           </chec:property>
        </chec:extraProps>
     </chec:CheckInUniversal>
  </soapenv:Body>
</soapenv:Envelope>';

flex_ws_api.make_request(
   p_url               => 'http://127.0.0.1/idc/idcplg',
   p_action            => 'http://www.stellent.com/CheckIn/',
   p_collection_name   => 'STELLENT_CHECKIN',
   p_envelope          => l_envelope,
   p_username          => 'sysadmin',
   p_password          => 'welcome1' );

 l_response_msg := flex_ws_api.parse_response(p_collection_name=>'STELLENT_CHECKIN',p_xpath=>'//idc:CheckInUniversalResponse/idc:CheckInUniversalResult/idc:StatusInfo/idc:statusMessage/text()',p_ns=>'xmlns:idc="http://www.stellent.com/CheckIn/"');

 :P1_RES_MSG := l_response_msg;

 END IF;
END;

--

In the Process Success Message text area I put &P1_RES_MSG. so the process success message will be the response from the Stellent server. When I checkin a new document, I get a response like "Successfully checked in content item 'TEST.DOC'."

The Stellent process is only an example. This API should be flexible enough to call any Web service and store its response into a collection you specify. Let me know if you feel there is missing functionality.

Application Express Web Services Integration Page on OTN

2008-04-01T13:13:00.000-07:00

I have written a number of white papers and sample applications about Web services integration with Application Express and they are now hosted on their very own page on OTN. Check it out here.

My colleague, Sathish Kumar, wrote the YouTube sample application. It was his work with that application which led to the discovery that the Manual Web references feature of Application Express works with XML-RPC style Web services and inspired the white paper with the same title.

Also, while we are talking about Web services and Application Express, check out Tyler's post on integrating Application Express with BiPublisher through the same Manual Web references feature of Application Express. It is a very nice and useful example.

NTLM HTTP Authentication and Application Express

2008-03-17T09:13:00.000-07:00

I think for my first blog post ever, I should start with a light topic, such as NTLM Authentication in Application Express...

Many customers have expressed interest in using NTLM with Application Express. The argument is that they are already using this authentication in their .NET intranet applications and users of those applications do not have to supply their domain credentials again, the application simply knows who they are. I know many customers have deployed Apache and mod_ntlm, and used a custom authentication scheme described in the following paper:

http://www.greenit.li/website/content/OracleApplicationExpressProofOfConceptNTLM.doc

There have been some problems reported with using mod_ntlm such as configuration and users getting prompted for username and password periodically. I decided to do some investigation to see if there have been any Java or .NET code examples of doing NTLM authentication to see if I could rewrite the code in PL/SQL. I found the following JSP:

http://www.rgagnon.com/javadetails/java-0441.html

There is a problem with the JSP implementation when you are using a browser that won't support NTLM. You get prompted for a username and password and the JSP will just accept whatever is typed in. Luckily though, you can detect that the user was prompted by the size of the token. I kept that in mind when trying to reverse engineer into a PL/SQL solution.

Through some brute force debugging and examination the HTTP traffic, I was able to successfully write some PL/SQL that does essentially the same thing as the JSP. I used the code in the mod_ntlm page sentry function from the white paper referenced above as a starting point. Unlike the JSP, this function will set the username to "nobody" if it detects that the browser prompted the user for their credentials instead of just silently negotiating them. You can then write authorization schemes that deny access to the "nobody" user.

First you need to configure your DAD used for Application Express so that mod_plsql can be aware of a CGI environment variable called "Authorization." To do this:

Find the file that contains the DAD description used for Application Express (most likely $OH/Apache/modplsql/conf/dads.conf)
Edit the DAD entry for Application Express adding PlsqlCGIEnvironmentList AUTHORIZATION
Save the file
Stop and start Apache/ Oracle HTTP Server

Now you can access the CGI environment variable "Authorization." Next you compile a function that will be used as a page sentry function for a custom authentication scheme. Compile this function in the same schema as your application.


create or replace function ntlm_page_sentry
return boolean
is
    l_username      varchar2(512);
    l_session_id    number;
    l_raw           raw(1000);
    l_domain        varchar2(128);
    l_user          varchar2(128);
    l_auth          varchar2(512);
    l_decode        varchar2(2000);
    l_off           pls_integer := 0;
    l_length        pls_integer;
    l_offset        pls_integer;
    l_htp_buffer    htp.htbuf_arr;
    l_htp_rows      INTEGER;
    l_url           VARCHAR2(500);
    l_charset       VARCHAR2(128);
begin
    -- check to ensure that we are running as the correct database user.
    if user != 'APEX_PUBLIC_USER' then
        return false;
    end if;
    -- get sessionid.
    l_session_id := wwv_flow_custom_auth_std.get_session_id_from_cookie;
    -- check application session cookie.
    if wwv_flow_custom_auth_std.is_session_valid then
        apex_application.g_instance := l_session_id;
        l_username := wwv_flow_custom_auth_std.get_username;
        wwv_flow_custom_auth.define_user_session(p_user => l_username,
                p_session_id => l_session_id);
        return true;
    else
        -- get username using NTLM
        l_auth := owa_util.get_cgi_env('AUTHORIZATION');
        if l_auth is null then
            owa_util.status_line(nstatus => 401,
                    creason => 'Unauthorized',
                    bclose_header => false);
            htp.p('WWW-Authenticate: NTLM');
            owa_util.mime_header('text/html', false, 'utf-8');
            owa_util.http_header_close;
            wwv_flow.g_unrecoverable_error := TRUE;
            return false;
        end if;
        if substr(l_auth,1,5) = 'NTLM ' then
            l_decode := utl_encode.text_decode(buf => substr(l_auth,6), encoding => UTL_ENCODE.BASE64);
            l_raw := utl_raw.cast_to_raw(l_decode);
            if utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,14,1)) != 130 then
                if utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,9,1)) = 1 then
                    owa_util.mime_header('text/html', false, 'utf-8');
                    owa_util.status_line(nstatus => 401,
                            creason => 'Unauthorized',
                            bclose_header => false);
                    htp.p('WWW-Authenticate: NTLM TlRMTVNTUAACAAAAAAAAACgAAAABggAAAAICAgAAAAAAAAAAAAAAAA==');
                    owa_util.http_header_close;
                    wwv_flow.g_unrecoverable_error := TRUE;
                    return false;
                end if;
                -- Determine DB charset and convert raw to WE8MSWIN1252, thanks to Andrew Barbaccia
                select value into l_charset from nls_database_parameters where parameter='NLS_CHARACTERSET';
                l_length := utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,32,1))*256 + utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,31,1));
                l_offset := utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,34,1))*256 + utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,33,1));
                l_domain := replace(replace(substr(convert(utl_raw.cast_to_varchar2(l_raw),l_charset,'WE8MSWIN1252'),l_offset + 1,l_length),chr(0),null),chr(15),null);
                l_length := utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,40,1))*256 + utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,39,1));
                l_offset := utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,42,1))*256 + utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,41,1));
                l_user := replace(substr(convert(utl_raw.cast_to_varchar2(l_raw),l_charset,'WE8MSWIN1252'),l_offset,l_length),chr(0),null);
                l_username := l_domain'\'l_user;
            else
                l_username := 'nobody';
            end if;
        end if;
        -- application session cookie not valid --> define a new apex session.
        wwv_flow_custom_auth.define_user_session(p_user => l_username,
            p_session_id => wwv_flow_custom_auth.get_next_session_id);
        -- tell apex engine to quit.
        apex_application.g_unrecoverable_error := true;
        if owa_util.get_cgi_env('REQUEST_METHOD') = 'GET'  then
            wwv_flow_custom_auth.remember_deep_link(p_url => 'f?'
            wwv_flow_utilities.url_decode2(owa_util.get_cgi_env('QUERY_STRING')));
        else
        wwv_flow_custom_auth.remember_deep_link(p_url => 'f?p='
          to_char(apex_application.g_flow_id)':'
          to_char(nvl(apex_application.g_flow_step_id, 0))':'
          to_char(apex_application.g_instance));
      end if;
      -- register the session in apex sessions table, set cookie, redirect back.
      wwv_flow_custom_auth_std.post_login(p_uname => l_username,
        p_session_id => nv('APP_SESSION'), p_flow_page => apex_application.g_flow_id
        ':'nvl(apex_application.g_flow_step_id, 0), p_preserve_case => true);
        -- get HTP output wwv_flow_custom_auth_std.post_login has written,
        -- it contains the session cookie we need.
        -- Thanks to Patrick Wolf for the following code
        l_htp_rows := 15; /* where and how to get an actual value for irows???? */
        htp.get_page
          ( thepage => l_htp_buffer
          , irows   => l_htp_rows
          );
        -- reset the HTP buffer so that we can write our own header, ...
        htp.init;
        -- See http://www.nabble.com/Empty-POST-requests-on-IE-td15332680.html
        -- We have to trick IE that he thinks the authentication fails, otherwise
        -- he doesn't send any data when issueing a POST because he wants to
        -- do the NTLM stuff again
        owa_util.status_line
          ( nstatus => 401,
            creason => 'Unauthorized',
            bclose_header => FALSE
          );
        -- write the session cookie into our output
        FOR ii IN 1 .. l_htp_rows
        LOOP
            IF l_htp_buffer(ii) LIKE 'Set-Cookie:%'
            THEN
                htp.p(rtrim(l_htp_buffer(ii), CHR(10)));
            END IF;
        END LOOP;
        --
        l_url := 'f?p='
                 apex_application.g_flow_id':'
                 nvl(apex_application.g_flow_step_id, 0)':'
                 apex_application.g_instance;
        --
        IF WWV_Flow.get_browser_version = 'NSCP'
        THEN
            -- Firefox: redirect can be set with a HTTP header attribute
            htp.p('Location: 'l_url);
            owa_util.http_header_close;
        ELSE
            -- For IE: The javascript is required so that we are redirected to the page as
            -- the wwv_flow_custom_auth_std.post_login would normally do with the
            -- HTTP 302 redirect
            owa_util.http_header_close;
            htp.p('<html><head>');
            htp.p('<script type="text/javascript">');
            htp.p('  location.href="'l_url'";');
            htp.p('</script>');
            htp.p('<noscript>');
            htp.p('<meta http-equiv="Refresh" content="0; URL="'l_url'">');
            htp.p('</noscript>');
            htp.p('</head>');
            htp.p('<body>');
            htp.p('You were logged in successfully. Click <a href="'l_url'">here</a> to continue.');
            htp.p('</body>');
            htp.p('</html>');
        END IF;
      return false;
    end if;
end ntlm_page_sentry;
/

The last step is to create a custom authentication scheme that uses the above function as the page sentry function. To create a custom authentication scheme:

Click Shared Components from the Application Builder home page
Click Authentication Schemes under Security
Click Create >
Choose From scratch and click Next >
Enter NTLM in the Name field and click Next >
Enter return ntlm_page_sentry in the Page Sentry Function text area and click Next >
Click Next > until the Confirm step
Click Create Scheme
Click Change Current
Choose NTLM and Click Next >
Click Make Current

Run the application and you should see your username in the format of DOMAIN\username provided you are using a browser that is configured to support NTLM negotiation.

Now, a couple of notes about browser support and NTLM. (Of course if you are already using NTLM for authentication with other applications, you are well aware of these notes). In order for Internet Explorer to automatically negotiate NTLM, the security settings of the browser must be set to Medium-low or Low. By default, IE is set to Medium-low for local intranet sites, and this authentication really only makes sense for local intranet sites.

Firefox will work with NTLM, but each browser has to be configured to trust each server where you want to employ NTLM. To configure Firefox to negotiate NTLM with a specific server:

Type about:config in the address bar
Type ntlm in the filter text box
Double click the preference network.automatic-ntlm-auth.trusted-uri's and enter a comma separated list of trusted servers on your network

Vista has local security policies that, by default, do not allow browser negotiation of NTLM authentication. (Again, you already know this if you have Vista and NTLM auth employed with applications in your environment). The link that follows contains information on how to change this.

http://www.jimmah.com/vista/Networking/ntlm.aspx

If you are now thinking to yourself, "wow, I can't believe I would have to change this setting on every Vista client in my organization," then you should familiarize yourself with the notion of group policy and the following document:

http://msdn2.microsoft.com/en-us/library/ms814176.aspx

Again, NTLM authentication is really only relevant for clients that are part of an Active Directory domain, and therefore, group policy would apply.

Finally, it is possible (with any application that authenticates with NTLM, not just this example) for someone to sniff traffic on your network, see the NTLM authorization token for a specific user, and then use that token to spoof the identity of someone and use your application. You should:

Find out who these people are and fire them or get them fired
Use SSL

Update 3/19/2008: I should mention that this solution only works with Apache/ Oracle HTTP Server and is not supported by the XDB HTTP Server with the embedded PL/SQL gateway (EPG), yet...

Update 4/17/2008: For more information on why this solution will not work with the embedded PL/SQL gateway, see the section titled "Configuring Static Authentication with DBMS_EPG" in the following document:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_web.htm#BGBCFIIB

"The database rejects access if the browser user attempts to connect explicitly with the HTTP Authorization header."

Update 5/8/2008: Patrick Wolf discovered an issue described at the following post:

http://forums.oracle.com/forums/thread.jspa?messageID=2511974&#2511974

He also came up with a very elegant solution described in the same post. I guess I should have tested this method with a more complex application (like one that posts a page). ;) Anyway, thanks to Patrick and I have included his fix in an updated version of the function.

Update 5/14/2008: John Scott may have discovered a way to use this authentication mechanism with the EPG, using Apache to proxy requests to EPG and rewriting the Authorization header:

http://forums.oracle.com/forums/thread.jspa?threadID=652805&start=15&tstart=0

Update 8/20/2008: It seems that checking the length of the NTLM token has proven unreliable to detect the case where the browser prompted for a username and password. I have found that when the browser prompts the user, the token "NTLM TlRMTVNTUAABAAAAB4IIAAAAAAAAAAAAAAAAAAAAAAA=" is consistently passed by the client. I have altered the PL/SQL code to test for this token instead of the token length.

Update 11/17/2008: I have updated the function to include two changes. The first is a suggestion from Andrew Barbaccia about character set conversion. See the comments below and the referenced forum discussion.

The second modification is how we detect when the browser prompted for username and password. I noticed that recenlty, the token changed in this case when using IE7, although the token was the same in FF. Ilmar in his comments below has come accross the same issue. I did a little more investigation and have found that the binary integer equivalent of the 14th byte of the NTLM token is equal to 130 when the browser is prompted. I will go with that for now.

Update 07/13/2009: It seems that Microsoft published the following which will make the ntlm_page_sentry function no longer work:

"Cumulative Security Update for Internet Explorer 7 for Windows Vista (KB963027)Security issues have been identified that could allow an attacker to compromise a system that is running Microsoft Internet Explorer and gain control over it. You can help protect your system by installing this update from Microsoft. After you install this item, you may have to restart your computer. This update is provided to you and licensed under the Windows Vista License Terms.

More information: http://go.microsoft.com/fwlink/?LinkId=146659

Help and Support: http://support.microsoft.com/"

One workaround is to de-install this update. I don't recommend that option. Another workaround listed in the comments below is to comment out the following check:

if utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,14,1) != 130

I don't recommend that option either. The purpose of the check above is to detect the case where the browser prompted for Username and Password. This will happen if someone visits your site using the ntlm_page_sentry function and your site is not listed as in the local intranet. If the above is commented out, users that visit your application where the browser thinks it is not the local intranet will be able to type in any username they want and be that user.I have spent some time trying to figure out a workaround but I don't have one. If any of you have any ideas, please post a comment.

Update 07/14/2009: A registry hack was provided at the following forum post which can be applied via group policy:

http://forums.oracle.com/forums/thread.jspa?forumID=137&threadID=921524

Update 08/14/2009: I also want to point out some text from the whitepaper based on this article to make it clear what this function does (decodes an NTLM token) and does not do (negotiate anything with any domain controller).

"This paper presents a pure PL/SQL code solution for decoding an NTLM token and using that decoded value as the authenticated user in APEX applications. The function will set the username to "nobody" if it detects that the browser prompted the user for their credentials instead of just silently negotiating them. You can then write authorization schemes that deny access to the "nobody" user. Note that unlike the mod_ntlm Apache module, this solution does not pass along credentials to a domain controller for authentication. This solution requests that the browser present an NTLM authentication token and decodes the username and domain from that token."