More Changes to flex_ws_api

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

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:

1. Click the Report link next to the Folders region


2. Uncheck the Show check box corresponding to the id column


3. Click the edit icon next to the name column


4. Scroll down to the Column Link region


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


6. Choose Page in this Application from the Target list


7. Enter 2 in the Page field


8. Choose P2_FOLDER_ID for Item 1 from the pop-up list


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


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

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

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

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

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

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

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:

1. Find the file that contains the DAD description used for Application Express (most likely $OH/Apache/modplsql/conf/dads.conf)
2. Edit the DAD entry for Application Express adding PlsqlCGIEnvironmentList AUTHORIZATION
3. Save the file
4. 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:

1. Click Shared Components from the Application Builder home page
2. Click Authentication Schemes under Security
3. Click Create >
4. Choose From scratch and click Next >
5. Enter NTLM in the Name field and click Next >
6. Enter return ntlm_page_sentry in the Page Sentry Function text area and click Next >
7. Click Next > until the Confirm step
8. Click Create Scheme
9. Click Change Current
10. Choose NTLM and Click Next >
11. 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:

1. Type about:config in the address bar
2. Type ntlm in the filter text box
3. 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:

1. Find out who these people are and fire them or get them fired
2. 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."