Application Express 4.0 EA Running on Amazon EC2

The Application Express 4.0 evaluation instance is running on an Amazon EC2 instance. You can access it at http://tryapexnow.com. You may also be interested in checking out the APEX 4.0 Web Services Evaluation Guide here.

flex_ws_api now on samplecode.oracle.com

Tyler recently made me aware of http://samplecode.oracle.com/. It is a great place to share code projects, allow others to contribute and comment through discussion forums. I have set up a project for the flex_ws_api and all further development will take place there. I have created discussions for general questions, bug reports, and enhancement requests. Feel free to participate there. All you need is an OTN account to login.

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!

HMAC_SHA256 in PL/SQL

I have been working on an Application Express 4.0 feature to support consuming REST Web services. REST Web services use a simpler architecture than the popular SOAP style Web services. Instead of posting some big XML document wrapped in a SOAP Envelope, REST requests are typically done by passing name/value pairs in the query string of a URL.

To prove that the support for REST I am building in Application Express is useful, I have been working with popular public REST Web services offered by Yahoo, Google and Amazon. I built a similar application to the Amazon Store sample application using the REST APIs. Everything was going great until I received the following email from Amazon.

"Dear Product Advertising API Developer,

We wanted to remind you that all Product Advertising API developers will be required to authenticate all calls to the Product Advertising API by August 15, 2009. We noticed that requests with your AWS Access Key ID are not being signed and, while you have more than 60 days until the date on which authentication is required, we are, as a courtesy, sending you this email to remind you of the new authentication requirement. Please remember that calls to the Product Advertising API that are not signed will not be processed after August 15, 2009."

What do they mean I need to sign my requests? Luckily they provided some links for developer resources on how to construct signed requests. In a nutshell, you must byte order all of your parameters, add a Timestamp parameter and then you create a base64-encoded HMAC_SHA256 signature using your AWS secret key. Should not be a problem. I recently added support for parameters in REST requests to be populated by the result of a function and I am somewhat familiar with the DBMS_CRYPTO package. Unfortunately I quickly found out that DBMS_CYRPTO only supports HMAC_SHA1 and I not so quickly found out that HMAC_SHA1 is not the same as HMAC_SHA256. Am I stuck?

I binged (Google gets enough love and Microsoft can use the help after their most recent earnings results) for HMAC_SHA256 and PL/SQL and found this forum post. This guy wanted to do exactly what I need to do. Someone suggested that he create a Java stored procedure and then he responded with some Java code example with and hmacSHA256 function. Unfortunately no-one could give him an example of exactly how to create that Java stored procedure and then use it in PL/SQL. That's when I remembered that my good friend and colleague Joel Kallman wrote about creating a Java stored procedure to create a zip file of BLOB's.

Well, surely I can follow Joel's example as a cookbook and do the same thing with this hmacSHA256 function, right? The answer is yes, after spending more time than I probably should have (don't tell Joel if you see him), but hey, I had to prove that this REST stuff would actually be useful to customers. If they can't sign their requests, they won't be able to use Application Express to build cool applications that interact with Amazon Web Services. (At least that is my attempt at invoking what another colleague calls The Oracle Justification Server.)

First, start by compiling the following Java source.

Code listing 1, java source hmacSHA256

create or replace and compile java source named hmacSHA256 as

import java.io.*;
import java.net.*;
import java.security.*;
import java.util.*;

