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:


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:

  1. Name: P1_KEYWORDS, Display As: Text Field
  2. Name: P1_SEARCHINDEX, Display As: Select List, List of Values – Display Extra Values: No, List of values definition: STATIC2:All;All,Apparel;Apparel,Automotive;Automotive,Baby;Baby,Beauty;Beauty,Books;Books,Classical;Classical,DigitalMusic;DigitalMusic,DVD;DVD,Electronics;Electronics,GourmetFood;GourmetFood,HealthPersonalCare;HealthPersonalCare,HomeGarden;HomeGarden,Industrial;Industrial,Jewelry;Jewelry,KindleStore;KindleStore,Kitchen;Kitchen,Magazines;Magazines,Merchants;Merchants,Miscellaneous;Miscellaneous,MP3Downloads;MP3Downloads,Music;Music,MusicalInstruments;MusicalInstruments,MusicTracks;MusicTracks,OfficeProducts;OfficeProducts,OutdoorLiving;OutdoorLiving,PCHardware;PCHardware,PetSupplies;PetSupplies,Photo;Photo,Shoes;Shoes,SilverMerchants;SilverMerchants,Software;Software,SportingGoods;SportingGoods,Tools;Tools,Toys;Toys,UnboxVideo;UnboxVideo,VHS;VHS,Video;Video,VideoGames;VideoGames,Watches;Watches,Wireless;Wireless,WirelessAccessories;WirelessAccessories
  3. Name: P1_TIMESTAMP, Display As: Hidden and Protected

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:

  1. Item Name: P1_TIMESTAMP
  2. Type: PL/SQL Function Body
  3. Computation Point: After Submit
  4. Computation: return to_char(sysdate + 8/24,'YYYY-MM-DD')||'T'||to_char(sysdate,'hh24:mi:ss')||'-08:00';

Now you create the process on that page that uses the flex_ws_api to call the Amazon Product Advertising API REST Web service. Create a process on the page with the following attributes:

  1. Name: Call Amazon Product Search API
  2. Type: PL/SQL anonymous block
  3. Process Point: On Submit – After Computations and Validations
  4. Source: <see code listing 1>
  5. Conditional Processing, When Button Pressed: SUBMIT

Code Listing 1, Call Amazon Product Search API Process

l_signature varchar2(4000);
l_response clob;
l_parm_name_tab wwv_flow_global.vc_arr2;
l_parm_val_tab wwv_flow_global.vc_arr2;
i number;
secure varchar2(1);

--compute the signature
l_signature := amazon_signature('GET'||chr(10)||''||chr(10)||'/onca/xml'||chr(10)||'AWSAccessKeyId='||:AWSACCESSKEY||'&AssociateTag='||:ASSOCIATETAG||'&Keywords='||replace(:P1_KEYWORDS,' ','%20')||'&Operation=ItemSearch&ResponseGroup=ItemAttributes%2CImages&SearchIndex='||:P1_SEARCHINDEX||'&Service=AWSECommerceService&Timestamp='||apex_util.url_encode(:P1_TIMESTAMP)||'&Version=2009-03-31',:AWSSECRETKEY);

--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;

--make the REST request
l_response := flex_ws_api.make_rest_request(
p_url => '',
p_http_method => 'GET',
p_parm_name => l_parm_name_tab,
p_parm_value => l_parm_val_tab );

--populate a collection with the response
p_collection_name => 'P1_ITEMSEARCH_RESPONSE',
p_clob001 => l_response );

--populate a collection for any response http headers returned

for i in 1.. flex_ws_api.g_headers.count loop
p_collection_name => 'P1_RESP_HEADERS',
p_c001 => flex_ws_api.g_headers(i).name,
p_c002 => flex_ws_api.g_headers(i).value,
p_c003 => flex_ws_api.g_status_code);
end loop;


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:

  1. Title: Results
  2. Type: SQL Query
  3. Source: <see code listing 2>
  4. Conditional Display, Condition Type: PL/SQL Expression
  5. Conditional Display, Expression 1: apex_collection.collection_exists('P1_ITEMSEARCH_RESPONSE')

Code Listing 2, Results SQL Query

select extractValue(value(t),'/*/ASIN','xmlns=""') ASIN
, extractValue(value(t),'/*/DetailPageURL','xmlns=""') DetailPageURL
, extractValue(value(t),'/*/ItemAttributes/Title','xmlns=""') Title
, '<img src="'||nvl(extractValue(value(t),'/*/SmallImage/URL','xmlns=""'),'')||'" width="'||nvl(extractValue(value(t),'/*/SmallImage/Width','xmlns=""'),'50')||'" height="'||nvl(extractValue(value(t),'/*/SmallImage/Height','xmlns=""'),'60')||'" />' "Image"
, extractValue(value(t),'/*/ItemLinks/ItemLink[4]/URL','xmlns=""') Link
from wwv_flow_collections c,
table(xmlsequence(extract(xmltype.createxml(c.clob001),'//Item','xmlns=""'))) t
where c.collection_name = 'P1_ITEMSEARCH_RESPONSE'

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.

  1. Uncheck Show for the column DETAILPAGEURL
  2. Enter the following HTML Expression for TITLE column: <a href="#DETAILPAGEURL#">#TITLE#</a>
  3. 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:

  1. Title: Headers
  2. Type: SQL Query
  3. Source: <see code listing 3>
  4. Conditional Display, Condition Type: PL/SQL Expression
  5. Conditional Display, Expression 1: apex_collection.collection_exists('P1_RESP_HEADERS')

Code Listing 3, Header SQL Query

 select c001 name, c002 value, c003 status_code
from apex_collections
where collection_name = 'P1_RESP_HEADERS'

Now run the page, enter a search term, choose a category and click Submit. You should see a page similar to the one below. That's all there is to it!


Unknown said...

Awesome work Jason, this additional REST support has just opened up some amazing doors for some mashlets that I'm keen to develop!

Michelle Skamene said...

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. :-)

Jason Straub said...


I have fixed the "Image" column. I needed to escape the HTML before it would display properly wihtin Blogger.

I am looking forward to seeing the application you have built. I am glad you found this post useful.



Michelle Skamene said...

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, for your info. I can send you a test logon if you wanted to take a look... :-)

Michelle Skamene said...

Jason, I just figured it out, I am sorry. Did not know I could add an index to the extractvalue function... Got it!

Jason Straub said...


Try adding the following:

, extractValue(value(t),'/*/ItemAttributes/Author[1]','xmlns=""') "Author"

That will get the first author. You can also get all the authors like this:

, extra(value(t),'/*/ItemAttributes/Author','xmlns=""').getstringval() "Author"



Tony said...

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.

Bhavin Adhvaryu said...

Hi Jason,

You have done excellent work. This post is very useful.

Further to this, have you done any APEX integration with Google Docs? It would be great if you can blog on it.

London, UK

Jason Straub said...


I have not worked with Google Docs but I am confident the flex_ws_api would work with that API. I agree it would make a good blog post. I will try to carve out some time. Stay tuned...



Unknown said...

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.

The code in the exception handler is:

function make_request(
l_myclob clob;

exception when others then
if sqlcode = -31167 then --64K limit reached, so try workaround
insert into myXML values (xmlparse(DOCUMENT l_clob WELLFORMED));

select x.col1.getClobVal() into l_myclob from hradmin.myXML x ;

return xmltype(l_myclob);

end if;

Jason Straub said...


Unfortunately I do not have a workaround for the 64K limit of an XML Node in XDB, except to upgrade your database to 11g.