Monday, November 10, 2008

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.

12 comments:

Tony said...

Just wanted to take a minute to tell you how much I appreciate the effort and time you have put into this package. I, for one, am appreciative of your efforts.

I'll be testing this out myself in the next week or so.

One thing I would also appreciate is a resource or tip on how to construct the queries against the collection - based on the returned SOAP response, what is the best method to determine how to use the extract value to generate your columns.

Jason Straub said...

Tony:

I usually use (and did so in this case) the report on collection containing web service result wizard. In this case, since it is a purely PL/SQL implementation, I created a dummy manual web reference and populated the collection name with GETWORKSPACES_RESPONSE. Choose Manually Created as the Web Reference Type when running the report on on web service result wizard. Most likely you can leave the defaults of Document for SOAP Style and Literal for Message format. Then you need to supply the XPath to the node that you want to report on. You can get the XML response envelope by clicking the session link in the developer toolbar, (after running a page that invokes the service), choose Collections, and then view the source of that page. The XML respone will be contained in that source.

I then take that XML response and paste it into an application like XML Spy, which allows me to look at the data in a grid view, and right click on a cell to get the XPath. So in this example, I determined the XPath to the node I was interested in as /GetWorkspacesResponse/return/libraryIDList. The message namespace can also be determined from the XML response, http://oracle.bee.platform.webservice/. Then you just supply the parameter names under the reporting node, in this case they were id, name, and description. The wizard will create the proper SQL statement for you.

For the report on page 2, I simply copied the region from page 1 and changed the XPath to the node to report on to //GetDocumentsInFolderResponse/return and changed the parameter references to /contentID/id, /content/name, /mimeMultipartType. I determined this from inspecting the XML response from the service.

Hope that helps.

Regards,

Jason

Tony said...

I am still struggling with the parsing of the messages.

I have a SOAP response that looks like this (it is from a microsoft WCF service)

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Header>
<ActivityId CorrelationId="12855f8e-d2be-40c7-81d8-fafa3cf9a779" xmlns="http://schemas.microsoft.com/2004/09/ServiceModel/Diagnostics">d24174d3-e095-460a-b9f8-2bae66efe813</ActivityId>
</s:Header>
<s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<FetchResponse xmlns="urn: WA.Ecy.ADS.FacilitySite.Services">
<FacilitySiteId>99997167</FacilitySiteId>
</FetchResponse>
</s:Body>
</s:Envelope>

I am storing these results in a local variable l_response xmltype

If I use the parse_xml to get the FacilitySiteId is get an error:

l_id := parse_xml (l_response,'/FetchResponse/FacilitySiteId','xmlns="http://microsoft.com/wsdl/type/"');


ORA-30625 method dispatch on null self argument is disallowed
ORA-6512 FLEX_WS_API line 349

I even tried storing the results in a table and performing the query
select extractvalue(x,'/FetchResponse/FacilitySiteId','xmlns="http://microsoft.com/wsdl/types/"')
from ws_results


This just returned a null.

I don't know if the problem is that this isn't an expected xml document (i.e. doesn't start with
?xml version="1.0" encoding="UTF-8" ?)

Jason Straub said...

Tony:

I am trying to figure out the proper XPath expression now. Hopefully I will have it soon.

Jason

Jason Straub said...

Tony:

Regarding the namespace of the FetchResponse node, xmlns="urn: WA.Ecy.ADS.FacilitySite.Services" is there really a space between urn: and WA.Ecy.ADS.FacilitySite.Services? Is it possible to change the namespace of the service so that it does not have a space in it? Finally, what version of the database are you on?

Jason

Tony said...

Jason -

There really is a space in the URN. I don't have any control over this service, and so changing it is not a possibility.

As for the database, I am running 11.1.0.6 on Windows.

Jason Straub said...

Tony:

It seems that XML DB expects that any whitespace in a namespace to be escaped with &37;20. It is possible that the response you are getting back is escaped with &37;20, so <FetchResponse xmlns="urn: WA.Ecy.ADS.FacilitySite.Services">
might actually be <FetchResponse xmlns="urn:&37;20WA.Ecy.ADS.FacilitySite.Services">. If that is the case, then the following code snippet shows how you could get the facility id:

