(The views expressed here are my own and do not necessarily reflect the views of Oracle.)

Friday, October 23, 2009

REST Now Supported in flex_ws_api and Other Good Stuff

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

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

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

AWSACCESSKEY

ASSOCIATETAG

AWSSECRETKEY

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

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

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

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

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

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

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

Uncheck Show for the column DETAILPAGEURL

Enter the following HTML Expression for TITLE column: <a href="#DETAILPAGEURL#">#TITLE#</a>

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

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

11 comments:

Jason,I just tried this, and it works like a charm!!! Thank you so much, once I've incorporated it into my application I will show you what I've done! You have saved me a ton of time, this is fantastic. By the way, there is a 'missing right parenthesis' somewhere in the select for the Image (it's the only thing I did not get to work). I'll try and figure it out! You have made my day. :-)

Me again... :-)I might be pushing it by asking for help again, but here I go... I wanted to include 'Author' in the results from Amazon, as I need to store this in my DB and I don't necessarily need to go the ItemLookup route. So I thought, easy, I'll just add /*/ItemAttributes/Author. But in some cases I was getting an ORA-19025: EXTRACTVALUE returns value of only one node error, which I believe is due to the fact that some books might have multiple authors? I know this is going beyond your ws_flex_api and all the other great stuff you've done, but do you know how I might be able to pull out either simply the first author returned or a concatenation of all in the Results list? If not, could you suggest a forum where I might post this appopriately? (not sure it is necessarily an APEX issue...). Thanks so much! By the way, my application (which I'm testing with my kids) is at www.reading-rewards.com, for your info. I can send you a test logon if you wanted to take a look... :-)

Jason - As always, very nice quality work. This is proving to be one of the best sites on the intraweb. The Flex_WS_API has proven nearly invaluable to me in my recent application. Thanks again for all of your hard work and your willingness to share.

I'm using this package but am running into the 64K limit. Is there a workaround for this problem? I've looked at various forums for a solution and one post suggested to return a clob from XML that was inserted into a table. I've tried this by creating a table via "create table myXML (col1 XMLType)" and in the exception handler code I'm still running into a 31167 error. I'm not sure if the code is correct and I think the return statement is causing another 31167 error.