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.