Showcase application

We plan to issue before the end of 2007 a showcase Oracle Apex application focusing on searching through the Office of Foreign Assets Control (OFAC) Specially Designated Nationals List (SDN) (and maybe others, per customer request).

If you are interested in receiving a note when it is available, please send us an email: info [at] ofac [dot] info

Best,
Peter

Anton Nielsen: Setting Up SSL for Application Express : Oracle, 10g, 10gAS, Application Express (Apex), C2 Consulting

Anton Nielsen: Setting Up SSL for Application Express : Oracle, 10g, 10gAS, Application Express (Apex), C2 Consulting

Consuming REST services within Oracle Apex

I decided to open my own technology blog on Oracle Apex and other cool stuff.

This post is my first showup into the bloggers community. Hope you find something for youself here :)

My first post is related to consuming webservices inside Oracle Apex applications.

I want to consume custom (non-SOAP) webservice inside Oracle Apex aplication. The webservice is called by submitting xml request via HTTP/S to a given URL and getting back a xml responce. After some considerations, I decided to enhance my service and make it available via REST protocol as well. The only change needed was to add some functionality to the server-side code to parse the URI formatted as REST request.

Inspired by Patrick Wolf's post on Integrating Yahoo Pipes into APEX
I decided to use the Oracle build-in HTTPURITYPE and the getXML function in order to get the XML output into an Oracle XMLTYPE. This suffices to construct a working Oracle Apex client for REST services.

Example:

I will use as basis for my example the following example from Caucho Resin website on Flickr REST

There is a method, flickr.people.findByUsername, that takes two arguments, an api_key and a username. The api_key allows flickr^TM to track and manage usage while the username is used to find a user's id. The method name and these two arguments are used to construct a URL to perform the REST operation:
http://api.flickr.com/services/rest/ ?method=flickr.people.findByUsername &api_key=XXX&username=foo

The data returned by the service uses a simple XML format. In this case, the service might return the following:

<?xml version="1.0" encoding="utf-8" ?>
<rsp stat="ok">
  <user nsid="12345678901@N01">
    <username>foo</username>
  </user>
</rsp>

Now, lets create a SQL based report in Oracle Apex with the following SQL statement:

SELECT EXTRACTVALUE(VALUE(USERNAME), '/rsp/user/username')  AS USER_NAME
, EXTRACTVALUE(VALUE(ITEM), '/rsp/user') AS USER_NSID
) AS PUBLISH_DATE
FROM TABLE
( XMLSEQUENCE
    ( EXTRACT
        (  HTTPURITYPE('http://api.flickr.com/services/rest/
 ?method=flickr.people.findByUsername &api_key=XXX&username=foo').getXML()
             , '/'
        )
                 )
) REST_RESPONCE
ORDER BY 1 DESC NULLS LAST

Then run the report and you will get the user name and the user nsid for the above request! Mission accomplished.

Stay tuned for more.