RMOUG, Kaleidoscope 2009, Slight Change to flex_ws_api

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

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.