set serveroutput on
declare
l_xml xmltype;
l_facilityid number;
begin
l_xml := xmltype.createxml('<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Header>
<ActivityId CorrelationId="12855f8e-d2be-40c7-81d8-fafa3cf9a779" xmlns="http://schemas.microsoft.com/2004/09/ServiceModel/Diagnostics">d24174d3-e095-460a-b9f8-2bae66efe813</ActivityId>
</s:Header>
<s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<FetchResponse xmlns="urn:&37;20WA.Ecy.ADS.FacilitySite.Services">
<FacilitySiteId>99997167</FacilitySiteId>
</FetchResponse>
</s:Body>
</s:Envelope>');

dbms_output.put_line('facility id: '||flex_ws_api.parse_xml(l_xml,'//FetchResponse/FacilitySiteId/text()','xmlns="urn:&37;20WA.Ecy.ADS.FacilitySite.Services"'));

end;
/

Is there a way for you to view the raw response (in SOAPUI or some other tool) and confirm whether that white space in the namespace of FetchResponse is escaped?

Jason

Jason Straub said...

Tony:

I goofed up on my escaping in the example above, wherever you see &37;, that should be a %.

Tony said...

Given the number of tags and bloggers complaints, I have forwarded the raw response to your yahoo account.

Véronique said...

I received an error while compiling the package:

ERROR line 103, col 27, ending_line 103, ending_col 27, Found '=', Expecting: ( * + - AT CURSOR DAY identifier MOD MODEL MULTISET NOT NULL REM string THE WHEN YEAR -or- : @ CASE CONNECT_BY_ROOT decimal number EXISTS FALSE float integer NEW PRIOR SQL TRUE -or- % . [ -or- (+) -or- ** -or- / -or- || -or- AND -or- OR -or- , -or- )

It doesn't seem to like the line:
while (pos < parameter="'NLS_CHARACTERSET';" l_db_charset =" 'AL32UTF8'" proxy =""> apex_application.g_proxy_server);

Is it because i'm in 10g ?

Jason Straub said...

Véronique:

Sometimes when I make changes at the blog site with the code formatter, some of the characters get corrupted. I have created a project at:

https://flex-ws-api.samplecode.oracle.com/

You only need an OTN account to sign-in. Can you get the package from there and try to compile it?

Regards,

Jason

Viktor said...

Hello Jason,

My problem is as same as yours. I would like to use beehive objects of apex. Your sample code is very good, but there is a big problem.
The Oracle does not support the soap webservice only the RESTful webservice has been supported since beehive 2. I tried your flex_ws_api, but it isn't working for me.
My code is: (i created a test user, user name: testuser1 and password: asdfg12 , it is allowed to try it)
I get allways http 400 error. What is the problem?

declare
l_signature varchar2(4000);
l_response clob;
l_c clob;
l_parm_name_tab wwv_flow_global.vc_arr2;
l_parm_val_tab wwv_flow_global.vc_arr2;
i number;
darab varchar2(4000);
c number;
l_user varchar2(4000);
l_pwd varchar2(4000);
l_url varchar2(4000);
begin
l_user := 'testuser1';
l_pwd := 'asdfg12';
l_url := 'http://beehive.isolutions.hu:7777/';

l_parm_name_tab := apex_util.string_to_table('Accept:Content%2DType:Authorization');
l_parm_val_tab := apex_util.string_to_table('application%2Fjson:application%2Fjson:Basic');
l_response := flex_ws_api.make_rest_request(
p_url => l_url||'comb/v1/d/session/login',
p_http_method => 'POST',
p_username => l_user,
p_password => l_pwd,
p_parm_name => l_parm_name_tab,
p_parm_value => l_parm_val_tab );
l_c := l_response;

htp.p('++++++++++++++++++++++++++++++++++++++++++++++++');
for i in 0..(floor(DBMS_LOB.GETLENGTH(l_c)/2000)+1) loop
darab:=dbms_lob.substr(l_c,2000,1+(i-1)*2000);
htp.p(darab);
end loop;
htp.p('++++++++++++++++++++++++++++++++++++++++++++++++');

l_parm_name_tab := apex_util.string_to_table('Accept');
l_parm_val_tab := apex_util.string_to_table('application%2Fxml');
l_response := flex_ws_api.make_rest_request(
p_url => l_url||'comb/v1/d/session/anticsrf',
p_http_method => 'GET',
p_parm_name => l_parm_name_tab,
p_parm_value => l_parm_val_tab );
l_c := l_response;

htp.p('++++++++++++++++++++++++++++++++++++++++++++++++');
for i in 0..(floor(DBMS_LOB.GETLENGTH(l_c)/2000)+1) loop
darab:=dbms_lob.substr(l_c,2000,1+(i-1)*2000);
htp.p(darab);
end loop;
htp.p('++++++++++++++++++++++++++++++++++++++++++++++++');
end;


Regard Viktor, Juhász (viktor.juhasz@isolutions.hu)