Just a quick post to point out that the Alexandria PL/SQL Library has been updated to v1.7, including updates to the Amazon S3 package and a new package for generating iCalendar objects – more details on Morten’s blog.
In Perth this morning, at a breakfast courtesy of the local AUSOUG, I spoke about using the Alexandria PL/SQL Library to automate various tasks with Amazon’s Simple Storage (S3) service. If you haven’t used Amazon Web Services before, or haven’t looked at Alexandria yet, and you enjoy discovering new capabilities with PL/SQL I think you’ll find this interesting.
The powerpoint slides and demo script are now available on my Presentations page.
In late October I’ll be speaking at the 20:20 Foresight Perth Conference – more details later.
Ever since I downloaded the Alexandria PL/SQL library, I haven’t been able to put it down. Just recently I decided I wanted to serve up a whole lot of media files directly from Amazon’s S3 simple storage service, instead of serving them from within my EC2 (elastic compute) instance. They were just wasting my linux server’s time responding to http requests.
So, I quickly wrote the following code to transfer them:
DECLARE l_blob BLOB; BEGIN /* initialise my AWS session */ ALEX.amazon_aws_auth_pkg.init ( 'yyy-my-aws-id-yyy' , 'xxx-not-telling-xxx' , p_gmt_offset => -8); FOR rec IN ( SELECT id, filename, mime_type, location FROM myfiles WHERE location = 'http://myserver/media/' ) LOOP /* read the file from its current location */ l_blob := ALEX.http_util_pkg.get_blob_from_url (rec.location || rec.filename); IF DBMS_LOB.getLength(l_blob) > 0 THEN /* upload the file to Amazon S3 */ ALEX.amazon_aws_s3_pkg.new_object ( 'mybucket' , rec.filename , l_blob , rec.mime_type , ALEX.amazon_aws_s3_pkg.g_acl_public_read); UPDATE myfiles SET location = 'https://mybucket.s3-ap-southeast-1.amazonaws.com/' WHERE id = rec.id; COMMIT; END IF; END LOOP; END;
After a short while, all the files had been copied across to my bucket on S3, and my table updated so that my web site now points people’s browsers to the new location for those files.
Of course, I could have used UTL_FILE to read the files from disk, but then I’d have to first create a directory, and write a loop to read the file in chunks into the BLOB. Why bother with all that when I can just call http_util_pkg.get_blog_from_url and get it all in one go?
That’s the trouble with powerful utilities like Alexandria: they’re too easy to use, make tasks like this trivial, and you start finding all sorts of uses for them. All of a sudden, Alexandria is your hammer, and the world is full of nails.
See also: this quick intro to using Alexandria’s API for Amazon S3.