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:

1. AWSACCESSKEY
   
2. ASSOCIATETAG
   
3. 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:

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

declare
  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);
begin

  --compute the signature
  l_signature := amazon_signature('GET'||chr(10)||'ecs.amazonaws.com'||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 => 'http://ecs.amazonaws.com/onca/xml',
      p_http_method => 'GET',
      p_parm_name => l_parm_name_tab,
      p_parm_value => l_parm_val_tab );

  --populate a collection with the response
  apex_collection.create_or_truncate_collection('P1_ITEMSEARCH_RESPONSE');
  apex_collection.add_member(
      p_collection_name   => 'P1_ITEMSEARCH_RESPONSE',
      p_clob001           => l_response );

  --populate a collection for any response http headers returned
  apex_collection.create_or_truncate_collection('P1_RESP_HEADERS');

  for i in 1.. flex_ws_api.g_headers.count loop
    apex_collection.add_member(
      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;

end;

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="http://webservices.amazon.com/AWSECommerceService/2009-03-31"') ASIN
   , extractValue(value(t),'/*/DetailPageURL','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"') DetailPageURL
   , extractValue(value(t),'/*/ItemAttributes/Title','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"') Title
   , '<img src="'||nvl(extractValue(value(t),'/*/SmallImage/URL','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"'),'http://ec1.images-amazon.com/images/G/01/x-locale/detail/thumb-no-image._V47060337_.gif')||'" width="'||nvl(extractValue(value(t),'/*/SmallImage/Width','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"'),'50')||'" height="'||nvl(extractValue(value(t),'/*/SmallImage/Height','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"'),'60')||'" />' "Image"
   , extractValue(value(t),'/*/ItemLinks/ItemLink[4]/URL','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"') Link
from wwv_flow_collections c,
    table(xmlsequence(extract(xmltype.createxml(c.clob001),'//Item','xmlns="http://webservices.amazon.com/AWSECommerceService/2009-03-31"'))) 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!