public class hmacSHA256 {
    public static String encrypt(
                        String message,
                        String keyStr) {

            //get the bytes of the keyStr
            byte[] key = keyStr.getBytes();
            // Start by getting an object to generate SHA-256 hashes with.
            MessageDigest sha256 = null;
            try {
                sha256 = MessageDigest.getInstance("SHA-256");
            } catch (NoSuchAlgorithmException e) {
                throw new java.lang.AssertionError(".hmacSHA256(): SHA-256 algorithm not found!");
            }
            // Hash the key if necessary to make it fit in a block (see RFC 2104).
            if (key.length > 64) {
               sha256.update(key);
                key = sha256.digest();
                sha256.reset();
            }

            // Pad the key bytes to a block (see RFC 2104).
            byte block[] = new byte[64];
            for (int i = 0; i < key.length; ++i) block[i] = key[i];
            for (int i = key.length; i < block.length; ++i) block[i] = 0;

            // Calculate the inner hash, defined in RFC 2104 as
            // SHA-256(KEY ^ IPAD + MESSAGE)), where IPAD is 64 bytes of 0x36.
            for (int i = 0; i < 64; ++i) block[i] ^= 0x36;
            sha256.update(block);
            try {
                sha256.update(message.getBytes("UTF-8"));
            } catch (UnsupportedEncodingException e) {
                throw new java.lang.AssertionError(
                        "ITunesU.hmacSH256(): UTF-8 encoding not supported!");
            }
            byte[] hash = sha256.digest();
            sha256.reset();

            // Calculate the outer hash, defined in RFC 2104 as
            // SHA-256(KEY ^ OPAD + INNER_HASH), where OPAD is 64 bytes of 0x5c.
            for (int i = 0; i < 64; ++i) block[i] ^= (0x36 ^ 0x5c);
            sha256.update(block);
            sha256.update(hash);
            hash = sha256.digest();

            // The outer hash is the message signature...
            // convert its bytes to hexadecimals.
            char[] hexadecimals = new char[hash.length * 2];
            for (int i = 0; i < hash.length; ++i) {
                for (int j = 0; j < 2; ++j) {
                    int value = (hash[i] >> (4 - 4 * j)) & 0xf;
                    char base = (value < 10) ? ('0') : ('a' - 10);
                    hexadecimals[i * 2 + j] = (char)(base + value);
                }
            }

            // Return a hexadecimal string representation of the message signature.
            return new String(hexadecimals);
    }
}
/

Next you create a standard PL/SQL function that uses the java source as in the following code listing.

Code listing 2, PL/SQL hmacSHA256 function

create or replace function hmacSHA256(
    p_string    in varchar2,
    p_key       in varchar2) return varchar2
as language java
    name 'hmacSHA256.encrypt(
          java.lang.String,
          java.lang.String) return String';
/

Finally, you create a function that takes in the string to sign, your AWS Secret Key, and then creates the HMAC_SHA256 signature.

Code listing 3, amazon_signature function

create or replace function amazon_signature(
    p_string    in varchar2,
    p_key       in varchar2) return varchar2
as
    encrypted_raw   raw(2000);
    output_string   varchar2(32000);
begin

    encrypted_raw := hmacSHA256(p_string,p_key);
    output_string := UTL_I18N.RAW_TO_CHAR (utl_encode.base64_encode(encrypted_raw), 'AL32UTF8');

    return output_string;

end amazon_signature;
/
show errors

Now let's test all this out. Amazon provides a nice self contained Web page utility that uses Javascript to help you create signed requests. I will use 123456 as my AWS Access Key ID and abcdefg as my AWS Secret Access Key. Plug in the following URL in the Unsigned URL text area:

http://ecs.amazonaws.com/onca/xml?Timestamp=2009-07-24T06%3A35%3A14-08%3A00&Service=AWSECommerceService&Version=2009-03-31&Operation=ItemSearch&ResponseGroup=ItemAttributes,Images&Keywords=liberty+and+tryanny&SearchIndex=Books&AWSAccessKeyId=123456&AssociateTag=apex30-20

This will come up with the following string to sign:

GET

ecs.amazonaws.com

/onca/xml

AWSAccessKeyId=123456&AssociateTag=apex30-20&Keywords=liberty%20and%20tryanny&Operation=ItemSearch&ResponseGroup=ItemAttributes%2CImages&SearchIndex=Books&Service=AWSECommerceService&Timestamp=2009-07-24T06%3A35%3A14-08%3A00&Version=2009-03-31

And will produce the following Signature parameter:

uMJX4cN6EXHyTUrC03Ae9hAcGdTnAHI0KqtovwQUHP8%3D

If I take the same string to sign and AWS Secret Key of abcdefg and run it through amazon_signature, I get the following results:

Code listing 4, result of amazon_signature function

jason@APX11W> declare
  2      l_token varchar2(4000);
  3  begin
  4      l_token := amazon_signature('GET
  5  ecs.amazonaws.com
  6  /onca/xml
  7  AWSAccessKeyId=123456&AssociateTag=apex30-20&Keywords=liberty%20and%20tryanny&Operation=ItemSearch&ResponseGroup=It
emAttributes%2CImages&SearchIndex=Books&Service=AWSECommerceService&Timestamp=2009-07-24T06%3A35%3A14-08%3A00&Version=20
09-03-31','abcdefg');
  8      dbms_output.put_line(l_token);
  9  end;
 10  /
uMJX4cN6EXHyTUrC03Ae9hAcGdTnAHI0KqtovwQUHP8=

PL/SQL procedure successfully completed.

The only difference in the result is the very end. Prior to using the signature, it must be URL encoded. If you run the string uMJX4cN6EXHyTUrC03Ae9hAcGdTnAHI0KqtovwQUHP8= through wwv_flow_utilities.url_encode2, you will get uMJX4cN6EXHyTUrC03Ae9hAcGdTnAHI0KqtovwQUHP8%3D.

New Web Services Integration White Paper and Sample Application on OTN

Late last year I promised a white paper and sample application on integrating Application Express and BI Publisher through Web services. Well five months later (hey, that's less than 1/2 a year), it is now available on the Application Express Web Services Integration page on OTN. The sample application allows you to login to a BI Publisher instance as a BI Publisher defined user, browse the folders and reports available to that user, and provides the ability to download the report all from within the Application Express application. The white paper describes in detail how to build the application using PL/SQL and the flex_ws_api package. I hope you find it useful.

20 cents an hour! Who's got that kind of dough?

In these trying economic times, I thought it would be prudent to try to save some money on the cost of my Cloud experiment. (Although a colleague of mine pointed out that my actions were "anti-stimulative.") I wanted to downsize my machine to the 10 cent per hour machine described as:

Small Instance (Default) 1.7 GB of memory, 1 EC2 Compute Unit (1 virtual core with 1 EC2 Compute Unit), 160 GB of instance storage, 32-bit platform

I didn't want to have to start from scratch; I wanted to keep the database and all the applications that I installed intact. But how could I do that? I knew full well that once I shut my instance down everything was gone. This is where the beauty of Amazon's Elastic Block Storage (EBS) comes in. It allows you to create persistent volumes that you can then attach, format, and mount on an EC2 instance. If you shutdown the EC2 instance for any reason your EBS volume persists and you can then mount it on another EC2 instance.

EBS has its own charges and I estimated it would cost me about $18 per month for my 10GB volume. But I am saving about $72 month by decreasing my hourly costs by 10 cents, so my net savings will be more than $50 a month. Not bad.

So the idea is that I create an EBS volume, move all the database files necessary to run the database to that volume, and then start a database using those data files on the cheaper machine. With some help from my friends at the Oracle Cloud Computing Center, I was able to do just that and I have detailed the steps below.

First I needed to create my EBS volume. This is very simple using Elasticfox and detailed in the Elasticfox Getting Started Guide. Click on the Volumes and Snapshots tab and then the create icon. Enter the size in GB (I chose 10) and also choose an availability zone. This is important since you can only attach EBS volumes in the same availability zone as your EC2 instance. My instance was in the us-east-1b zone so I chose that.

Now that I have the EBS volume I attached it to my instance by going to the Instances tab in Elasticfox, right clicking on my instance, and choose Attach an EBS volume. I gave it a device name of /dev/sdf1. Next I need to format the volume as a file system before I can use it so I issue (as root):

$ mkfs.ext3 /dev/sdf1

Figure 1, Create an EBS volume

Mount it to a temporary place, like:

$ mount /dev/sdf1 /mnt

My instance had a mount point of /u02 which contained all of the file necessary for my database including the datafiles, control files, redo log files, etc.

$ df -m

Filesystem 1M-blocks Used Available Use% Mounted on
/dev/sda1 9647 7116 2042 78% /
none 871 539 332 62% /dev/shm
/dev/sda2 342668 3000 322262 1% /u02

I copied all those files to the temporary mount point on /mnt. First I shutdown the database or the files will be unusable.

$ su - oracle
$ sqlplus / as sysdba
SQL> shutdown immediate
SQL> exit
$ cp /u02/* /mnt/
$ exit #this will return me to the root user

Finally I un-mounted the current /u02 mount point and remount /dev/sdf1 to /u02. I only did this to prove I can start the database using the EBS volume with these data files. If I can't start the database using this EBS volume mounted as /u02, I don't want to proceed until I can.

$ umount /dev/sda2
$ umount /dev/sdf1
$ mount /dev/sdf1 /u02
$ su - oracle
$ sqlplus / as sysdba
SQL> startup

In my case, the database started up so I did a shutdown immediate. I un-mounted /dev/sdf1 and then detached the EBS volume from this instance. I kept this instance available until I successfully started the database on the new smaller instance.

SQL> shutdown immediate
SQL> exit
$ exit
$ umount /dev/sdf1

With the database shutdown and the EBS volume detached, I created a snapshot of the volume. The snapshot is a point-in-time backup of the EBS volume and it is stored using Amazon's S3. Once I have a snapshot, I can create new EBS volumes based on that snapshot. This gives me the flexibility of shutting down all instances and deleting my EBS volume, and then I only pay S3 storage charges. I can create a new EBS volume based on this snapshot sometime in the future, start an EC2 instance, attach the EBS volume and I am good to go.

Figure 2, Create a snapshot of the EBS volume

Next, I started a new instance using the same 11gR1 32 bit AMI on a smaller instance with Elasticfox. It was important that I chose the same availability zone as my EBS volume, us-east-1b.

Figure 3, Launch the smaller EC2 instance

I connected to the instance using Putty and chose "No" when asked if I wanted to create a database at this time. This gave me an instance with the Oracle software installed in an Oracle home, but without a database. Now for the magic. I created a /u02 mount point under /, attached the EBS volume to this instance as /dev/sdf1, and then mounted it to /u02. To start the database on this instance I also needed to set my ORACLE_SID environment variable and create symlinks in $ORACLE_HOME/dbs to the spfile and password file in the admin directory on /u02.

$ mkdir /u02
$ mount /dev/sdf1 /u02
$ su – oracle
$ export ORACLE_SID=orcl
$ cd $ORACLE_HOME/dbs
$ ln -s /u02/admin/orcl/dbs/spfileorcl.ora spfileorcl.ora
$ ln -s /u02/admin/orcl/dbs/orapworcl orapworcl
$ sqlplus / as sysdba
SQL> startup

And my database started on the new 10 cent/hour machine!

Test Drive Oracle Application Express 3.2 for 60 cents (USD)

Would you like to kick the tires of Oracle Application Express 3.2 on your own instance but do not have the spare hardware? Do you have 60 cents (USD)? Read on.

Amazon has a service called the Elastic Compute Cloud (EC2) where you can fire up virtual machines in the cloud on a whim. They have partnered with other software vendors to provide images with pre-configured software. Oracle is one of those vendors and you can read about the offerings at the Oracle Cloud Computing Center on OTN. Oracle has an image which contains Enterprise Linux and Oracle Database 11g R1.

The purpose of this post is to describe how I started an 11g R1 instance in the cloud, upgraded it to Application Express 3.2, and then completed the "Converting Your Oracle Forms Applications to Application Express 3.2" and "Utilizing Improved Security Enhancements in Application Express 3.2" Oracle By Example tutorials (OBEs). If you can spare 60 cents you may wish to try this yourself at home.

First, you need to get your AWS account in order. Sign up for AWS at the previous link and then sign up for EC2. Review the EC2 pricing and then establish a payment method. If you already are a customer of Amazon.com you can choose one of the credit cards you have on file or establish a new one. Unfortunately, there is no infrastructure in place for you to insert two quarters and a dime for this test drive.

Now you are going to need to get some software to startup an instance, manage it, and transfer files to it. Get the Elasticfox Firefox plug-in and review the Getting Started Guide. The Getting Started Guide will give detailed instructions on associating your AWS EC2 account with Elasticfox and also creating a KeyPair that you can use to identify yourself when connecting to the machine. You need to download Putty & PuttyGen to connect to your instance via SSH. You should download WinSCP to transfer files to your instance. Finally you need to download Application Express 3.2.

The keypair that you created when reviewing the Elasticfox Getting Started Guide needs to be converted to a format that can be used by Putty and WinSCP. You can use PuttyGen to create this key. Simply start PuttyGen, click load and browse to find the .pem keypair file you created with Elasticfox, then click Save private key to save it as a .ppk file.

Figure 1, Creating a .ppk from your .pem with PuttyGen.

You are ready to view Deploying Oracle Database in the Cloud viewlet that is available on the Oracle Cloud Computing Center page on OTN. The viewlet will show you how to find and start image ami-cecb2fa7 which is 32bit Enterprise Linux with 11g. It will take you through creating and configuring the database. I chose to start a c1.medium instance type which costs 20 cents/hr and is described as:

High-CPU Medium Instance 1.7 GB of memory, 5 EC2 Compute Units (2 virtual cores with 2.5 EC2 Compute Units each), 350 GB of instance storage, 32-bit platform

Figure 2, Instance Details

You manage the instance by connecting via SSH and you can use Putty as the viewlet demonstrated. Before you can connect to the machine on port 22 from your computer you will have to specifically allow it from your IP address. In Elasticfox first identify the Security Group used to start the instance. It is most likely called "default" unless you added another Security Group. Click the Security Group tab, highlight default and then click the green checkmark under the Group Permissions pane to add a permission. You can either allow a specific host or a network range. Allow your host to connect on port 22 and then create another permission to allow anyone to connect on port 8080 wich is the default port that EPG will be listening for HTTP requests. You choose Network and allow 0.0.0.0/0.

Figure 3, Creating a Security Group

Before logging in to Oracle Application Express on this instance, I had to change the password for the Application Express ADMIN account by following these steps (which changed the password to oracle):

su - oracle
sqlplus / as sysdba
SQL> @?/apex/apxxepwd oracle

Login to Oracle Application Express and create a workspace.

Figure 4, Create a Workspace

Now for the fun part. Start WinSCP and connect to your instance and copy apex_3.2.zip there.

Figure 5, Copy apex_3.2.zip to your instance

Return to your Putty terminal session connected to your instance and install Oracle Application Express 3.2:

su - oracle
unzip apex_3.2.zip
cd apex
sqlplus / as sysdba
SQL>@apexins SYSAUX SYSAUX TEMP /i/
SQL>@apxldimg /home/oracle

Follow Joel's blog post on making Application Express run faster. You are ready to complete the Converting Your Oracle Forms Applications to Application Express 3.2 and Utilizing Improved Security Enhancements in Application Express 3.2 Oracle by Example (OBE) tutorials. An easy way to get the files for the Forms OBE to your machine is to run the following command in your terminal session:

wget http://www.oracle.com/technology/obe/apex32/files/forms_conversion.zip

Figure 6, Application Express 3.2 in the Cloud!

So there you have it. Test drive your own instance of Oracle Application Express 3.2 for around 60 cents (USD). For a limited time (until my manger finds out), you can try out my cloud instance using the following application linked below. I have configured my cloud instance to use the APEX Listener, which is a Java based HTTP listener that should release with the next version of Oracle Application Express.

RMOUG, Kaleidoscope 2009, Slight Change to flex_ws_api

I know that I am horribly overdue for a post. I was out at the RMOUG Training Days last week and presented on Integrating Web Services and Application Express. The presentation should be linked from the referenced page soon. Unfortunately I only had 60 minutes to present 90 minutes worth of material. I presented on integrating Web services created from JDeveloper, XDB Native Web Services, BPEL Web services and finally integration with Oracle Content Server (formerly Stellent). The presentation was supposed to end with a discussion of debugging techniques using a variety of tools such as SOAPUI, ProxyTrace and XMLSpy.

Luckily, I will be presenting at the ODTUG Kaleidoscope 2009 in Monterey California in June. I have 90 minutes so I will be able to get to the debugging topics. Please join me if you can swing it.

Lastly, I have made a couple of minor tweeks to flex_ws_api. The changes allow for a more graceful response from parse_xml and parse_xml_clob if the node is not found using the supplied Xpath expression. It now simply returns null.